Google Apps Script executing out of order

Question

The company I work at has been making and printing daily reports as .ods files. Historically it's been a painstaking process, so I'm trying to automate pieces of it with Google Sheets and Apps Script. The bookkeepers however still expect the identical .ods file every day.

I have a function that saves a copy of my Google sheet as .ods. It works fine for the most part, but there are some functions that don't quite work correctly so I need to strip them out and replace them with the values only before I save. I have code that looks like this:

function saveAsOds(){
  let rangeWithFormulas = ss.getRangeByName("myRange");
  rangeWithFormulas.copyTo(rangWithFormulas, {contentsOnly:true});

  var urlExport = "https://docs.google.com/spreadsheets/d/" + ssId + "/export?format=ods&gid=" + sheetId;
  var filename = Utilities.formatDate(date,"GMT-7","M-dd-yy")

  // make sure copy function is finished before saving
  Utilities.sleep(10000);
  let blob = getFileAsBlob(urlExport);

  blob.setName(filename)
  let file = DriveApp.createFile(blob);
  file.moveTo(targetFolder);

}

For some reason, this only works part of the time. I can confirm in my Google sheet that the copy function worked correctly and the range contains only values and no formulas, but about half the time the .ods file still ends up with formulas that don't work in it. I added the 10 second sleep, thinking that the blob was somehow being generated before the copy was done, but it doesn't seem to help. Any idea what's going on?

In your script, how about replacing `Utilities.sleep(10000);` with `SpreadsheetApp.flush()` and test it again? — Tanaike, Jun 18 '23 at 00:04
That worked! Don't know how I never knew about that function. — Zach, Jun 18 '23 at 22:56
Thank you for replying. From `That worked! Don't know how I never knew about that function.`, I understood that your issue was resolved. In this case, can you post it as an answer? By this, it will be useful for other users who have the same issue. Of course, I can do it, too. — Tanaike, Jun 19 '23 at 00:31

score 1 · Answer 1 · answered Jun 19 '23 at 06:21

The answer, as Tanaike pointed out, is SpreadsheetApp.flush(). This function applies all pending changes to the spreadsheet and solved my problem. It seems spreadsheet operations are sometimes bundled together for performance reasons, and that was the hurdle I couldn't get over before.

score 1 · Answer 2 · edited Jun 26 '23 at 19:44

I'm writing this answer as a community wiki since the solution was provided by Tanaike and also I found this helpful case about some use cases for SpreadsheetApp.flush()

A programmer will use flush() when they want to ensure that the previous code's output and/or effects are written to the spreadsheet before continuing. If you do not flush(), then the code may be automatically "optimized" by using some built-in caching and bundling of operations. In general, you do not need to use flush() until you specifically DO need to... if that makes sense.

flush()

Applies all pending Spreadsheet changes. Spreadsheet operations are sometimes bundled together to improve performance, such as when doing multiple calls to Range.getValue(). However, sometimes you may want to make sure that all pending changes are made right away, for instance to show users data as a script is executing.

References:

flush()

Google Apps Script executing out of order

2 Answers2

References: