0

I am running a setValues() function for a sheet with massive data count (20,000 * 50) so I finished the data in batch then used the setValues() function to update the sheet at once but the function is updating data in a manner that looks like (x * 50) sequentially, so I wanted to know the limitation of the maximum cells updated at once when using this function.

2 Answers2

1

There is no direct maximum for setValues(), but you might want to consider the following three points:

Of those 3 limits the amount of values you can set with one setValues() call is most likely to be limited by the maximum script execution time.

ziganotschka
  • 25,866
  • 2
  • 16
  • 33
  • yes I am aware of these limits, I just didn't know that setValues() will just keep setting values sequentially until runtime is exceeded instead of setting the whole range at once so I wanted to know the limit for the maximum number of cells that could be set at once –  Jan 25 '22 at 15:46
1

It seems that when the values are put to the sheet using setValues, at first, the cells are kept and the values are put to the cells. When I had tested the maximum number of cells for putting the values using setValues before, 2,625,000 cells could be put. But, when I tested over 2,635,000 cells, an error of Exceeded maximum execution time occurred. So at that time, I had resulted that the maximum cells were between 2,625,000 and 2,635,000. By the way, when 4,000,000 cells are tested, it was found that the 4,000,000 cells are kept in the destination sheet while the values couldn't be put. This is the result in my environment.

So, in the case of 20,000 * 50, I thought that 1,000,000 cells can be put with setValues. But I'm worried that when such data is put with setValues, the process cost was high. For example, after the script is finished and I checked the sheet, I was required to wait to completely put the values. I thought that this might be the reason for your issue.

In this case, when Sheets API instead of Spreadsheet service (SpreadsheetApp) is used for putting the values to the sheet, such an issue could be removed. Ref

From the above situation, in your situation, as a workaround, how about putting the values using Sheets API instead of setValues of Spreadsheet service?

Reference:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • thank you, from the graphs there I figured I can cut my execution time to half of what it is now, but what could be a limitation using the sheets API instead of the spreadsheet service? –  Jan 25 '22 at 15:57
  • @Youssef Ahmed Thank you for replying. About your additional question of `but what could be a limitation using the sheets API instead of the spreadsheet service?`, you can see "Usage limits" at [here](https://developers.google.com/sheets/api/reference/limits). But I thought that your situation might be able to be achieved using one API call. In that case, I think that this limitation will not be affected. – Tanaike Jan 26 '22 at 00:08