Lookup and Convert in n8n

Hi Community!

I am creating a workflow where data comes in via a Google Sheet to my workflow. This data is in the format of text strings. I then do a HTTP request which calls an API and gives me a series of ID codes related to a series of text strings. The text strings from my Google Sheet correspond to the ID’s which have been called from the API.

My issue is that I want a way to lookup the text string from the output data given in the HTTP request, and match it to the unique ID outputted from the same node. I then want to convert the string using a SET node to the ID value, rather than the raw string value.

Is there anyway to do this within n8n? I think i need to use loops however my experience with this is limited. Any help would be really appreciated as its quite an important use case.

Thanks

Hey @aziadlourad,

You can use the Merge node and select the relevant mode (in your case it can be either Merge By Key, Keep Key Matches, or Remove Key Matches).

I am not sure what exactly you mean by converting the string to the ID value. What is the data type of the ID that you want to use? Also, are you trying to append this to a database?

Hi @harshil1712

Thanks for the response.

I want to essentially change the string value to a different string value depending on certain conditions. The issue is that when the initial string comes through, I need to check if it matches the other data source’s possible 10 values, and return the corresponding ID (which is also a string). Does that make sense?

Can you share the output of both the nodes? I think I have understood your use case, but having an output from the nodes will help me provide a solution faster :slight_smile:

(Please replace any sensitive data with dummy data)

An update for anyone who may face the similar issue:

The HTTP Request node returns an object that contains an array of attributes. If an attribute is of the type multi-select, it displays the list of all the possible options. We want to search through the value returned by the Google Sheets node in this options array.

A possible solution:

{
  "nodes": [
    {
      "parameters": {
        "mode": "keepKeyMatches",
        "propertyName1": "title",
        "propertyName2": "option_value"
      },
      "name": "Merge",
      "type": "n8n-nodes-base.merge",
      "typeVersion": 1,
      "position": [
        1350,
        350
      ]
    },
    {
      "parameters": {
        "fieldToSplitOut": "attributes[2].options",
        "options": {}
      },
      "name": "Item Lists",
      "type": "n8n-nodes-base.itemLists",
      "typeVersion": 1,
      "position": [
        900,
        250
      ]
    },
    {
      "parameters": {
        "functionCode": "return [\n{json: {\n  \"id\": \"9058afb68e\",\n  \"name\": \"Example Data\",\n  \"is_public\": false,\n  \"created_at\": \"2021-04-08T10:03:43.685Z\",\n  \"attributes\": [\n    {\n      \"id\": \"bf3e12casdf\",\n      \"collection_id\": \"9058afb6asdf68e\",\n      \"name\": \"Notes\",\n      \"type\": \"text\",\n      \"created_at\": \"2021-09-07T09:14:31.110Z\"\n    },\n    {\n      \"id\": \"4a197577-8a70b4c363\",\n      \"collection_id\": \"9058afb6-56c6-425868e\",\n      \"name\": \"How much raised?.\",\n      \"type\": \"text\",\n      \"created_at\": \"2021-09-06T20:47:27.648Z\"\n    },\n    {\n      \"id\": \"70cbf3d3-93369eb163\",\n      \"collection_id\": \"968e\",\n      \"name\": \"Primary Sector\",\n      \"type\": \"multi-select\",\n      \"options\": [\n        {\n          \"id\": \"304549d95\",\n          \"attribute_id\": \"70cbf33369eb163\",\n          \"title\": \"Option 1\"\n        },\n        {\n          \"id\": \"7ca21b5e43\",\n          \"attribute_id\": \"70cbe3369eb163\",\n          \"title\": \"Option 2\"\n        },\n        {\n          \"id\": \"9492c040be7\",\n          \"attribute_id\": \"70c4eb163\",\n          \"title\": \"Option 3\"\n        },\n      ],\n    }\n  ],\n  \n}\n}\n]"
      },
      "name": "Mock HTTP",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        700,
        250
      ]
    },
    {
      "parameters": {
        "functionCode": "const _id = $node[\"Mock HTTP\"].json[\"id\"];\n\nfor (item of items) {\n  item.json[\"_id\"] = _id;\n}\n\nreturn items;"
      },
      "name": "Add ID",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        1100,
        250
      ]
    },
    {
      "parameters": {
        "keepOnlySet": true,
        "values": {
          "string": [
            {
              "name": "option_value",
              "value": "Option 2"
            }
          ]
        },
        "options": {}
      },
      "name": "Mock Sheets",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        1040,
        450
      ]
    }
  ],
  "connections": {
    "Item Lists": {
      "main": [
        [
          {
            "node": "Add ID",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Mock HTTP": {
      "main": [
        [
          {
            "node": "Item Lists",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Add ID": {
      "main": [
        [
          {
            "node": "Merge",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Mock Sheets": {
      "main": [
        [
          {
            "node": "Merge",
            "type": "main",
            "index": 1
          }
        ]
      ]
    }
  }
}