Nest Thermostat History Data Logger

14,441

28

125

About: When given the choice between being right or being kind, choose kind. - Dr Wayne W. Dyer

Nest thermostat tracks temperature, humidity and furnace/AC usage and users are able to see historical data only for 10 days. I wanted to collect historical data (>10 days) and came across google spreadheets script that pings nest every set time and also gets local weather data from openweathermap.org and saves in the spreadsheet.

Everything was going well for a year, and the script suddenly stopped collecting data. After some google searching, I realized that one row every 5 minute for google spreadhseet means hitting the max limit of cells a google spreadsheet can accomodate. I updated the original script to still ping Nest every 5 mins, and but collect data in 1 row per day. Script checks the last row and if it is the same day, then it appends the data to the same row rather than adding a new row.

Credit for the original script. I just made a few edits to suit my needs.

// michael-pesce's work: https://gist.github.com/michael-pesce/a4ba55d4fc4...
// --> BEEZLY's work: https://gist.github.com/beezly/9b2de3749d687fdbff...

Keywords: Nest Thermostat History, Nest Heat, Nest Temperature History, Nest Thermostat Hack, Nest Thermostat Tips, Nest Energy History, Nest Daily Use, Nest Daily Usage, Download nest thermostat data

Step 1: Create a New Google Sheet (Do a Save As on My Shared Spreadsheet)

Start with my shared google sheet linked below (Open that file and click File and then "make a copy" and save in your google drive).

P.S: Do not ask me to give you the permission to edit this file. Before you make any changes, which you will not be able to make because I shared this as a read only spreadsheet, do a "make a copy" in your own google drive and then proceed with making edits.

https://docs.google.com/spreadsheets/d/1zTHUfiltWomhPYmfD3TYRRoJZsgcjrQ_A2xHSTK5_dE/edit?usp=sharing

To folks who are having issues with authorization: Try the script in the following file. It has additional functionality related to Nest's new 2.0 authorization protocols. I have not tried it, so if you come across any questions or issues, please post in the comments section.Credit to mcr2582.

https://www.dropbox.com/s/8rbtg7pb0xl9n9x/nest%20t...

Another variation of the script by Coder56: Additional details in the comments section. I have not tried it, but script is organized very well and it appears to be working well for may users.

https://docs.google.com/spreadsheets/d/15bTn9_Cv9I...

Step 2: Copy Script

Skip this step if you did save a copy on my shared google sheet.

  • On the menu bar click Tools -> Script Editor... to open Script Editor (new window)
  • In Script Editor delete all the default scripts/files, and create a new one (I called it "NestScript.gs")
  • Cut and paste this entire text from the attached file into NestScript.gs, then SAVE THE SCRIPT(For this step, please use the script from the google sheet I shared in step 1. If you did a save a copy on that file, you should already have the script. If you didn't you can open that file and go to script section and copy past the text. I deleted the script text file that was attached to this step as it was not up to date and may create confusion.).

Step 3: Deploy As Web App

  • On the menu bar click Tools -> Script Editor... to open Script Editor (new window)
  • On the menu bar click Publish -> Deploy as Web App
  • Select "Execute the App as Me"
  • Select Who has access to the app: "Anyone, even anonymous"
  • Copy/take note of the link to your new web app for now, and it will be added to the runDataCollection routine below (the first code) in later steps.

Step 4: Triggers

This is where you define how often to collect data.

  • On the menu bar click Current Project's Triggers
  • Click add new trigger
  • For Run select the function runDataCollection, Events: time-driven, and select the rest per your preference (I do every 5 mins)

Step 5: Additional Information in Script

Let's modify the script to your specific thermostat, city and google sheet.

Each change is listed with a line number of the script. You will have to go to that line in the script and update as instructed below. (Line numbers should be correct if line 40 is "runDataCollection()...").

  • Line 45: add webapp link in the runDataCollection routing (This is what you noted in one of the previous steps)
  • Line 53: Nest username and password
  • Line 77: Thermostat device ID

You can get the id for each thermostat by going to the Nest dashboard, clicking on the Thermostat, clicking the gear icon on the top right then copying the "Serial no."" field. It will look something like: 02XX01XX471XXX3S

  • Line 90: City ID (additional instructions in the script above this line may be helpful.)

To find city ID go to "http://openweathermap.org/find?q=" search for your city, click on the city link and the ID will be the 7 digit number in the URL

  • Line 103: Google sheet ID (additional instructions in the script above this line may be helpful.)

The Sheet id can be grabbed from the shee URL See this pattern for where the Sheet id is in the URL:https://docs.google.com/spreadsheets/d/THIS_IS_WHERE_THE_SHEET_ID_IS/edit#gid=123456789

Step 6: Finalize the Spreadsheet

Skip this step if you started with my shared spreadsheet.

These two lines need to be in the spreadsheet for the code to work.

First line (Header row): Space separates columns

Date/Time Month Day Year Temp Humidity OutsideTemp OutsideHumidity Heat_Usage AC_Usage Weather AutoAway

Second Line:

Add yesterday's date in the first column and zeros in the remaining columns.

That is it. Let the script run and it should add one row per day and pinging your thermostat and local weather for data per the trigger frequency you set.

If you redeploy the webapp, use the new revision. I had problems using the same revisions with script not running.

If script is not running, go over the previous steps again and make sure you have updated the script as suggested by these steps accurately. This is the most likely cause of the issue with script not running.


Known Issues (If someone knows the fix, please reply in the comments section):

1) Script fails to obtain data from nest throughout the day. I have my trigger every 5 minutes, which should results in total 288 reads throughout a day. I get ~170. Lowest I had gotten is 16 and highest is 264.



Share

    Recommendations

    • Optics Contest

      Optics Contest
    • Plastics Contest

      Plastics Contest
    • Make it Glow Contest 2018

      Make it Glow Contest 2018

    125 Discussions

    7
    None
    coder56

    3 months ago

    Nice work. This was what I was looking for. Thank you.

    I updated the spreadsheet to handle my two homes with 4 thermostats. The script puts each thermostat on a separate tab. I simplified the configuration to just a user id and password and placed them at the top of the script. I got rid of the web app (no need since the script runs off of a trigger and not from an external request, and this simplifies setup). I also switched to the nest weather api (this also simplifies setup). The weather, time, and temperature units are those at the thermostat. I removed the roll up computations from the data collection script (I believe this can be done in the spreadsheet itself or added as a separate archival script to be run periodically, e.g. weekly, monthly, quarterly, ...).

    BTW, I did try the new OAUTH2 based script, but found the configuration WAY too complicated for the average user and the new nest developer api too limited. Instead, I opted to make my changes based upon the script in this article.

    https://docs.google.com/spreadsheets/d/15bTn9_Cv9I...

    19 replies
    0
    None
    Mattelfessocoder56

    Reply 19 hours ago

    Coder56... Downloaded your script. Followed instructions in header. Added login credentials. Worked immediately.

    Nice!

    0
    None
    coder56Mattelfesso

    Reply 1 hour ago

    Thank you. I just continued where others left off. Thanks goes to bmw220 for his work and for this page. And to the michael-pesce, and beezly for their initial work.

    0
    None
    djvj1coder56

    Reply 5 days ago

    Thanks so much!

    I got it working, but my AC Usage/Heater Usage/Away cells are always blank.

    It is critical I have AC and Heater cells daily usage working somehow. What can I do to troubleshoot this?

    Also I have 3 nests, so I made 3 tabs named after each nest like instructed. Each tab keeps getting 3 rows of data each time the trigger runs. How do I filter so it only stores the one nest on each tab?

    nest spreadsheet.png
    0
    None
    coder56djvj1

    Reply 5 days ago

    Hi. You are close. Change the tab name to the 'structure' name plus the 'thermostat' name, e.g. Home Upstairs, or whatever your home name is set to. Look in the phone nest app, select the thermostat, select settings (the gear), select Home info, and make note of the name. That is the name you should put before 'Upstairs'. Do the same for the other thermostats. Finally, No usage is accumulated because the thermostats are off -- see the state column. The script will also be confused because all the data in one sheet. Solve that problem, make sure your thermostat is heating / or cooling and not off, and you should be good to go.

    0
    None
    djvj1coder56

    Reply 4 days ago

    Ah ok, I added the structure name, "Home" to the tabs.
    So the script does not pull previous usage, like what's visible in the History tab's daily usage?

    My goal is to keep track of total amounts of daily usage based on zone for each month so I know how much oil each zone used.

    The thermostats are never off. Are you saying the script has to catch when
    temperature reaches its trigger temp and turns my burner on? Cause that could be a problem if it only comes on for 5 minutes every now and then, there's a high chance it won't catch the heat was on for a moment.

    I changed trigger to 5 minutes. I noticed it's filling up with data the state is "off". Is there a way to only record data when state is not "off"?

    0
    None
    coder56djvj1

    Reply 3 days ago

    The script does not pull from the thermostat's Energy History. This is not available. The script uses the nest public api to pull the current device status, a snapshot of the device at the time the script is run. If your heater is never on longer than the trigger rate, then yes, you will not get an accurate picture of what your heater is doing. Currently, there is no mechanism in the script to skip data when the device state is "off". But you can modify the script yourself to do this. Look at the script's doDataCollection method. Skip the sheet.appendRow(newRow) call if you don't want to add the row. I'm not sure you want to skip all "off" samples. Maybe successive "off" samples would be a better choice. Remember, other data is captured besides heater state: settings, temperature, humidity, outside temperature. You will also lose these values when you skip "off" samples. I run a monthly rollup by setting a monthly trigger on rollUpLastMonth function.

    0
    None
    RocioMattRcoder56

    Reply 18 days ago

    Nice work! Your setup was very quick and simple.

    Does anyone have remote sensors? Apparently Nest has yet to release an API for the remote sensors, so there is no way to pull from them directly. But, what I did discover is if the sensor is the active selection on the Nest App that the logger will log the temperature of the remote sensor for that pull. I wonder if there is a way to change which sensor a thermostat is using through an API. If there was you could in theory set the thermostat to use itself as the sensor, then pull the data, then switch to use the remote sensor as the sensor and pull the data again, but this time store in a new tab.

    0
    None
    coder56RocioMattR

    Reply 15 days ago

    Hello. I am glad you had some success. I have no experience with external sensors. I do believe the api doesn't provide much, if any, support. Good luck with your experiments.

    0
    None
    TacticalApplescoder56

    Reply 4 weeks ago

    Hey! Thanks for your script, I've been trying to figure out how to get this to work. It seems that the script runs fine, and the only error I get sometimes is the "Too many requests" error. However, that error only really shows when I trigger the script too many times in a short amount of time - makes sense why that errors out.

    My issue is, although it seems that the script runs successfully, nothing gets populated in the spreadsheet itself. Is there some sort of setup that I'm missing? Thanks again for your help!

    0
    None
    Evan1127coder56

    Reply 2 months ago

    Thanks for writing this. I'm a novice and am getting the below error messages. Any suggestions?

    Login failed: Too many requests (line 548, file "NestScript")

    TypeError: Cannot read property "current" from undefined. (line 198, file "NestScript")

    Thanks!

    0
    None
    coder56Evan1127

    Reply 8 weeks ago

    That is normal for Nest. I have seen this response for up to an hour at a time. It seems more an indication of how busy nest is or perhaps difficulties contacting your thermostats than actual usage. I would say 10 to 20 percent of my log ins fail.

    Good luck.

    0
    None
    Evan1127coder56

    Reply 8 weeks ago

    Thanks for the reply!

    I guess part of the issue is that it hasn't successfully populated the spreadsheet at all. When I run the function manually to test it, if I don't get the "too many requests" error, I get the "cannot read property 'current' from undefined" error. I assume that happens on the scheduled runs, as well.

    Actually, I am almost positive, because I have it set to run every five minutes, and Google reported to me 118 of the failed logins in 24 hours and 170 of the "current" property error. Those total 288, which is how many five-minute increments there are in 24 hours.

    Thanks.

    0
    None
    coder56Evan1127

    Reply 8 weeks ago

    The 'current' property is part of the weather data structure. This indicates a problem getting the weather information at your thermostat's location. It is based upon the zip code setting of your thermostat. I have updated the script to deal with missing weather information. Feel free to update your script.

    Double check your thermostat's location information. A zip code should be specified. I have no experience with values outside the US.

    If the problem persists. The next time you check you logs (or the next time google emails you a summary), record the line number of the error.

    0
    None
    Evan1127coder56

    Reply 8 weeks ago

    Thanks again for replying and thanks for updating the code. It's working now (except the weather).

    I am in the US and confirmed my zipcode is entered. One thing that occurred to me is that my zipcode begins with a 0 and I've seen it happen before with other things that the leading zero gets dropped automatically when the data is used, so whatever function is running only sees a four-digit code. Is it possible that is related? It's the only thing I can think of that would be different from others'.

    Thanks again.

    0
    None
    coder56Evan1127

    Reply 8 weeks ago

    Yup, the leading 0 is the problem. The nest weather data structure drops the leading 0. I have updated the script to deal with it.

    Good luck...

    0
    None
    benryder1979coder56

    Reply 2 months ago

    Thanks for this. One question - it seems to be running, but the date/time seems to be the first time it runs each day. Should this automatically update each time the script runs?

    0
    None
    coder56benryder1979

    Reply 2 months ago

    Hello. The time should show the time that each sample was made and should be different for each line. Resize the date column to make sure you are seeing everything. Check the format of date column. It should be auto or date.

    0
    None
    benryder1979coder56

    Reply 2 months ago

    See image attached/below. I would expect it to update more frequently? What am I doing wrong?

    Thanks for your help!

    Nest.png