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

I see the problem - you changed the sheet name in line 5 of the script, but didn’t change the name of the sheet in the spreadsheet - it’s still the default “Sheet1”.

var sheet = ss.getSheetByName("Logs/ST_Scripted_Log");

(I don’t know if slashes work in there, btw).

Okay it changes the error I get somewhat, so that’s progress… thanks! I was thinking “sheet” means the whole thing (it’s called Google Sheets, right?), but hereby just learned Sheet1 means what they call a worksheet or tab in Excel. Not the whole spreadsheet. Okay. A typical but obvious rookie mistake. It would be great if you added a phrase to step 6c saying. “Note, this is the name of the individual sheet (worksheet tab at bottom), not the entire spreadsheet”.

Can you help me a little, Step 14 reads:

Step 14: In the SmartThings IDE, create a new Smartapp From Code using smartapps/cschwer/google-sheets-logging.src/google-sheets-logging.groovy

I was able to figure out, and think it would be really helpful to novices to flesh out the first part (you’re welcome to cut and paste this). It’s from the PC interface if it matters:

Step 14a: Go to the the SmartThings IDE at https://graph.api.smartthings.com. You may need to make an account if you haven't already, but if you're this serious about ST data, you want access to the IDE anyway.

Step 14b: Go to "My SmartApps", hit "+ New SmartApp" on the right, then select the "From Code" tab.

Step 14c:

I am a bit lost on “using smartapps/cschwer/google-sheets-logging.src/google-sheets-logging.groovy”. Is this somewhere on ST or gitHub? Searching for that on either site returns odd things and I don’t want to make a wrong guess. If I google that phrase I get other dubious results. If I put it directly into ST or github URLs like:

https://github.com/smartapps/cschwer/google-sheets-logging.src/google-sheets-logging.groovy

I get errors on both sites.

Can you be a little more explicit here? Thanks.

See post 125 for the active repository

By that I meant the code in the GitHub repository. I’ll update that in a moment.

Yes okay now I see. May I suggest using my expanded text above and for 14c say:

Use the code from smartapps/cschwer/google-sheets-logging.src/google-sheets-logging.groovy. This is found directly above (before Step 1). Drill down to the code and copy it out.

I hope I don’t sound too lame. But every one of us once had their very first time for using GitHub or google sheets as a web app… this is my first time for both, without anyone to help but the board. Your instructions really are great. If you do the little fixes I suggest, they’ll be great even for complete github and sheet app noobs.

Sorry to keep bugging everyone - and thanks especially to John - I seem to be very close but still something’s not right.

At the point of Step 10, if I hit the hyperlinked “latest code” in the popup shown there, I get the message:
The script completed but did not return anything.
But it does append a row with a Timestamp (but only a timestamp) into the sheet.

However if I copy the web app URL from Step 10 and add ?Temp1=15&Temp2=30 (per Step 13), I get:
TypeError: Cannot call method "getRange" of null. (line 11, file "Code", project "ST_Logging_Script")
I don’t know why it would say this of line 11 because that’s a blank line. Ack.

On the plus side, it looks like I did make my SmartThings SmartApp okay. it is chugging away with values from sensors being posted to the log in the bottom of the SmartApp’s window. Every now and then it says something like:

4ad03058-9e1a-40d0-8c87-9adc8e82c3d2 4:59:23 PM: debug Google accepted event(s) 4ad03058-9e1a-40d0-8c87-9adc8e82c3d2 4:59:23 PM: debug 200 4ad03058-9e1a-40d0-8c87-9adc8e82c3d2 4:59:22 PM: debug https://script.google.com/macros/s/[snipped]/exec?Time=2016-6-5+16%3A54%3A21&Aeon+Mains+Meter+power=4702&Aeon+Mains+Meter+energy=2482.573&Door+Sensor+temperature=106&Aeon+Meter+Switch+energy=564.050&Aeon+Meter+Switch+power=620

Despite all this, the only thing showing up in the sheet is 4 timestamps. From when I performed four tests using that first step described at the beginning of this post. John if you can see what stupid thing I’ve done wrong (again), it would be great. I’d love to be logging this data. I am also logging the electricity used by every breaker in my house on a minute-by-minute granularity with Efergy (www.Efergy.com)… it would be wonderful to compare this against house temperatures and especially attic temperature from my SmartThings sensors, to get a grip on heat loads and degree days relative to A/C triggering.

John- I appreciate what you’re doing in picking up this code. I’m not sure the limit of your support, so this request may exceed your intentions. Just before the time @Charles_Schwer stopped developing for ST, I asked him to support Gmail addresses that were not of the form abc@gmail.com, I.e. not Gmail domains. I struggled with getting it to work until I switched to using an alternate standard-form Gmail address - then it was and has been great. But I’d like to use my mainstream Gmail account which is my domain. Understand if this kind of support is not in your plan. Thanks.

I have Google Apps on a domain I own, so at least I can take a look at what is needed to make that work. If I figure anything out, I’ll let you know.

@RedKnight looks like your spreadsheet is working now. (You should undo the share with me)

I’ve updated the instructions with your suggestions. However, I don’t take credit for them; @Charles_Schwer did all the good parts.

1 Like

I got it, everyone. I was not paying attention to the Version in the publishing popup. It was stuck on Version 1 when I should have been changing this to New every time I changed (fixed) my code. So instead, it was running the old/bad code over and over. As in, come on, it’s the code that’s right in front of me when I hit Publish… but could that possibly be the code it will publish? lord no, lol. It will be wonderful if a little note about revising code and the Version in the popup can be put in the directions.

I have come to believe in God due to how many things go wrong only for me. It’s true! and also sadly funny

I’m glad you got this working. I think this problem happened to me when I first wrote the instructions on how to create the spreadsheet from scratch. I updated the script and it didn’t take effect. I eventually just started over and it all worked. Thanks for figuring this out! I’ve updated the instructions to include the text you suggested.

You’ve been wonderful John, thank you so much!

Does anyone know what I need to delete so that I can have the data always got to row 2 and overwrite and previous data.

Many thanks,
Stephen

Do you mean only keeping 1 row of data?

Note that the Google Script code is written to write a new row. You’d have to modify that if you always wanted to just write to row 2. (it looks like most of the code would in fact go away)

I’ve just pushed v1.1 which:

  • adds support for non-Google Apps domains
  • changes the timestamp format to “M/D/Y H:M:S”, which is what Sheets inserts for timestamps automatically
  • adds dimmer switches to devices (but I’ve not tested them yet)

This only changes the SmartApp. The Google helper script code remains the same. You only need update the SmartApp via the IDE.

4 Likes

That’s great, John. Thanks! I’m traveling now with only a tablet for internet. I’ll be home this weekend and will implement as soon as I can get in front of a computer.

If you play around in the groovy code you can manually add items to your google spreadsheet log with the following:

def url="https://script.google.com/macros/s/<URL_KEY>/exec?obs_temp_f=${obs.temp_f}&precip_1hr_metric=${obs.precip_1hr_metric}&precip_today_metric=${precip_today_metric}"
def putParams = [
	uri: url]

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

edit: you can simplify the above down to one line if you forgo the error logging:

httpGet([uri:"https://script.google.com/macros/s/<URK_KEY>/exec?precip_today_metric=${precip_today_metric}"])

In this example I added some weather unsupported precipitation data to the Smart Weather Station Tile device handler.

Is it possible for a Device Handler to create an API for other Device Handlers or SmartApps to use? If so we could simplify this with something like
logGoogleSheets("precip_today_metric",precip_today_metric)

1 Like

Thanks so much, John! Kevin too. I’m just an old amateur VBA coder and a little lost trying to learn these new languages and paradigm, and how they interact. Your posts and work are so helpful. I am still really amazed that we can be capturing all this - delivering even better value for a data monger like me than any paid home monitoring or alarm services - all for free. I almost went with Wink but decided to go with SmartThings due to its strong development and user community. Looks like I chose wisely.

If I could Like your posts more, I would. :grin:

Have you also added something like this? (just wondering)

obs = getWeatherFeature("conditions" , zipcode)