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

SmartApps & Automations Community Created SmartApps
437

Hiya Nezmo,

I never liked the Time column in the CSchwer log. When my log approaches the 2M-cell limit, the Time variable keeps repeating itself. Right now I am almost up to 2M cells, and I see rows where it has said e.g. “07/30/18 03:52:29” for a total of FORTY TWO TIMES, then switched to “07/30/18 10:57:58” and repeated that 12 times. Etc. See the attachment.

I found a workaround and got tired of bugging the wonderful souls that set up the CSchwer log. I stuck in several other timestamps:

  • LastTime = the latest time out of all the data that contributed to a given CSchwer output row

  • SendTime = the time as of the point the row is sent from the ST app code, to Google (i.e., the send time as the ST server sees it)

  • PostTime = the time that the record is posted to one’s Google Sheet (i.e., the time that Google sees and posts it)

Generally, SendTime and PostTime are only a few seconds apart. But, importantly, PostTime is a completely independent query of the time. This is critically important for when things go wrong. A problem with the original CSchwer code as I see it, is that it assumes everything works simple and right. But things don’t always work right. And when they don’t, you’re at a loss for knowing the real time(s) involved. It has no safety net.

I have never been able to figure out the code for the CSchwer Time variable myself. I can see how it can get stuck, but I’m too dumb to fix it. So I simply made these other columns, which I don’t mind having anyway.

I have about 2 dozen multisensor devices, and about 100 columns output in each CSchwer log row, every 10 minutes.

The log is a truly wonderful and amazing thing to be getting all this data for free, for two years now.

But still, the Time variable breaks down when one’s Sheet is getting full. And I don’t know how to fix it. So, while I do capture its Time variable, really I mostly ignore it and use my other time variables, basically.

In the appended screen cap, see the “T ditto” columm. This is a computed column which says +1, +2, etc., for each row where Time repeated itself. You can see where it repeated 41 times. Which is to say, for 42 x 10 minutes (7 hours) it was repeating. All my other time variables updated just fine, though.

As I said before, while I LOVE the CSchwer concept, I never liked how it only had one Time variable - and that one was subject to errors. Nobody seemed to listen. Or, more likely, we’re all exhausted from tweaking, laugh.

I am appending my CSchwer log code (for LastTime and SendTime) and also my Sheets code (for PostTime) if anyone wants to see. You will have to edit your code by hand if you want to add them.

If I were skilled, I might have forked/edited the GitHub code. But I don’t feel comfortable doing that. If anyone else wants to include my edits, please do.

My CSchwer code also includes some nice things like the ability to shorten column headers (so they don’t take up so much of the screen, as you see in my screen cap), a Zoom To Last Row when opened, and also, my Sheets code repeats certain computed lines each time a row is posted. I have shown the same data on two screens (portrait and landscape) so you can see the difference better.

I use CSchwer to capture “continuous” data like temperature, humidity, light levels, and power, and @KRLaFramboise’s Simple Event Logger for discrete things (movement, batteries, switches, contacts, presence). Otherwise the discrete columns would have a lot of blanks, which would really cut into the 2M-cell limit of the CSchwer log when multiplied across all my devices. Plus, CSchwer doesn’t actually record specific times (it just has a 10-minute window per row, or whatever), but the Simple event Logger captures the exact time.

Screen%20cap%20of%20repeating%20Time%20as%20of%202018-07-30
Screen cap of repeating Time as of 2018-07-30.jpg4242×2603 2.33 MB

*** We can’t attach .txt files, so here is my current code. First my SmartThings SmartApp code, then search for *** to find my Google Sheets code:

SmartThings SmartApp code that outputs CSchwer log:

/**

  • SmartThings example Code for Google sheets logging
  • Copyright 2016 Charles Schwer
  • Licensed under the Apache License, Version 2.0 (the “License”); you may not use this file except
  • in compliance with the License. You may obtain a copy of the License at:
  • http://www.apache.org/licenses/LICENSE-2.0
  • Unless required by applicable law or agreed to in writing, software distributed under the License is distributed
  • on an “AS IS” BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License
  • for the specific language governing permissions and limitations under the License.
  • First used by Mike Fay 6/5/2016 19:14:22
  • Abbreviated 3 column-header values 6/6/16, e.g. temperature to temp. Changed back 6/7 sigh.
  • 1/1/17: Added code to try to make AMS accelerometers be their own “proper” box. So I don’t have
  • to hassle with that odd “Sensors Attributes (comma delimited)” thing that doesn’t work if you have a comma.
  • Look for two with acceleration. Note that “My Devices” suggests AMSs have acceleration AND tamper,
  • but the “Aeon Multisensor 6 - RV 2.3” code suggests that it is simply using tamper for acceleration.
  • I am assuming that that is the case. Also note: Don’t use the sensor name straight up as shown in My Devices;
  • RV’s code metadata / definition / name / capability section is a little different, and better matches what
  • is seen in the code below. So use “accelerationSensor” from RV’s code (space removed),
  • not simply “acceleration” from My Devices.
  • 1/30/17: Added PostTime code for a second time variable (in addition to SendTime) to see
  • if I can get LaFramboise’s code working for it,catching it on Sheets side

*/

definition(
name: “Google Sheets Logging”,
namespace: “cschwer”,
author: “Charles Schwer”,
description: “Log to Google Sheets”,
category: “My Apps”,
iconUrl: “https://raw.githubusercontent.com/loverso-smartthings/googleDocsLogging/master/img/logoSheets.png”,
iconX2Url: “https://raw.githubusercontent.com/loverso-smartthings/googleDocsLogging/master/img/logoSheets@2x.png”,
iconX3Url: “https://raw.githubusercontent.com/loverso-smartthings/googleDocsLogging/master/img/logoSheets@2x.png”)

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/closed”, “true/false”, “1/0”], defaultValue: “open/closed”, required: true, multiple: false
}

section("Motion Sensors to Log") {
    input "motions", "capability.motionSensor", title: "Motion Sensors", required: false, multiple: true
    input "motionLogType", "enum", title: "Value to log", options: ["active/inactive", "true/false", "1/0"], defaultValue: "active/inactive", required: true, multiple: false
}

section("Thermostat Settings") {
    input "heatingSetPoints", "capability.thermostat", title: "Heating Setpoints", required: false, multiple: true
    input "coolingSetPoints", "capability.thermostat", title: "Cooling Setpoints", required: false, multiple: true
    input "thermOperatingStates", "capability.thermostat", title: "Operating States", required: false, multiple: true
}

section("Locks to Log") {
	input "locks", "capability.lock", title: "Locks", multiple: true, required: false
    input "lockLogType", "enum", title: "Value to log", options: ["locked/unlocked", "true/false", "1/0"], defaultValue: "locked/unlocked", required: true, multiple: false
}

section("Log Other Devices") {
	input "batteries", "capability.battery", title: "Batteries", multiple: true, required: false
	input "temperatures", "capability.temperatureMeasurement", title: "Temperatures", required:false, multiple: true
    input "energyMeters", "capability.energyMeter", title: "Energy Meters", required: false, multiple: true
    input "powerMeters", "capability.powerMeter", title: "Power Meters", required: false, multiple: true
    input "humidities", "capability.relativeHumidityMeasurement", title: "Humidity Sensors", required: false, multiple: true
    input "illuminances", "capability.illuminanceMeasurement", title: "Illuminance Sensors", required: false, multiple: true
    input "presenceSensors", "capability.presenceSensor", title: "Presence Sensors", required: false, multiple: true
    input "switches", "capability.switch", title: "Switches", required: false, multiple: true
    input "acceleration", "capability.accelerationSensor", title: "Acceleration", required: false, multiple: true
    input "sensors", "capability.sensor", title: "Sensors", required: false, multiple: true
    input "sensorAttributes", "text", title: "Sensor Attributes (comma delimited)", required: false
}

section ("Google Sheets script url key...") {
	input "urlKey", "text", title: "URL key"
}

section ("Technical settings") {
    input "queueTime", "enum", title:"Time to queue events before pushing to Google (in minutes)", options: ["0", "1", "2", "3", "4","5","6","7","8","9", "10", "15"], defaultValue:"10"
    input "resetVals", "enum", title:"Reset the state values (queue, schedule, etc)", options: ["yes", "no"], defaultValue: "no"
}

}

def installed() {
setOriginalState()
initialize()
}

def updated() {
log.debug “Updated”
unsubscribe()
initialize()
if(settings.resetVals == “yes”) {
setOriginalState()
settings.resetVals = “no”
}
}

def initialize() {
log.debug “Initialized”
subscribe(locks, “lock”, handleLockEvent)
subscribe(batteries, “battery”, handleNumberEvent)
subscribe(contacts, “contact”, handleContactEvent)
subscribe(motions, “motion”, handleMotionEvent)
subscribe(heatingSetPoints, “heatingSetpoint”, handleNumberEvent)
subscribe(coolingSetPoints, “coolingSetpoint”, handleNumberEvent)
subscribe(thermOperatingStates, “thermostatOperatingState”, handleStringEvent)
subscribe(temperatures, “temperature”, handleNumberEvent)
subscribe(energyMeters, “energy”, handleNumberEvent)
subscribe(powerMeters, “power”, handleNumberEvent)
subscribe(humidities, “humidity”, handleNumberEvent)
subscribe(illuminances, “illuminance”, handleNumberEvent)
subscribe(presenceSensors, “presence”, handleStringEvent)
subscribe(switches, “switch”, handleStringEvent)
subscribe(acceleration, “acceleration”, handleStringEvent)
if (sensors != null && sensorAttributes != null) {
sensorAttributes.tokenize(’,’).each {
subscribe(sensors, it, handleStringEvent)
}
}
}

def setOriginalState() {
log.debug “Set original state”
unschedule()
atomicState.queue = [:]
atomicState.failureCount=0
atomicState.scheduled=false
atomicState.lastSchedule=0
}

def handleStringEvent(evt) {
log.debug “handling string event ${evt}”
if(settings.queueTime.toInteger() > 0) {
queueValue(evt) { it }
} else {
sendValue(evt) { it }
}
}

def handleNumberEvent(evt) {
if(settings.queueTime.toInteger() > 0) {
queueValue(evt) { it.toString() }
} else {
sendValue(evt) { it.toString() }
}
}

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)
}

}

def handleMotionEvent(evt) {
// default to active/inactive, the value of the event
def convertClosure = { it }
if (motionLogType == “true/false”)
convertClosure = { it == “active” ? “true” : “false” }
else if (motionLogType == “1/0”)
convertClosure = { it == “active” ? “1” : “0” }

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

}

def handleLockEvent(evt) {
// default to locked/unlocked, the value of the event
def convertClosure = { it }
if (lockLogType == “true/false”) {
convertClosure = { it == “locked” ? “true” : “false” }
}else if (lockLogType == “1/0”) {
convertClosure = { it == “locked” ? “1” : “0” }
}
if(settings.queueTime.toInteger() > 0) {
queueValue(evt, convertClosure)
} else {
sendValue(evt, convertClosure)
}
}

def shortEvtName(evt) {
String name = evt.name
if (name == “temperature”)
return “temp”
if (name == “illuminance”)
return “illum”
if (name == “humidity”)
return “hum”
if (name == “acceleration”)
return “accel”
if (name == “ultravioletIndex”)
return “UV”
return name
}

private sendValue(evt, Closure convert) {
def keyId = URLEncoder.encode(evt.displayName.trim()+ " " +shortEvtName(evt))
def value = URLEncoder.encode(convert(evt.value))

log.debug "Logging to GoogleSheets ${keyId} = ${value}"

def dummyDate1 = new Date(2001, 1, 1);
def eventPostTime = URLEncoder.encode(dummyDate1.format('MM/dd/yyyy HH:mm:ss'))

def url = "https://script.google.com/macros/s/${urlKey}/exec?${keyId}=${value}" + '&SendTime=' + URLEncoder.encode((new Date()).format( 'M/d/yyyy HH:mm:ss', location.timeZone )) + '&PostTime=' + eventPostTime
log.debug "${url}"

def putParams = [
	uri: url]

httpGet(putParams) { response ->
	log.debug(response.status)
	if (response.status != 200 ) {
		log.debug "Google logging failed, status = ${response.status}"
	}
}

}

private queueValue(evt, Closure convert) {
checkAndProcessQueue()
if( evt?.value ) {

	def keyId = URLEncoder.encode(evt.displayName.trim()+ " " +shortEvtName(evt))
	def value = URLEncoder.encode(convert(evt.value))

	log.debug "Logging to queue ${keyId} = ${value}"

	if( atomicState.queue == [:] ) {
  		def eventTime = URLEncoder.encode(evt.date.format( 'MM/dd/yy HH:mm:ss', location.timeZone ))
        addToQueue("Time", eventTime)
	}
    else { 
    	def eventLastTime = URLEncoder.encode(evt.date.format( 'MM/dd/yy HH:mm:ss', location.timeZone ))
        addToQueue("LastTime", eventLastTime)
	}

	addToQueue(keyId, value)
    
    log.debug(atomicState.queue)

	scheduleQueue()
}

}

/*

  • atomicState acts differently from state, so we have to get the map, put the new item and copy the map back to the atomicState
    */
    private addToQueue(key, value) {
    def queue = atomicState.queue
    queue.put(key, value)
    atomicState.queue = queue
    }

private checkAndProcessQueue() {
if (atomicState.scheduled && ((now() - atomicState.lastSchedule) > (settings.queueTime.toInteger()*120000))) {
// if event has been queued for twice the amount of time it should be, then we are probably stuck
sendEvent(name: “scheduleFailure”, value: now())
unschedule()
processQueue()
}
}

def scheduleQueue() {
if(atomicState.failureCount >= 3) {
log.debug “Too many failures, clearing queue”
sendEvent(name: “queueFailure”, value: now())
resetState()
}

if(!atomicState.scheduled) {
	runIn(settings.queueTime.toInteger() * 60, processQueue)
    atomicState.scheduled=true
    atomicState.lastSchedule=now()

	// def eventBeginTime = URLEncoder.encode(evt.date.format( 'MM/dd/yy HH:mm:ss', location.timeZone ))  // ^ end period timestamp 
	// addToQueue("BeginTime", eventBeginTime)
        
}

}

private resetState() {
atomicState.queue = [:]
atomicState.failureCount=0
atomicState.scheduled=false

	def eventBeginTime = now() // URLEncoder.encode(evt.date.format( 'MM/dd/yy HH:mm:ss', location.timeZone ))  // boundary timestamp 
	addToQueue("BeginTime", eventBeginTime)

}

def processQueue() {

log.debug "Got into PQ"
// end period timestamp
// def eventEndTime = URLEncoder.encode(evt.date.format( 'MM/dd/yy HH:mm:ss', location.timeZone )) 
// addToQueue("EndTime", URLEncoder.encode(evt.date.format( 'MM/dd/yy HH:mm:ss', location.timeZone )))
// addToQueue("EndTime", eventEndTime)
log.debug "Got past EndTime timestamp"
log.debug(atomicState.queue)

atomicState.scheduled=false
log.debug "Processing Queue"

if (atomicState.queue != [:]) {
    def url = "https://script.google.com/macros/s/${urlKey}/exec?"
    for ( e in atomicState.queue ) { url+="${e.key}=${e.value}&" }
    def eventSendTime = URLEncoder.encode((new Date()).format( 'MM/dd/yyyy HH:mm:ss', location.timeZone ))
    url = url[0..-2] + '&SendTime=' + eventSendTime
    // def dummyDate2 = Date.parse('MM/dd/yyyy HH:mm:ss', '02/02/2002 02:02:02');
    def dummyDate2 = new Date(2002, 2, 2);
    def eventPostTime = URLEncoder.encode(dummyDate2.format( 'MM/dd/yyyy HH:mm:ss'))
    url = url[0..-2] + '&PostTime=' + eventPostTime
    
    log.debug(url)
    try {
        def putParams = [
            uri: url]

        httpGet(putParams) { response ->
            log.debug(response.status)
            if (response.status != 200 ) {
                log.debug "Google logging failed, status = ${response.status}"
                atomicState.failureCount = atomicState.failureCount+1
                scheduleQueue()
            } else {
                log.debug "Google accepted event(s)"
                resetState()
            }
        }
        atomicState.queue = [:]
        atomicState.failureCount=0
        atomicState.scheduled=false

		// def eventBeginTime = URLEncoder.encode(evt.date.format( 'MM/dd/yy HH:mm:ss', location.timeZone ))  // ^ end period timestamp 
		// addToQueue("Begin", eventBeginTime)
        
    } catch(e) {
        def errorInfo = "Error sending value: ${e}"
        log.error errorInfo
    }
}

}

*** Google Sheets code (Code.gs) for CSchwer log:

function onOpen() {
// Example taken from https://developers.google.com/apps-script/guides/menus
// 11/16/16 in attempt to get a “Zoom to last row” function.
// I couldn’t get onOpen then Zoom to work without making menu item. - Mike Fay
// 1/30/17 - new code to capture PostTime per Kevin’s 1/22 ST PM.

var ui = SpreadsheetApp.getUi();
// Or DocumentApp or FormApp.
ui.createMenu(‘BottomRow’)
.addItem(‘BottomRow’, ‘menuItem1’)
// .addSeparator()
// .addSubMenu(ui.createMenu(‘Sub-menu’)
// .addItem(‘Second item’, ‘menuItem2’))
.addToUi();

menuItem1()

}

function menuItem1() {
var sheet = SpreadsheetApp.getActiveSheet();
SpreadsheetApp.setActiveRange(sheet.getRange(sheet.getLastRow(), sheet.getActiveCell().getColumnIndex()));
// sheet.getActiveCell().getA1Notation().getColumnIndex()));
return;
}

// 11/25/16: I tried to make this function to filter on time of day, but
// YOU CAN’T USE BUILT-IN SHEET FUNCTIONS IN CUSTOM FUNCTIONS!!! wth?!?
// See https://www.reddit.com/r/GoogleAppsScript/comments/5evpiz/why_wont_this_simple_custom_sheets_function_work/
//function toDnAgain(timestampIn) {
// return 24 * TIMEVALUE(timestampIn);
//}

function doGet(request) {

// From https://github.com/loverso-smartthings/googleDocsLogging/
// which I got to from Log events to Google Sheets [see post 154 for current Github repo and v1.1]
// First created (copied from gitHub) 6/4/16; first working the day after that
// Notes to self: 1) “Sheet1” (below) means the worksheet tab, not the spreadsheet
// 2) You must SAVE (Control-S) any changes here before publishing or
// THEY WILL NOT BE REFLECTED IN WHAT’S PUBLISHED even though edits persist even across sessions. Wait No -
// 3) In the Publish popup YOU HAVE TO SAY IT’S A NEW VERSION! If you have it on, e.g., “1”, it will keep
// running the particular ALREADY MADE version called “1”. (It is doing version management behind the scenes.)
// Many thanks to John LoVerso for all his help to a GitHub newbie!
//
// 10/22/16: Edit to fix formatting using Riël Notermans in G+ App forum fix at
// https://plus.google.com/u/0/101895821219259188815/posts/2UpNYwhQ8zM
//

Logger.log(request);

if(request != null) {

var ss = SpreadsheetApp.openById("1M2AxCDAZU0xWFDFkA0CSI5vjoZt5eC3GJMGMUY4IgaU");
var sheet = ss.getSheetByName("Sheet1");
var firstRowRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());

var sheetHeaders=firstRowRange.getValues();
// columns start at one, but arrays start at 0, so this is the right number for inserting into an array that will be placed into the columns
var newColumnArrayCount=sheet.getLastColumn();

// initialize new row to be inserted before it gets filled with data
var newRow = new Array(sheetHeaders[0].length);
for(var x=0;x<newColumnArrayCount;x++) newRow[x]="";

for (var i in request.parameters) {
  var foundRow=false;
  for(var x=0;x<sheetHeaders[0].length;x++) {
    if(i.toString().toLowerCase()==sheetHeaders[0][x].toString().toLowerCase()) {
      // newRow[x]=request.parameter[i];  // changed to next two lines per Riël Notermans in G+ App forum (see lead-in comments)
      var currentpar = request.parameter[i];
     
      if (i.toString().toLowerCase() == "posttime") {
        // currentpar = null;
        // currentpar = Date.now(); DOESN'T WORK - SEE https://plus.google.com/u/0/101895821219259188815/posts/MayxoFFQvi8
        newRow[x] = new Date();
      } else
      {
        isNaN(currentpar) ? newRow[x] = currentpar : newRow[x] = Number(currentpar);
      }
      foundRow=true;
      break;
    }
  }
  if(foundRow==false) {
    if(sheet.getLastColumn()==sheet.getMaxColumns()) {
      sheet.insertColumnAfter(sheet.getLastColumn());
    }
    sheetHeaders[0][newColumnArrayCount]=i;
    newRow[newColumnArrayCount]=request.parameter[i];
    firstRowRange=sheet.getRangecft6VGY&(1, 1, 1, sheet.getLastColumn()+1);
    firstRowRange.setValues(sheetHeaders);
    firstRowRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
    sheetHeaders=firstRowRange.getValues();
    newColumnArrayCount++;
  }
}

if(newRow[0]=="") {
  Logger.log("setting date");
  newRow[0]=new Date();
}

// Appends a new row to the bottom of the
// spreadsheet containing the values in the array
// var lastRowRange = sheet.getRange(sheet.getLastRow(), 1, 1, sheet.getLastColumn()); // New from John
sheet.appendRow(newRow);
sheet.getRange(sheet.getLastRow()-1, 1, 1, sheet.getLastColumn()).copyFormatToRange(sheet, 1, sheet.getLastColumn(), sheet.getLastRow(), sheet.getLastRow());  // New from SEBASTIAN CORONA FERNANDEZ 11/8/16 in Sheet chat (!)
sheet.getRange(sheet.getLastRow()-1, 5, 1, 4).copyTo(sheet.getRange(sheet.getLastRow(), 5)); // New from me 11/11/16, to copy columns C-H (time math and totals)

}
}

*** end of code

1 Like