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)
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?
To check for nativeadd-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.
, 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
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 UpsertRow action is intended to work like this:
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.
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 UpsertRow actions.
~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”}~
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.