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

Really nice graphs. Are you using Aeon HEM for the graph? and how did you incorporate the cost data?

I will post here after work how to do it.

Okay great thanks

Jason or Tama, can you show some screen captures from your phone? An overview, and a close up. Not your PC. Thanks.

Screen shots of what?

Is this thread/topic depreciated. Post 1 is deleted.

No, this is the official thread for Google Sheets Logging as far as I know. @jlv currently has the latest version in github, see post 161. Based on github, looks like version 1.3 is latest.

Personally I would like everyone who shares a SmartApp or DH to create a new thread and as needed, change the title/first post to indicate latest version, github url, release notes, etc
 It gets confusing when multiple variations of a DH are in one thread, sometimes with same unchanged version/name/header info.

1 Like

Jason, whenever you get a chance can you post how you got your charts done

ok, this was a big pain to get done simply because of the formulas to pull in the correct data.

so I have a master sheet that collects the data. I then have a sub sheet that calls the data from the master based on the criteria in the main formula. So the below sheet is the sub sheet that pulls in the data. I have the main formula pulling data by month and leaving out any blanks. This populate col A, B C. I then created a formula in D using the difference of the values in B to generate and actual usage of the kWh. Then I used another formula in col E based on the rate in my electric bill. The graphs are then generated using the data.

Send me a PM if you need help!

3 Likes

Great graphs Jason, thanks for taking the time to write it up!

If you ever get the time, could you paste in the code of the “main formula” you use?

Does the subsheet auto-update based on activity in main sheet, or do you update it by hand from time to time?

I’m not sure if it’d help here but if you think it would, you could make a copy of the Sheet and give us a link.

Thanks!

Mike

I will figure out the best way to do this. I don’t really want to share all of the keys in my spreadsheets, but I might just copy one of them and one of the master sheets as well and just make them dummy sheets.

Everything Auto Updates, I don’t touch a thing.

PS, I am in the process of making a dummy sheet to share that will have all formulas working.

Here you go, working sheets with View access. This is for the month, I also have sheets for Day, Week, Month, Year and Billing Month.

When you open the sheets give them 60 seconds to load up before you try to do anything or you might lock them up. When you open the sheet all data will disappear and then will be imported back in and sorted properly the sheet is fully loaded when the cost col is showing values.

Also, the formula in Col D is very complicated, this is to make sure that drastically off set #s are not calculated. For example if kWh goes from 1 to 100 and then back down to 2 the formula will skip the 100 and give you the difference between 1 and 2. This was done to compensate for weird #s the HEM likes to throw up sometimes.

Hem Master Data Month

Hem Graph Sheets

Here are the additional Formulas. For This bill, you will have to alter the formula for your billing dates, mine are set for the 9th of every month. Also, on the master data sheet you do not have to add rows it does it for you but on the graph sheets you have to set the # of rows to what you will be using. For example my HEM logs every 3 minutes so I you need to make sure the data page has enough rows to cover the incoming data. Every 3 min, 60 min in an hour and there are 24 hours which comes at to 480 rows. I made my day sheet 550 for the week, 550 x 7 for the month 550 x 31 and the year is 550 x 365. This must be done or the graphs will not pick up the data.

Today
=query({importrange(“Spreadsheet Key”,"‘HEM_Data_Days’!A1:C30000");importrange(“Spreadsheet Key”,"‘HEM_Data_Days’!A30001:C")},“select * where todate(Col1) = date '”&text(today(),“yyyy-MM-dd”)&"’ and Col2 is not null and Col2<2000 and Col3 is not null",1)

This Week
=query({importrange(“Spreadsheet Key”,"‘HEM_Data_Weeks’!A1:C30000");importrange(“Spreadsheet Key”,"‘HEM_Data_Weeks’!A30001:C")},“select * where todate(Col1)>= date '”&text(today()-weekday(today())+1,“yyyy-MM-dd”)&"’ and todate(Col1)<= date ‘"&text(today()-weekday(today())+6,“yyyy-MM-dd”)&"’ and Col2 is not null and Col2<2000 and Col3 is not null",1)

This Bill
=query({importrange(“Spreadsheet Key”,"‘HEM_Data_Billing’!A1:C30000");importrange(“Spreadsheet Key”,"‘HEM_Data_Billing’!A30001:C")},“select * where Col1 >= date '”&text(if(today()>=eomonth(today(),-1)+9,eomonth(today(),-1)+9,eomonth(today(),-2)+9),“yyyy-mm-dd”)&"’ and Col1 <= date ‘"&text(if(today()>=eomonth(today(),-1)+9,eomonth(today(),0)+9,eomonth(today(),-1)+9),“yyyy-mm-dd”)&"’ and Col2 is not null and Col2<2000 and Col3 is not null")

I know this is all a little confusing, I am trying to make the steps as easy as possible. I spent 2 months building 15 sheets with 3 graphs in each sheet makes 45 graphs I have running. I ran into many many issues, the biggest was the abnormally large data set. I had all 45 graphs pulling for 1 master data sheet which resulted in getting no data. This was because by the time the HEM posted the data and the master sheet imported it and the graph sheet then did its import all of that took long enough that as soon as it was done it started to update again from the new inbound data so the sheets were in a constant update and never showed data. To fix this I now have google logging, logging to 5 master data sheets, 1 for days, weeks, months, year and bills. I probably went a little overboard, but here is a list of all the graphs I have. The formulas in the post above can be simply altered to do all the graphs.
Graphs
Today
Yesterday
Day before
Day before that
This week
Last Week
Week Before
This month
Last Month
Month Before
This bill
last bill
bill before
This year

So if anybody needs help just send me a Private Message and I can log into your sheet with you to try and help resolve issues.

1 Like

Thanks so much, Jason! I will be looking at it, and I’m sure others will, too


I got myself in a corner where I have ~25 devices and ~100 sensors but don’t have any good way to graph it! I loved how SmartThings Data Visualisation using InfluxDB and Grafana looked but you have to have a Linux server plus at least two more packages on top of that
 way too much work for this old Windows guy.

1 Like

–EDIT-- I’ve read the rest of the comments and see that you already shared the sheets. I’ll be reading them more closely to figure out the way to adjust for my meter reading cycle and such, but I love the “overkill” on the tracking. I, too, am a big fan of just keeping track of stuff. Super looking forward to being able to track this data starting with day 1 when I get the Nest set up!

@jasonrwise77 you have done EXACTLY what I want to do with the Nest thermostat I’ll be buying next month! I’ve been watching my power usage more closely the past month since it’s been so hot where I live and I’ve been wanting to create a way to log the energy tracked by the Nest Thermostat (i.e my furnace/AC), and it appears you’ve figured out an awesome way to do it! Kudos on the work! Would you be willing to share your creation with instructions?

@jasonrwise77 So I’ve got my SmartApp created and published, and the script set up in the Google Sheet, and my test worked successfully. What I’m wondering, however, is once I get my Nest Thermostat set up, will I need anything else in order to get things to log to the spreadsheet through my WebApp? Sorry if that is a noob question! I skimmed through all 336 replies on this post and didn’t see anything saying “For this to work, you will need, a,b,c pieces of equipment, and x,y,z pieces of software, etc”

Truman,

Welcome to SmartThings and this thread! It’s pretty cool tech


No, you do not need any other equipment whatsoever to get your Sheets logging going. And you don’t need to pay anything, you just need your free SmartThings account and a free Google account. It’s all free and equipment independent.

The only thing to keep in mind is that eventually Sheets get full if they have 2 million cells filled. If you only have a Nest, that will probably be years from now.

Wow, thanks for the quick reply! So I’ve got the sheet up and running and the test was successful. I’ve got the SmartThings Monile App installed on my iPhone. Once I get the Nest setup after purchase, what would I need to do to get it logging into the sheet? I’m wanting to log the energy usage that the Nest reports based on the runtime of my furnace/ac.

Thanks again for the quick reply!

You’ll need to walk through the steps that John LoVerso carefully documented here.

Any data that your Nest shares to SmartThings can be snagged into the Log. Just check your API on the ST site to see what variables Nest has.

Let us know if you need help. But if you do every step carefully - don’t rush - it should all work.

I too was once a complete noob, but the steps worked great.