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

Question

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).

score 1 · Accepted Answer · answered Jun 15 '16 at 14:08

1

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.

answered Jun 15 '16 at 14:08

Thank You @soup, In above case we have fixed columns. Suppose in sheet already have 14 columns and total number of columns are 26. Now we are adding 6 columns. So how to identify empty columns and delete it. Please help us if you have solutions. – Gaurav Ashara Jun 16 '16 at 06:32
I don't understand what you mean by "already have 14 columns and total number of columns are 26". Is it 14 or 26? Anyway, it seems the only way to determine the last column with data in v4 is to request all values from the sheet and process them, looking for nonempty ones. See http://stackoverflow.com/a/37687676 – Jun 16 '16 at 06:37
I am talking about 14 filled with data and reaming 12 are empty.So How can we identify it empty one? Oho we have 80,000+ rows process it and find last Columns will take to to much time. Is there any alternative ? Like suppose we have add rows with columns without empty rows/columns. – Gaurav Ashara Jun 16 '16 at 07:47

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

1 Answers1