Find and update a key row

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.

Tnks for your fast reply

Yes, I tried it

But I have some problem

For example

I have to have only one KEY updated with the last value get from postback

If

Date | Key | Name | Status
16/11/2021 07:00 | 1 | Teste | Wait
16/11/2021 08:00 | 1 | Teste | Progress
16/11/2021 09:00 | 1 | Teste | Aproved

In that case above I receibe 3 postback and append 3 rows in my sheets

In my case, Only to have to maint the last row

16/11/2021 09:00 | 1 | Teste | Aproved

I was think about delete other, but was not possible

Is there anyway to do it?

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:

In this example I have used a Set node labelled “Incoming Data” to represent data coming in from an external source:

My sheet looks like so:
image

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):

Example Workflow
{
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        250,
        300
      ]
    },
    {
      "parameters": {
        "conditions": {
          "number": [
            {
              "value1": "={{$json[\"Key\"]}}",
              "operation": "equal",
              "value2": "={{$items(\"Incoming Data\")[0].json[\"Key\"]}}"
            }
          ]
        }
      },
      "name": "Result Found",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        850,
        300
      ]
    },
    {
      "parameters": {
        "authentication": "oAuth2",
        "operation": "lookup",
        "sheetId": "1FGrt5vgdPDrInFXQAWBfRuRl61HrdvdAdy7kf0LtX1M",
        "lookupColumn": "Key",
        "lookupValue": "={{$items(\"Incoming Data\")[0].json[\"Key\"]}}",
        "options": {}
      },
      "name": "Lookup Key",
      "type": "n8n-nodes-base.googleSheets",
      "typeVersion": 1,
      "position": [
        650,
        300
      ],
      "alwaysOutputData": true,
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "31",
          "name": "[email protected]"
        }
      }
    },
    {
      "parameters": {
        "values": {
          "string": [
            {
              "name": "Date",
              "value": "16/11/2021 07:12"
            },
            {
              "name": "Name",
              "value": "Teste"
            },
            {
              "name": "Status",
              "value": "Aproved"
            }
          ],
          "number": [
            {
              "name": "Key",
              "value": 1
            }
          ]
        },
        "options": {}
      },
      "name": "Incoming Data",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        450,
        300
      ]
    },
    {
      "parameters": {
        "authentication": "oAuth2",
        "operation": "update",
        "sheetId": "1FGrt5vgdPDrInFXQAWBfRuRl61HrdvdAdy7kf0LtX1M",
        "key": "Key",
        "options": {}
      },
      "name": "Update Row",
      "type": "n8n-nodes-base.googleSheets",
      "typeVersion": 1,
      "position": [
        1250,
        200
      ],
      "alwaysOutputData": true,
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "31",
          "name": "[email protected]"
        }
      }
    },
    {
      "parameters": {
        "values": {
          "string": [
            {
              "name": "Date",
              "value": "={{$items(\"Incoming Data\")[0].json[\"Date\"]}}"
            },
            {
              "name": "Name",
              "value": "={{$items(\"Incoming Data\")[0].json[\"Name\"]}}"
            },
            {
              "name": "Status",
              "value": "={{$items(\"Incoming Data\")[0].json[\"Status\"]}}"
            }
          ],
          "number": [
            {
              "name": "Key",
              "value": "={{$items(\"Incoming Data\")[0].json[\"Key\"]}}"
            }
          ]
        },
        "options": {}
      },
      "name": "Data to Create",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        1050,
        400
      ]
    },
    {
      "parameters": {
        "values": {
          "string": [
            {
              "name": "Date",
              "value": "={{$items(\"Incoming Data\")[0].json[\"Date\"]}}"
            },
            {
              "name": "Name",
              "value": "={{$items(\"Incoming Data\")[0].json[\"Name\"]}}"
            },
            {
              "name": "Status",
              "value": "={{$items(\"Incoming Data\")[0].json[\"Status\"]}}"
            }
          ],
          "number": [
            {
              "name": "Key",
              "value": "={{$items(\"Incoming Data\")[0].json[\"Key\"]}}"
            }
          ]
        },
        "options": {}
      },
      "name": "Data to Update",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        1050,
        200
      ]
    },
    {
      "parameters": {
        "authentication": "oAuth2",
        "operation": "append",
        "sheetId": "1FGrt5vgdPDrInFXQAWBfRuRl61HrdvdAdy7kf0LtX1M",
        "options": {
          "valueInputMode": "USER_ENTERED"
        }
      },
      "name": "Append Row",
      "type": "n8n-nodes-base.googleSheets",
      "typeVersion": 1,
      "position": [
        1250,
        400
      ],
      "alwaysOutputData": true,
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "31",
          "name": "[email protected]"
        }
      }
    }
  ],
  "connections": {
    "Start": {
      "main": [
        [
          {
            "node": "Incoming Data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Result Found": {
      "main": [
        [
          {
            "node": "Data to Update",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Data to Create",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Lookup Key": {
      "main": [
        [
          {
            "node": "Result Found",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Incoming Data": {
      "main": [
        [
          {
            "node": "Lookup Key",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Data to Create": {
      "main": [
        [
          {
            "node": "Append Row",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Data to Update": {
      "main": [
        [
          {
            "node": "Update Row",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

Greaaat man, you’re the guy

Tnks for the fast of the fast reply xD

I get it, but hava feel questions

What the IF conditions?

Cause If the lookup dont have result, my IF dont work one time that it get values from lookup data

If the lookup return value, great, IF works so good

Witch condition can I put in the IF condition when lookup dont have value IF works so good?

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:
image

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:
image

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):
image

The second expression is {{$items("Incoming Data")[0].json["Key"]}}:image

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.

Perfect

You’re the guy

Word very well
Tnk you very much man

1 Like