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

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.

I have the latest code and everything works fine. I just want to get creative with the output statements. I’m not good with groovy code, so I can’t really find where you put the statements to output open and output closed. Just for example, if I wanted it to say doorisopen and the doorisclosed. Trying to learn how to manipulate the code. Another example, is for motions have it output MotionDetected and MotionStopped.

@gjpinoy, what is it that you are looking to do? The code snippet I gave you shows where in the preferences that the output statement choices are presented, then you can change the actual output inside the handler for the event, if I am reading the Groovy code correctly (again, I have never coded in Groovy, but I code for a living).

Here is an example to guide what I think needs to happen:

Assume that we want to change the “Contact Sensors to Log” preferences from having the option “true/false” to “Red/Blue”. Furthermore, let’s change the output to the log to save “Red Light” or “Blue Light”.

Old:

preferences {
section("Contact Sensors to Log") {
	input "contacts", "capability.contactSensor", title: "Doors open/close", required: false, multiple: true
input "contactLogType", "enum", title: "Value to log", options: ["open/close", "true/false", "1/0"], defaultValue: "open/close", required: true, multiple: false
	}

New:

preferences {
section("Contact Sensors to Log") {
	input "contacts", "capability.contactSensor", title: "Doors open/close", required: false, multiple: true
input "contactLogType", "enum", title: "Value to log", options: ["open/close", "Red/Blue", "1/0"], defaultValue: "open/close", required: true, multiple: false
	}

Now, the actual logging is done during the execution of the handler, which uses your preferences selection to specify the output to the log:

Old:

def handleContactEvent(evt) {
	// default to open/close, the value of the event
def convertClosure = { it }
if (contactLogType == "true/false")
	convertClosure = { it == "open" ? "true" : "false" }
else if ( contactLogType == "1/0")
	convertClosure = { it == "open" ? "1" : "0" }

if(settings.queueTime.toInteger() > 0) {
	queueValue(evt, convertClosure)
} else {
	sendValue(evt, convertClosure)
}
}

New:

def handleContactEvent(evt) {
	// default to open/close, the value of the event
def convertClosure = { it }
if (contactLogType == "Red/Blue")
	convertClosure = { it == "open" ? "Red Light" : "Blue Light" }
else if ( contactLogType == "1/0")
	convertClosure = { it == "open" ? "1" : "0" }

	if(settings.queueTime.toInteger() > 0) {
	queueValue(evt, convertClosure)
} else {
	sendValue(evt, convertClosure)
}
}

Now, if I have this wrong, I apologize for misguiding you. I am sure one of the ST gurus will help me out by correcting my attempt at helping you out. :smiley:

@Belgarion For some reason it didn’t take. Edited the code the same way you did, then click saved. Went into the app to select those Value to log as Red/Blue, but it didnt log. Not sure what I did wrong. Works fine once I select open/close or 1/0 for the Value to log.

@gjpinoy I think you have to “Publish” -> “For Me” for it to take affect in the App.