Why Do We Use Spreadsheetapp.Flush();

Why do we use 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.

First, ye olde official documentation:

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.

How about an explainlikeimfive analogy: Let's say you're counting apples on a tree with 100 apples.

You could count and record each apple individually, like so:

1, 2, 3, 4, 5, 6... etc.

This is like doing a flush() within a loop, since you are literally writing after each count operation. You will end up writing to your paper 100 times, and let's assume it takes longer to write with your hand than it does to count with your eyes.

An "optimized" process (in this case) would be to use your memory/buffer and count 5 apples before writing a number down, so you'd write

5, 10, 15, 20... etc.

Now you will end up writing to your paper 20 times (an 80% reduction), and despite having to count the same number of apples, you've reduced the number of writes you have to do, so you'll see a drastic performance benefit by way of reduced runtime.

This translates roughly to how Apps Script operations work. As in all computing, the in-memory operations are the quickest to execute, and the read/write (aka input/output) operations are the slowest (check your Execution Transcript for further proof). That's why you should only use flush() when you specifically need to write your data to the spreadsheet at a particular point in your code's execution.

Hope this helps.

Set SpreadsheetApp.flush(); and Utilities.sleep(5000); lines location in script (Google App Script)

You should add

SpreadsheetApp.flush();
Utilities.sleep(milliseconds); // milliseconds should be a number big enough to allow the spreasheet finish to recalculate

on each line above each copyTo

NOTE: Instead of using Utilities.sleep(milliseconds) you could poll the spreadsheet to check if the formula results were added to it. See How to pause App Scripts until spreadsheet finishes calculation

---

Why?

SpreadsheetApp.flush() forces Google Apps Script to apply the changes made so far. This should be done before coping the range that is intended to hold the result of the formula otherwise it will be empty. Utilities.sleep(milliseconds) is necessary (or somethin equivalent) to wait for the spreasheet recalculation finish before copying the range, otherwise the only value to copy might be Loading...

Is there a SpreadsheetApp.flush() option for the Google Sheets API?

I was curious about that too, so I reviewed the documentation, and it seems that there is no flush() method for Sheets API.

However, I found a workaround that might help so you can make sure that you are working on the latest version of the sheet. I took inspiration from the Writecontrol parameter in Google Docs API.

"Determines the revision of the document to write to and how the
request should behave if that revision is not the current revision of
the document."

So what I was thinking is to use the Drive API Revisions by using the Revisions: list method and get the latest version of the Sheet. After that, use Revision: get to retrieve the latest version of the sheet to work on it.

You can also submit a missing feature, Google might add this method to the Sheets API later on.

Reference:

• Flush() method.
• Writecontrol Google Docs API.
• Changes and revisions overview.
• Revisions: list.
• Revisions: get.

Multiple SpreadsheetApp.flush() within a sequence of function runs

Unless you provide the exact code (as it is possible that it can still be optimized), what I can say about flush is that it should be fine to have multiple calls except that it can possibly increase your runtime compared to less or no flush at all.

Basically, it makes sure the previous pending commands are all executed before proceeding. Without flush, it tries to optimize your operations by executing them by batch.

These optimizations have benefits and drawbacks.

Main benefit is that it would improve the performance of the script as it reduces your runtime.

Main drawback is that, it might not execute your commands the way you want it to be. (I'm not really sure how it is being optimized so anything can happen here)

So aside from possible increase in runtime, you should be fine doing multiple flush calls.

Reference:

• Why do we use SpreadsheetApp.flush();?

Adding lockservice and SpreadsheetApp.flush to a script (Google App Script / Google Sheets)

There is a misundertanging of the referred answer, the suggested code is not intended to be used several times in a function, it's purpose is to avoid that certain part of the code be excecuted when there is another execution of the same script still running.

---

The following code reduce the number of calls to Google Apps Script methods include the use of Spreadsheet.flush() and a do... while statement to wait for the spreadsheet recalculation fishish.

NOTE: I didnt' test this as the URL was not provided.

function myFunction() {
  var ss = SpreadsheetApp.getActive();

  // Add formulas

  ss.getRange('Monster!A1').setFormula('=IMPORTXML(Gerais!R1,"//*[@class=\'header-label\']"');
  ss.getRange('Monster!B1').setFormula('=IMPORTXML(Gerais!R2,"//*[@class=\'header-label\']"');
  ss.getRange('Monster!C1').setFormula('=IMPORTXML(Gerais!R3,"//*[@class=\'header-label\']"');

  // Force apply the above changes 

  SpreadsheetApp.flush();

  // Give time for spreadsheet recalculation
  var start = Date.now();
  var limit = 10000;
  do{
    Utilities.sleep(1000);
    var values = ss.getRange('Monster!A2:C2').getValues()[0];
  }
  while( values.every(value => value === '') || Date.now() - start < limit);

  // Copy / paste the formula results
  ss.getRange('Monster!A1:C').copyTo(ss.getRange('Page 2!A1:A'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

  // Clear A1, B1, C1
  ss.getRange('Monster!A1:C1').clear({contentsOnly: true, skipFilteredRows: true});
}

Exception of 50000 characters at 'SpreadsheetApp.flush()' in Google Sheets?

The culprit may be elsewhere in your code. Chances are that there is an attempt to put more than 50,000 characters in some cell.

Spreadsheet writes are lazy, and the error may only get shown when the script terminates, or at flush(), as the case seems to be here. To debug, add a flush() after every Range.setValues() and Range.setValue() call. Use console.log() to find what the values you are writing look like.

What is faster: ScriptDb or SpreadsheetApp?

ScriptDB has been deprecated. Do not use.

---

Related Topics