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

Nothing wrong. That’s the timestamp raw value. You will need to format the column in Sheets.

Format > Number > Date time.

Great thanks.

1 Like

@tamathumper Has that been deployed in the smart app or do I need to add that myself? If I need to add it, where should I do that? Thanks!

1m
Check out post 371 in this thread - that has my full code in it, and you’ll see that snippet in there.

This is exactly what I am looking for. I have no idea how to get to that point. What you mind offering a few suggestions to get me pointed in the right direction? Basically all I have up to this point is Google Sheets and the Smart App installed.

1 Like

Good stuff!!! I think I can handle that. Unfortunately, this had led me to 3 more questions, and I hope not to bother you anymore. I looked around but couldn’t find the info. I have about 3 profiles/events (scenarios if you will) that I would like to log. I see in the smart app where I can name the profile of the devices I want to monitor. Question 1…how would I log each profile on its own sheet (basically 3 tabs at the bottom of the page Sheet 1 for scenario 1, Sheet 2 for scenario 2, Sheet 3 for scenario 3). Question 2…how can I reset the data on a sheet (basically I have 3 days worth of data on some devices and I want to reset back to “starting now”. The reason I want to know this is because I read somewhere while searching for answers that a sheet can have only so much data once the page fills up). Question 3…How do I “include the last row the sheet in data ranges”? Again, great work on your part and your assistance is not unappreciated.

Q1 I’m not sure, I’ve done this by logging everything to one sheet then filtering out the data I need into separate sheets from there.

Q2 I don’t know how to do this automatically, I use the following code to get the most recent 75 logged events for the purposes of making a graph:
=QUERY(Sheet1!A:B, “SELECT A, B ORDER BY A DESC LIMIT 75”)
which outputs the most recent 75 results from sheet 1, columns A and B. The data limit is 2 million cells per spreadsheet - all sheets within one spreadsheet are counted and blank cells count too but depending on what and how often you’re logging that could take a VERY long time to fill up. for example i’ve been logging temperature data for just under a month, and the total cell count in that spreadsheet is 7224 with 3532 data points.
ETA: i’ve filled up one spreadsheet with logging but that was 8130 records over 245 columns. far more than is realistically useful

Q3: use the data range !A1:A (for column A, replace with the letter of the column you want and replace the 1 with the starting cell)

I have the sheets I created posted in this thread, you should be able to use that to get you started. Let me know if you get stuck.

Has anyone managed to generate a decent step type graph for motion or door data based on “1” and “0” values logged using this?

For Example see the attached graph. I made this manually by adding timestamps 1s before a value change and putting the previous value, then filling in all blanks so every cell was populated.

Tried to do this in both google sheets and excel but struggling. My eventual application is for thermostat on and off states so I can track actual temperatures against the heating on/off status.

Thanks
Mike

This is the best I could come up with:

Since Sheets doesn’t have this type of chart built-in (from what I could find), the best workaround is to offset each device to “up” to unique values. Someone else came up with this idea, so I can’t take credit for it… Let the smart app do it’s normal logging 1/0, on/off or whatever. Then in Sheets create a new column for each device you want to chart, and convert the on/off like this (the equation is copied down to fill all rows):

Column C=if($B4="Living Room Motion active",10.9,10.1)
Column D=if($B4="Upstairs Motion active",9.9,9.1)
Column E=if($B4="SHM Armed/Away",6,if($B4="SHM Armed/Stay",5.5,if($B4="SHM Disarmed",5,if($B4="AlarmTriggered on",8,E3))))

Actually I’m doing this in my EventHistory Sheet which is not really using the SmartApp in this thread. It uses WebCoRE to log in Sheets every time a selected device changes state. The SmartApp in this thread works better for numerical data like temperature where it’s okay to sample every 5 minutes and don’t care about each exact data point.

I’m having a new issue with this SmartApp. I am getting duplicate entries in Sheets from time to time. See the log below for one example:

2:38:04 PM: debug Google accepted event(s)
2:38:04 PM: debug 200
2:38:03 PM: debug https://script.google.com/macros/s/AKfycbzEDvMuRcvht2eLirOLjcguDlUkeWE-lPUglL51ZF65RyFmgW8-/exec?Stairs+Multisensor+temperature=75.12&Shed+Door+temperature=84&Time=7%2F30%2F2018+14%3A33%3A03&Beverage+Fridge+Door+temperature=40&Home+Energy+Meter+energy=1521.019&Home+Energy+Meter+power=1622.610
2:38:03 PM: debug url [https://script.google.com/macros/s/AKfycbzEDvMuRcvht2eLirOLjcguDlUkeWE-lPUglL51ZF65RyFmgW8-/exec?]
2:38:03 PM: debug Processing Queue
2:38:04 PM: debug Google accepted event(s)
2:38:04 PM: debug 200
2:38:03 PM: debug https://script.google.com/macros/s/AKfycbzEDvMuRcvht2eLirOLjcguDlUkeWE-lPUglL51ZF65RyFmgW8-/exec?Stairs+Multisensor+temperature=75.12&Shed+Door+temperature=84&Time=7%2F30%2F2018+14%3A33%3A03&Beverage+Fridge+Door+temperature=40&Home+Energy+Meter+energy=1521.019&Home+Energy+Meter+power=1622.610
2:38:03 PM: debug url [https://script.google.com/macros/s/AKfycbzEDvMuRcvht2eLirOLjcguDlUkeWE-lPUglL51ZF65RyFmgW8-/exec?]
2:38:03 PM: debug Processing Queue

And then it showing-up in my Sheets:

I’ve had some issue in the past where I would get multiple entries in Sheets for the same timestamp (yet the data was changing) because the queue wasn’t flushing in the SmartApp/ST. The latter was caused as my Sheet grew huge and it was taking too much time apply some conditional formatting before saying ‘I’m done.’ This would actually show up as an error in the Live Logging whereas this new duplicate entry issue just looks like ST sending it twice.

Anyone have any ideas?

EDIT: Whelp … I just notice the ‘power’ value in the string being sent in those dupes is for the prior timestamp entry (14:33). So I’m wondering if this is in fact similar to my old issue. But there is no error in Live Logging.

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.

*** 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

Thanks for that detailed run down of what you are using. I’ll give it some thought and see if I can capitalize on it.

This is what I would see but on a much smaller file. My issue was with the conditional formatting I was doing in Sheets, Sheets was not responding to SmartThings fast enough (the conditional formatting would take time on each new data row add) so the SmartApp would think it had failed. And from what I can tell, the SmartApp would not purge it’s queue of data although it would add new data points if there were any to report. The SmartApp would then send the whole queue again (with the old timestamp). I personally got around this by cleaning out my Sheets rows from time to time but it’s a kludge.

This new dupe issue I have is slightly different. The queue is clearing (mostly, see my comment above about the power value seeming to hang over from the prior send) but sometimes it sends twice.

Hi Nezmo, okay, this is informative to me … I never understood just why mine starts repeating when there’s a lot of data. (There are others for whom this is a problem, too.) But given what you said, I bet my problem, too, is simply that Google is not giving a Success fast enough for SmartThing’s liking. In your case it’s because of conditional data… in my case, it’s because it takes so long to process, once the Sheet itself gets so long.

I never considered the possibility that the error level feedback might be the problem with the Time variable. Maybe the code could be revisited for a fix, then. Or maybe there’s some way to increase the feedback time interval?

This is what I was hoping for but to date I can’t figure out where or how.

FYI, in the case of the many multiple entries for the same date like we are describing here I actually got an error in Live Logging. I reported it further up in this thread …

I am so sorry to bump this thread, but I am stuck.

I have followed all the code to get the data to use last value if there is not a new reported value. However when I do this, it applies the last value to the time stamp as well and thus the time never updates. Could someone share or help with their code that allows me to back-fill the data if there is no change between reporting intervals but refresh the timestamp on each new row?

Can this be used with webcore to log a few variables to a spreadsheet?

@rswing, sorry for the delay - I was busy back then and forgot -

My post a few messages before talks about how the time stamp in the existing code has problems, and I posted workarounds. It’s a long post (due to including a lot of code, much of it the same old CSchwer base code), but shows how to put alternate time stamps into your CSchwer ST code and a separate one in your Google Sheets code, if you want.

@Wajo357, I don’t know about it working with webcore. I don’t know what that is.

The CSchwer log code works directly with SmartThings to output data to a Google Sheet you make. It’s all free. I don’t think you’d want a third app in there… it would probably get messy.

Maybe someone who knows Webcore can speak up. But I think the answer is probably No. Nor would you probably want to involve some other app … you’d have to edit both CSchwer and Webcore to talk to each other then, I bet.