1

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.

Improve this question
  • Hi there, first of all it seems like you might have copied something wrong from your original code? Look at the last line of processData(), after freeze();. Second, maybe try using SpreadsheetApp.flush(); before calling freeze();, as that will ensure all pending changes to the spreadsheet are made. – Mark Aug 28 '18 at 16:21
  • 1
    @Mark you're right, that was a piece of code on the following line I had commented out. Have no idea how that got in there. Anyways, SpreadsheetApp.flush(); did the trick and even allowed me to remove the sleep. Thanks! If you can reply as an answer I can credit you. – Tock Aug 28 '18 at 16:42
  • Okay, glad to hear it worked for you, @Tock! – Mark Aug 28 '18 at 16:47
2

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()

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.