Why does Google Sheets Upsert function sporadically show an error about exceeding cell limit when the sheet doesn't have near 10,000,000 cells?

Bugs
1

This topic was automatically generated from Slack. You can find the original thread here.

We’re occasionally seeing an error in the Google Sheets Upsert function of:

Invalid requests[0].addSheet: This action would increase the number of cells in the workbook above the limit of 10000000 cells.

Stack trace:

at Gaxios._request (/pipedream/dist/code/0ae9614b3f85752ab0911dca5e8a07f1527fb117c14e87ec36f374b5818fa76f/node_modules/.pnpm/gaxios@6.1.1/node_modules/gaxios/build/src/gaxios.js:141:23)
at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
at OAuth2Client.requestAsync (/pipedream/dist/code/0ae9614b3f85752ab0911dca5e8a07f1527fb117c14e87ec36f374b5818fa76f/node_modules/.pnpm/google-auth-library@9.4.1/node_modules/google-auth-library/build/src/auth/oauth2client.js:379:18)
at Object.batchUpdate (file:///pipedream/dist/code/0ae9614b3f85752ab0911dca5e8a07f1527fb117c14e87ec36f374b5818fa76f/code/google_sheets/google_sheets.app.mjs:343:15)
at Object.createWorksheet (file:///pipedream/dist/code/0ae9614b3f85752ab0911dca5e8a07f1527fb117c14e87ec36f374b5818fa76f/code/google_sheets/google_sheets.app.mjs:447:15)
at Object.run (file:///pipedream/dist/code/0ae9614b3f85752ab0911dca5e8a07f1527fb117c14e87ec36f374b5818fa76f/code/google_sheets/actions/upsert-row/upsert-row.mjs:110:28)
at null.executeComponent (/var/task/launch_worker.js:267:22)
at MessagePort.messageHandler (/var/task/launch_worker.js:764:28)

What is very weird about it is:

  • The sheet has no where near 10,000,000 cells
  • It only seems to happen sporadically

Any ideas?

2

It seems like the error you’re encountering with the Google Sheets Upsert function is related to the limits imposed by Google Sheets on the number of cells in a workbook. While you mention that your sheet does not have anywhere near 10,000,000 cells, the error suggests that the action being performed might inadvertently be causing the sheet to exceed this limit. This could potentially happen if the Upsert action is trying to add a new sheet or a large number of rows or columns that collectively push the total cell count over the limit. Since the issue is sporadic, it might be related to specific conditions or data being processed at the time the error occurs. To troubleshoot this further, you could: 1. Review the specific requests being made to the Google Sheets API at the time of the error to see if there’s a pattern or a particular data set that triggers the issue. 2. Check if there are any other operations or scripts running on the same Google Sheets document that might be contributing to the cell count. 3. Consider implementing error handling in your Pipedream workflow to catch this specific error and perform a different action, such as logging the error for further investigation or notifying an administrator. If you continue to experience issues and need more direct support, you can visit Pipedream Support for additional assistance.

3

The Google Sheets Upsert Row action creates a temporary, hidden worksheet with 2 cells in the spreadsheet so it can run a formula to find a duplicate key. It looks like you’re getting an error at that step of the action. Do you possibly run any scripts that could add a lot of cells to a sheet when a worksheet is created?

4

Posted thread to Discourse: Why does Google Sheets Upsert function occasionally error out, claiming cell limit exceeded despite significantly less cells used?

5

, sorry for the delay here. No, we don’t have any external scripts in use, at least to my knowledge. Any way we can check this do you know?

6

To check if you have any external scripts acting on your sheet, I think you would have to check the third-party app connected to your Google account. You can see a list of connected third-party apps and services on https://myaccount.google.com/connections?continue=https://myaccount.google.com/data-and-privacy.
You could also check for unexpected edits in your sheet’s version history. Or check for hidden sheets under View → Show [Name].

To check for native add-ons and Google Apps Scripts in your Spreadsheet, you can open your spreadsheet then click on Extensions → Add-ons or Apps Script in the menu bar.

One add-on that may be useful for finding a sheet that’s using a significant amount of the 10m cell limit is Size My Sheet. It shows the number of cells in the spreadsheet and the sheet with the largest number of cells.