Nest Thermostat History Data Logger

6,162

23

29

Published

Introduction: Nest Thermostat History Data Logger

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

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

    • Make it Move Contest

      Make it Move Contest
    • Casting Contest

      Casting Contest
    • Clocks Contest

      Clocks Contest
    user

    We have a be nice policy.
    Please be positive and constructive.

    Tips

    Hello I added the following code because I wanted a more precise logger, I log minute by minute but I check if the values have changed significantly and I also check if it has changed for longer than one minute since it otherwise gives massive spikes. I thought some people might enjoy using this code as well, good luck!

    Edit: I had to adjust something use the "fixed" code.

    13 Questions

    Is there someway of making the graphs automatically zoom to let's say 1 week or 1 day? Instead of defualting to max?

    0

    Anyone else getting less than expected counts? Counts are in column "M" and that is how many times google wrote in that row. If you are recording every 5 minutes, the total counts for a day (row) should be (60/5)*24 = 288. I typically get between 220-260. It tells me that script fails to write to the sheet many times throughout the day.

    Is there anyone who is not experiencing this issue, and getting correct number of counts in column "M"?

    One potential cause could be that script is trying to ping nest when it is busy communicating with nest server, and script connection gets rejected. If you have more information on the cause and solution, please do post. I have tried changing trigger times to every 10 minutes without success.

    I am definitely getting less than expected, I think you are right something else in also pinging the server, I wanted to compare different ways of getting the data so I have multiple sheets set up, since they all ping the server my counts have dropped.

    Hi, just set this up and all seems to be working great, I have configured the date in the spreadsheet to display as UK d/m/y however the time is off , it is 6 hours adrift, where is the script getting the time from and how do I change it to UK timezone?

    Thanks in advance for a great solution to an annoying problem.

    0

    I experience the same problem. I'm at CET and the spreadsheet time is 6 hours earlier. I checked the script properties, but this is set okay. Has it something todo with te call "Session.getScriptTimeZone" on lines 134 to 136?

    2 more answers

    Hi, I found that I had to set the timezone both in the script area and also in the spreadsheet properties (file spreadsheet settings). I dont think you need to make any code changes.

    0

    Hello to you across the pond!

    Try changing the timezone in the script properties. Open script > File > Project Properties > Timezone

    First of all: nice script!

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

    regards

    0

    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?

    Would it be possible to add Home/Away statuses as well in each row? I dont know the command to add these. I have an ifttt for this but would prefer statuses to be shown in this one, at the desired ontervals i set already.

    0

    This is fantastic! I'm try to create a log of how much a heat pump uses the different stages. Thanks for adding the images. I'm getting log in errors. Username, password and device ID all look good. The following is the error...
    Request failed for https://home.nest.com/user/login returned code 400. Truncated server response: {"error":"access_denied","error_description":"invalid user credentials","instance_id":"214ea592-ead0-4962-b291-a79f0237d8a4"} (use muteHttpExceptions option to examine full response) (line 37, file "NestScriptWoods")

    Any advice would be appreciated

    0

    Hello,

    for the code 404 error I found out that I should use this:

    var bogus = DriveApp.getRootFolder();

    I will try this now.

    Any other suggestion to get of the truncated server response?

    Cantece

    2 more answers

    0

    hello kfong422,
    how did you solve the code 400 error. I have the same issue.
    Request failed for https://script.google.com/macros/u/0/s/XXXXXXXX/exec returned code 404. Truncated server response: <meta name="viewport" c... (use muteHttpExceptions option to examine full response) (line 45, file "CodeDat Nest")
    Cantece

    0

    Try redeploying the app again (step 3), and select new project version.

    0

    Is there a way to integrate 2-factor authentication? I keep receiving a verification code every time it updates. I can turn if off if need be, but would rather have it on for obvious reasons.

    Hi bmw220, very nice program and good, easy to follow instructions. I have a question about how you're merging all the data from one day into a single row. It looks like you're combining them into an average value for the day. For example, with the outside_temp, you're taking the previous value from the row, adding the current value and dividing by 2 to get the average. I don't think that's giving the right average. Let's say there were 4 measured temperatures: 40,42,44,46. The average would be (40+42+44+46)/4 = 43. However the calculation in the script would be: 1st insert = 40, 2nd insert = (40+42)/2 = 41, 3rd insert = (41+44)/2 = 42.5, 4th insert = (42.5+46)/2 = 44.25. 44.25 is quite different from 43.
    I think you want to have a column for the total outside_temp and a column for the count of the number of measurements, and another column for outside_temp, which takes the total and divides by the count.
    Unless I'm not understanding what you're trying to do, in which case, could you explain?

    0

    No wonder why my day's temperature was off from the weather site. Great catch! Thanks for bringing that to my attention, and I will look into addressing that in the script. Just curious, have you come across this issue before in another code?

    1 more answer

    No problem. Actually, I got it wrong on a quiz about 25 years ago, and have remembered it ever since.

    0

    Does this work with DD/MM/YYYY date formats and Celsius temps?

    29 Comments

    I have nest sensors in other rooms. Does the "current_temperature" property change when there are sensors integrated with the thermostat? In my nest app I have "downstairs" and "upstairs"

    Hello I've tried tooling this script to record data every 5 minutes as a test but often times I get a "Too Many Requests Sent" and it fails to complete/record data. Also, setting it to every 5 minutes, the temperature/humidity records the same. Maybe there's no refresh when you login/ping so quickly?

    I've since set it to hourly since that's what I was going for and every few hours it will still complain about that error and miss recording data for an hour. Any ideas?

    Also, for some reason, I could never get it to run using the function runDataCollection.

    The doGet function worked for me and setting the trigger to run hourly was all I needed to do to record data hourly. I also commented out the deleteRow line as I wanted to keep all the data.

    I'm just trying to figure out if there's a reason why I get a "Too Many Requests Sent" when the trigger is sent hourly. I haven't even set up the 2nd thermostat but I feel like i'd be way over in requests at this rate

    The script fails when trying to login to Nest. I've verified the email address and password.

    I believe I got it to work. Wasn't aware that it posts a row every day and not every five minutes ... Thank you very much, this will be interesting to look at. Only comment is that I feel a little uneasy about having my login credentials unscrambled in there - these days we should probably build code from the ground up safer. Then again it's just for the thermostat :)

    I followed the your way and checked it several times but this is the error im getting any ideas?

    Message details

    Request
    failed for https://home.nest.com/user/login returned code 400.
    Truncated server response:
    {"error":"access_denied","error_description":"invalid user
    credentials","instance_id":"b89e08a6-14d0-4861-a094-47e785363c93"} (use
    muteHttpExceptions option to examine full response) (line 202, file
    "NestScript")

    1 reply

    Sorry, I am not sure why some people are getting this error. It looks like Nest is unable to authenticate your credentials. Google is your best bet (5 minutes of searching did not bring anything worth mentioning). Or, try to reset your thermostat and see if that fixes the issue.

    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.

    Oddly, even though I was experiencing that error it seems to be logging data.

    I was also getting the 404 error when I tried running from the script, but I just left it and it's now collecting data :-)

    @bmw220 I have just set this great script up also, but am seeing the same "Request failed for https://home.nest.com/user/login returned code 400. Truncated server response: {"error":"access_denied","error_description":"invalid user credentials","instance_id":"4ddb1f9a-d972-4a8b-abd3-a9946ac356fa"} (use muteHttpExceptions option to examine full response)" message both when I run in debug and when the script runs for real. Does the user account that I am looged into on my Google Sheets ahve to be the same as the one I use for my Nest account? Any suggestions to solving this invalid user credentials issue would be greatly appreciated.

    I was getting this error, but I let the script run on it's normal schedule and it worked fine.

    I don't think your accounts for nest and google sheet need to match. If you have already checked that your username and password specified in the script are correct, I am not sure what the cause of the error is.

    Hi, just set this up and all seems to be working great, I have configured the date in the spreadsheet to display as UK d/m/y however the time is off , it is 6 hours adrift, where is the script getting the time from and how do I change it to UK timezone?

    Thanks in advance for a great solution to an annoying problem.

    @seadraggin: The Q/A section is getting confusing with answers or new followup questions not getting posted in order. That section of instructable does not appear to be fully thought through because the "Answer" button is not shown in android. Anyways, I will use the comment section to respond to your question.

    Yes, you can record data in a new line every 5 mins. That is how I used to do until I ran across google sheet cell count limitation. If you decided to go that route, below are the challenges:
    1) you will only be able to log data for roughly a year and sheets will stop recording because it will have reached the cell count threshold. The old data will have to be archived to make space for new data before it will work again.
    2) The script has some algorithm to calculate running sum of your furnace use, autoaway and average of environmental conditions. That will likely need to happen separately in a pivot table.

    If you are interested, and have some programming experience, I suggest you start looking into the script and update as it suits your needs. If you come across any stumbling block and need help, let me know and I can try to help.

    5 replies

    Hi, great script, it works fine for me, but is it possible to change the new line from 24hr to 1hr? What do i need to change in the script?

    try changing definition of cat1 and cat2 in the script (lines 139.140). HH is added to compare hour.

    var cat1 = Utilities.formatDate(time0, Session.getScriptTimeZone(), "MM-dd-YYYY-HH");

    var cat2 = Utilities.formatDate(time, Session.getScriptTimeZone(), "MM-dd-YYYY-HH");

    Ok, got it recording hourly (sometimes skips an hour, but that's ok) by adding the "HH" and commenting out the line 172 (delete lastrow).

    However, my heat usage is now coming up with 0's and "undefined". I see you just commented that you aren't sure about Inameiname's undefined issue, but does the fact that just the two changes that I made are causing the issue give you any ideas?

    My last comment was a little ambiguous, so far today it has only added one line and updated that line around 4:50 and then 8:50.

    I've added the HH to lines 139 and 140, but it doesn't seem to be adding new lines hourly. It also seems to be updating every 4 hours or so as well.

    Any ideas?