Hi,
I am pretty experienced in sending and reading data to and from Google sheets incl. appending data line by line.
During the latest project I run into a very strange problem as I can add and update data, but not append in a certain sheet.
What I am trying to do below is adding alltime stats for my PV battery in line 3 using a node with "update" setting. This works flawlessly.
From line 8 onwards I am trying to do the same but per day (one line per day), using the "append" setting. This works, as long as line 8 is empty. If it is not empty, the node just does nothing.
If I am sending the same data using "append" to a different sheet, appending works flawlessly.
I was hoping, that somebody can tell me, what I am doing wrong here. Please find the code below.
[{"id":"2b9eae8bd4a82d59","type":"group","z":"41094396.d2565c","name":"Battery statistics","style":{"label":true},"nodes":["c3982332396295c9","f46589c5582ff467","7b63355d92b3b9cc","7f5d7cd6dc4ba8be","3cf7e6b783a949fa","2a463ebdc57a7c70","49488861ddcf9258","f81b52235144e1bd","e52c61fee0d6d6af","a799afbabb097621","b799209bb9d312a9"],"x":34,"y":739,"w":952,"h":322},{"id":"c3982332396295c9","type":"mqtt in","z":"41094396.d2565c","g":"2b9eae8bd4a82d59","name":"Battery Daily Yield Export","topic":"openWB/housebattery/DailyYieldExportKwh","qos":"2","datatype":"auto-detect","broker":"7a4dd03f.f90ad","nl":false,"rap":true,"rh":0,"inputs":0,"x":170,"y":780,"wires":[["49488861ddcf9258","f81b52235144e1bd"]]},{"id":"f46589c5582ff467","type":"mqtt in","z":"41094396.d2565c","g":"2b9eae8bd4a82d59","name":"Battery Daily Yield Import","topic":"openWB/housebattery/DailyYieldImportKwh","qos":"2","datatype":"auto-detect","broker":"7a4dd03f.f90ad","nl":false,"rap":true,"rh":0,"inputs":0,"x":170,"y":840,"wires":[["49488861ddcf9258","f81b52235144e1bd"]]},{"id":"7b63355d92b3b9cc","type":"mqtt in","z":"41094396.d2565c","g":"2b9eae8bd4a82d59","name":"Battery Lifetime Import","topic":"openWB/housebattery/WhImported","qos":"2","datatype":"auto-detect","broker":"7a4dd03f.f90ad","nl":false,"rap":true,"rh":0,"inputs":0,"x":180,"y":900,"wires":[["49488861ddcf9258","f81b52235144e1bd"]]},{"id":"7f5d7cd6dc4ba8be","type":"mqtt in","z":"41094396.d2565c","g":"2b9eae8bd4a82d59","name":"Battery Lifetime Export","topic":"openWB/housebattery/WhExported","qos":"2","datatype":"auto-detect","broker":"7a4dd03f.f90ad","nl":false,"rap":true,"rh":0,"inputs":0,"x":180,"y":960,"wires":[["49488861ddcf9258","f81b52235144e1bd"]]},{"id":"3cf7e6b783a949fa","type":"debug","z":"41094396.d2565c","g":"2b9eae8bd4a82d59","name":"debug 29","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":820,"y":980,"wires":[]},{"id":"2a463ebdc57a7c70","type":"inject","z":"41094396.d2565c","g":"2b9eae8bd4a82d59","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"59 23 * * *","once":false,"onceDelay":0.1,"topic":"Report","payload":"","payloadType":"date","x":210,"y":1020,"wires":[["f81b52235144e1bd"]]},{"id":"49488861ddcf9258","type":"debug","z":"41094396.d2565c","g":"2b9eae8bd4a82d59","name":"debug 31","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":500,"y":960,"wires":[]},{"id":"f81b52235144e1bd","type":"function","z":"41094396.d2565c","g":"2b9eae8bd4a82d59","name":"Gather statistics data an report","func":"context.data = context.data || new Object();\nvar CurrentDate = new Date().toLocaleString();\nvar LifeTime = new Object();\nvar Daily = new Object();\nvar DailyStats = [];\n\n\nswitch (msg.topic) {\n case \"openWB/housebattery/DailyYieldExportKwh\":\n context.data.DailyYieldExportKwh = parseFloat(msg.payload);\n break;\n\n case \"openWB/housebattery/DailyYieldImportKwh\":\n context.data.DailyYieldImportKwh = parseFloat(msg.payload);\n break;\n\n case \"openWB/housebattery/WhImported\":\n context.data.WhImported = parseInt(msg.payload)/1000;\n break;\n \n case \"openWB/housebattery/WhExported\":\n context.data.WhExported = parseInt(msg.payload)/1000;\n break;\n\n case \"Report\":\n var WhImported = context.data.WhImported;\n var WhExported = context.data.WhExported;\n LifeTime.payload = [{CurrentDate, WhImported,WhExported}];\n\n var DailyImport = context.data.DailyYieldImportKwh;\n var DailyExport = context.data.DailyYieldExportKwh;\n DailyStats[0] = [CurrentDate, DailyImport, DailyExport];\n \n Daily.payload = DailyStats;\n return [LifeTime,Daily];\n break;\n\n default:\n msg = null;\n break;\n\n}\n","outputs":2,"timeout":0,"noerr":0,"initialize":"// Der Code hier wird ausgeführt,\n// wenn der Node gestartet wird\n//context.data.DailyYieldExportKwh = 0;\n//context.data.DailyYieldImportKwh = 0;\n//context.data.WhImported = 0;\n//context.data.WhExported = 0;\n","finalize":"","libs":[],"x":570,"y":880,"wires":[["3cf7e6b783a949fa","e52c61fee0d6d6af"],["3cf7e6b783a949fa","b799209bb9d312a9"]]},{"id":"e52c61fee0d6d6af","type":"google-spreadsheet","z":"41094396.d2565c","g":"2b9eae8bd4a82d59","name":"Write LifeTime Stats","auth":"4ea411a2.fe143","sheet":"1UKvqEo3yGbNzWyS3AA8jb4CNSL0H4-wk_99dpqQC_2U","range":"Batteriestatistik!A3","method":"update","direction":"line","action":"set","clear":false,"line":false,"column":false,"fields":"all","save":"_sheet","selfields":[""],"cell_l":"","cell_c":"","input":"payload","output":"payload","saveType":"global","inputType":"msg","outputType":"msg","sheetType":"str","rangeType":"str","cell_lType":"str","cell_cType":"str","x":860,"y":840,"wires":[[],[]]},{"id":"a799afbabb097621","type":"inject","z":"41094396.d2565c","g":"2b9eae8bd4a82d59","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\"CurrentDate\":\"27.7.2024, 09:52:38\",\"WhImported\":1302.967,\"WhExported\":1275.812}","payloadType":"json","x":570,"y":1020,"wires":[["3cf7e6b783a949fa","b799209bb9d312a9"]]},{"id":"b799209bb9d312a9","type":"google-spreadsheet","z":"41094396.d2565c","g":"2b9eae8bd4a82d59","name":"Write Daily Stats","auth":"4ea411a2.fe143","sheet":"1UKvqEo3yGbNzWyS3AA8jb4CNSL0H4-wk_99dpqQC_2U","range":"Batteriestatistik!A8","method":"append","direction":"line","action":"set","clear":false,"line":false,"column":false,"fields":"all","save":"_sheet1","selfields":[""],"cell_l":"","cell_c":"","input":"payload","output":"payload","saveType":"global","inputType":"msg","outputType":"msg","sheetType":"str","rangeType":"str","cell_lType":"str","cell_cType":"str","x":840,"y":920,"wires":[[],[]]},{"id":"7a4dd03f.f90ad","type":"mqtt-broker","name":"OpenWB","broker":"127.0.0.1","port":"1883","clientid":"","autoConnect":true,"usetls":false,"protocolVersion":"4","keepalive":"60","cleansession":true,"autoUnsubscribe":true,"birthTopic":"","birthQos":"0","birthPayload":"","birthMsg":{},"closeTopic":"","closeQos":"0","closePayload":"","closeMsg":{},"willTopic":"","willQos":"0","willPayload":"","willMsg":{},"sessionExpiry":""},{"id":"4ea411a2.fe143","type":"google-service-account","name":"Energiestatistik","scope":["https://www.googleapis.com/auth/spreadsheets"],"way":"json","check_dialogflow":"","check_speech":""}]
