Post-Groovy Google Spreadsheet Integration

Many of us used a nice SmartApp called Simple Event Logger to post SmartThings info to a Google spreadsheet. Now what? Is there a way to do this that doesn’t require a SmartApp?

The answer is yes, with the catch that you need a SmartThings hub and an intermediate computer to bridge between Edge devices and Googlesheets. On your always-on computer like a Raspberry Pi or other, you run a very small, 30-line nodeJS app that will accept HTTP posts from Edge containing the event info and it will use the Google API to post the contents to your spreadsheet.

How do you send the HTTP requests from Edge? You can use my Webrequestor or HTTP Devices drivers, which can be configured with the event info you want (text, number, boolean). The info is appended as a row in your spreadsheet along with a timestamp.

The biggest challenge in setting this up is knowing how to configure authorization in the Google Console to allow the app to have access to your spreadsheet. I’ve addressed this by providing a screenshot-by-screenshot walkthrough, which makes it a quick and painless process.

Summary of requirements:

If this project interests you, please see my github repository that has complete instructions and the nodeJS app to download to your computer.

Thanks to @wptracy for inspiration and thorough testing and feedback on the setup process documentation.

12 Likes

I am trying to find a way to log power and energy usage. Could power and energy be added? this looks like it maybe a way to do this. I have also been working with your MQTT process and wonder if that could be integrated with this approach.

Hey Todd, once again, thanks for the great work. I have so many questions on this one (haha) but will start with the most basic. Are we able to extract the values of a device’s capability? I probably worded that wrong but if I had a device called Aeon Energy Monitor with a capability of Energy Meter that reports kwh, would I be able to record the kwh in Googlesheets using this app? If so, would you be willing to elaborate on how to set that up? BTW, great tutorial on setting up the Google piece - never would have completed that without the step-by-step.

Edit: Sorry, just re-read: “For example, there is no way to send dynamic, variable measurement values like temperature or energy using this method.”

@ragoss, @raisingskell

I don’t think there is any way to log specific device properties without:

a) A custom device driver for the device in question

b) a SmartApp

c) A script that would run on your own computer that uses the SmartThings API to get a device property and then logs it to a spreadsheet (could be done directly or through my nodeJS app).

Thanks for the quick response. So trying a simple example I received a 400 error with gsheet_server showing: “SyntaxError: Unexpected token " in JSON at position 1” for the following event:
image
Is this the straight quotes issue or did I enter the Body or Headers info incorrectly? Thanks.

EDIT: Did it again, answered my own question. On iphone had to disable Smart Punctuation under Settings/General/Keyboard.

One other note for future users, even though the spreadsheet instructions show Date and Event as capitalized, in the app “event” seems to need to be all lower case. Working well now.

That’s an anomaly of the SmartThings app - it likes to capitalize all the first characters in the device Settings - even though they really aren’t, as you can see when you edit the field. It confuses a lot of people…

And yes, everyone needs to watch out for those left/right quotes. On iOS, you just long press the quote key and it will give you the quote options to choose from.

Thanks again. Is there anyway that Web Request #1 can log to one sheet of the book while Web Request #2 logs to a different sheet? Sure would save me a lot of formula writing.

Hmmm sounds like a good thing to add to the enhancements list…

Maybe make it an optional url parameter, i.e.

?sheet=mysheet

Thanks for the consideration - much appreciated. One other crazy suggestion for the ever-growing enhancements list? Whereas the first 5 Web Requests allow additional options, would it be possible to make it an even number such as 6? Since the static events criteria {“event”:“open”} often is binary like on/off, open/closed, etc., an even number of Web Requests with this feature would be convenient. Just a thought.

Yeaaaaaa….maybe. Alternatively you can always create additional webrequestor devices.

Is there anyway to do this with temperature and humidity?