So I got this all installed and it’s logging my energy monitor to a Google Spreadsheet. Couple questions, anytime I apply any formulas to the data in the spreadsheet it stops working and won’t write new data. I’m not putting in any new info in cell column it’s writing to so nothing is getting in the way… Also how do I get smartthings to report more then just KWH to the sheet? In the app I have amps, watts etc…
Hi D,
Not sure what the problem is with your first item. All I can think of is, are you making your formulas in columns that don’t have names? It may confuse the script if there are columns without names. I may be wrong here, but that’s all I can think of. If so, just give them any name that’s not the same as the real data columns. If this isn’t the issue, I personally would need more details to see what you are doing. Which must be something unusual, is all I can guess. I personally have a half-dozen formula (not data) columns which are copied forward to every new row, as described in message 201 above.
I’ve never had a problem with calculations in some columns (not output by the logger) stopping my script.
For the second issue, if you look at your app in the simulator in the IDE, there’s a box called “Sensor Attributes (comma delimited)” near the bottom. Put additional attributes you want to log here, and also checkmark which sensors it applies to in the box directly above that one.
Use the name that the attribute is called in the device’s properties. For example, in the IDE, go to “My Devices”, click on a device of interest, and you’ll see the names of sensors under Current States. Use the exact terms you see there.
I have tried to put more than one attribute into this field, separated by a comma, but it always gives me an error. So I can only get it to work for one type of attribute. If anyone can get it to work for more than one attribute, please tell us the exact phrase you use. This gives me an error: “acceleration,tamper” although they both work individually.
It is also probably possible to make your own categories with a little bit of coding. You can see examples of how it works there in the code. Then they would show up as regular, selectable boxes in the simulator, like other sensors do.
Update for anyone interested:
I looked into Google’s “2 million cell limit per Sheet” via testing and asking on the Google Docs Sheets forum, and the answer seems pretty clear:
The 2M limit is the number of rows times columns that exist in each Sheet, pure and simple. Empty cells count and empty columns do too (!). By default spreadsheets have 26 columns (A to Z), so delete columns out to Z that you’re not using or they’ll count against you.
It applies to total cells in one Sheet; including ones in subsheets (tabs). But you can have many Sheets with 2M cells.
All this is easily tested by adding blank rows (then filling them in with data or not - makes no difference). For example you can have 100,000 rows if you have 20 columns, and not a single row more. Etc. You can add up to 40,000 rows at a time so it’s easy to check.
Happy new year!
My New Year’s resolution is to eat less data. 
Mike
@Charles_Schwer, Thanks for creating this smart app. I am new to smartthings, I tyring to get this app working for me. I have followed all the steps for creating the google spreadsheet, script and the smart app. I have published the smart app and can see it under My smart apps in the smartthings api webiste. But I still can’t see it on the smartthings app under My apps. Can you please let me know what I might be doing wrong?
In the SmartThings app, Marketplace, SmartApps tab, scroll to bottom, MyApps. I think it should show in there; been a while since I added one and they have changed the GUI a bit since then.
Is the range selector working for you on your chart? I can’t get it to show up, and my google search turns up people complaining about it being broken for nearly a year.
Hi. This is my first time posting and first time trying to do anything like this, so please be kind… 
I followed all of the steps on this page (https://github.com/cschwer/googleDocsLogging), and I see the SmartApp online, but I don’t see My Apps on the app on my phone (this step: https://github.com/cschwer/googleDocsLogging#install-the-smartapp)
Any idea what I may have done wrong?
Thank you!
Hi JT,
It is true you won’t see it in the ST (SmartThings) app like a regular app. (You won’t see it under the Automation button along bottom of ST app, then SmartApps tab.)
You will see it is as a SmartApp for each device it is logging. If you look at the device’s info (My Home / Things / select one) then go to SmartApps tab, you should have an entry here saying “Google Sheets Logging” (or whatever you named it, for your copy).
I don’t actually try to control it from the phone SmartApp (except perhaps to remove it from a device). I control it from the ST web API.
I am free-handing here (without checking back against the documentaiton), but once you have it copied into the web API for your hub (under “My SmartApps”), AND you have made a Google Sheet with the reciprocating code that receives that output,
Once all that is done,
You will then have to go to “My SmartApps” in the web API, find where you have the Google Sheets Logging code, and then do the following:
- Save it
- Publish it … “For Me”
- Hit Simulator
- Set Location (your hub). A long drop down of Preferences will appear.
- Select which devices to log.
- Be sure to put the URL key. (This is the URL for the Sheets script receiving the output. Not the URL of the Sheet itself.)
- Hit Install.
You should then start seeing a Log which shows you the string being built as time goes by and devices report data. Then when your queue time is up, you will see an output message in the log.
Does this help?
If it does, please give some feedback on where you got off track. So we might update the docs to better assist.
Good luck!
Thank you so much for the detailed response!
Under the individual sensor on the app on my phone, I don’t have anything under SmartApps except for IFTTT.
I also followed the steps below, but don’t have anything under “List Devices” under my hub.
Also, did I miss in the original instructions all of the info on going into the web api and publishing, hitting simulator, etc…? If it’s there and I missed it I apologize.
One other thing I’m trying to figure out, is how does all of this google spreadsheet, github, smarthings api ‘stuff’, get associated with my actual SmartThings account. Where is it pulling/getting that info from?
Finally, and I wonder if this is the issue, I used a different gmail account to create the spreadsheet, than I use for everything else (including my SmartThings account). Do I need to use google sheets under my regular email address? I ran the test on the google sheet and it did record data, so the sheet itself is fine, I think I’m just not property connecting my SmartThings account/devices to the whole process.
Thanks again!
Further update: When I click on simulator and then location, I don’t have any listed and I have to create one. But if I go to ‘My Locations’ at the top of the api page, I do have my “Home” location listed along with my devices. Any idea why this “Home” location isn’t showing under simulator/location?
Your last post sounds like something is wrong. You should have your hub already available to be selected in the app’s Location dropdown. You should not have to create one. Is your hub showing under My Hubs?
So I seem to be getting closer. After going into and out of My Locations and My Hubs (and sometimes seeing my home hub and others note), it now seems to be there correctly. So now when I go to MySmartApps and click simulator I do see Home as the location. I clicked Set Location, and then I get this:
Preferences
Google Sheets Logging Done
Create a new logging automation.
Google Sheets Logging Automation
About
Version 1.3
Installation instructions
Uninstall / Install
So I’m not getting a long drop down of preferences as mentioned in the instructions above. I can click on Google Sheets Logging Automation, but I just get a spinning icon next to Preferences. So something is either still wrong, or I’m not in the right spot as I’m not seeing where to pick my devices and add the URL key.
Thanks again!
Just wondering if anyone had any further thoughts as I’m definitely stuck. I tried a couple different browsers to make sure it wasn’t just an incompatibility with the browser (that was ‘hiding’ the options), but nothing has worked.
Again, I can now see/set my hub, but I don’t get the preferences to select the sensors and add the URL key.
Thanks again!
JT, did you get it to work? It sounds to me like there could be something wrong (or not set or whatever) with your API account. In other words, that no SmartApp would work, not just this one. You might ask in a more general part of the forum, about a SmartApp not working right for you. Or even call ST support. If all else fails, you can PM me and we could Skype share screens. Maybe I will see something, maybe not. Ultimately though if you are using ST, you really need to be able to use SmartApps.
Do any others work for you; have you tried any others? Where you have loaded code in through the web/PC API.
Formula for Time Of Day as a Decimal Number from Sheets Date Value
If anyone is interested, here’s a formula that will take the time of day from a Google date value, and turn it into a decimal number. For example, “1/17/17 10:36 a.m.” would become ONLY 10.60, without the date. (That’s 10:36 as a number.)
If the datetimestamp is in B2, just say:
=24*TIMEVALUE(B2)
The value of having the hour as a regular numeric is that now you can use filters to do more intuitive things like “show values not between 7 and 19” to see only night data in your log. Or chart when you left and came home (Presence detector) versus time of day. Or whatever.
You can also use it in other formulas. Datetime values incorporate the date as integer and the time as its decimal (or some such rot), which can make time of day a real hassle to work with.
You can’t otherwise put this formula in a filter. (If you use TIMEVALUE() in a custom formula for a Sheets filter, how would you put the cell it applies to inside the parentheses?) If anyone knows a way, please say.
Here’s a variant that uses my LastTime of reporting data, then subtracts 5 minutes (half of 10) because the best a priori estimate of when the sensor reading is for, is arguably the middle of the time interval (10 minutes, in my case). Also adjusts for wrap-around at midnight:
=24*(TIMEVALUE(B2)-(5/24/60))+if(timevalue(B2)<(5/24/60),24,0)
Dividing by 24 and 60 turns the 5 into minutes as the value is represented internally. You could write the resulting value if you want, shrug.
Thanks so much for getting back to me. I tried a similar logging app and was able to install it and see all the preferences when I picked my home location. I had some trouble selecting the correct options thereafter (still working on it) but at least I was able to see the preferences and pick the sensors.
So it seems that I either copied/pasted incorrectly from your info, or something else is wrong.
In any case, I’ll probably delete it all and start over and see what happens. Can you point me to the latest code that I should put in the app? The way I did it was via the github repository, but maybe I’ll just cut and paste the code directly.
On a separate note, I do actually see the app under SmartApps on my phone. I’m able to select the sensors and even paste in the code that I emailed to myself. But I don’t see the google sheet updating.
Thanks again!
So earlier today (hours and hours ago) I deleted it all and then tried it again. Same exact issue as before.
However, I just noticed this in the live logging:
3:12:39 PM: error java.lang.NullPointerException @ line 283
3:12:39 PM: debug Logging to queue Office+temperature = 65
No idea why there would just be the two entries (rather than errors all day), but any thought on what the error means?
Also, it didn’t update the spreadsheet.
Couple more items in the live logging:
4:38:36 PM: error java.lang.NullPointerException @ line 283
4:38:36 PM: debug Logging to queue family+room+temperature = 64
4:33:37 PM: error java.lang.NullPointerException @ line 283
4:33:37 PM: debug Logging to queue family+room+temperature = 65
3:12:39 PM: error java.lang.NullPointerException @ line 283
3:12:39 PM: debug Logging to queue Office+temperature = 65
Hi JT, I don’t know what to say from afar. Have you changed your code, or is line 283 the same as at GitHub:
def eventTime = URLEncoder.encode(evt.date.format( 'M/d/yyyy HH:mm:ss', location.timeZone ))
In which case, you may have a problem with the time in your hub. @Jacques_Aucamp had a problem with it not updating, see this message and ones near it. The message directly above that shows how to see more details of what’s going on with your hub and its current smartapp status and variable values.
Are any rows or time values being output to the log? You could try sticking a line into your code just before line 283 saying something like
log.debug(evt.date)
So it will try to show the timestamp in the log. I am no expert with groovy so I may have phrased this wrong, but you get the idea. There is probably a sandbox somewhere to experiment with.
I am grasping at straws here, hope it helps.
Correct, line 283 is:
def eventTime = URLEncoder.encode(evt.date.format( ‘M/d/yyyy HH:mm:ss’, location.timeZone ))
Thanks for the trail about the other user who was having issues. That’s all a bit beyond my level at this point though.
Nothing is hitting the spreadsheet.
I put in the code above line 283 like you suggested and I’ll see what happens.
Thank you again.
So after putting in the code at 283, it of course moved the prior code down to 284, so now the error shows on 284, but I do get a time stamp in the logging (still nothing on spreadsheet):
8:26:00 AM: error java.lang.NullPointerException @ line 284
8:26:00 AM: debug Thu Jan 19 13:26:00 UTC 2017
8:26:00 AM: debug Logging to queue Master+Bedroom+temperature = 63