Google sheet node executed only once

Hey! I’m experiencing an issue with Google Sheet node which is executed only once. I don’t know if this is a bug or a misunderstanding of how n8n works. I created a workflow to reproduce the issue:

A Functions node returns an array with several “spreadsheet-id”. The next node is a Google Sheet node which should read each spreadsheet based on all the “spreadsheet-id” returned by the previous nodes.

Finally, I just have a Slack node for debugging which get the data from the Gsheet node to send it in a channel. Only one message is sent to the channel while each spreadsheet should return a different message.

Is it a bug or am I missing an important concept of n8n? Thanks in advance!

{
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "position": [
        -200,
        220
      ],
      "typeVersion": 1
    },
    {
      "parameters": {
        "authentication": "oAuth2",
        "sheetId": "={{$json[\"spreadsheet-id\"]}}",
        "options": {}
      },
      "name": "Get sheet data",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        400,
        220
      ],
      "typeVersion": 1,
      "alwaysOutputData": false,
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "11",
          "name": "Google Sheets account"
        }
      }
    },
    {
      "parameters": {
        "channel": "marketing-webhooks-test",
        "text": "={{$json[\"message\"]}}",
        "attachments": [],
        "otherOptions": {}
      },
      "name": "Slack",
      "type": "n8n-nodes-base.slack",
      "position": [
        680,
        220
      ],
      "typeVersion": 1,
      "credentials": {
        "slackApi": {
          "id": "5",
          "name": "Slack account"
        }
      }
    },
    {
      "parameters": {
        "functionCode": "return [\n  { \n    json: {\n      \"spreadsheet-id\": \"\"\n    },\n  },\n  { \n    json: {\n      \"spreadsheet-id\": \"\",\n    }\n  }\n];"
      },
      "name": "Function",
      "type": "n8n-nodes-base.function",
      "position": [
        120,
        220
      ],
      "typeVersion": 1
    }
  ],
  "connections": {
    "Start": {
      "main": [
        [
          {
            "node": "Function",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get sheet data": {
      "main": [
        [
          {
            "node": "Slack",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Function": {
      "main": [
        [
          {
            "node": "Get sheet data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

Update: I get it worked with a custom loop but it seems a bit overcomplicated.

The Functions node named “Get single sheet” return a single item from the node “All sheets” at each iteration with runIndex, and the IF node returns false to stop the loop when runIndex < $items(“All sheets”).length.

{
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "position": [
        -360,
        -360
      ],
      "typeVersion": 1
    },
    {
      "parameters": {
        "authentication": "oAuth2",
        "sheetId": "={{$json[\"spreadsheet-id\"]}}",
        "options": {}
      },
      "name": "Get sheet data1",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        480,
        -560
      ],
      "typeVersion": 1,
      "alwaysOutputData": false,
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "11",
          "name": "Google Sheets account"
        }
      }
    },
    {
      "parameters": {
        "channel": "marketing-webhooks-test",
        "text": "={{$json[\"message\"]}}",
        "attachments": [],
        "otherOptions": {}
      },
      "name": "Slack1",
      "type": "n8n-nodes-base.slack",
      "position": [
        660,
        -560
      ],
      "typeVersion": 1,
      "credentials": {
        "slackApi": {
          "id": "5",
          "name": "Slack account"
        }
      }
    },
    {
      "parameters": {},
      "name": "NoOp1",
      "type": "n8n-nodes-base.noOp",
      "typeVersion": 1,
      "position": [
        820,
        -340
      ]
    },
    {
      "parameters": {
        "conditions": {
          "number": [
            {
              "value1": "={{$runIndex}}",
              "value2": "={{$items(\"All sheets\").length}}"
            }
          ]
        }
      },
      "name": "IF1",
      "type": "n8n-nodes-base.if",
      "position": [
        80,
        -360
      ],
      "typeVersion": 1
    },
    {
      "parameters": {
        "functionCode": "return [\n  { \n    json: {\n      \"spreadsheet-id\": \"\"\n    },\n  },\n  { \n    json: {\n      \"spreadsheet-id\": \"\",\n    }\n  }\n];"
      },
      "name": "All sheets",
      "type": "n8n-nodes-base.function",
      "position": [
        -160,
        -360
      ],
      "typeVersion": 1
    },
    {
      "parameters": {
        "functionCode": "return [$items(\"All sheets\")[$runIndex]];"
      },
      "name": "Get single sheet",
      "type": "n8n-nodes-base.function",
      "position": [
        280,
        -560
      ],
      "typeVersion": 1
    }
  ],
  "connections": {
    "Start": {
      "main": [
        [
          {
            "node": "All sheets",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get sheet data1": {
      "main": [
        [
          {
            "node": "Slack1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Slack1": {
      "main": [
        [
          {
            "node": "IF1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "IF1": {
      "main": [
        [
          {
            "node": "Get single sheet",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "NoOp1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "All sheets": {
      "main": [
        [
          {
            "node": "IF1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get single sheet": {
      "main": [
        [
          {
            "node": "Get sheet data1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

Hi @fadrien, many thanks for sharing your solution!

You’re quite right, in many cases it shouldn’t be necessary to build a loop. Until your post I thought all exceptions are listed here, but that’s doesn’t seem to be the case here, so I’ll make sure to add the Google Sheet’s node read operation to the list.

If you can update your logic to use the lookup operation instead, you wouldn’t need the loop (but this would obviously depend on whether your sheets allow looking up data).

If you’d like to simplify your flow a bit, I think you could modify it like so (replace the NoOp node with your Slack node):

Example Workflow
{
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        240,
        300
      ]
    },
    {
      "parameters": {
        "authentication": "oAuth2",
        "sheetId": "={{$json[\"spreadsheet-id\"]}}",
        "options": {}
      },
      "name": "Get sheet data",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        920,
        300
      ],
      "typeVersion": 1,
      "alwaysOutputData": false,
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "31",
          "name": "[email protected]"
        }
      }
    },
    {
      "parameters": {
        "functionCode": "return [\n  { \n    json: {\n      \"spreadsheet-id\": \"1XjTNE0RWJf4rRf-0xbRE1mgxXu3XdmC29yn3R84q8Ms\"\n    },\n  },\n  { \n    json: {\n      \"spreadsheet-id\": \"1JloLbcDVSAbMZJAkIFWlZzoedH3Su4ZihyU2Bc8ZOKo\",\n    }\n  }\n];"
      },
      "name": "Function",
      "type": "n8n-nodes-base.function",
      "position": [
        480,
        300
      ],
      "typeVersion": 1
    },
    {
      "parameters": {
        "batchSize": 1,
        "options": {}
      },
      "name": "SplitInBatches",
      "type": "n8n-nodes-base.splitInBatches",
      "typeVersion": 1,
      "position": [
        700,
        300
      ]
    },
    {
      "parameters": {},
      "name": "Do Something Here",
      "type": "n8n-nodes-base.noOp",
      "typeVersion": 1,
      "position": [
        1140,
        300
      ]
    }
  ],
  "connections": {
    "Start": {
      "main": [
        [
          {
            "node": "Function",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get sheet data": {
      "main": [
        [
          {
            "node": "SplitInBatches",
            "type": "main",
            "index": 0
          },
          {
            "node": "Do Something Here",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Function": {
      "main": [
        [
          {
            "node": "SplitInBatches",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "SplitInBatches": {
      "main": [
        [
          {
            "node": "Get sheet data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}
1 Like

Thank you @MutedJam for the simplified flow. I tested it this morning and it works well but I encounter a really weird issue in some contexts. I don’t know if it’s related to Google Sheet node or Split in batches node.

As you can see, in the first screenshot, Google sheet node called “Get sheet data” is executed twice which is what I want.

When I change the execution order of all my nodes, the behavior is not the same: “Get sheet data” is executed only once (the error in the Functions node is caused by this issue). I didn’t change anything related to the loop.

So this is a special behavior in the Merge node described here:

When the “Wait all data” node from your screenshot is executed, it would try executing the Function node (which presumably fails because it doesn’t have the required data available yet). I am afraid that’s how it currently works, but you might want to vote on the feature request linked above to change this.

Thank you for the explanation. Is there any workaround to achieve what I’m trying to do?

Seeing the Merge node in your screenshot is running in Wait mode, I’d build a sequential flow here, e.g. something like this:

Example Workflow
{
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        240,
        300
      ]
    },
    {
      "parameters": {
        "values": {
          "string": [
            {
              "name": "value",
              "value": "foo"
            }
          ]
        },
        "options": {}
      },
      "name": "Set",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        460,
        300
      ]
    },
    {
      "parameters": {
        "values": {
          "string": [
            {
              "name": "value",
              "value": "bar"
            }
          ]
        },
        "options": {}
      },
      "name": "Set1",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        680,
        300
      ]
    },
    {
      "parameters": {
        "values": {
          "string": [
            {
              "name": "value",
              "value": "baz"
            }
          ]
        },
        "options": {}
      },
      "name": "Set2",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        900,
        300
      ]
    },
    {
      "parameters": {
        "functionCode": "let setItems = $items(\"Set\");\nlet set1Items = $items(\"Set1\");\nlet set2Items = $items(\"Set2\");\n\nreturn [].concat(setItems, set1Items, set2Items);"
      },
      "name": "Process Data",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        1120,
        300
      ]
    }
  ],
  "connections": {
    "Start": {
      "main": [
        [
          {
            "node": "Set",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set": {
      "main": [
        [
          {
            "node": "Set1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set1": {
      "main": [
        [
          {
            "node": "Set2",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set2": {
      "main": [
        [
          {
            "node": "Process Data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

Here, every node left of my “Process Data” Function node would run first, the Function node is then able to access all data.

Yes, this is what I did, but I wanted to know if there are any solutions to parallelize multiple actions. My sequential flow works fine but it’s quite slow.

Contacting multiple APIs at the same time and waiting for all the responses to do other actions would be great.