Hi, how can I do to find and update a row in my sheets?
Ex
Before apend a row find if the key row already exist and update it with the last value get from postback
Example:
I get a postback like:
Date | Key | Name | Status
16/11/2021 07:00 | 1 | Teste | Wait
I get second postback like
Date | Key | Name | Status
16/11/2021 08:00 | 1 | Teste | Aproved
I want to update the row with Key 1 to mantain only one with the last update (Status aproved)
I try to use lookup fuction, I get with success the value but i dont know how can I do to update the row with the last values or delete all date get from lookup with that KEY ID and append the last value get from postback
Hi @Frank_da_Silva_Costa, are you using Google Sheets? If so, did you have a look at the example from our docs? It includes a workflow performing a lookup and update on Google Sheets which has been built by our very own @harshil1712. In case your lookup returns multiple items, you could use for example an Item Lists node to sort by your Date column in order to identify the latest update.
Thanks for the additional examples. It’s always a bit tricky to use Google Sheets over a “real” database, but we’ll figure this out. I am not entirely sure what you mean by postback though. Would this be data coming in from sources other than your Google Sheet and which you want to use to update a row in your Google Sheet (or create if it doesn’t exist yet)?
If so, can you confirm if the Key column from your Google Sheet would be a unique value? E.g. would the value 1 only exist a single time in the Google Sheet?
If that’s the case, you can do something relatively simple like this:
The workflow then performs a lookup to see if a row with that key exists. If so, this row will be updated. If not, a new row will be appended. Here’s the full workflow which you can copy and paste into your n8n canvas (you’ll need to adjust some details such as credentials or the ID of your sheet):
Hey @Frank_da_Silva_Costa. In the example workflow I have provided you can inspect all relevant nodes in detail, but these are the key pieces:
The first Google Sheets node (“Lookup Key”) has the Always Output Data setting enabled:
This allows the workflow to continue even if this node does not find any items. If nothing is found, the node passes on an empty item.
The IF node then compares two expressions to determine whether the previous node has found the item we were looking for:
The first expression is {{$json["Key"]}} and would just resolve to the Key the previous node returns. If no Key field is found (when the previous node has not found an item), it will return nothing (and is highlighted red in the expression editor):
The second expression is {{$items("Incoming Data")[0].json["Key"]}}:
It simply refers to the data returned by the initial “Incoming Data” Set node of the example workflow. This comparison is just one possibility though and could be anything else that works for you.
So if an item with a “Key” value matching the one from the initial “Incoming Data” Set node is found, this item is passed on to the “true” output of the IF node and we update the row with the data from the initial “Incoming Data” Set node. If no matching item is found, then the flow will continue on the “false” output and we can create the non-existing row.
