Google Sheets Lookup for Update Row Function

Hello Guys,
I cant refer to a looked up Row in a specific Coloumn to Update it in Google Sheets.
I can use the Lookup Row Feature but it doesnt make me able to refer to the found values/specific cell after on.
Is this function missing or is the whole process (lookup + update) possible with the currently exsisting Update fuction for Google Sheets?

Thank you for your help, very nice to work with n8n! :slight_smile:

Welcome to the community @leadandconversion and very happy to hear that you enjoy n8n!

Yes you are right, it is currently not possible to get the exact call (for example: D14) returned. But what you want to achieve should still be possible to following way.

Assuming you have a Table with the columns: Name, Age
You want to search for a specific “Name” and then change its value for “Age”.

Then you can simply query Spreadsheet via “Loopup”, change the value for “Age” with a Set-Node and then update its value in the Spreadsheet again via the “Update” functionality. Here an example where we look for the Name “Jim”:

{
  "nodes": [
    {
      "parameters": {
        "operation": "lookup",
        "range": "A:B",
        "lookupColumn": "Name",
        "lookupValue": "Jim",
        "options": {}
      },
      "name": "Google Sheets",
      "type": "n8n-nodes-base.googleSheets",
      "typeVersion": 1,
      "position": [
        652,
        666
      ],
      "credentials": {
        "googleApi": ""
      }
    },
    {
      "parameters": {
        "values": {
          "number": [
            {
              "name": "Age",
              "value": 66
            }
          ]
        },
        "options": {}
      },
      "name": "Set",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        802,
        666
      ]
    },
    {
      "parameters": {
        "operation": "update",
        "sheetId": "",
        "range": "A:B",
        "key": "Name",
        "options": {}
      },
      "name": "Google Sheets2",
      "type": "n8n-nodes-base.googleSheets",
      "typeVersion": 1,
      "position": [
        952,
        666
      ],
      "credentials": {
        "googleApi": ""
      }
    }
  ],
  "connections": {
    "Google Sheets": {
      "main": [
        [
          {
            "node": "Set",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set": {
      "main": [
        [
          {
            "node": "Google Sheets2",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

That works however only correctly if the value is unique. If there would be two different “Jim” it would only update the first one.

I hope that helps!

1 Like

@jan

Thank you I had another look into it and understood the Set Node System and how the to update data is passed to the Sheets node. Very nice when you once got the turn. Grüße nach Berlin :slight_smile:

Great to hear that it is now clear how it works. We are currently working hard on adding more and more examples for nodes to make it easier in the future.