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

Deleted <20 characters>


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.