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.
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.”
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:
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.
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.
Hi - wanted to let you know that I added the option to specify the sheet name in the JSON body of the HTTP message. Download the latest gsheet_server.js file from my repository.
Unfortunately building HTTP messages with variables like that aren’t currently possible. It would take a custom driver or maybe some clever Rule building.
If you have a Pi or other computer, it would probably be better just to grab the values on some kind of periodic basis using the SmartThings RESTful API. It would take only a few lines of Python or javascript to do and you could still use my nodeJS app for the Google spreadsheet integration - or just incorporate the code in your own app that is getting the values through the API.
If you want to pursue this, I’m happy to help. You wouldn’t use javascript & HTML within a browser, but would rather use nodeJS, assuming you installed node on your computer. Depending on what computer you have, if there is already Python installed, it might be easier to use that. Either one is fine. Direct message me and we can continue the conversation in a separate thread.
yay, I have this mostly working… need a gsheet_server_monitor or something now… got an 11 hour gap in output before I realized that I should check if its still running. I did get an alert that my edgebridge process had died. But, it had actually failed long before that point…it was spewing messages of “HTTP send error sending response:” The only thing hitting it is the edgebridge_monitor, since after setting it up…I found this. beats calling (paid) webhook server to create lines in google sheets.
One problem I’ve run into is that HTTP Device Created Momentary switches don’t show up in Alexa. So, does that mean I’m going to try standing up 100 additional Momentary switches along side the 100 virtual ones that I have now? Things get weird as I push close to the 300 devices mark. Had trimmed down to 277 at one point, but I’m at 289 now. Hmm, hadn’t looked to see if the HTTP Device virtuals show up in IFTTT. Guess not. Switch and Contacts seem to work…could put a routine onto every contact switch to make them momentary… The days of tweaking my own driver and self-publishing it are gone I guess.
Not sure if I still might pursue ‘becoming’ a developer…I’ve done Lua programming (during my configuration management craze… hmm, wasn’t there a DTH for sending events to HammerSpoon on macOS?) But, I stopped being one after being forced into early retirement and then losing everything I own. Was into a number of crazes between those two events…had a dual path fiber ring around my studio apartment…oh well…
Alexa doesn’t recognize these kind of devices in SmartThings. Depending on exactly what you want to do, you’d need a switch if you want to control it from Alexa; or you’d need a contact if you want to trigger an Alexa routine. That’s why the virtual Alexa “switches” in SmartThings have both a switch and contact that are synchronized. I have not done this in my HTTP switch, but if that’s something you would find useful, I could provide it.
Well, I guess the issue was that I replaced my DTH Alexa Virtual Buttons with vEdge Momentary devices, and was hoping to replace these with HTTP Buttons…instead of doubling my devices…or using a Web Requestor Multi with every 5. I’m up to 308 devices, where 11 of them are Web Requestor Multis.
@TAustin Great work. I use the Web Requester a lot.
Your proposal of retrieving temperature values at regular intervals would be ideal for my needs. Could you guide me on how to write a script that can fetch values from this API?
I would be very grateful for any assistance you can provide.
