I have been a smartthings user for a while now and have seen it go through many changes, including the Samsung acquisition and Classic App sunset (RIP!) into the current version. One of the things I use heavily is the Smartthings IDE to add custom Smartapps and Device handlers. CoRE was one of my favorites, but I never migrated to the Web version and didn’t have the time to learn the new coding system.
Some of you may know about this already but I just discovered a way to automatically log data – anything that smartthings can generate such as switch on or off states, energy usage/power consumption, etc. – to Google Sheets.
As I am a data and Excel nerd, this is awesome. While the platform itself has a lot of functionality, it is always nice to be able to have this kind of rich data logged and imported into a spreadsheet instead of just using the App. And automatically is even better!
An example use case is for tax purposes and business efficiency to save on costs. Energy usage by your home business may be tax-deductible on your utility bill based on square footage or specific use (depending on your business type). This guide is for logging specific electricity use of your office or business operations.
My home office and studio consumes a lot of energy such as studio lights, computers and servers to render videos, keeping the nights on at night, office printers, fans, etc.
If you have a smart energy plug with power metering, not only can it turn a device on or off through Smartthings, but also record energy consumption in Watts as well as usage in kWh. This is not anything new to most of you. But this is much more advantageous than using say, a Kill-A-Watt because it brings it to the year 2021 and imports data into Smartthings. This is because the native smart energy metering for 3rd party smart devices isn’t very functional. It displays a simple graph in day or hourly increments. However, if you want to look back or even keep a running total of this data, it wouldn’t make sense to do it by hand. This guide will show you how to automatically import this data into Google Sheets in addition to its normal functionality.
Now, this guide is considered advanced, but it is pretty straightforward if you are familiar with logging into the Smartthings IDE, adding a Smartapp through code, saving it, and then adding the new Smartapp in the App itself. The Smartapp I used was by user krlaframboise called Simple Event Logger – SmartApp v 1.5.
Here are the two main issues I ran into (due to being a noob and user-error):
- When testing the new Webapp created on Google Sheets, I kept getting an error related to script function doGet. This happened no matter what I did and I even tried a simple Hello World script, but go the same error message. Turns out you have to click the little Save Floppy icon in the code itself before you Deploy it as a Smartapp.
I think I have gotten so used to things being on the Cloud these days and not really having to manually save things on Google’s Suite of services.
- Ever since the Smartthings App redesign, I haven’t really messed around with SmartApps. I love and hate the new user interface. It still runs slow and things are hard to find, but adding a new SmartApp you just published is even harder to find! By the way, you also have to publish your SmartApp by clicking on the Code (top right) button on Smarthings IDE. I usually do this, but couldn’t find the Publish button for the life of me. To add your new Simple Event Logger Smartapp in the new Smartthings, go to the Automation tab, + button, Add routine, Discover, then scroll all the way down to your new Custom Smartapp. From there, it’s pretty self-explanatory for settings. You just choose which devices you want to log, e.g., energy meters, paste the WebApp URL that was generated by Google Sheets Script Editor, give the SmartApp a recognizable name, and save.
Pretty cool stuff. Once you have the data in Google Sheets, you can do more powerful things like filtering by device, finding averages, making charts, etc.
I took this one step further because the kWh is a running total from an arbitrary time. You can reset this meter back to 0, but this is much harder to do in the new Smartthings App. In fact, I could not find a native way to do this in the App (there used to be a reset button in the device page itself) without doing a workaround.
I subscribe to SharpTools (very awesome by the way if you don’t use it) and it has the ability to execute device actions such as resetting energy meters. I believe you can do this on the web interface, but if you have Tasker, you can execute a reset as a plugin through Tasker.
Tasker > SharpTools Plugin > Smartthings Device
What I wanted to do next was have the energy meter automatically reset to 0 kwH every quarter because I pay quarterly taxes and want the power meters keep a running total for each specific tax quarter.
Resetting energy and power meters automatically through tasker and SharpTools plugin:
- Create a Tasker Profile – Date. I chose the 1st of the month for Jan, Apr, Jun, and Sep to correlate with the US tax dates for the start of each quarter.
- Create a new Task – for this to work, you have to: (1) be subscribed to SharpTools and (2) have their Tasker plugin.
- Choose your device that supports energy monitoring.
- Choose RESET as the action.
- Repeat step 3 to add additional devices.
- (Optional) Add a notification to alert you when this Tasker macro runs automatically.
- Save your changes by closing out the menus and tapping the checkmark at the top.
Now, at the start of every 1st of the month, Tasker will execute the SharpTools plugin to Reset the energy meters for the devices that you specify.
When you combine this with the Simple Event Logger SmartApp, Google Sheets will keep a running total of kWh usage from the start of each tax quarter, then reset to 0 at the start of the next one.
Come tax time, log into the Google Sheets and find the row that corresponds with the closest time and date to each new tax quarter. Because the Simple Event Logger logs everything in one big dump, you can then copy and paste these entered of interest into yet another Google Sheets to keep for tax records and reduce the clutter.
These numbers make me sad as I pay a pretty high kWh here in California. Hoping to go solar very soon!