8

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?

kae_screechae
  • 169
  • 12
Ranjani
  • 1,015
  • 1
  • 9
  • 15

1 Answers1

0

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.

William Turrell
  • 3,227
  • 7
  • 39
  • 57