Error appending data with Google Sheets

Questions
,
1

Describe the problem/error/question

I am using n8n cloud with version 0.236.2 for reference. I wanted to switch to using the new version of the Google Sheets node since a few of my workflows were still using an old version (which had its own issues appending rows since it was overwriting data at the top of my sheet instead of appending).

There are other posts here and here that have a similar error but don’t seem to be using an arrayformula so maybe they’re not related.

I’ve created a demo workflow and Google Sheet to reproduce the error. It appears that when I am using an =ARRAYFORMULA(...) what happens is a new row is added first, and then the data is automatically filled on the last row by the array formula on the Google Sheets side. After that, there is an attempt to append data beyond the allowed range which likely causes the following error. I’m not sure if this is the intended effect when using the arrayformula along with n8n appending data or if I should just avoid using the arrayformula.

What is the error message (if any)?

ERROR: Range (Sheet1!7) exceeds grid limits. Max rows: 6, max columns: 26
Http Code 400
NodeApiError: Range (Sheet1!7) exceeds grid limits. Max rows: 6, max columns: 26
    at Object.apiRequest (/usr/local/lib/node_modules/n8n/node_modules/n8n-nodes-base/dist/nodes/Google/Sheet/v2/transport/index.js:49:15)
    at processTicksAndRejections (node:internal/process/task_queues:96:5)
    at GoogleSheet.updateRows (/usr/local/lib/node_modules/n8n/node_modules/n8n-nodes-base/dist/nodes/Google/Sheet/v2/helpers/GoogleSheet.js:127:26)
    at GoogleSheet.appendData (/usr/local/lib/node_modules/n8n/node_modules/n8n-nodes-base/dist/nodes/Google/Sheet/v2/helpers/GoogleSheet.js:114:26)
    at Object.execute (/usr/local/lib/node_modules/n8n/node_modules/n8n-nodes-base/dist/nodes/Google/Sheet/v2/actions/sheet/append.operation.js:212:5)
    at Object.router (/usr/local/lib/node_modules/n8n/node_modules/n8n-nodes-base/dist/nodes/Google/Sheet/v2/actions/router.js:67:38)
    at Workflow.runNode (/usr/local/lib/node_modules/n8n/node_modules/n8n-workflow/dist/Workflow.js:655:19)
    at /usr/local/lib/node_modules/n8n/node_modules/n8n-core/dist/WorkflowExecute.js:596:53

Basically, I had rows 1-5 populated with data (row 1 is the header). You can see an example sheet here. The arrayformula just splits and concatenates parts of a number in column B. When I run my workflow, it actually appends a row but doesn’t insert the data. Which matches up with the error saying there is a Max rows: 6. It seems that the node attempts to reference a single row beyond the 6 max to append the data instead of modifying the row it added.

Please share your workflow

Information on your n8n setup

  • n8n version: 0.236.2
  • Running n8n via (Docker, npm, n8n cloud, desktop app): n8n cloud
3

Hi @michaelnguyen :wave: Sorry you’ve run into this, and thank you so much for providing an easily reproducible example! I’m seeing the same as you’re seeing, so I’ve flagged this with our engineers to take a closer look. I’ll be back with updates when I have them!

Unfortunately after playing around with your example sheet, I’m afraid I’m not too sure of a workaround at the moment :see_no_evil: Perhaps the HTTP Request node may not throw the same error?

5

Yeah I’m pretty sure the HTTP request node would work well since I’d instead handle appending the row myself. It’s a bit more manual and not as convenient as using the Sheets node to append.

Another way I thought of is to just not use the arrayformula and handle that in a separate sheet. And so I can continue to just add rows to the sheet that is no longer using the arrayformula.

But hopefully this is documented as a case that could happen with out of range errors :sweat_smile:

1 Like
6

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.

7

New version [email protected] got released which includes the GitHub PR 7684.