Question 189732 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – Google Apps Script executing out of order

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?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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.


  2. 0

    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:

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search