Google sheet API values batchupdate, limit of number of ranges in body

user_1744744 0
Viewed 1333

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/batchUpdate

The documentation here does not say how many ranges in the body can be updated all at once with one call. Has anybody tested limits?

Right now I have code that updates not many cells at once. I'm trying to see if I should change the code to doing all of it at once. But I don't want to spend the time rewriting the code if there is a limit.

Could it handle 1000 different ranges? My ranges are non-continuous between.

google-apps-script google-sheets-api
1 Answers

About your following question,

Could it handle 1000 different ranges?

I think that it's yes. As a simple sample script for testing this, I would like to introduce the following Google Apps Script.

Sample script:

This is the Google Apps Script. So when you test this, please copy and paste the following script the script editor of Google Apps Script. And before you use this, please enable Sheets API at Advanced Google services.

function myFunction() {
  const spreadsheetId = "###"; // Please set the Spreadsheet ID.
  const max = 1000;
  const data = [];
  for (let i = 0; i < max; i++) {
    data.push({range: `Sheet1!A${i + 1}`, values: [[`A${i + 1}`]]});
  }
  Sheets.Spreadsheets.Values.batchUpdate({data: data, valueInputOption: "USER_ENTERED"}, spreadsheetId);
}
  • In this script, the value is put to "Sheet1" using the method of "spreadsheets.values.batchUpdate".

Note:

  • Unfortunately, I couldn't directly confirm the maximum length of data. But, in my test, I could confirm that when mas is 100000, no error occurs, and the script worked.
  • But when you test with const max = 100000, when the sheet has no 100,000 rows, an error occurs. At first, please add rows to 100,000 rows. Please be careful this.

References:

user_71086530
Related