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.
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!
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.
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.
â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.