CODEWITHSUNDEEP
javagoogle-sheetsgoogle-sheets-api
Gaurav Ashara
Gaurav Ashara

Reputation: 463

Append 1 millions rows and specify cell position using Google Sheets API v4

We are using Google Sheets API V4. We are looking to add 1 millions rows as from v4 we have support to write 2 million cells in Spread sheet. So, we try to add 80,000 row with 6 columns. 80,000 * 6 = 480000 Cells but we got following error.

{
  "code": 400,
  "errors": [
    {
      "domain": "global",
      "message": "Invalid requests[0].appendCells: This action would increase the number of cells in the workbook above the limit of 2000000 cells.",
      "reason": "badRequest"
    }
  ],
  "message": "Invalid requests[0].appendCells: This action would increase the number of cells in the workbook above the limit of 2000000 cells.",
  "status": "INVALID_ARGUMENT"
}

We are adding 1000 row each time of loop of 80. After error we check sheets we found that 73000 rows was inserted.

We think that Google Sheets API also calculates empty cells after 6th columns. Suppose we calculate with reaming cells 73000 * 26 (A-Z) = 1898000 and when we try add more and we got error.

Please help us for any suggestion how remove reaming empty cells or any other alternative. We are using following code 

AppendCellsRequest appendCellReq = new AppendCellsRequest();
appendCellReq.setSheetId(ssDetails.getSheets().get(4).getProperties().getSheetId());
appendCellReq.setRows(listRowData);
appendCellReq.setFields("userEnteredValue");

Request request = new Request();
request.setAppendCells(appendCellReq);

List<Request> requests = new ArrayList<>();
requests.add(request);

BatchUpdateSpreadsheetRequest batchRequests = new BatchUpdateSpreadsheetRequest();

batchRequests.setRequests(requests);

service.spreadsheets().batchUpdate(spreadsheetId, batchRequests).execute();

Second thing while appending cells we are not able sapcify columns possition of cells it always start with first columns(A).

Upvotes: 0

Views: 2180

Answers (1)

user3717023
user3717023

Reputation:

Yes, the limit of 2 million cells applies to all cells, blank or not. Since by default a sheet has 26 columns, this means there can be at most 2,000,000/26 = 76923 rows unless the number of columns is reduced.

To delete unwanted columns (those after the 6th), send a POST request to 

https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}:batchUpdate

with request body of the form 

{
  "requests": [
    {
      "deleteDimension": {
        "range": {
          "sheetId": sheetId,
          "dimension": "COLUMNS",
          "startIndex": 7,
          "endIndex": 26
        }
      }
    }
  ]
}

Reference: delete rows or columns.

Upvotes: 1

Related Questions