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

Deleted <20 characters>

37 Likes

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.

e.g.
where column R contains true/false for a motion detector
=ArrayFormula(SUM(FullData!R2:R240*1)) vs.
=countif(FullData!R2:R240,ā€œactiveā€)

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:

http://github.com/cschwer/googleDocsLogging/blob/master/smartapps/cschwer/google-sheets-logging.src/google-sheets-logging.groovy

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.