copyTo is failing to copy data

Question

I have a pretty simple problem that is for some reason killing me. I have the following script which should just autofill a formula down column O, then copy the values to column P (excuse my amateur coding):

function processData() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();
  var lastWorkDate = dateCheck();
  sheet.getRange('O1').setValue("=index('"+lastWorkDate+"'!O:O,match(B1,'"+lastWorkDate+"'!B:B,0))");
  sheet.getRange('O1').autoFillToNeighbor(SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
  Utilities.sleep(1000);
  freeze();
};

function freeze() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();
  sheet.getRange('P1').activate();
  sheet.getRange('O:O').copyTo(sheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
};

However, the copying just isn't doing anything. The script will successfully autofill O:O, but will fail to copy the values to P:P.

Manually running freeze() itself after running processData() (and thus already having O:O filled) works fine, so I figured it might be running too quickly and added the Utilities.sleep(1000), however, that's not helping either.

Answer 1

In your processData() function, use the SpreadsheetApp.flush(); function just before calling freeze();. This will force any pending changes (such as updates to a large number of cells) to finish right away. See the API documentation for more information: SpreadsheetApp.flush()