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

Can someone post the code and instructions? I was in process of setting this up when it was withdrawn.

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