Google Sheets Set Key is duplicated

Describe the issue/error/question

Running the workflow with slight changes results in 2 situations:

A) Only first file name and URL are added to ALL rows in Google Sheet (duplicate of first one)
B) All filename and URLs are added except ONLY the first URL is added correctly (in subsequent rows the data after “/” gets deleted.

Image A:

Image B:

Google Sheets Key Row

Message & Pin Title (red circle) => Filename (with .mp4 removed) :white_check_mark:
Video URL (yellow circle) => AWS S3 file URL (only works for first row) :x:
Year & Category (green circle) => predefined using Set :white_check_mark:

Ideal Outcome?

Loop append the correct URL - meaning somewhere in the SET to Google Sheets node, the URL expression gets cut off for subsequent rows.

Expression for URL Key
https://beeloud.s3.us-west-2.amazonaws.com/{{encodeURI($node[“Notion”].json[“property_name”] + " shorts")}}/{{encodeURI($node[“Google Drive”].json[“name”])}}

Problem except the first row that works well, in following rows, the bolded part above gets cut off.

Please share the workflow

Share the output returned by the last node

Set node output

[
{
"success": true,
"Year": 2022,
"VideoURL": "https://beeloud.s3.us-west-2.amazonaws.com/1%20How%20we%20ended%20up%20here%20P%20shorts/Her%20parents%20let%20her%20come%20to%20Canada%20%F0%9F%8D%81%F0%9F%99%84.mp4",
"Message": "Her parents let her come to Canada 🍁🙄",
"Category": "BeeLoud",
"PinTitle": "Her parents let her come to Canada 🍁🙄"
}
]

In the set node I am mapping the S3 data to columns in Google Sheet which are different

Information on your n8n setup

  • **n8n version:**0.182.0
  • **Database you’re using (default: SQLite):**default
  • **Running n8n with the execution process [own(default), main]:**own
  • **Running n8n via [Docker, npm, n8n.cloud, desktop app]:**desktop app

Hey @pooria,

What happens if you use the set the and Google sheets steps in the loop does that resolve the issue?

Nope :smiling_face_with_tear:

Tried it in the loop, before the loop, after the loop, it just doesn’t.

You are going to want it in the loop so I would start by keeping it there, When you set the values does it work if you try using $json[“value”]?

Where should I add $json[“value”]?

As you can see in the set node there’s multiple string name/value pairs

Anytime you are trying to use the data for the node before. Can you share the output of the Notion node and google drive node? I might be able to pop together an example using the data you have with function nodes to mock the data.

Feel free to replace any sensative content with a dummy value.

Absolutely!!

Thanks @jon

Notion Node Output: I just use "property_drive_shorts"

[
{
"id": "023da278-a860-43eb-8ca4-4e4d01855d3f",
"name": "1 How we ended up here P",
"url": "https://www.notion.so/1-How-we-ended-up-here-P-023da278a86043eb8ca44e4d01855d3f",
"property_cover": [
],
"property_responsible": [
"[email protected]"
],
"property_recorded": {
"start": "2022-03-29",
"end": null,
"time_zone": null
},
"property_drive_long": null,
"property_long_status": "✅ Published Long ",
"property_drive_shorts": "https://drive.google.com/drive/folders/1YXwV3DzJIXiK2AKk4m4scgZT8Yx2q1Bu?usp=sharing",
"property_you_tube": null,
"property_short_status": "✅Published Shorts",
"property_anchor": null,
"property_guest": [
],
"property_scheduled": null,
"property_descript": null,
"property_name": "1 How we ended up here P"
}
]

Screenshot of files in Google Drive folder

Google Drive Output JSON

[
{
"id": "14t5mwTebPk8n3CLjZUeXqyCVMoGDCbBU",
"name": "Her parents let her come to Canada 🍁🙄.mp4"
},
{
"id": "1-onbMu4MElCAi2w3o5AKPj3x1n-CtFhF",
"name": "Her parents let her come to Canada 🍁🙄.mp4"
},
{
"id": "1XqnmeX6wBXch2iaRE764omTQYpoLcIvT",
"name": "Her parents let her come to Canada 🍁🙄.mp4"
},
{
"id": "1bw-pg8kCOhzILxnMxhUMhHn5wCNM4v0c",
"name": "$250,000 scholarship to Canada🍪_.mp4"
},
{
"id": "1sfpW3I266Xe4w4ErjSfysfXZ2YeK8oqB",
"name": "$250,000 scholarship to Canada🍪_.mp4"
},
{
"id": "1Xg0MSOIJhk9LihNN7U-W0YcWu0v3PXQQ",
"name": "Let's go to Saturn 🌙.mp4"
},
{
"id": "1-OF49CegIggKmXU1Ol9zzLcn8tv1kO2M",
"name": "Let's go to Saturn 🌙.mp4"
},
{
"id": "1NrfwY4fb7pAK0hV7ErOziqxICYNNDAw7",
"name": "Let's go to Saturn 🌙.mp4"
},
{
"id": "16knN7DPmNMMYCc_oUFsmKicEmFTl-bz8",
"name": "Life goals achieved_ start a podcast ✅.mp4"
},
{
"id": "1db4l2eGtixLgg8oni5hqSISDPpeueXqA",
"name": "Life goals achieved_ start a podcast ✅.mp4"
}
]

Seems like they’re being repeatedIs there a way to reset the node or delete cache? Maybe that helps****

Different IDs means different files in the world of Google so it is unlikely to be a cache thing, Could they be different file revisions?

Give this a bash…

2 Likes

Duuuude. You’re a magician. That worked!!

Part of the URL is not missing now!!

BUT the repetition is stil there:

Small steps :slight_smile: I am still not able to reproduce that part but I can see another thread is opened so will look into it shortly.

Thanks @jon

My dumbass found the solution:

In set node above, change …“Google Drive” to “Google Drive1” to get the name from the loop in split branches. Thanks @jon

2 Likes