[OBSOLETE] Log events to Google Sheets [see post /36719/154 for Github repo and v1.1]

I got it, everyone. I was not paying attention to the Version in the publishing popup. It was stuck on Version 1 when I should have been changing this to New every time I changed (fixed) my code. So instead, it was running the old/bad code over and over. As in, come on, it’s the code that’s right in front of me when I hit Publish… but could that possibly be the code it will publish? lord no, lol. It will be wonderful if a little note about revising code and the Version in the popup can be put in the directions.

I have come to believe in God due to how many things go wrong only for me. It’s true! and also sadly funny

I’m glad you got this working. I think this problem happened to me when I first wrote the instructions on how to create the spreadsheet from scratch. I updated the script and it didn’t take effect. I eventually just started over and it all worked. Thanks for figuring this out! I’ve updated the instructions to include the text you suggested.

You’ve been wonderful John, thank you so much!

Does anyone know what I need to delete so that I can have the data always got to row 2 and overwrite and previous data.

Many thanks,
Stephen

Do you mean only keeping 1 row of data?

Note that the Google Script code is written to write a new row. You’d have to modify that if you always wanted to just write to row 2. (it looks like most of the code would in fact go away)

I’ve just pushed v1.1 which:

  • adds support for non-Google Apps domains
  • changes the timestamp format to “M/D/Y H:M:S”, which is what Sheets inserts for timestamps automatically
  • adds dimmer switches to devices (but I’ve not tested them yet)

This only changes the SmartApp. The Google helper script code remains the same. You only need update the SmartApp via the IDE.

4 Likes

That’s great, John. Thanks! I’m traveling now with only a tablet for internet. I’ll be home this weekend and will implement as soon as I can get in front of a computer.

If you play around in the groovy code you can manually add items to your google spreadsheet log with the following:

def url="https://script.google.com/macros/s/<URL_KEY>/exec?obs_temp_f=${obs.temp_f}&precip_1hr_metric=${obs.precip_1hr_metric}&precip_today_metric=${precip_today_metric}"
def putParams = [
	uri: url]

httpGet(putParams) { response ->
log.debug(response.status)
if (response.status != 200 ) {
	log.debug "Google logging failed, status = ${response.status}"
	}
}

edit: you can simplify the above down to one line if you forgo the error logging:

httpGet([uri:"https://script.google.com/macros/s/<URK_KEY>/exec?precip_today_metric=${precip_today_metric}"])

In this example I added some weather unsupported precipitation data to the Smart Weather Station Tile device handler.

Is it possible for a Device Handler to create an API for other Device Handlers or SmartApps to use? If so we could simplify this with something like
logGoogleSheets("precip_today_metric",precip_today_metric)

1 Like

Thanks so much, John! Kevin too. I’m just an old amateur VBA coder and a little lost trying to learn these new languages and paradigm, and how they interact. Your posts and work are so helpful. I am still really amazed that we can be capturing all this - delivering even better value for a data monger like me than any paid home monitoring or alarm services - all for free. I almost went with Wink but decided to go with SmartThings due to its strong development and user community. Looks like I chose wisely.

If I could Like your posts more, I would. :grin:

Have you also added something like this? (just wondering)

obs = getWeatherFeature("conditions" , zipcode)

I think the change that added the “Sensors” logging device exists to allow you to select arbitrary values exposed by devices as attributes. For instance, the SmartWeather tile has some of the fields from the weather API calls mapped to attributes.

If that works, I’d suggest adding the Weather Station Tile 2.0 device, which exposes almost all of the values as attributes. Thus, you can select that device and set the sensors list to something like

percentPrecipToday,percentPrecipLastHour

No, because that was already in the smart weather tile DH, before my log statement

i saw that but didn’t expiriment with it, I’ll give it a try too

Ah, I didn’t realize your snippet of code was in the SmartWeather Tile.

[quote=“kevin, post:160, topic:36719”]
i saw that but didn’t expiriment with it, I’ll give it a try too
[/quote]I just tested it and it works. What I found is that it seems that values are only sent when they change, so if percentPrecipLastHour is currently 0, it won’t show up until it does non-zero (and then eventually back to 0).

1 Like

[quote=“0121stephen, post:131, topic:36719”]
One thing, the app seems to give me a “snap shot in time”, i.e. if there is no motion when the app push fires I don’t get a reading and it is possible for somebody to walk through a room, trigger a motion sensor but not provide a reading or open and door and close it gain and I don’t get a reading unless I set the push delay to 0.

Is it possible to get the app collate instances in the last 5 minutes or longer and return a positive reading if there has been any motion during this period?[/quote]

Maybe I’m confused, but that shouldn’t be how it behaves. I’m not logging from any motion sensors, so I can’t say this for sure. BUT… the code (like all the other SmartApps) only subscribes to events sent by the DTHs. E.g., when your motion sensor senses motion, the DTH publishes this event, and SmartThings delivers the event to everything that has subscribed to it. This SmartApp immediately notices the event; depending upon if you have a queue time sent, it will either immediately sent it to Sheets or delay sending it a short while. No matter what, the event should be logged; this should never miss an event that has been raised (unless there is an error posting the event to Google).

Basically, this SmartApp does not poll devices.

UPDATE: Ah, I see the problem now. If multiple events come in on a device, all of them are reported to Sheets in a single request. However, only the last value is saved since in the end they are all being reported at the same time, and thus there is only 1 cell to report them in. Thus, if you have a 5 minute queue time and at 2 minutes it reports motion and then at 3 minutes it clears and reports no motion, at the 5 minute mark both events will get posted to Sheets, and will result in an entry of no motion. You can tell this has happened because otherwise if there had been no other events on the device, the cell at that report time should be blank.

Basically, some devices need to have their status latched; e.g., there has been any motion in the last 5 minutes. This is basically what you asked, but now I understand why it happens and what it will take to fix it.

This won’t happen with some other loggers, like GroveStreams or InitialState, since those send a single message with multiple values, but each value has it’s own timestamp. This Sheets logger, when queuing, sends a single message with all the device values, but only includes the timestamp of the time the message is sent. Thus, they all get stored in the same spreadsheet row; otherwise it would be consuming many more cells and generating many more rows (of a single data value each).

1 Like

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!!!

1 Like

You don’t need to do that. It will automatically add rows as needed. My current sheet has 4107 rows, the last of which was automatically added 7 minutes ago.

[quote=“RedKnight, post:164, topic:36719”]3.INSERT ROWS FROM TOP:
Plus, Sheets doesn’t have Excel’s nice ability to simply hit End and then Down to zoom to the end of your data.
[/quote]
CTRL-DOWN moves to the last row in a column that has data. If you do this in column A (Time), you will jump to the last line logged into the sheet. Alternately, CTRL-END moves to the last column of the last line that exists in sheet; however, this isn’t helpful if you add blank rows at the bottom (but you don’t need to do that). See Help->Keyboard shortcuts.

1 Like

I’m not applying any special formatting to the data, and most of my data is numbers. The default format seems to work, so I guess I’d need an example to understand better.

[quote=“RedKnight, post:164, topic:36719”]
5.RENAME SENSORS?
So the spreadsheet columns aren’t so wide, I would like to shorten…
This isn’t an issue that wrapping can help with, not the way I like to do it.[/quote]
Funny, I was about to ask for a device alias mechanism for the Ask Alexa SmartApp for much the same reason. I’d like to ask/set devices without speaking the long names.

However, I think you might be able to use wrapping to solve most of this. This is a snippet of my spreadsheet. I’ve just set row 1 to wrap text and then narrowed the columns as they got created. Yes, they could all be narrower if the words were abbreviated. But, IMHO I don’t think the end-goal is to view the data in the spread sheet; instead, I think the end goal is to store it there but then use graphs to visualize things. With that you don’t need to worry about the column widths.

That said, you could try something like adding a new subroutine like this:

private def shortEvtName(evt) {
    String name = evt.name
    if (name == "temperature")
        return "temp"
    if (name == "illuminance")
        return "illum"
    if (name == "humidity")
        return "RH%"
    return name
}

and then replace the two instances of evt.name with shortEvtName(). I’ve not tested this, however, and were I really to add it, I’d probably add a preference to control it. But this might give you a starting point.

1 Like

Offhand, I’m not sure if that event is made available; I’d have to search the doc. Realize the SmartApp is running on a SmartThings server somewhere (“in the cloud”)…

I had thought about making the queuing logger use the timestamp of when it sends the data, but the way it works now makes (somewhat) more sense. If you only have 1 event queued, then right now it is sent with the correct time. If you have multiple events queued, then they will all get logged with the timestamp of the first queued event. It’s a trade-off between the number of rows in the database and timestamp accuracy.

For instance, I have an SDR that reads my electric meter so that I can measure my usage. I started logging it a year ago when our Solar PV array went live. I originally started logging every data point I collected; that quickly resulted in a few thousand data points a day. I then switched to only changed values. Even then, I have 100K points for a year of data. I finally decided to keep an every 15-minute value and a daily value. Just the daily value is enough to see the trend over time (at least for me):
https://thingspeak.com/channels/123559/charts/1?bgcolor=%23ffffff&color=%23d62020&dynamic=true&results=600&type=line

I’m still deciding how to do visualizations of my data. As you can see, I’m using ThingSpeak for some data. I’m setting up plots off the Google Spreadsheet, and I’m still playing with Grovestreams and local solutions.

1 Like

I’m a newb, too, but I’ll try to give my best explanation.

The SmartApps runs on the SmartThings server (“cloud”). It could possibly run locally on the hub IFF (1) it was an officially released SmartApp and (2) all the devices it used were also local. That isn’t likely to happen, so it will always run in the cloud.

Among the infrastructure that SmarttThings provides is an event driven, publish/subscribe system. Devices publish information and SmartApps subscribe to those feeds. This isn’t the only mechanism provided (some SmartApps directly query devices), but this SmartApp is entirely passive. It only gets events that other devices raise.

Time keeping is happening on the server. This uses runIn() to schedule the upload event, which (IIRC) purposely uses a random skew on when the event to avoid too many things firing off at the same time.

That said, if there are no events, then nothing will get logged, so it’s not impossible to see events 30 minutes apart. But this in checkAndProcessQueue() there is code to determine if the queue has not been processed in a long time (twice the queue time) and if so, force the upload. Were that to happen, it would be logged, but it might not be easy to see (maybe it should keep a counter of the number of times that fires called missedPolls, similar to failureCount).

The Google Sheets script runs on Google’s server (“cloud”). It does all the manipulation of the spread sheet, on behalf of the SmartApp. It only does something when the SmartApp sends an HTTP request.

1 Like