While we can efficiently read and update multiple ranges in Google Sheets with the Google Sheets API Advanced Service in Google Apps Script, there is often occasions where I want to run multiple batch updates on different Google Sheet files.
As you have probably discovered, updating multiple sheets consecutively can be time-consuming and frustrating. I wanted a way to read and update my Google Sheets asynchronously.
Why?
Let’s say I have a bunch of Google Sheets, one each for my users. Perhaps on a daily cadence, I want to gather aggregate data for their manager. Perhaps my manager wants me to add some new spreadsheet features to the current Google Sheets tools. I need an efficient way of updating and reading these sheets.
So was I successful?
Well, mostly.
I discovered that I could run batch updates on up to around 60 Google Sheets somewhat asynchronously.
How did I do this?
I discovered that while “UrlFetchApp.fetchAll()” does not claim to be asynchronous, benchmark research by Kanshi TANAIKE suggests that “fetchAll()” behaves asynchronously.
In conjunction with the Google Sheets API’s “spreadsheets.values” batchGet and batchUpdate requests, we can do a pretty good job updating up to around 60 Google Sheets very quickly.
Let’s dive in!
Continue reading “Multiple Google Sheets Async Requests in Google Apps Script”