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

Deleted <20 characters>

Get Sensor Data
IFTTT Triggers
King on the hill: Who makes the most accurate temp sensor
Device no longer reporting and not accessible in IDE
How To Log Temperature Changes To Google Sheets Using Web Request
ROUNDUP: Data Logging and Charting Solutions
Google Sheets Charts
Any way to track and record temperature readings?
Logging Data to Google Sheets
Graphs and charts?
Smartapp to export the log?
Long-term graphing woes, roll my own?
ROUNDUP: Data Logging and Charting Solutions
Aeotec home energy meter V1(DSB09104-ZWUS) log to spread sheet
Inexpensive NIST thermal probe with cloud storage but no SmartThing integration?
Automate external event logging and charting?
[RELEASE] Enhanced Z-Wave Plus Thermostat Device Handler - Honeywell, GoControl, CT, Linear, Trane, MCO, Remotec
[OBSOLETE] Aeotech Multisensor 6 (gen 5 zwave plus, model ZW100-A)
NEW: Aeon Home Energy Monitor v2 Device
Z-Wave Smart Energy Meter - 9.99....Really?
Bloomsky Weather Station
Looking for Outdoor Illuminance Data
FortrezZ Water Flow Meter
Aeon HEM v1/v2 Firmware 3.64 Numbers are Off?
Difference between Save, Publish, Install, and Update?
[DEPRECATED] - Hive (Connect) V2.6 (British Gas Hive)
Dweet Dashboard
How to monitor/log energy usage? Daily/month numbers Aeon Labs Aeotec Z-Wave Smart Switch 6, Gen5
Using ST Multisensor accelerometer as mailbox mail detector
SmartTiles v5.8: Deployed to Blue & Green Editions. Release notes, etc. (follow this Topic for updates...)
JUST power monitoring plugs/extension sockets?
Google Sheets Charts
Kind of a nerd thing but..... ST Data Dump?
[OBSOLETE] Aeon HEM v1 DH Discussion (DTH link in post 411)
[OBSOLETE] Iris Smart Plug (3210-L) Zigbee Plug with Z-wave Repeater

This looks really cool, nice job. It should be pointed out in this thread(it’s in the screenshots you provided in your GitHub) that you do need to publish this Google Sheet webapp as access to everyone, including anonymous, so if someone does manage to get a hold of the URL, they can see this data. Please correct me if I am mistaken here.

Very cool. Thanks for the work

This is great. Any chance of adding the ability to log energy usage?

1 Like

The log shows it like this:

I’ll try out the new code and see if it works.

This is great, i will give this a shot. I have been playing with initial state trying to do some logging. I want to eventually build a heatmap for each of my floors so I can visualize how the heat moves within my house. I just finished installing sensors on all of my doors and windows, now i need to start collecting data.

1 Like

Log says theres no event handler for the power event:

c4975fc1-6e29-47b9-b5b2-84e08c7b9cf9 2:00:04 PM: warn No event handler found for power event ‘zw device: 20, command: 3202, payload: 21 74 00 0B E8 C9 01 2C 00 05 4E E1’

As you would imagine, it’s not logging anything to the spreadsheet for it either.

It is logging Energy properly though.

Figured as much, works like a charm now :slight_smile:

Cool, so many possibilities, thanks for this! I’ve needed something like this for a while, finally an easy way to analyze my home data.

I’d like to publish a chart as an image but it doesn’t seem to work, seems to be a Google problem? But if it worked out it could probably be embedded in a device handler to display in the app.

There also could be some rate limiting in place - I had the same issue with ThingSpeak. They have a 15s rate limit in place so I ended up adding the data to a map car and then scheduling the actual submission (of all values in the var) using runIn(15, …). Of course that wouldn’t work if you consistently get data faster than once every 15s but that could be handled.

just a thought, you should update the icon for the google sheets icon, something like this…

Is there a way to retain previous values? I have temps from open/close sensors being logged, and while most of them seem to get reported every 15 minutes, occasionally, I do have some empty cells. Ideally, these would carry over the previously reported value.

1 Like

Can you add motion sensors to the list being logged?

What do you define for motion as True and False? True = motion detected? False=motion stopped?
Also for Open/Close sensors?

Yes it would be better to log it that way. Makes it less confusing. I figured that what you meant, but I really had to think about it first, then I figured how about I just confirm on the thread.

Will you be updated the code?
Also, would you be able to add a comment on the line where I can edit the name in google docs. Example: I have “Open/Closed Front Door temperature” and I want to make it “Open/Closed Front Door Temperature” capital “T”. I know its something really small but I like to format it a little more to something to my liking.

Not that I necessarily have a use case for this but want to throw out there that you can do math with true/false in a spreadsheet. With this you could get a sum of the active or open instances without having to resort to string evaluation.

where column R contains true/false for a motion detector
=ArrayFormula(SUM(FullData!R2:R240*1)) vs.

Charles, this is excellent. Great work!

Charles, the logging is awesome and works great.

My coding skills are extremely rusty and I’m trying to figure out where you find/edit the code where you define the output statements to open or closed. I’m not even sure how you get it to say closed. I was experienced in C++ but not sure about the code language here. I would be a novice to coding. If you have time and if you can help that would be great.

The code is available in GitHub:


I am not a Groovy coder myself, but I would assume the new choices are defined in “Preferences”:

Tried messing with the preferences, but didn’t get what I wanted. Unless I’m doing something wrong.