Good morning,
Is anyone able to help me with the following issue?
This is basically the flow
This is the function used to iterate the results
const results = []
for (const item of items) {
results.push.apply(results, item.json.reports)
}
const data = results.map(element => ({ json: element }))
return data;
which works and shows me the data I want and the following in the execution preview window
but in google sheets I’m getting this
Is anyone able to help?
Thanks in advance.
What you can do is to manually JSON.stringify each of the values beneath json. It should then work fine.
The Google Sheet Node did not get created to handle that but it should not be to complicated to add that natively in a future version.
I’m afraid i have no idea how to code that, I usually use integromat that does all this in the backgrounds for me.
Are you able to show me the code and where i need to add it to resolve this?
Be great to know if you can add support to google sheets in the future 
Hey @RedPacketSec,
You can use the following code snippet to convert the JSON into a string.
const results = []
for (const item of items) {
results.push.apply(results, item.json.reports)
}
const data = results.map(element => ({ json: JSON.stringify(element)}))
return data;
Another approach is to use the Set node use the JSON.stringify() method for all the items. Here’s an example for that:
{
"nodes": [
{
"parameters": {
"keepOnlySet": true,
"values": {
"string": [
{
"name": "myVariable",
"value": "={{JSON.stringify($json[\"myVariable\"])}}"
}
]
},
"options": {}
},
"name": "Set",
"type": "n8n-nodes-base.set",
"typeVersion": 1,
"position": [
1406,
295
]
}
],
"connections": {}
}
Ah yes, we have to return an object but we are returning a string. Without replicating the output it is a bit difficult for me to provide a solution that can be used in the Function node. Did you try with the other solution that involves using the Set node?
To build on top of your above code, something like that should work:
const results = []
for (const item of items) {
results.push.apply(results, item.json.reports)
}
return results.map(element => {
const json = {};
for (const key of Object.keys(element)) {
json[key] = typeof element[key] === 'object' ? JSON.stringify(element[key]) : element[key];
}
return { json };
})
ill give it a go
I now get the following instead of [object], [object].
[{“id”:“111”,“name”:“TAGRESULT1”},{“id”:“222”,“name”:“TAGRESULT2”},{“id”:“333”,“name”:“TAGRESULT3”},{“id”:“444”,“name”:“TAGRESULT4”},{“id”:“555”,“name”:“TAGRESULT5”}]
not just the text e.g
TAGRESULT2
TAGRESULT3
TAGRESULT4
etc
Yes, that is the only way we can code an example without knowing how your data looks like and how you want it to be transformed. The only example you had above showed only some numbers and was so totally different to what you post now.
Considering that the format is probably different for each row (and you seem to have very many different ones) I do have to suggest hiring someone who can program JavaScript to take care of that part for you.
If I could afford to pay someone to do the work, I wouldn’t be here struggling to get it done. I do appreciate all the help you have given so far. I won’t bug you again.
Regards
In this case, is it sadly very hard for us to help you. The reason is that you have a complex task to solve but the provided data is incomplete which makes it impossible for us to do so. The only way I see forward, is to provide you one more generic example that you can then change and extend.
Hope it is helpful!
Here the code of the Function-Node:
const results = []
for (const item of items) {
results.push.apply(results, item.json.reports)
}
return results.map(element => {
const json = {};
for (const key of Object.keys(element)) {
// Assume that is one thing you want to do. to have instead of "[1,2,3]" just "1, 2, 3"
if (key === 'classification') {
json[key] = element[key].join(', ');
continue;
}
// Replace "noIdea" with the actual name of the row from your previous example
if (key === 'noIdea') {
json[key] = element[key].map(data => data.name).join(', ');
continue;
}
// You can add copy the if`s and then change the data accordingly
// All others that are not specifically set will fall back to the default logic
json[key] = typeof element[key] === 'object' ? JSON.stringify(element[key]) : element[key];
}
return { json };
})
Here the example workflow with the mocked data:
{
"nodes": [
{
"parameters": {
"functionCode": "items[0].json.reports = [\n {\n justString: \"myString\",\n justNumber: 3,\n classification: [1,2,3],\n noIdea: [{\"id\":\"111\",\"name\":\"TAGRESULT1\"},{\"id\":\"222\",\"name\":\"TAGRESULT2\"},{\"id\":\"333\",\"name\":\"TAGRESULT3\"},{\"id\":\"444\",\"name\":\"TAGRESULT4\"},{\"id\":\"555\",\"name\":\"TAGRESULT5\"}]\n }\n];\nreturn items;"
},
"name": "Mock Data",
"type": "n8n-nodes-base.function",
"typeVersion": 1,
"position": [
450,
300
]
},
{
"parameters": {
"functionCode": "const results = []\nfor (const item of items) {\n results.push.apply(results, item.json.reports)\n}\n\nreturn results.map(element => {\n const json = {};\n for (const key of Object.keys(element)) {\n // Assume that is one thing you want to do. to have instead of \"[1,2,3]\" just \"1, 2, 3\"\n if (key === 'classification') {\n json[key] = element[key].join(', ');\n continue;\n }\n // Replace \"noIdea\" with the actual name of the row from your previous example\n if (key === 'noIdea') {\n json[key] = element[key].map(data => data.name).join(', ');\n continue;\n }\n // You can add copy the if`s and then change the data accordingly \n \n // All others that are not specifically set will fall back to the default logic\n json[key] = typeof element[key] === 'object' ? JSON.stringify(element[key]) : element[key];\n }\n return { json };\n})\n"
},
"name": "Transform",
"type": "n8n-nodes-base.function",
"typeVersion": 1,
"position": [
650,
300
]
}
],
"connections": {
"Mock Data": {
"main": [
[
{
"node": "Transform",
"type": "main",
"index": 0
}
]
]
}
}
}
Btw. obviously still happy to help if you get stuck! Just make sure to provide all the required data that we are able to do so.
yeah sort of. thanks for your help.
Glad to hear!
If there is a an object with a data structure you want to convert but you can not figure out yourself, post an example of it here (how data looks now and how you want it to look like), we should then be able to to help you to create it.
Have fun!