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

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.

@Belgarion I did numerous test. So, I notice in the original code true/false came out capitalized, so it can out TRUE or FALSE and not sure why. Is there a string somewhere forcing it to be capitalized? When I changed it to “Red Light” it didn’t work, but when I removed the space to be “RedLight”, it started working. So I guess the space broke the code? Any input?

@gjpinoy, after glancing at the code some more my guess would be that since the value is being used as part of the URL to execute the Google script, the space was causing a problem. This does not happen with the spaces in the label since that string is being encoded to work with URL’s.

Again, not a Groovy coder, but that is my take on it. As written, I do not believe the app can handle values with spaces.

@Charles_Schwer @Belgarion Thanks guys for taking the time to explain things.

Do you know why the option true/false in the Value to log the outputs in google docs come out capitalized? TRUE or FALSE

Also, what is the option “Reset the state values (queue, schedule, etc.)” do?

This is due to the way Boolean values are treated by spreadsheets, Google Sheets, Excel, etc. They are auto-capitalized, can be used in math, can be used in functions that accept Boolean arguments…

1 Like

@Charles_Schwer The fix works like a charm

btw, I just noticed that the time is off by an hour. its 11:05AM and everything is an hour backwards. Any trouble shooting for that?

It was the spreadsheet settings. Everything is ok now.

@Charles_Schwer This is amazing - thank you for making it!

Can I ask for some new data?

Humidity tracking (%)
Luminesce tracking (lux)
presence (present / not present)
switches (turned on / turned off)

Charles,

Your app is awesome! Thank you so much for all your work on this. I found your work after searching for a way to backup/save my ST hub events long term as ST doesn’t save past a few day’s. This is perfect! Can’t wait until you add the other events like presence, switches etc.

I have three logging app instances running pushing with a queue of 0 minutes. Are there any ill effects of having it push these so often?

Thanks again!

Perfect! Thanks for finding the limits. I don’t think I’ll ever hit the 50k/day mark even logging every event. I’ll let you know if I do.
Also, if possible could you add thermostat state logging as well(heating, cooling, idle etc) That way I can monitor HVAC efficiency and compare to temperature sensors.
Thanks again for all your work. This is one of my favorite smart apps now.

Wow, that was fast, thanks for adding those in. Everything is working great so far.

Thanks!.. 20 chars…

Maybe in next update, if its not too much trouble, would be nice to log Smart Home Monitor state and mode changes.

Thanks!

I added logging of generic sensors (custom devices), if you want to add that to your source that would be great!

Under section(“Log Other Devices”)

input "sensors", "capability.sensor", title: "Sensors", required: false, multiple: true input "sensorAttributes", "text", title: "Sensor Attributes (comma delimited)", required: false

Under initialize()

if (sensors != null && sensorAttributes != null) { sensorAttributes.tokenize(',').each { subscribe(sensors, it, handleStringEvent) } }

Hey, I’m noticing that my spreadsheet is logging the time as 2 hours later than the local time. Is there a setting in the SmartApp or the sheet Script that will fix this?

Perfect, thanks! I figured it was a setting hiding somewhere.

Been a couple days and I see you added more cool stuff. wanted to add some wish list.

  1. Battery Percentage Reporting
  2. User customized outputs in the smartapp. example open/close sensor i can unslect default and select custom and i type in “door is open”/“door is closed”