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

(Charles Schwer) #1

Deleted <20 characters>

IoT Logger [RELEASE]
King on the hill: Who makes the most accurate temp sensor
IFTTT Triggers
Get Sensor Data
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?
Long-term graphing woes, roll my own?
Smartapp to export the log?
Aeon HEM v1 DH (updated 06-14-2017) Discussion (DTH link in post 329)
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
Aeotech Multisensor 6 (gen 5 zwave plus, model ZW100-A)
FortrezZ Water Flow Meter
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?
ROUNDUP: Data Logging and Charting Solutions
Graphs and charts?
Looking for Outdoor Illuminance Data
Google Sheets Charts
[RELEASE] Enhanced Z-Wave Plus Thermostat (CT30/CT50/CT80/CT100/CT101/CT110/ZTS110/ZTS500/GoControl/Honeywell/Universal) Device Handler with Battery, Humidity, Lock Controls, Clock Set, Alarm, Swing/Temp, Deadband Configuration and Updated User Interface
Kind of a nerd thing but..... ST Data Dump?
[RELEASE] Iris Smart Plug (3210-L) Zigbee Plug with Z-wave Repeater
Bloomsky Weather Station
Z-Wave Smart Energy Meter - 9.99....Really?
NEW: Aeon Home Energy Monitor v2 Device
Aeon HEM v1/v2 Firmware 3.64 Numbers are Off?
(Marc) #3

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

(Ben Lebson) #6

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

(Ben Lebson) #9

The log shows it like this:

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

(Frank ) #10

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.

(Ben Lebson) #11

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.

(Ben Lebson) #13

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.

(Andreas A.) #17

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.

Get Sensor Data

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

(Frank ) #22

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.

(Adkhkr1) #24

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.

(Ken) #29

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.

(Dean Smith) #33

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.

(David) #35

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.