What are 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.

First, ye olde official documentation:


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.

Leave a Comment