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.
processData()
, afterfreeze();
. Second, maybe try usingSpreadsheetApp.flush();
before callingfreeze();
, as that will ensure all pending changes to the spreadsheet are made. – Mark Aug 28 '18 at 16:21SpreadsheetApp.flush();
did the trick and even allowed me to remove thesleep
. Thanks! If you can reply as an answer I can credit you. – Tock Aug 28 '18 at 16:42