Thanks for the PM Jason, let me share here in case anyone else has the issue,
For anyone having trouble with formats propagating,
Firstly, know that in theory the Google Apps Script (GAS) in your spreadsheet is supposed to copy formats down to newly-appended lines. So make sure that your bottom line has the format you want. (Only this matters… the top of your columns can be different from data, etc. Only the format of the bottom-most cells (not the column per se) matters for the format of newly-added rows.)
If that doesn’t solve the issue, and you seriously want to resolve it, then see this message about how to modify your GAS code to make sure numeric formats propagate the way you want.
That should solve it.
Slight word of warning: Once you have a really big Log (a million cells) that takes a while to load, sometimes the act of loading it interferes with propagating formats (presumably because it stays locked up a full minute loading and an output row came then). So once your spreadsheet becomes huge, occasionally format propagation “randomly” stops. What really happened is that format copy bombed at the first bad-format row, then the next ones did not have the right formats to copy. The solution is just to copy the right format down manually, and the script will then resume copying the right format from the bottom row.
Also keep in mind the hard 2 million cell limit: You must create a copy of your Google sheet, then empty it, before hitting the limit. No Google sheet can hold more than 2 M cells; there is NO way around this for ANYONE (and empty cells count!). There is no warning in the CSchwer log… you will simply stop adding more data (and not know it). I have a hundred columns and have to copy my data out every few months; I set up an electronic calendar reminder. Then I concatenate new quarters in a big Excel sheet. Sad but true, but then… free Google Sheets and ST data logging is still super cool overall.
Finally got everything working. The only thing that solved my errors was to create a brand new sheet. Even copying my master sheet did not fix the problem, there most of been some sort of internal error, I can’t explain it. But wanted to thank you again, Master sheet is up and running and feeding into my 12 graphs again. Thank you!!!
So another update on this. It seems that logging on 1 min is causing this. I put my old sheet to 10min logging. Let it run for an hour and then put it back to 1 min and the IDE errors are now gone and the time logging is back to normal. So if you are having this issue it might be worth a try to do what I did.
Okay well mine is at 10 minutes, but I have a lot more devices. Maybe it is the amount of time per device that is the problem (too many devices per time period). When you had one device but a very short time, it kept tripping up.
Just thinking out loud. It never used to be a problem.
I see my hub is still firmware version 17.12 and it has been since April 7, so that’s probably not the issue.
In two days (start of July) I am copying out my spreadsheet and emptying it since it’s almost full (2M cells). I will see if that helps. It takes a long time to open it these days; a couple of minutes. Maybe this is what is causing me to time out (the time versus number of devices idea).
Thanks for keeping in touch! Let me know if you see it again at 10 minutes. Or if it goes away at 1 minute.
Here is one thing I noticed. So I have my old sheet and new sheet. My old sheet I set to 10 min and just left it. Don’t want to delete it for now. I was looking this morning and noticed it is certainly logging. However, it only is putting up one log in the sheet every 10 min. I watched it log maybe 15 entries in the queue, but when the queue processed to google sheets only 1 line is being added every 10 min. I figured it would log anything that was put into the queue of the 10 min span. I also have simple device logger installed and that app will log all entries over the time span.
That’s by design. Consider the CSchwer log a summary (by 10 minute intervals). SDL is a hitman, every data point. (For things you sign up for.) I used CSchwer for all my sensors. Then only use SDL for a few I want to know every single thing about.
If CSchwer was not at regular intervals, it is a real mess to make sense of when reviewing a large dataset. Because every single record could have greatly different timestamps, like SDL does.
Does anyone know how to create a column that sums a days worth of values? So it would show a sum for every date. Would probably need to be in it’s own sheet. I have the KWH coming in from my energy monitor and would like to figure out the usage per day and possibly chart that.
I use this app to log to Google Sheets, then I have two graphs that auto-update and display on multiple SmartTiles / ActionTiles dashboards - one for temperatures and one for energy utilization.
An unfortunate bug in Google Sheets breaks the graphs (they stop updating) when the columns aren’t all contiguous (next to each other in the range) and when there’s a “break” in the data (a blank row, caused by no sensor update within the elapsed time).
I fixed it by repeating the “Time” column next to each range of data I want to graph, so I have the initial “Time” on the left in column A, and then I repeated that column at the right in column “M”.
But of course only the first column would update with the Time, so by commenting out the single line in the code that says “break;” the program will fill in multiple columns of the same name, so just adding a column and giving it the heading of “Time” it now will give you multiple updated columns.
This fixes (or works around) the Google Sheets bug and my graphs all update automatically now.
Yes, you can zoom in on the graph tiles to see them in detail, it works great. Especially now that the graphs are updating consistently!
Here’s a shot of the media tiles (notice the cameras are not working now, Chrome must have updated and now completely disallows URLs with user:pass format.)
