CODEWITHSUNDEEP
google-sheets-api
Ranjani
Ranjani

Reputation: 1025

Getting "This action would increase the number of cells in the worksheet above the limit of 2000000 cells" when calling Sheet API

I'm using Google Sheets V4 append API to insert data into a newly created sheet. I am inserting data with 1000 rows and 1001 columns i.e 1001000 cells. 

String url = https://sheets.googleapis.com/v4/spreadsheets/<spreadSheetId>/values/<workSheetName>!A1:append?access_token=<accessToken>&valueInputOption=USER_ENTERED&insertDataOption=INSERT_ROWS
    //CREATING THE REQUEST BODY
reqBody.put("range", <workSheetName>+"!A1");
reqBody.put("majorDimension", "ROWS");
reqBody.put("values", <values>);

I am getting 400 Bad Request with the message :

This action would increase the number of cells in the worksheet above the limit of 2000000 cells

I know that Google has 2 million cells per sheet limit, but I'm unable to find out why I'm getting this exception especially since I have not exceeded the limit.

Any help on why this error may occur?

Upvotes: 8

Views: 3779

Answers (1)

William Turrell
William Turrell

Reputation: 3326

Google seemingly counts cells that are are already there, even if they're blank, even in a brand new spreadsheets. The solution is to wipe those columns first. Manually you'd do it by selecting a column, right clicking, and choosing Delete.

Using the API, you need deleteDimension - here's a PHP example:

        new Google_Service_Sheets_Request([
          "deleteDimension" => [
            "range" => [
              "sheetId" => $sheetId,
              "dimension" => "COLUMNS",
              "startIndex" => 1,
              "endIndex" => 26
              ],
          ],
        ]);

Notes:

  • Google has unusual syntax for indicating ranges - the first column does start at 0, but the end column needs to be the one after you want, so 0-1 is column A, not A and B.

  • In this example, we delete all but the first column - it won't let you delete the entire sheet, e.g. if you were to do 0-26 (i.e. all of them) you'd get:

Invalid requests[1].deleteDimension: You can't delete all the columns on the sheet.

  • don't worry, although you now only have a single column, as many new columns as you need will be added automatically when you add data

  • The workbook limit I get in Dec 2019 is 5,000,000, not 2,000,000:

Invalid requests[2].pasteData: This action would increase the number of cells in the workbook above the limit of 5000000 cells.

Upvotes: 0

Related Questions