Nest Thermostat History Data Logger

20,827

34

145

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 many 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

    • PCB Contest

      PCB Contest
    • Toys Contest

      Toys Contest
    • Safe and Secure Challenge

      Safe and Secure Challenge

    145 Discussions

    7
    None
    coder56

    5 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
    JurriënWcoder56

    Reply 7 days ago

    Thanks all. I've got it working nicely!

    For those facing issues where data is not coming into the sheet: for me it worked to have the same username to login to Nest as the google account you are using. E.g. if the account name for your Google account is abc@gmail.com, ensure you use abc@gmail.com as your Nest username as well.

    Cheers!

    0
    None
    coder56JurriënW

    Reply 7 days ago

    Interesting. This was not my experience. My Google account email and Nest account email are not the same.

    0
    None
    RocioMattRcoder56

    Reply 2 months 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
    AnthonyC306RocioMattR

    Reply 10 days ago

    Hi there- can you elaborate on this - I run a monthly rollup by setting a monthly trigger on rollUpLastMonth function. - this sounds interesting.

    0
    None
    coder56AnthonyC306

    Reply 8 days ago

    I have a monthly trigger that runs rollUpAllSheets(). This rolls up the penultimate month, i.e. current month - 2. This results in the spreadsheet having detailed (15 min) samples for upwards or 2 months, and a daily summary for everything earlier.

    The roll up functions collapse daily data samples into a single entry. The multiple roll up functions simply roll up different time periods.

    0
    None
    coder56RocioMattR

    Reply 2 months 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
    alex-f38coder56

    Reply 4 weeks ago

    HI Finally I managed to make it run by removing in the script everything in relation with weather. It appears the weather data are not provided anymore by accuweather for free
    Do you have this problem as well in the US ? anyway most important for me was to save the temperature in real time and this is working ok. Thank you

    0
    None
    coder56alex-f38

    Reply 4 weeks ago

    Sorry to hear about the problem with weather data. I am not experiencing the problem in the US. Unfortunately, I don't have a way to verify your issues. Are you sure the weather data is not provided at all? Perhaps there is a variation on the postal_code that the script does not take into account. Check to see what is returned from the fetch of the 'weather_url'

    0
    None
    coder56coder56

    Reply 12 days ago

    The latest script adds the country_code the weather request. This should fix the non us weather requests.

    0
    None
    coder56NickS366

    Reply 5 weeks ago

    Thank you for you comment. I don't recall any security dialogs. But it has been a while since I set up my environment. I have updated the script per the developer guide.

    0
    None
    VaikisOcoder56

    Reply 5 weeks ago

    Hello coder56,
    I'm getting (TypeError: Cannot read property "gmt_offset" from undefined. (line 207, file "NestScript") I'm outside US. How to fix it? Also I see that it reads "location invalid"
    Thank you.

    0
    None
    coder56VaikisO

    Reply 4 weeks ago

    I have no experience outside the US. Sorry. The function getStructureWeather appends the 'postal_code' to the 'weather_url' to fetch the data. This should return a json data structure containing the weather information for the location. 'location invalid' indicates something went wrong. You could experiment with the fetch to see if there is a 'correct' format to request weather data for your location. I have updated the script to try and detect a failure fetching weather data. It will not fix the problem, but the script should continue to run without the weather data.

    0
    None
    coder56coder56

    Reply 13 days ago

    I updated the script to add the 'country_code' to the weather request. This should fix the problem.

    0
    None
    RyanM468coder56

    Reply 16 days ago

    Does this work if you have 2 factor authentication enabled?

    0
    None
    coder56RyanM468

    Reply 13 days ago

    Probably not. I have not seen 2 factor authentication in the api. That would require user interaction when the script ran. OAUTH2 authentication is also not supported by the script.

    0
    None
    alex-f38coder56

    Reply 4 weeks ago

    Hi
    Thankks for this usefull sheet. I m based in France. Any idea why your script would not work in my territory? what is the usage of 'Accept-Language': 'en-us',
    thanks for your hints
    alex

    0
    None
    JemArmescoder56

    Reply 8 weeks ago

    Also thx from me, Coder56! Nice and simple, as per your instructions in the script. I'm fairly new to all this, and am looking to pool temp data from various smart devices I have into one spreadsheet - where I can get historical snapshots of how rooms are being heated - and how long they take to cool down etc (just me nerding out for the family). Excellent place to start, and I thank you!
    Could you point me to the right block of code that actually rewrites all values to the same day? I want to remove this, and have all data polled say every 30 mins. The various "rollup" functions have confused me - so appreciate a little point, if you would be so kind!

    0
    None
    coder56JemArmes

    Reply 5 weeks ago

    A sample is collected every MINUTESPERSAMPLE. The roll up variations aggregate a day's worth of samples into a single sample. Each variation simply defines which day range to aggregate, e.g. last month, month before last, the last quarter, or between arbitrary dates. Automatic roll up is controlled by the rollUpAllSheets trigger and rolls up the month before last. To disable automatic roll up, remove the rollUpAllSheets trigger.