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

SmartApps & Automations Community Created SmartApps
174

Thanks greatly John, your draft worked great. For anyone who wants to change their column headers, do it like this. Search your code for the first instance of “evt.name” and insert this, up to the line with evt.name in it:

def shortEvtName(evt) {
    String name = evt.name
    if (name == "temperature")
        return "temp"
    if (name == "illuminance")
        return "illum"
    if (name == "humidity")
        return "hum"
    if (name == "acceleration")
        return "accel"
    return name
}

private sendValue(evt, Closure convert) {
	def keyId = URLEncoder.encode(evt.displayName.trim()+ " " +shortEvtName(evt))

There is also one more instance of evt.name to replace with shortEvtName(evt), be sure to get that, too.

You see the examples and can play with them. I found I could include a numeral or end the new name with a period and it worked okay.

If you have a lot of sensors, then edit your code but don’t save it until the next time google accepts data (load your simulator and watch it). Then Save your code and quickly change column headers before the next update and everything will proceed smooth as can be. (It won’t make new columns and orphan your previous names; it will just go right into the right columns whose names you just updated.) If you want, experiment on one column that’s updated a lot, first.

I also used Kevin’s suggestion of a different font for column headers, it does help some. In addition to the several “narrow” fonts (go to “more font” then filter on “narrow”), there’s a hella narrow one called BenchNine. I went with PT Sans Narrow at 12 point to match my Arial 10 for data:

Sheets log format 2.jpg2563×1365 557 KB

I have 9 Aeon Multisensors each tracking 6-7 things plus some other sensors. Now you can see why I like it tight. At 10 minute intervals. Right now I am trying to calibrate their temperatures against each other, thus the graph. I found you have to repeat column headers to get them to show on an inserted graph if you are only graphing a selection of your data. That’s what I repeated the headers right in the data (which is where the graphed data starts).

As we discussed, next I will see if I can get cells’ numeric format to stay the same. And I can try to add those other timestamps myself. It may not seem like I did a lot above, but it’s a steep curve at first; even just the concept of what is on what servers. So I really appreciated your examples.

2 Likes