Google Sheet "Create or Update"

[aymkdn]

Hellow,

I read data from a SQL table and it returns the below json:

I then use the “Create or Update” function of Google Sheets with the below parameters, which seems to work because no error and I have the below output:

But if I check in Google Sheet, it keeps adding new rows instead of updating the existing ones:

I tried to delete the sheet and create a new one, I also tried to delete the file and to create a new one, but the issue persists.

Any idea?

Thanks

[treyr]

Strange - mine works with similar values.
Here are my settings of they help

Play with the options I have at the end

[KevinK]

@aymkdn I just came to the Community Forum earlier with the same problem & (I think) I found a solution:

It could be a problem with the Googlesheet Node version under the current N8N version. The solution ( according to @fxholl is mentioned here : Update Google Sheets node: Could not find column for key) Basically use the old Googlesheets Node instead of the new one - worked for me

From the screenshot @treyr has provided I believe It’s also the old GS Node (?)

[treyr]

I have the latest version BUT I checked and the node is old.
I just added a new workflow with the new node and boy is it different !
Not sure how this new node works for the same functionality

[KevinK]

The interface for the new Google Sheet Node sure is fancy (I personally love the look & feel of it all) The only problem is they omitted some of the old node functionalities which were superb (e.g in this case adding Key vales) I’ve reverted to using the old one as well as I await…

Thank goodness N8N updates don’t affect Node versioning inside workflows (Versioning - n8n Documentation) - otherwise we’d be in deep doo-doo!

[treyr]

I agree the upsert by key is a critical function for middleware like n8n which doesnt have its own persistent data storage between workflow instances.
So by design, you have to rely on keeping the state someplace else and Google Sheets are popular and also easy to use and a natural choice.
Please add this functionality back - it is CRITICAL.

Thanks

[KevinK]

Hope they add it back - fingers crossed!

[treyr]

In the meantime, you can copy paste an old node or change the typeVersion from 3 to 2

      "name": "Google Sheets",
      "type": "n8n-nodes-base.googleSheets",
      "typeVersion": 2,

[KevinK]

From where does one change these settings?

[treyr]

Export to JSON - edit in any text editor and then paste back or import

[treyr]

I just tried the new google sheets node (typeversion 3) and it does seem to have a bug.
It only appends and does not update an existing value

Here is what happens on multiple runs

It just adds new rows to the end and doesnt update on the following values being passed

[KevinK]

It does indeed. Took me a minute to figure it out as well

Quick one - is there a “changelog” page of some sorts where one can see vairous Node Versionings? (to know which typeversion to revert to/update as it were?)

[aymkdn]

Thanks @treyr !

With the below option (“Value Input Mode” = “User Entered”), it worked:

I noticed the “id” column was recognized as a string, but with this option it has been recognized as a number, and it’s probably why it now works.

[treyr]

Glad it worked !