Nest Thermostat History Data Logger

9,590

26

80

Introduction: Nest Thermostat History Data Logger

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%20temp%20logger%20edited.txt?dl=0

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.

Share

    Recommendations

    • Tiny Home Contest

      Tiny Home Contest
    • Fix It! Contest

      Fix It! Contest
    • Creative Misuse Contest

      Creative Misuse Contest

    80 Discussions

    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 unites 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...

    6 replies

    I tried this script and it worked but all my thermostats are on the same page. I then created 2 new tabs because i have 3 thermostats but i dont see where to configure the thermostats for the separate tabs. was i supposed to create the tabs before the script was ran? How should i go about fixing this?

    Sorry I forgot to document this.

    nestDataCollection() looks for a tab named for the structure and thermostat in the spreadsheet. If no tab with that name is found, the first tab is used.

    Name each tab "{structure name} {thermostat name}", where {structure name} is the home name (see nest settings -- the gear --, Home info, name), and {thermostat name} is the name of the thermostat in the home (see nest settings -- under thermostat at the bottom of the screen, you may need to scroll).

    Once you have renamed your tabs, move the appropriate rows from the original tab to the correct tab. You may need to sort the data on the new tabs by Date to ensure the rows are in the correct order.

    Hello,

    I'm looking for something closer to your setup, albeit I've only got 1 house, 1 thermostat, and 1 external sensor. I would like to pull data from all of these, and I'm not particularly gifted at coding. I'm hoping to figure this out! Nice work.

    Derek

    After looking thorugh this, and reading your comment, I realized I just need to put my login info, and it takes care of everything else itself? Awesome! Thanks.

    Derek

    You are correct, update the username and password, set the trigger, and it should just work. I don't know where the external sensor data is located within the nest data structures. I haven't tried anything but thermostats. Good luck!

    0
    None
    LJi

    Question 23 days ago

    Does this work with the Nest Thermostat 3rd gen with external temperature sensors? Will it read and record all of the attached temperature sensors?

    I did all the steps (checked it three times) for 2 Nests. It doesn't seem to be running... no rows get added to the Google Sheet. I'm a bit of a noob at this stuff. Can someone help me diagnose it? Thank you!

    Hi
    As I expected Google sheets works as it should on my desktop but not on my iPad.

    I have two main concerns, both linked. Why is it necessary for anyone to have access to the app including anonymus viewers? Does this not pose a threat that someone might be able to gain access to the script and potentially some of my details re the Nest Thermostat?

    The second point is I cannot deploy the app unless I give the file permission to access my data on Google.

    Your thoughts would be welcome.

    Thank you

    2 more answers

    Hi Charles, I am not sure why it is necessary to have the script set it that way. To be honest, I don't think I tried by keeping the script with other setting. Feel free and if that works for you, it make sense to keep the script access to only you. For someone to access your script, I think they will have to figure out the script address, and that level of security was acceptable to me and thus I proceeded that way.

    Answer to your second question is likely because the script will need to write to your google spreadsheet and thus it needs your permission.

    Hope it helps.

    First of all: nice script!

    the Q: is it possible to have the script in celsius (instead of fahrenheit)

    regards

    2 more answers

    You can change the math that is used to convert it to fahrenheit, or switch to the new API (updated script listed above), which returns both C and F values.

    Line 81 and 92 of the script converts C to F. You can delete "* 1.8000 + 32.00" from those lines to keep temperature in C.

    hello! I love the script! I have a question. I have (2) houses both with a Nest. So i set up a different script for each location. BUT one of the houses has (3) thermostats - how do I list the devices (SN's) on line 77? Seperated with what specific text?

    1 more answer

    If you use the updated script referenced above, the new API returns an array of all thermostats on your account and puts them into the spreadsheet automatically.

    See the post above where an alternative script has been posted to address this.

    I don't think I have seen this error before. Based on the error message, it looks like the username and/or password you have in script for the thermostat are incorrect.