How to put the list data into new line in the same cell of excel

General
1

Hi,

I have this below flow which takes the data and puts into excel and sends it as an attachment.

[{"id":"75213df52c14c49d","type":"tab","label":"Flow 1","disabled":false,"info":"","env":[]},{"id":"5d64f6b3253b286e","type":"function","z":"75213df52c14c49d","name":"","func":"msg.payload=\n    [{\n    \"No1\": \"101010101\", \n    \"No2\":\"236482634763\", \n    \"No3\": \"324523623\",\n    \"No4\" : [\"11\",\"22\",\"32\",\"16\",\"102\"],\n    \"No5\": [\"15\",\"236\",\"362\",\"87\"]\n    }]\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":120,"y":300,"wires":[["2155bee091949ab6","d5bc86d05379693c"]]},{"id":"2155bee091949ab6","type":"nanoid","z":"75213df52c14c49d","name":"","idLength":8,"property":"filename","propertyType":"msg","x":260,"y":300,"wires":[["185a201b05e8d807"]]},{"id":"185a201b05e8d807","type":"change","z":"75213df52c14c49d","name":"","rules":[{"t":"set","p":"filepath","pt":"msg","to":"'/tmp/'&filename&'.xlsx'","tot":"jsonata"},{"t":"set","p":"filename","pt":"msg","to":"filepath","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":420,"y":300,"wires":[["e1f1cd60f8aea8fb"]]},{"id":"e1f1cd60f8aea8fb","type":"excel","z":"75213df52c14c49d","name":"","file":"/tmp/Details.xlsx","x":570,"y":300,"wires":[["5ac652da600e84dc"]]},{"id":"5ac652da600e84dc","type":"file in","z":"75213df52c14c49d","name":"","filename":"","format":"","chunk":false,"sendError":false,"encoding":"none","allProps":false,"x":700,"y":300,"wires":[["1d4f39e4ef111f74"]]},{"id":"1d4f39e4ef111f74","type":"base64","z":"75213df52c14c49d","name":"","action":"str","property":"payload","x":860,"y":300,"wires":[["b79bb9dd8d53aae6"]]},{"id":"b79bb9dd8d53aae6","type":"template","z":"75213df52c14c49d","name":"Email body","field":"payload","fieldType":"msg","format":"json","syntax":"mustache","template":"{\n    \"message\": {\n        \"subject\": \"Welcome letter\",\n        \"body\": {\n            \"content\": \"Hello\",\n            \"contentType\": \"html\"\n        },\n        \"toRecipients\": [\n            {\n                \"emailAddress\": {\n                    \"address\": \"neeta.jadhav@global.ntt\",\n                    \"name\": \"Neeta Jadhav\"\n                }\n            }\n        ],\n        \"attachments\": [\n            {\n                \"@odata.type\": \"#microsoft.graph.fileAttachment\",\n                \"name\": \"details.xlsx\",\n                \"contentBytes\": \"{{{payload}}}\"\n            }\n        ]\n    }\n}","output":"json","x":410,"y":380,"wires":[["462c7e725b1ab173","034229d7d7c23b8d"]]},{"id":"462c7e725b1ab173","type":"http request","z":"75213df52c14c49d","name":"Post to Microsoft Graph API","method":"POST","ret":"txt","paytoqs":"ignore","url":"https://graph.microsoft.com/v1.0/me/sendMail","tls":"","persist":false,"proxy":"","authType":"","senderr":false,"x":640,"y":380,"wires":[["e043a8183ed4cb47","fa05ace26c752e27"]]},{"id":"e043a8183ed4cb47","type":"exec","z":"75213df52c14c49d","command":"rm ","addpay":"filepath","append":"","useSpawn":"false","timer":"","winHide":false,"oldrc":false,"name":"","x":830,"y":380,"wires":[["98157b38449d41c6"],[],[]]},{"id":"98157b38449d41c6","type":"debug","z":"75213df52c14c49d","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":970,"y":380,"wires":[]},{"id":"b6a27e12b4f50d4f","type":"inject","z":"75213df52c14c49d","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":100,"y":200,"wires":[["4898ccf62d6b7603"]]},{"id":"d5bc86d05379693c","type":"debug","z":"75213df52c14c49d","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":190,"y":380,"wires":[]},{"id":"034229d7d7c23b8d","type":"debug","z":"75213df52c14c49d","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":530,"y":440,"wires":[]},{"id":"fa05ace26c752e27","type":"debug","z":"75213df52c14c49d","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":810,"y":440,"wires":[]},{"id":"4898ccf62d6b7603","type":"http request","z":"75213df52c14c49d","name":"Get Email Token","method":"GET","ret":"obj","paytoqs":"ignore","url":"http://maplet-authrzr-0-1656663433.a927252e-fb71-472b-ae58-dce6bfd35690.svc:80/token/email","tls":"","persist":false,"proxy":"","authType":"","senderr":false,"x":460,"y":180,"wires":[["efa8e7a3cdc63b37"]]},{"id":"efa8e7a3cdc63b37","type":"change","z":"75213df52c14c49d","name":"Set headers","rules":[{"t":"set","p":"headers","pt":"msg","to":"{}","tot":"json"},{"t":"set","p":"headers['Authorization']","pt":"msg","to":"'Bearer ' & payload.accessToken","tot":"jsonata"},{"t":"set","p":"headers['Content-Type']","pt":"msg","to":"application/json","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":670,"y":180,"wires":[["5d64f6b3253b286e"]]}]

Output is below

image
image827×214 7.94 KB

But expectation is to put the list in numbers in new line

image

Kindly help me on this topic.
Hope I have provided all the required data.

Thanks,
Neeta

2

Have you tried sending a string with newlines in
e.g.

msg.payload=
    [{
    "No1": "101010101", 
    "No2":"236482634763", 
    "No3": "324523623",
    "No4" : "11,\n22,\n32,\n16,\n102",
    "No5": ["15","236","362","87"]
    }]
return msg;
3

I have a dynamic data from api and now it works when I use .split(',').join('\n').
Is it possible to put the data in new cell instead of same cell? as shown below

image

4

Yes, but more complicated, you would need to generate a array element per spreadsheet row. After first row you would need to skip the no1,no2,no3. and no5 you would need to skip row 5.
so you need your function to work out longest array, then use that array to loop and create rows skipping as above or if value is undefined. Hard to really say as we know nothing about your api return, or id no4 and no5 are variable in length.

5

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.