Google Sheets range expression with multiple items

Questions
#1

I’m having hard time grasping expressions with item lists. I’m sending 3 items to GSheets and trying to uptade 3 different sheets with range (sheetname!A1:G1) which name comes as input. But it only works with first item. Doesn’t update expression with each item. The things I tried :

{{$json["title"]}}!A1:G1
{{$item($position).$json["title"]}}!A1:G1
{{$item($runIndex).$json["title"]}}!A1:G1
{{$item($json["index"]).$json["title"]}}!A1:G1
{{$items()[$runIndex].json["title"] }}!A1:G1
{{$items()[$position].json["title"] }}!A1:G1
{{$items()[$json["index"]].json["title"] }}!A1:G1

Title property has same name as the sheet. It works for the first item.

chrome_0UcHC6FxmP
chrome_0UcHC6FxmP866×845 43.9 KB 

[
  {
    "data": [
      [
        "Position",
        "Type",
        "URL",
        "Domain",
        "Title",
        "Description",
        ""
      ]
    ],
    "spreadsheetId": "hidden",
    "sheetId": 1592557853,
    "title": "şekerpare",
    "index": 1,
    "sheetType": "GRID",
    "gridProperties": {
      "rowCount": 1000,
      "columnCount": 26
    },
    "tabColor": {
      "red": 0.99607843,
      "green": 0.33333334,
      "blue": 0.33333334
    },
    "tabColorStyle": {
      "rgbColor": {
        "red": 0.99607843,
        "green": 0.33333334,
        "blue": 0.33333334
      }
    }
  },
  {
    "data": [
      [
        "Position",
        "Type",
        "URL",
        "Domain",
        "Title",
        "Description",
        ""
      ]
    ],
    "spreadsheetId": "hidden",
    "sheetId": 736830411,
    "title": "õlireostus",
    "index": 2,
    "sheetType": "GRID",
    "gridProperties": {
      "rowCount": 1000,
      "columnCount": 26
    },
    "tabColor": {
      "red": 0.99607843,
      "green": 0.33333334,
      "blue": 0.33333334
    },
    "tabColorStyle": {
      "rgbColor": {
        "red": 0.99607843,
        "green": 0.33333334,
        "blue": 0.33333334
      }
    }
  },
  {
    "data": [
      [
        "Position",
        "Type",
        "URL",
        "Domain",
        "Title",
        "Description",
        ""
      ]
    ],
    "spreadsheetId": "hidden",
    "sheetId": 322588546,
    "title": "πι",
    "index": 3,
    "sheetType": "GRID",
    "gridProperties": {
      "rowCount": 1000,
      "columnCount": 26
    },
    "tabColor": {
      "red": 0.99607843,
      "green": 0.33333334,
      "blue": 0.33333334
    },
    "tabColorStyle": {
      "rgbColor": {
        "red": 0.99607843,
        "green": 0.33333334,
        "blue": 0.33333334
      }
    }
  }
]
#2

Hi @burakyccl, sorry to hear you’re running into trouble here. What error exactly are you seeing? For the first working item, were you also using a range including the sheet name?

As a workaround, have you tried using the Split in Batches node to split the execution into “batches” of 1 item each? This should allow you to use your single item logic for multiple items.

1 Like
#3

I’m not getting any errors, workflow completes successfully.

Yes I’m using expression for the first item too. it works for first item ({{$json[“title”]}}!A1:G1) but doesn’t iterate to second one.

When I use split in batches node I get the same result as previous node they are identical so It didn’t work either.

If I write index manually like this ({{$items()[1].json[“title”] }}!A1:G1) it writes to second sheet so I don’t think there is problem with naming

#4

When I use split in batches node I get the same result as previous node they are identical so It didn’t work either.

I quickly tried this with your example data and all three sheets of mine were updated as expected. Could you give the below workflow a quick go on your end? You can simply copy and paste it right into your n8n canvas.

image
image854×249 13.8 KB

Example Workflow
1 Like
#5

Yes! It worked. I didn’t connect it back to the output so I think that was the problem. I love the community and thank you for your time. I appreciate it. :pray:

1 Like
#6

Glad to hear this works for you, thanks a lot for confirming!

This also was a new learn to me, so I’ll make sure to update our looping documentation later to clarify the Google Sheets node won’t update multiple sheets without a loop.

1 Like