[Node - Google Sheets V2]: Appending rows loads unnecessary data

[soerenuhrbach]

Bug Description

I'm using Google Sheets Node v2 to append rows to a single Google Sheet with more than 10.000 rows (and 35 columns) in that sheet.

When running "update", "append" or "appendOrUpdate" operation leads to high memory when modifying sheets with a bigger amount of data. This results in performance and in worst case in out of memory exceptions. The n8n application crashes in my case.

It seems that running all of these operations loads the whole spreadsheet before inserting. I understand, that it's necessary to load all rows for update operations.

To Reproduce

1. Create big set of data in google sheets with more than 10k rows and 35 columns.
2. Create a workflow to insert a single row in that google spreadsheet using "append" operation of Google Sheets v2 node
3. Run workflow

Expected behavior

My expectations for the "append" operations is ...

• appending rows doesn't require to load any other rows expect the key row to identify the columns
• the performance of the operation is not affected by the amount of data in the spreadsheet

Operating System

Docker

n8n Version

1.63.1

Node.js Version

20.10.0

Database

PostgreSQL

Execution mode

main (default)

[Joffcom]

Hey @soerenuhrbach,

We have created an internal ticket to look into this which we will be tracking as "GHC-310"

[soerenuhrbach]

I'll submit a pull request to fix that performance issues in a few days.

The code (here) shows pretty clear that the getData-method is called without any restrictions on the rows, since only the sheet name (here) will be used as range.

In case of appending a row, we can restrict the range to the header row.

[Joffcom]

Hey @soerenuhrbach

Does the operation have an option to make less api calls under the additional settings? This could help with the amount of data being loaded as we then change how we add the data.

If you are working with 10,000 rows and 35 columns I would recommend splitting the data into smaller chunks and using a sub workflow where possible which will help with the memory issues.

If you do decide to look into it and put in a pull request don't forget to update and include any new tests.

[soerenuhrbach]

@Joffcom I tried this option as well, but that didn't have any effect. I've already debugged the code.

The operations always load all the data of a spreadsheet.

And btw - I don't want all the data to be loaded. The issue is that I only want to insert a single rows, which doesn't work pretty well, because the node always loads the whole spreadsheet data - even if I don't need it.

[soerenuhrbach]

The debugger shows pretty clear that the whole dataset (12.466 rows) is been loaded, even tough my workflows has only a single input item.

Debugger:

Workflow:

[michael-radency]

Hello @soerenuhrbach , thanks for your PR, you could also try to use "Minimise API Calls" option, as it could help with performance

[soerenuhrbach]

Hi @michael-radency, unfortunately it doesn't matter whether I active minimise api calls or not.. The linked line of code (see previous comments) show pretty well where the issue is.

[Joffcom]

Looks like the PR has been accepted and merged, I am going to mark this as closed. Thanks for the contribution 🙌🏻

[danberindei]

@Joffcom we have recently started using n8n and we the Google Sheets node to append rows to a table with 40k rows, and we see the append operation always takes more than 10 seconds (as opposed to make.com or Power Automate managing to insert the row in under 2 seconds most of the time).

Is it because the PR was reverted with #11326, and n8n still reads 40k rows every time it append one row?