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

I was able to get my hands on the Groovy code, but this requires a backend Google Sheets spreadsheet to handle the URL requests. Can someone make that public so others can copy it?

Thank you!

How would we make the weather log to google sheets (temp humidity and precipitation in mm) every hour? Currently it logs temp and humidity about once per day. Trying to add the precip_1hr_metric paramter to the weather underground based device type, but don’t see how to log it.

I too could use a copy of the google spreadsheet if anyone has it still. Could you please make a copy and share it here for the community?

Mucho Gracias!

The code is still available on GitHub in several forks. (see below)

Thank you for at least leaving it licensed this way, so as to not prevent others from using this.

2 Likes

I have put the Google Script code into a clone of the original repo (I started from @jdetmold’s ) and updated the instructions on how to create a new spreadsheet from scratch. Thus, you no longer need to start with a copy of a spreadsheet.


(updated to point to master branch)

6 Likes

Thanks, I instructions were excellent and easy to follow. I keep running into an error though I get the following:

TypeError: Cannot call method “getRange” of null. (line 6, file “Code”, project “Event Logger Home”)

whenever I try to log into the app or test it. Any ideas?

Thanks for any help!

This is a bit from memory, but try typing something into cell A1, just to “initialize” the sheet.

That is covered in the current instructions. @0121stephen, did you do this?
step 4: Add the single title "Time" in cell A1. You might consider selecting View -> Freeze -> 1 Row

[quote=“0121stephen, post:126, topic:36719”]
instructions were excellent and easy to follow.
[/quote]I take no credit here. That is almost all @Charles_Schwer’s work.

I did, but in my haste I tried it without typing first and got an error that I was hoping might be your case as well, but unfortunately I’ve exhausted my expertise with this app. Sorry I couldn’t be more help.

Thanks for your help. I had not shared the app with everyone including annoyomous once I did this it started working great.

One thing, the app seems to give me a “snap shot in time”, i.e. if there is no motion when the app push fires I don’t get a reading and it is possible for somebody to walk through a room, trigger a motion sensor but not provide a reading or open and door and close it gain and I don’t get a reading unless I set the push delay to 0.

Is it possible to get the app collate instances in the last 5 minutes or longer and return a positive reading if there has been any motion during this period?

Thanks

I second this

I’ve updated the the icons and merged to the master branch:
github integraion: owner loverso-smartthings / name googleDocsLogging / branch master

1 Like

Hi, I am getting the same error as 0121stephen (above), namely,

TypeError: Cannot call method "getRange" of null. (line 11, file "Code", project "ST_Logging_Script")

I did follow step 4 (putting “Time” in A1). I have saved everything, closed it, and opened it again to make sure everything took and was still there (and it was).

I also shared it with everyone, including Anonymous.

I have never tried a Sheets script before, but greatly appreciate the detailed instructions.

Can anyone help? I would love to log my ST stuff.

Edit: Does it matter if it’s in a subdirectory of my G Drive? Do I need to put that in the name (step 6c, line 5 of Code)? I tried variations that included it, but it didn’t seem to help. (Backslash or forward slash in this case?)

did you try using the test url that writes generic data? it’s near the end of the instructions I think

I stopped at step 12 (testing it) because when I try to test it, it gives that error; the script is not working. Is that the step you mean?

If you want you can PM me a link to the sheet and I’ll look at it.

Two things I note: my sheet is private to just me. Also, in the Code.gs I have, line 11 is an empty line and the getRange is on line 6.

I’m only logging temperature and power usage at the moment; I hadn’t thought to log motion detected. I can’t say I’ll do this, but it’s interesting. (It is open source, so take a crack at it)!

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