Google Sheets provide list to MySQL query

I am trying to create the following workflow:

Google Sheet -> MySql

But, the following logic should be applied:

  • Read data from sheet (the sheet contains two columns)
  • Only pass the second column through to MySQL - I am currently using a function or this. Is this the best way to do it?
  • Execute a query on MySQL, using the output from the function: E.g: “SELECT id, title FROM my_table WHERE id IN (<<output_from_function>>)” - How do I define this?

Here’s the definition of my current workflow:

    {
      "name": "Fulfilment Tracker - Study Material Import",
      "nodes": [
        {
          "parameters": {},
          "name": "Start",
          "type": "n8n-nodes-base.start",
          "typeVersion": 1,
          "position": [
            250,
            300
          ]
        },
        {
          "parameters": {
            "sheetId": "1KSBZluDUrsx6271bnSCANs4346HjvKTi4em-u0LoLUk",
            "range": "study_material!A:B",
            "options": {}
          },
          "name": "Google Sheets",
          "type": "n8n-nodes-base.googleSheets",
          "typeVersion": 1,
          "position": [
            460,
            290
          ],
          "credentials": {
            "googleApi": "Google Service Acount"
          }
        },
        {
          "parameters": {
            "functionCode": "const allItems = $items(\"Google Sheets\", 0, $runIndex);\nlet output = [];\nfor (let item of allItems) {\n  output.push({\n    json: {\n      tsin: item.json.TSIN\n    }\n  })\n};\nreturn output;\n"
          },
          "name": "Function",
          "type": "n8n-nodes-base.function",
          "typeVersion": 1,
          "position": [
            700,
            290
          ]
        }
      ],
      "connections": {
        "Start": {
          "main": [
            [
              {
                "node": "Google Sheets",
                "type": "main",
                "index": 0
              }
            ]
          ]
        },
        "Google Sheets": {
          "main": [
            [
              {
                "node": "Function",
                "type": "main",
                "index": 0
              }
            ]
          ]
        }
      },
      "active": false,
      "settings": {},
      "id": "2"
    }

There is theoretically no need to remove the second column you do not need.

Here are two examples how what you need to be done can be achieved:

{
  "nodes": [
    {
      "parameters": {
        "functionCode": "return [\n  {\n    json: {\n      id: 1,\n      ignoreData: 'whatever1'\n    }\n  },\n  {\n    json: {\n      id: 2,\n      ignoreData: 'whatever2'\n    }\n  }\n]"
      },
      "name": "Mock Data Sheets",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        450,
        500
      ]
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "=SELECT id, title FROM my_table WHERE id IN ({{$node[\"Combine IDs\"].json[\"ids\"].join(',')}})"
      },
      "name": "MySQL",
      "type": "n8n-nodes-base.mySql",
      "typeVersion": 1,
      "position": [
        850,
        700
      ],
      "credentials": {
        "mySql": ""
      }
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "=SELECT id, title FROM my_table WHERE id={{$node[\"Mock Data Sheets\"].json[\"id\"]}}"
      },
      "name": "MySQL1",
      "type": "n8n-nodes-base.mySql",
      "typeVersion": 1,
      "position": [
        650,
        500
      ],
      "credentials": {
        "mySql": ""
      }
    },
    {
      "parameters": {
        "functionCode": "return [\n  {\n    json: {\n      ids: $items().map(item => item.json.id)\n    }\n  }\n]"
      },
      "name": "Combine IDs",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        650,
        700
      ]
    }
  ],
  "connections": {
    "Mock Data Sheets": {
      "main": [
        [
          {
            "node": "Combine IDs",
            "type": "main",
            "index": 0
          },
          {
            "node": "MySQL1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Combine IDs": {
      "main": [
        [
          {
            "node": "MySQL",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

Perfect! Your simplified solution worked 100%. Thanks, @jan!

Great to hear! Have fun!