Why am I periodically receiving an error stating that adding to Google Sheets would exceed the cell limit, despite the sheet being far from the alleged limit?

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.

7

Posted thread to Discourse: 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?

8

, where does Pipedream clean up any hidden sheets it creates? We’ve found 1449 hidden sheets and no connected apps should have created them, so we think it’s being created by Pipedream

9

wow, that’s a lot of hidden sheets. It would make sense that they would have been created by Pipedream and left over if the clean up failed. The Google Sheets Upsert Row action is supposed to clean up the hidden sheet it creates by deleting it after it finds a matched row, in the code here or here.

The Upsert Row action is intended to work like this:

  1. Create a hidden sheet with 2 cells
  2. Add a row to the hidden sheet with your key value in A1 and a MATCH formula in A2
  3. Delete the hidden worksheet, and
    a. If there’s a match, update the row
    b. Otherwise, insert a row
    If the API request to delete the hidden sheet in step 3 fails, then the sheet won’t get deleted. Or, if the API request in step 2 fails, step 3 won’t run and the hidden sheet won’t get deleted.

I think the action can be improved so that it attempts to clean up the hidden sheet even when step 2 fails. I can make a pull request in the public pipedream GitHub repository to make that change.

10

Thanks Jacob. In the meantime, I may look at trying to write a custom action to delete all hidden sheets at the end of the run as a fail safe.

11

Good idea! Sorry for the issue, but thanks for sharing it!

12

If a pull is made, can you link/notify me? Just want to keep on top of this as the client isn’t happy with the issue.

13

Yes, I can ping you in this thread.

14

I wrote a custom code step to delete all hidden sheets, like you mentioned, in the meantime. If there are hidden sheets you want to keep, it will delete those too. So it has the potential to interfere with the execution of concurrent Upsert Row actions.

15

Awesome, thank you.

I may just run this ever night when I know the system is quiet. Might help a little.

16

~pd publish google-sheets-delete-hidden-worksheets.js~
~component create api call responded with status: 400, body: {“error”:“Validation failed: Key component code must have a key attribute, Version component code must have a version attribute in semver form”}~

~Any idea how to solve that?~

Solved, add key and verison to the export

17

Ah, I wrote the code as a code step rather than an action, so it didn’t have a key or version. Here it is an action in case you have an other issues.

18

I created a pull request with the improvement to the Upsert Row action I mentioned above: Fix GSheets Upsert Row hidden sheet cleanup by js07 · Pull Request #9831 · PipedreamHQ/pipedream · GitHub
It’s in the Ready for PR review column of the pipedream repo’s Component Backlog project. Someone should have a chance to review it soon.

19

The pull request with the Google Sheets Upsert Row improvement has been merged. You can update a workflow step to use the latest version of the action by clicking the “Update” button.

update-upsert-row-action.png
update-upsert-row-action.png1428×292 38.1 KB

20

Wonderful. Thank you so much.