CODEWITHSUNDEEP
google-sheets-api
Yutaka Omido
Yutaka Omido

Reputation: 11

Are there any limitations for number of cells that can be updated/fetched at once using google spreadsheet API?

Are there any limitations for number of cells that can be updated/fetched at once using google spreadsheet API?

I can find the limitation of number of API calls in the page below, But I cannot find number of cells limitation on updating/fetching. https://developers.google.com/sheets/api/limits

If there are some documentation about that, I would appreciate it if you could tell me.

Upvotes: 1

Views: 1042

Answers (1)

Tanaike
Tanaike

Reputation: 201573

About Are there any limitations for a number of cells that can be updated/fetched at once using google spreadsheet API?, in the current stage, I cannot confirm the clear limitation value. But, I thought that the following information might be useful for understanding your question.

Experiment:

In the current specification, 10,000,000 cells can be used in a Google Spreadsheet. Using this, I have tested to embed 10,000,000 cells as all different values using a sheet with 10 columns x 1,000,000 rows. Ref The result is as follows.

About retrieving values from cells Ref

When the values are retrieved from 10,000,000 cells using one call of Sheets API, it was found that this cannot be done. As the result, the values retrieved from 200,000 cells can be achieved by one API call. But when the number of cells is increased by more than 200,000 cells, an error like response too large sometimes occurred. But, I'm not sure whether 200,000 cells is the maximum limitation number. When I tested more than 200,000 cells, there was in the case of no error. But, in this case, there was the case of an error. When the value of a cell is long, the limitation has been changed from 200,000 to smaller. So, I guessed that this limitation might depend on the size of all response data from Sheets API.

About putting values to cells Ref

When 10,000,000 cells are embedded by all different values using one call of Sheets API, it was found that this cannot be done. As the result, 200,000 cells can be updated by one API call. But when the number of cells is increased by more than 200,000 cells, an error like API call to sheets.spreadsheets.values.update failed with error: Empty response sometimes occurred. But, I'm not sure whether 200,000 cells is the maximum limitation number. When I tested 300,000 cells, there was in the case of no error. But, in this case, there was the case of an error. So, I guessed that this error of API call to sheets.spreadsheets.values.update failed with error: Empty response might not be the maximum limitation number for processing cells.

Note:

  • This is the result on 25 Apr 2022 that I investigated. So, by the future update on the Google side, I think that this situation might be changed. I would like to believe that the future update brings better change from the current situation.

Reference:

Upvotes: 2

Related Questions