Hello,
I have used the google spreadsheet api get access a public spreadsheet.
I have filtered the data so far but I am not able to format the data properly in order to push the data into my personal google spreadsheet.
How can I do this?
Please check the following screenshot in order to understand my current format:
Thank you in advance!
What you want to do? Can you explain a bit.
Thank you for the reply.
My goal is to paste the rows and columns (from the screenshot) into my private google sheet.
However, I am struggling with how to do this…
Kind regards
Silas
Hi @Silas_Hundhausen, from looking at the data structure from your screenshot, this will almost certainly require custom JS through the Function node.
Since the data structure is somewhat nested, maybe you could copy the actual JSON code in here rather than a screenshot (of course you can replace the actual values with some dummy text)? Otherwise people trying to help with the transformation would have to retype all the keys from the screenshot manually and get everything right.
Also, did you run into any trouble with the existing Google Sheets node? This node would return a data structure that’s much easier to process with n8n than the raw HTTP response from the Google Sheets API.
Thanks for reply.
Attached you can find the JSON-Structure as requested (shortened output).
My main goal is to access a public google sheet (Read only permission) and upload the data into my database. I have tried to use the google sheets node with the “service account” but I didn’t receive output. Do you have a better solution?
[
{
"sheets": [
{
"data": [
{
"rowData": [
{},
{
"values": [
{
"formattedValue": "##### Text #####"
},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{
"formattedValue": "##### Text #####"
}
]
},
{
"values": [
{},
{
"formattedValue": "##### Text #####"
},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{
"formattedValue": "##### Text #####"
}
]
},
{
"values": [
{
"formattedValue": "##### Text #####"
},
{
"formattedValue": "##### Text #####"
},
{
"formattedValue": "##### Text #####"
},
{
"formattedValue": "##### Text #####"
},
{
"formattedValue": "##### Text #####"
},
{
"formattedValue": "##### Text #####"
},
{
"formattedValue": "##### Text #####"
},
{
"formattedValue": "##### Text #####"
},
{
"formattedValue": "##### Text #####"
},
{
"formattedValue": "##### Text #####"
},
{
"formattedValue": "##### Text #####"
},
{
"formattedValue": "##### Text #####"
},
{
"formattedValue": "##### Text #####"
},
{
"formattedValue": "##### Text #####"
},
{
"formattedValue": "##### Text #####"
},
{
"formattedValue": "##### Text #####"
},
{
"formattedValue": "##### Text #####"
},
{
"formattedValue": "##### Text #####"
},
{
"formattedValue": "##### Text #####"
},
{
"formattedValue": "##### Text #####"
}
]
}
]
}
]
}
]
}
]
Hi @Silas_Hundhausen, many thanks for sharing!
Here is an example workflow setting your data in the initial Function node (you can ignore that, it’s just to simulate your HTTP Request node). It then uses the Split Items node to convert the rowData into individual items before using an IF node to check if the respective item has a values property (and thus excluding empty rows). It then uses a Function Item node to split up the values field into individual fields, one per column like so:
return {
"Column A": item.values[0].formattedValue,
"Column B": item.values[1].formattedValue,
"Column C": item.values[2].formattedValue,
"Column D": item.values[3].formattedValue,
"Column E": item.values[4].formattedValue,
"Column F": item.values[5].formattedValue
};
I assume you know the actual column names (or have own ones you want to use), so you might want to replace Column A, Column B etc. with these. Also these are just the first six columns, but you can add more columns as required. The entire workflow looks like so:
And here’s the JSON data which you can copy into your own n8n instance:
Hope this helps! Let me know if you have any questions on this 
Thank you very much!!! It works
