Problems with MQTT flow after Excel import and msg modifications

[AleXSR700]

Hello everyone,
the original thread where everything started is now closed, so I will continue here

How to create payloads (strings merged/concatenated from array objects) from Excel data?

No $keys() gives an array of all keys of a object,. Unless only one is returned. To force that to an array, [$keys()] would `force a single result to a single array, it would not affect a array with multiple values. To get values of payload $$.payload.* Or if you are in map of an array of objects the context in loop is $ $$.payload.($.property_name) $.* would return an array of all values from the object, in a map of an array of objects.

I have modified and improved on the entire flow with huge help of this community and some discord channels. Big thank you to all who have helped so far!

I have now created the follow flow in which, for some reason, two issues pop up:

1. When I select only one row in my excel, i.e. I change the range to e.g. A3:A3, then the output is undefined

29/04/2023, 11:44:56node: topic
msg.payload : array[1]
[ object ]
29/04/2023, 11:44:56node: ip
msg.payload : array[1]
[ object ]
29/04/2023, 11:44:56node: ip payload
msg.payload : undefined
undefined
29/04/2023, 11:44:56node: ip topic
msg.topic : undefined
undefined
29/04/2023, 11:44:56node: ip payload
msg.payload : undefined
undefined
29/04/2023, 11:44:56node: ip topic
msg.topic : undefined
undefined
29/04/2023, 11:44:57node: ip payload
msg.payload : undefined
undefined
29/04/2023, 11:44:57node: ip topic
msg.topic : undefined
undefined
29/04/2023, 11:44:57node: ip payload
msg.payload : undefined
undefined
29/04/2023, 11:44:57node: ip topic
msg.topic : undefined
undefined

2. If I select multiple rows, e.g. A3:A4, then the output is duplicated

29/04/2023, 11:46:10node: ip
msg.payload : array[2]
[ object, object ]
29/04/2023, 11:46:10node: topic
msg.payload : array[2]
[ object, object ]
29/04/2023, 11:46:10node: ip payload
cmnd/tasmota_02A70A/Backlog : msg.payload : string[45]
"IPAddress1 192.168.0.202; savedata; restart 1"
29/04/2023, 11:46:10node: ip topic
cmnd/tasmota_02A70A/Backlog : msg.topic : string[27]
"cmnd/tasmota_02A70A/Backlog"
29/04/2023, 11:46:10node: ip payload
cmnd/tasmota_02A70A/Backlog : msg.payload : string[45]
"IPAddress1 192.168.0.202; savedata; restart 1"
29/04/2023, 11:46:10node: ip topic
cmnd/tasmota_02A70A/Backlog : msg.topic : string[27]
"cmnd/tasmota_02A70A/Backlog"
29/04/2023, 11:46:11node: ip payload
cmnd/tasmota_228C3D/Backlog : msg.payload : string[45]
"IPAddress1 192.168.0.203; savedata; restart 1"
29/04/2023, 11:46:11node: ip topic
cmnd/tasmota_228C3D/Backlog : msg.topic : string[27]
"cmnd/tasmota_228C3D/Backlog"
29/04/2023, 11:46:11node: ip payload
cmnd/tasmota_228C3D/Backlog : msg.payload : string[45]
"IPAddress1 192.168.0.203; savedata; restart 1"
29/04/2023, 11:46:11node: ip topic
cmnd/tasmota_228C3D/Backlog : msg.topic : string[27]
"cmnd/tasmota_228C3D/Backlog"

Correct would have been

29/04/2023, 11:46:10node: ip
msg.payload : array[2]
[ object, object ]
29/04/2023, 11:46:10node: topic
msg.payload : array[2]
[ object, object ]
29/04/2023, 11:46:10node: ip payload
cmnd/tasmota_02A70A/Backlog : msg.payload : string[45]
"IPAddress1 192.168.0.202; savedata; restart 1"
29/04/2023, 11:46:10node: ip topic
cmnd/tasmota_02A70A/Backlog : msg.topic : string[27]
"cmnd/tasmota_02A70A/Backlog"
29/04/2023, 11:46:11node: ip payload
cmnd/tasmota_228C3D/Backlog : msg.payload : string[45]
"IPAddress1 192.168.0.203; savedata; restart 1"
29/04/2023, 11:46:11node: ip topic
cmnd/tasmota_228C3D/Backlog : msg.topic : string[27]
"cmnd/tasmota_228C3D/Backlog"

Can anybody see what I am doing wrong? I seem to have somehow created an incompatibility which I cannot locate

[{"id":"febe25a17f3bb5df","type":"tab","label":"Flow 2","disabled":false,"info":"","env":[]},{"id":"615139fa2640c38e","type":"file in","z":"febe25a17f3bb5df","name":"Select Excel.xlsx  to read as binary buffer","filename":"C:\\00_AlexanderBaumann\\02_Wohnen\\Home Automation\\IoT_Overview.xlsm","filenameType":"str","format":"","chunk":false,"sendError":false,"allProps":false,"x":780,"y":240,"wires":[["1cda64f2abc632ed"]]},{"id":"1cda64f2abc632ed","type":"book","z":"febe25a17f3bb5df","name":"Workbook","raw":false,"x":1030,"y":240,"wires":[["c64d62601408de1a"]]},{"id":"c64d62601408de1a","type":"sheet","z":"febe25a17f3bb5df","name":"Worksheet \"Tasmota\"","sheetName":"Tasmota","x":1220,"y":240,"wires":[["ded216226abc4114"]]},{"id":"acad3c53061aecb7","type":"sheet-to-json","z":"febe25a17f3bb5df","name":"ip","raw":"false","range":"","header":"A","blankrows":true,"x":1649,"y":400,"wires":[["2f9711e26ceb074e","7e828685296706d6"]]},{"id":"2f9711e26ceb074e","type":"debug","z":"febe25a17f3bb5df","name":"ip","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1829,"y":440,"wires":[]},{"id":"7e828685296706d6","type":"link out","z":"febe25a17f3bb5df","name":"link out ip","mode":"link","links":["ae55786b02c5acbe","faa9dc8028d5a820","9ff9022892a1a75c","f540d60c1c9795cc","207a854257265474","bc3293d4d6e165aa"],"x":1839,"y":400,"wires":[],"l":true},{"id":"e281f7fa2f723087","type":"sheet-to-json","z":"febe25a17f3bb5df","name":"topic","raw":"false","range":"","header":"A","blankrows":true,"x":1649,"y":240,"wires":[["df116f1a5e0c35f3","6871bbea69ab6f09"]]},{"id":"df116f1a5e0c35f3","type":"debug","z":"febe25a17f3bb5df","name":"topic","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1829,"y":280,"wires":[]},{"id":"6871bbea69ab6f09","type":"link out","z":"febe25a17f3bb5df","name":"link out topic","mode":"link","links":["4f99f781213c479a","c87999928e7e1dc8","f51fa24b8e086482","607f46cbc14a07e2","3f7c64842fee66eb","aaa5d29a52c59e38"],"x":1849,"y":240,"wires":[],"l":true},{"id":"971dc5905010b1ff","type":"file in","z":"febe25a17f3bb5df","name":"Select Excel.xlsx  to read as binary buffer","filename":"C:\\00_AlexanderBaumann\\02_Wohnen\\Home Automation\\IoT_Overview.xlsm","filenameType":"str","format":"","chunk":false,"sendError":false,"allProps":false,"x":780,"y":400,"wires":[["b1a9c646c5c5c721"]]},{"id":"b1a9c646c5c5c721","type":"book","z":"febe25a17f3bb5df","name":"Workbook","raw":false,"x":1030,"y":400,"wires":[["dda61baea268a6ae"]]},{"id":"dda61baea268a6ae","type":"sheet","z":"febe25a17f3bb5df","name":"Worksheet \"Tasmota\"","sheetName":"Tasmota","x":1220,"y":400,"wires":[["0a1fd4d6bb6cbae5"]]},{"id":"ded216226abc4114","type":"change","z":"febe25a17f3bb5df","name":"Change Column to G","rules":[{"t":"change","p":"selectRange","pt":"msg","from":"A","fromt":"str","to":"G","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":1460,"y":240,"wires":[["e281f7fa2f723087"]]},{"id":"0a1fd4d6bb6cbae5","type":"change","z":"febe25a17f3bb5df","name":"Change Column to I","rules":[{"t":"change","p":"selectRange","pt":"msg","from":"A","fromt":"str","to":"I","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":1460,"y":400,"wires":[["acad3c53061aecb7"]]},{"id":"c8998becd997cead","type":"switch","z":"febe25a17f3bb5df","name":"Router","property":"setting","propertyType":"msg","rules":[{"t":"nempty"},{"t":"eq","v":"result","vt":"str"},{"t":"eq","v":"ip","vt":"str"},{"t":"eq","v":"template","vt":"str"},{"t":"eq","v":"settings","vt":"str"},{"t":"eq","v":"timers","vt":"str"},{"t":"eq","v":"rules","vt":"str"}],"checkall":"true","repair":false,"outputs":7,"x":360,"y":100,"wires":[["67e2212b80eb63f2"],[],["971dc5905010b1ff"],[],[],[],[]]},{"id":"44af0c7b8e2026f0","type":"change","z":"febe25a17f3bb5df","name":"Cell Range","rules":[{"t":"change","p":"selectRange","pt":"msg","from":"x","fromt":"str","to":"3","tot":"str"},{"t":"change","p":"selectRange","pt":"msg","from":"y","fromt":"str","to":"4","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":220,"y":100,"wires":[["c8998becd997cead"]]},{"id":"65d8f7286d111083","type":"inject","z":"febe25a17f3bb5df","name":"start ip","props":[{"p":"payload"},{"p":"topic","vt":"str"},{"p":"setting","v":"ip","vt":"str"},{"p":"selectRange","v":"Ax:Ay","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":"","topic":"","payload":"","payloadType":"str","x":70,"y":100,"wires":[["44af0c7b8e2026f0"]]},{"id":"aaa5d29a52c59e38","type":"link in","z":"febe25a17f3bb5df","name":"link in topic","links":["013efd06ec318bf9","e843196c4aa6e844","74b1cc4a1f7f173e","43f249b71d98e440","6871bbea69ab6f09"],"x":90,"y":530,"wires":[["53828175b3e77d7e"]],"l":true},{"id":"bc3293d4d6e165aa","type":"link in","z":"febe25a17f3bb5df","name":"link in ip","links":["505668f692e2a4a3","0cbba96f0e71caa3","7e828685296706d6"],"x":90,"y":620,"wires":[["cbb948607bf7300e"]],"l":true},{"id":"65c6ad1ad8596053","type":"mqtt out","z":"febe25a17f3bb5df","name":"MQTT ip","topic":"","qos":"0","retain":"false","respTopic":"","contentType":"","userProps":"","correl":"","expiry":"","broker":"0d7f426189a0c8d3","x":1880,"y":570,"wires":[]},{"id":"7ab9247c51dd3881","type":"debug","z":"febe25a17f3bb5df","name":"ip payload","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1890,"y":620,"wires":[]},{"id":"6183dad489dc8bc3","type":"debug","z":"febe25a17f3bb5df","name":"ip topic","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"topic","targetType":"msg","statusVal":"","statusType":"auto","x":1880,"y":660,"wires":[]},{"id":"fb1bf3e9349c81a4","type":"change","z":"febe25a17f3bb5df","name":"change topic and payload (string array)","rules":[{"t":"set","p":"topic","pt":"msg","to":"$keys($$.payload[0])","tot":"jsonata"},{"t":"set","p":"payload","pt":"msg","to":"$$.payload.*","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":650,"y":620,"wires":[["22bf21ad565d5d4b"]]},{"id":"22bf21ad565d5d4b","type":"join","z":"febe25a17f3bb5df","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":870,"y":620,"wires":[["2503c6354184c8a2"]]},{"id":"2503c6354184c8a2","type":"change","z":"febe25a17f3bb5df","name":"add \"/Backlog\"","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload.topic#$i.{\"topic\": $ & \"/Backlog\", \"ip\": $$.payload.ip[$i]}","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":1020,"y":620,"wires":[["f23ca99667d01beb"]]},{"id":"f23ca99667d01beb","type":"split","z":"febe25a17f3bb5df","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":1170,"y":620,"wires":[["73a32f630f65b873"]]},{"id":"73a32f630f65b873","type":"change","z":"febe25a17f3bb5df","name":"move payloads to topic and payload","rules":[{"t":"set","p":"topic","pt":"msg","to":"payload.topic","tot":"msg"},{"t":"move","p":"payload.ip","pt":"msg","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":1380,"y":620,"wires":[["8698c3ae4e1c4790"]]},{"id":"8698c3ae4e1c4790","type":"delay","z":"febe25a17f3bb5df","name":"rate limit (prevent flooding)","pauseType":"rate","timeout":"1","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"allowrate":false,"outputs":1,"x":1660,"y":620,"wires":[["7ab9247c51dd3881","6183dad489dc8bc3","65c6ad1ad8596053"]]},{"id":"cbb948607bf7300e","type":"function","z":"febe25a17f3bb5df","name":"Change key to ip","func":"msg.payload = msg.payload.map((obj) => {\n    const o = Object.values(obj);\n    return { \"ip\": o[0] };\n});\nreturn msg;\n\n//in this output it's an array that contains two indexes and each index contains an object with the key \"IPAddress1 192.168.0.201; savedata; restart 1\"\n\n//alternative:\n        // loop over the array\n//msg.payload = msg.payload.map((obj) => {\n        // split the object in an array of key/value pairs\n//    const o = Object.entries(obj);\n        // assign the value to \"IP\" key\n//    return { \"IP\": o[0][1] };\n//});\n\n//return msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":380,"y":620,"wires":[["fb1bf3e9349c81a4"]]},{"id":"53828175b3e77d7e","type":"function","z":"febe25a17f3bb5df","name":"Change key to topic","func":"msg.payload = msg.payload.map((obj) => {\n    const o = Object.values(obj);\n    return { \"topic\": o[0] };\n});\nreturn msg;\n\n//in this output it's an array that contains two indexes and each index contains an object with the key \"IPAddress1 192.168.0.201; savedata; restart 1\"\n\n//alternative:\n        // loop over the array\n//msg.payload = msg.payload.map((obj) => {\n        // split the object in an array of key/value pairs\n//    const o = Object.entries(obj);\n        // assign the value to \"IP\" key\n//    return { \"IP\": o[0][1] };\n//});\n\n//return msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":270,"y":530,"wires":[["fb1bf3e9349c81a4"]]},{"id":"5f55046615018acf","type":"inject","z":"febe25a17f3bb5df","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"},{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"topic2","payload":"pay2","payloadType":"str","x":120,"y":290,"wires":[[]]},{"id":"67e2212b80eb63f2","type":"rbe","z":"febe25a17f3bb5df","name":"","func":"rbe","gap":"","start":"","inout":"out","septopics":true,"property":"payload","topi":"topic","x":550,"y":90,"wires":[["615139fa2640c38e"]]},{"id":"0d7f426189a0c8d3","type":"mqtt-broker","name":"MQTT Home Assistant","broker":"192.168.0.2","port":"1883","clientid":"","autoConnect":true,"usetls":false,"protocolVersion":"4","keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","birthMsg":{},"closeTopic":"","closeQos":"0","closePayload":"","closeMsg":{},"willTopic":"","willQos":"0","willPayload":"","willMsg":{},"userProps":"","sessionExpiry":""}]

[E1cid]

I suspect it is one of the JSONata expressions, as explained in your OG thread you may need to force an array

@How to create payloads (strings merged/concatenated from array objects) from Excel data?

Unless only one is returned. To force that to an array, [$keys()] would `force a single result to a single array, it would not affect a array with multiple values.

Please supply the data going into change key and ip functions so i can test.

[AleXSR700]

When selecting two rows:

29/04/2023, 13:06:22node: topic
msg.payload : array[2]
array[2]
0: object
G: "cmnd/tasmota_02A70A"
1: object
G: "cmnd/tasmota_228C3D"
29/04/2023, 13:06:22node: ip
msg.payload : array[2]
array[2]
0: object
I: "IPAddress1 192.168.0.202; savedata; restart 1"
1: object
I: "IPAddress1 192.168.0.203; savedata; restart 1"

When selecting one row:

29/04/2023, 13:07:24node: ip
msg.payload : array[1]
array[1]
0: object
I: "IPAddress1 192.168.0.202; savedata; restart 1"
29/04/2023, 13:07:24node: topic
msg.payload : array[1]
array[1]
0: object
G: "cmnd/tasmota_02A70A"

[E1cid]

I aked for the data going into the two nodes, what you provided is useless to me. Please provide the data required, add debugs set to complete message object, and use the copy value button/icon that appears by the msg object name when you hover the mouse

[AleXSR700]

Apologies. This is what comes out of the link-in nodes when only one row:

{"_msgid":"ed0faf9234c9c4fd","payload":[{"I":"IPAddress1 192.168.0.202; savedata; restart 1"}],"topic":"","setting":"ip","filename":"C:\\00_Alex\\02_Wohnen\\Home Automation\\IoT_Overview.xlsm","selectedSheetName":"Tasmota","selectedRange":"I3:I3"}

and

{"_msgid":"ed0faf9234c9c4fd","payload":[{"G":"cmnd/tasmota_02A70A"}],"topic":"","setting":"ip","filename":"C:\\00_Alex\\02_Wohnen\\Home Automation\\IoT_Overview.xlsm","selectedSheetName":"Tasmota","selectedRange":"G3:G3"}

And this for two rows:

{"_msgid":"bcce71e518cd8403","payload":[{"G":"cmnd/tasmota_02A70A"},{"G":"cmnd/tasmota_228C3D"}],"topic":"","setting":"ip","filename":"C:\\00_Alex\\02_Wohnen\\Home Automation\\IoT_Overview.xlsm","selectedSheetName":"Tasmota","selectedRange":"G3:G4"}

and

{"_msgid":"bcce71e518cd8403","payload":[{"I":"IPAddress1 192.168.0.202; savedata; restart 1"},{"I":"IPAddress1 192.168.0.203; savedata; restart 1"}],"topic":"","setting":"ip","filename":"C:\\00_Alex\\02_Wohnen\\Home Automation\\IoT_Overview.xlsm","selectedSheetName":"Tasmota","selectedRange":"I3:I4"}

[E1cid]

As i suspected the add /backlog needs to force an array
e.g.

payload.topic#$i.{"topic": $ & "/Backlog", "ip": $$.payload.ip[$i]}[]

[AleXSR700]

Thank you so much!
That was the final piece of the puzzle!

Is it necessary to force an array because of the split afterwards? Or which node is not compatible unless it receives an array of objects?

[E1cid]

Yes, as you want to split as array. When JSONata returns a single item it does not return it as an array unless you tell it to. The split node would receive an object not an array when a singleton is returned