Handling Json array data from spreadsheet and send through HTTP

[Priya_Kumar]

Please check the screen shot.

I am getting the following output from the spreadsheet node.

I need to upload the data to rest API in following format

[
{
“key1”: “value1”,
“body”: “This is our first article”
},
{
“title”: “Hello again, world!”,
“body”: “This is our second article”
}
]

I tried using expression in HTTP node but I don’t know which will be the right expression to view the complete json from the spreadsheet node so I can use it properly to add it to HTTP body parameters

[Shirobachi]

Hey @Priya_Kumar,

If you’ll wait until all data will be downloaded on the left side you will see available options.
I don’t understand which data you want to use but for example if you would like to use Client_name you would write sth like that {{$node["Spreadsheet File"].json.Client_name}}

You said that you want to send it to rest API so try to use request node

[Priya_Kumar]

Thanks for your response.

I tried the expression shared but I am getting below response. don’t know the right expression for the attached json file

spreadsheet output json is like this

[

{

“Client_Name”: “Client 4”,

“Client_Id”: 45325,

“Client_Type”: “DIRECT”,

“Point”: 22.91

},

{

“Client_Name”: “Client 4”,

“Client_Id”: 45325,

“Client_Type”: “DIRECT”,

“Point”: 22.91

}
]

[Shirobachi]

My found should be {{$node["Spreadsheet File"].json.Client_Name}} if not try also {{$node["Spreadsheet File"].Client_Name}}

BTW. please share your code inside ``` code ```

How to share workflow properly

I just would like to show how to share your workflow to other user, so they can easier and faster help you. If you do not use ``` in my case workflow will not become able to paste into n8n interface. Be aware that no credentials or getter data by nodes are shared via that way. Only nodes, their properties (like URL in request node or interval setting in interval node) and connections between nodes are shared. [recording (1)]

[Priya_Kumar]

Please check the workflow attached. I don’t have a clue how to send spreadsheet data to HTTP and Airtable.?

{
“nodes”: [
{
“parameters”: {
“triggerTimes”: {
“item”: [
{
“mode”: “everyMinute”
}
]
}
},
“name”: “Cron”,
“type”: “n8n-nodes-base.cron”,
“position”: [
860,
1100
],
“typeVersion”: 1
},
{
“parameters”: {
“functionCode”: “\nconst date = new Date(new Date().setDate(new Date().getDate() - 1)).toISOString().split(“T”)[0].split(”-");\nitem.date = ${date[1]}-${date[2]}-${date[0]};\nreturn item;"
},
“name”: “Get Date”,
“type”: “n8n-nodes-base.functionItem”,
“position”: [
1200,
1100
],
“typeVersion”: 1
},
{
“parameters”: {
“url”: “=https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_daily_reports/{{$json[“date”]}}.csv”,
“responseFormat”: “file”,
“options”: {},
“headerParametersUi”: {
“parameter”: []
},
“queryParametersUi”: {
“parameter”: []
}
},
“name”: “Fetch Script”,
“type”: “n8n-nodes-base.httpRequest”,
“position”: [
1440,
1100
],
“typeVersion”: 1
},
{
“parameters”: {
“options”: {}
},
“name”: “Spreadsheet File2”,
“type”: “n8n-nodes-base.spreadsheetFile”,
“typeVersion”: 1,
“position”: [
1740,
1100
],
“executeOnce”: false
},
{
“parameters”: {
“requestMethod”: “POST”,
“url”: “http://localhost:8080”,
“options”: {},
“bodyParametersUi”: {
“parameter”: [
{
“name”: “={{$node[“Spreadsheet File”].json}}”
}
]
},
“headerParametersUi”: {
“parameter”: [
{
“name”: “Content-Type”,
“value”: “application/json”
}
]
}
},
“name”: “HTTP Request5”,
“type”: “n8n-nodes-base.httpRequest”,
“position”: [
2140,
1100
],
“typeVersion”: 1,
“alwaysOutputData”: false,
“retryOnFail”: true
},
{
“parameters”: {},
“name”: “Airtable”,
“type”: “n8n-nodes-base.airtable”,
“typeVersion”: 1,
“position”: [
2140,
1320
],
“credentials”: {
“airtableApi”: {
“id”: “1”,
“name”: “Airtable account”
}
}
}
],
“connections”: {
“Cron”: {
“main”: [
[
{
“node”: “Get Date”,
“type”: “main”,
“index”: 0
}
]
]
},
“Get Date”: {
“main”: [
[
{
“node”: “Fetch Script”,
“type”: “main”,
“index”: 0
}
]
]
},
“Fetch Script”: {
“main”: [
[
{
“node”: “Spreadsheet File2”,
“type”: “main”,
“index”: 0
}
]
]
},
“Spreadsheet File2”: {
“main”: [
[
{
“node”: “HTTP Request5”,
“type”: “main”,
“index”: 0
},
{
“node”: “Airtable”,
“type”: “main”,
“index”: 0
}
]
]
}
}
}

[Priya_Kumar]

I am able to send the sing json object of the array through HTTP but don’t know how to send a complete JSON array to through HTTP Node

Can someone help here

[Shirobachi]

Sorry for delay, could you please paste workflow following this instruction:

How to share workflow properly

I just would like to show how to share your workflow to other user, so they can easier and faster help you. If you do not use ``` in my case workflow will not become able to paste into n8n interface. Be aware that no credentials or getter data by nodes are shared via that way. Only nodes, their properties (like URL in request node or interval setting in interval node) and connections between nodes are shared. [recording (1)]

I am not able to paste this workflow to my n8n ;/

[Priya_Kumar]

Thanks for your response. I made few changes. Tried paste in another n8n instance it was working. Hope it will work for you now

[Shirobachi]

Hey @Priya_Kumar

I am not sure what you are struggling with, I cannot run filter because of 413 error, but this should work for you

Please let me know if I can help more

[Priya_Kumar]

@Shirobachi

my problem is
spread sheet output is array of JSON with multiple objects but the current workflow makes HTTP post only with first json object. I need guidance how can post complete json array

[Shirobachi]

Check this out, is it what you need?

[Priya_Kumar]

Thanks. I want to multiple calls for each JSON object

example if spread sheet returns below 3 objects then I want to fire 1 HTTP call /Json Object. it means Http call will happen 3 times with {{ $item(“0”).$node[“Spreadsheet File1”].json }} , {{ $item(“1”).$node[“Spreadsheet File1”].json }} and {{ $item(“3”).$node[“Spreadsheet File1”].json }}.

there can be more than 3 objects so it has to make a http call based on the spreadsheet output.

[
{
"Country_Region": "Afghanistan",
"Last_Update": 44563.18115740741,
"Lat": 33.93911,
"Long_": 67.709953,
"Confirmed": 158107,
"Deaths": 7356,
"Combined_Key": "Afghanistan",
"Incident_Rate": 406.1488261213084,
"Case_Fatality_Ratio": 4.652545428096163
},
{
"Country_Region": "Albania",
"Last_Update": 44563.18115740741,
"Lat": 41.1533,
"Long_": 20.1683,
"Confirmed": 210224,
"Deaths": 3217,
"Combined_Key": "Albania",
"Incident_Rate": 7305.024671624156,
"Case_Fatality_Ratio": 1.5302724712687419
},
{
"Country_Region": "Algeria",
"Last_Update": 44563.18115740741,
"Lat": 28.0339,
"Long_": 1.6596,
"Confirmed": 218818,
"Deaths": 6284,
"Combined_Key": "Algeria",
"Incident_Rate": 499.00295416006406,
"Case_Fatality_Ratio": 2.871792996919815
},

[Priya_Kumar]

Solution. I was missing basic knowledge on n8n when I posted this. Each n8n iterates the JSON object if the response comes from the previous node as an object. No need to create the loop.

[Shirobachi]

This is true for most of nodes, happy it’s working!