The Sheet logging has been great. Purely because of this, I got 4 more Aeon Multisensor 6s (on top of my 3 current environment sensors) so I can log house conditions real well. Suddenly my SmartThings is a real workhorse friend, instead of being a problematic novelty. In the future I will pair sensor data with Efergy 1-minute electricity logging of every breaker in my house to get a full energy/heat/cooling view.
Here, I’m going to post a number of small requests and questions. Not a single one is critical, so John, Kevin, and others, you can ignore all of this if busy. But if you get time or it’s easy, please go for it. I sort of experimented with doing some of this myself but found there’s so much going on, I’m not sure what’s what… some things simply stopped reporting, etc., until I revert.
Without further ado,
#1. INSERT ROWS, don’t overwite them:
Is it possible to have a new row be inserted instead of overwriting the next blank row? So we don’t have to keep adding new lines to Google Sheets for fear of running out.
One would hope there’s a way around this micromanagement… plus its hidden potential for disaster. I would like to collect years of data to make multi-year energy use assessment. I don’t want to find out two years from now, when it’s all very s.o.p., that it stopped logging a month ago because I forgot to do my quartlerly log in-to add rows.
You might say “just add 40k rows already” or whatever, but that makes the Sheet unwieldy for scrolling… now you have a vast empty tracts and your actual data is only a tiny part of the sheet.
So, can’t it simply add a new row to the sheet instead of overwriting an existing one?
#2. Optionally, OVERWRITE LAST ROW:
This would be an option which contradicts #1, for those who want it…
This could help Stephen, when he only wanted a Row 2, forever. But it could be a little more sophisticated. If it could be turned off or on, a person could sometimes log, and sometimes not (just show the most recent report). Maybe even depending on conditions, like, for certain times of day, if some condition is met, or every 20th report, capture 5 in a row.
This would be optional because the current approach (adding, not overwriting) is the best default. You could put a note in the code of how to turn it on or off, if it’s hard to make into an actual app switch.
This is not for me, but for Stephen and others who might want it.
#3. INSERT ROWS FROM TOP:
Enough scrolling to the bottom of long spreadsheets. Insert new rows up top (at Row 2), pushing other data down, so you always see your latest info easily when opening the Sheet. Plus, Sheets doesn’t have Excel’s nice ability to simply hit End and then Down to zoom to the end of your data. Once there are many thousands of data lines, checking your latest data is a real pain.
You’re welcome to make this optional if you want. But I personally would want it as my default.
#4. KEEP NUMERIC FORMATTING:
When a new row is posted, it keeps the centering format of a column (left, right, center) but does not keep number/date/time format of the column. Is there some way to keep it either by not having the new row impose its own format, or by copying row format prior to posting the new row, then painting that format back onto the new row once it is copied in?
You can see how some of the above elements could be combined. If you insert rows from top, you may have to play a bit to keep formatting of the data rows (Row 2 and onward) and not the header row (Row 1). My header rows are formatted different from my data rows. This can be gotten around by: 1) Copy Row 2. 2) Insert a copy of Row 2 at Row 2 (pushing down the rest of data; Row 2 data is now also on Row 3). 3) Delete data (but not format) from Row 2. 4) Copy in new data on Row 2. 5) Paint original format over it (if needed). And 6) you’re done.
#5. RENAME SENSORS?
So the spreadsheet columns aren’t so wide, I would like to shorten, e.g., these three names:
temperature to temp or temp.
illuminance to illum or illum.
humidity to hum. or RH%
This isn’t an issue that wrapping can help with, not the way I like to do it. I have short sensor names like “AMS4” and everything can be nice and compact with narrow columns, if I wrap (e.g., “AMS4” on first line, “temp” on second one) and can get the names of the data types short. Then I can see a ton of data on my PC screen at a glance. At this point I have 7 temperature and 5 humidity sensors, and about 30 others (and more will be coming).
I only see these particular words in one place of the Sheets logging code but if I change it there, that data type stops being reported. So obviously these particular words must essentially be keywords that are important elsewhere.
I see a couple of lines like
def keyId = URLEncoder.encode(evt.displayName.trim()+ " " +evt.name)
that probably formulate the column headers. It occurred to me to change these both with some IF/THEN sort of statements based on the three names. Sort of fooling the code after its main work is done; just changing final output steps. But the language is a little tricky and I will probably mess it up.
This one is kind of a nicety for me, but maybe others would like it, too. You might set up an IF/THEN example of how to do it in the code (where appropriate), and comments on how to do it for more, if you want. (Have it show them how to change something without changing it - change “temperature” to “temperature”.) Then people can readily see how to change names however they might want.
#6. LOG HUB UP AND DOWN:
I would love to log some sort of feedback on whether my hub is up or down. For long term data collection, this is important in many ways. If you have big gaps in your data (which you only see reviewing months later), you will know if this was the problem. From my house monitoring standpoint across years, it’s also a surrogate for how much my power and/or internet has been down. Very important for troubleshooting or understanding data gaps long after the fact. And simply good to know all by itself - what’s my true hub/net/power up time, past all the many many factors that can mess with it?
I have no idea how this would be done. But I know I get a phone notification if my hub goes down, and when it comes back up. I don’t know who is doing what, where, for much of this scripting. Is the Sheets script running in Google’s cloud? Where’s the SmartThings app; on my phone or on ST servers? Are my phone notices on a purely local wifi basis??
Anyway, it would be great to log hub up/down. As its own entirely separate script and log, if necessary.
Can you capture the phone notices? Or would it have to poll the hub constantly? Still, I’d want this if that’s all there is. Perhaps polling at one-minute intervals. You wouldn’t want to log every up minute, but if you log it whenever it’s down (not responding), plus say when was the previous one or two timestamps it was down, you have some error correction there. (If one log entry is somehow lost, it’ll get covered by the next one.) I don’t like systems or logs that don’t have any indicator of data being lost… I don’t like systems that assume everything will otherwise work okay. Because in the real world, it doesn’t. Especially for me since I’m cursed, lol.
And something even more different:
#7. Just an explainer on what is going on where, for the scripts. For us newbs.
I have noticed that the timestamp going into the log is not the current time, it is the time minus the requested interval. (If I ask for 15-minute intervals, it will be a timestamp from 15 minutes ago.) So I just learned new values always replace old ones, which John just said for the motion sensor. So if I have selected “15 minutes” but something is changing fast (like my whole house electricity monitor; kWh are constantly increasing) what I’m actually seeing isn’t data from the time of the timestamp, it is actually more like data from timestamp + 15 minutes. Similar things happen to all other data, all dependent on how frequently they trigger (if it’s often less than the reporting interval).
Where is the Google Sheets script running? Is it actively sending requests for data, or passively accumulating something sent up by SmartThings?
Is the SmartThings apps on the phone or a ST server…
What kinds of things can go down or cause problems in the data, past my sensors and hub?
I have noticed that when I selected 15 minute intervals, it was actually reported 18 minutes apart about 10% of the time, and 30 minutes apart 1% of the time. But at a ten minute interval, it’s almost always within 11 minutes.
Who exactly is keeping time here? Why aren’t these always exactly the interval requested? Why would the 15-minute be so haphazard but not the 10-minute interval? (Both were done over periods of at least 12 hours.)
Questions like that, smile.
Okay, now I’ve overspent my welcome (smile). If someone can help with some of these, that would be so cool. If not, that’s okay too.
Thanks!!!