Number of Requests for Sheets API using Google Apps Script

submit.md

Number of Requests for Sheets API using Google Apps Script

This is a report for checking the number of requests for Sheets API. I had contact about the quota for Sheets API. So, in order to explain this, I used the following simple sample scripts.

Sample 1

This sample puts a value of "sample" to a cell "A1" using the batchUpdate method. This request body includes one request. When this script is run, one API quota is used.

function sample1() {
  const spreadsheetId = "###";
  const sheetId = 0;

  const requests = [{
    "updateCells": {
      "rows": [
        {
          "values": [
            {
              "userEnteredValue": {
                "stringValue": "sample"
              }
            }
          ]
        }
      ],
      "range": {
        "sheetId",
        "startRowIndex": 0,
        "endRowIndex": 1,
        "startColumnIndex": 0,
        "endColumnIndex": 1
      },
      "fields": "userEnteredValue.stringValue"
    }
  }];
  Sheets.Spreadsheets.batchUpdate({ requests }, spreadsheetId);
}

Sample 2

This sample puts 1000 values like "sample#" to the cells of "B1:B1000" using the batchUpdate method. This request body includes 1000 requests. When this script is run, one API quota is used.

function sample2() {
  const spreadsheetId = "###";
  const sheetId = 0;
  const max = 1000;

  const requests = [...Array(max)].map((_, i) => ({
    "updateCells": {
      "rows": [
        {
          "values": [
            {
              "userEnteredValue": {
                "stringValue": "sample" + (i + 1)
              }
            }
          ]
        }
      ],
      "range": {
        "sheetId": 0,
        "startRowIndex": i,
        "endRowIndex": i + 1,
        "startColumnIndex": 1,
        "endColumnIndex": 2
      },
      "fields": "userEnteredValue.stringValue"
    }
  }));
  Sheets.Spreadsheets.batchUpdate({ requests }, spreadsheetId);
}

Result

From the above results, for the batchUpdate method, when 1 request is included in the request body, one quota is used. And, even when 1000 requests are included in the request body, one quota is used.

When the request of Sheets API is run in a loop, an error like quota exceeded occurs. In that case, if your situation can be populated in the batchUpdate methods (values.batchUpdate, values.batchGet, batchUpdate), when you request those requests in one batchUpdate method, those requests can be done by one API call. By this, such an error can be avoided.

As an important point, for example, when 100 new documents are created using Drive API with the batch request, this can be done by one API call. So, the used API is only one. But, the quota (250 / day) for creating files is reduced by 100. Ref Please be careful this.

Reference

• Method: spreadsheets.batchUpdate