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

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?

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.

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?