Nest Thermostat History Data Logger

29,401

40

171

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

  • Sensors Contest

    Sensors Contest
  • Frozen Treats Challenge

    Frozen Treats Challenge
  • 1 Hour Challenge

    1 Hour Challenge

171 Discussions

1
None
LJi

Question 10 months 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?

1 answer
0
None
strongbad111LJi

Answer 13 hours ago

I'm wondering this as well. Can anyone assist us?

0
None
rbrown999

5 weeks ago

@coder56, this is fantastic, thanks!

I do have a couple of questions that I can't seem to fix having read the past comments.

1) My script seems to be running, showing that it's completed but the spreadsheet is not populating.
2) I actually haven't tried to look into this because I'm trying to solve one problem at a time, but I do have 3 Nests that I'm trying to capture data for.

Thanks,
Rob

9
None
coder56

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

15 replies
0
None
wyk126coder56

Reply 7 weeks ago

Thank you very much, it worked! I followed the instructions on the scripts, initially I got "too many failed login" then I got "Login pending" error, then I realise that I turned on the 2-step verification on my Nest account, after I disabled that it worked perfectly! thankyou so much for your work! I really hope there will be an official support from Nest at some point though...

0
None
RobertW96coder56

Reply 2 months ago

Thanks, this worked like a charm! I wasn't able to have the data from my two thermostats split by duplicating the main spreadsheet and renaming them appropriately: "Living Room" and "Basement"

Am i missing an important detail in how to name the sheets?

0
None
coder56RobertW96

Reply 2 months ago

The sheet name must match the name of the thermostat in the Nest application. Is all of the captured data going into the first sheet? Check your logs after the script captures data. You should see the names of the nest thermostats that must match the sheet names.

-Coder56

0
None
alex-f38coder56

Reply 6 months 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 5 months 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 5 months ago

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

0
None
KevinM643coder56

Reply 4 months ago

Hey Coder56,

thanks for that, works like a charm!

Just wondering also, do you know if the actual temp/humidity has 0.1 degree measurments? I seem to get only 0.5 degree increments.

Regardless its good enough as is.

Thanks again to you an all who contributed before.

0
None
coder56KevinM643

Reply 4 months ago

I am glad you are working. Wrt to temperature, I do not know what precision is used. My units are farenheit to the nearest unit.

0
None
KevinM643coder56

Reply 4 months ago

No worries, I have a feeling the thermostat is only reporting the display value regardless of likely measuring to the 10th of a degree. For celcius it's 0.5*C degree increments on the display.

Thanks again.

0
None
KevinM643coder56

Reply 4 months ago

Hi Coder56,

I am another european eejit who wanted to setup this script, i initally tried bmw220's with limited success. Next i tried your mod and the logging seems to be better with just one downside. the weather is not showing for me also. :D

I had a look at the logs for the script an found the link for the weather collection. If i take the URL from the logs I am able to open the weather data:

{"0017,IE":{"current":{"temp_f":44.0,"temp_c":6.7,"condition":"Mostly Cloudy","sunrise":1549871880,"sunset":1549906080,"humidity":88,"gmt_offset":"0","wind_dir":"S","wind_mph":4,"icon":"mostlycloudy"},"location":{"station_id":"unknown","city":"Monaghan","state":"County Monaghan","country":"IE","lat":"54.246760","lon":"-6.969591","short_name":"Monaghan,County Monaghan","timezone":"GMT","timezone_long":"Europe\/Dublin","full_name":"Unit 9 \/10, Monaghan Shopping Centre, Dawson St, Tirkeenan, Monaghan, Ireland","gmt_offset":"0","zip":"0017"},"forecast":{"daily":[{"temp_low_f":41.0,"temp_low_c":5.0,"temp_high_f":47.0,"temp_high_c":8.3,"humidity":84,"condition":"Mostly Cloudy","icon":"mostlycloudy","date":1549843200},

But the script seems to be still logging the data as missing. See the logs from the relavent setion below. I wonder are the headers messed up by the location somehow?

[19-02-11 03:55:36:316 PST] Logger.log([Parse data..., []]) [0 seconds]
[19-02-11 03:55:36:317 PST] Logger.log([Parse structure: House..., []]) [0 seconds]
[19-02-11 03:55:36:317 PST] Logger.log([Parse device: Downstairs..., []]) [0 seconds]
[19-02-11 03:55:36:318 PST] Logger.log([Getting weather..., []]) [0 seconds]
[19-02-11 03:55:36:365 PST] UrlFetchApp.fetch([https://apps-weather.nest.com/weather/v1?query=0017,IE, {headers={Authorization=Basic b.13504944.NZmQdKHVt0nfyV3QP7EEqeEGskqkDhHaLFaYq9DBy8y3eoApHlI0L7BAzI1jEDER1oo3TXpdSKqcgcI7YpzuAn3zhxbW0ferI1TY1UN6TfYrd, =, =, =, =, =}}]...) [0.047 seconds]
[19-02-11 03:55:36:366 PST] SpreadsheetApp.getActiveSpreadsheet() [0 seconds]
[19-02-11 03:55:36:367 PST] Spreadsheet.getSheets() [0 seconds]
[19-02-11 03:55:36:367 PST] Sheet.getName() [0 seconds]
[19-02-11 03:55:36:368 PST] Logger.log([Appending row to sheet: 'House Downstairs', []]) [0 seconds]
[19-02-11 03:55:36:452 PST] Sheet.appendRow([[Mon Feb 11 11:55:36 GMT+00:00 2019, Downstairs, off, 20.66747, 21.48, 39.0, 0.0, 0.0, missing]]) [0.083 seconds]
[19-02-11 03:55:36:522 PST] Sheet.getLastRow() [0.069 seconds]
[19-02-11 03:55:36:522 PST] Logger.log([Compute usage..., []]) [0 seconds]
[19-02-11 03:55:36:523 PST] Sheet.getRange([6, 1]) [0 seconds]
[19-02-11 03:55:36:607 PST] Range.getValue() [0.083 seconds]
[19-02-11 03:55:36:608 PST] Sheet.getRange([6, 3]) [0 seconds]
[19-02-11 03:55:36:608 PST] Range.getValue() [0 seconds]
[19-02-11 03:55:36:609 PST] Logger.log([Success..., []]) [0 seconds]
[19-02-11 03:55:36:610 PST] Execution succeeded [1.258 seconds total runtime]

0
None
coder56KevinM643

Reply 4 months ago

@KevinM643,

Thank you for the detailed request. The problem is that the postal code has leading zeros. These get stripped when looking for the weather in the weather response -- weather reported in the US strips the leading zeros (I'm programming from example, not from any documentation). I modified the code to check both. Grab the script from the template and see if that doesn't fix your weather issue.

Cheers,
Coder56

0
None
denismoncoder56

Reply 4 months ago

Very nice work. It's working like a charm for me. I also replaced the visualisation with a google data studio dashboard: https://datastudio.google.com/open/17dRf488OAzfkbV...
Do you know how to explain the values in Heater Usage? I get values from 0,20 to 0,33 or 0.
How do you read this ?
Thanks

0
None
coder56denismon

Reply 4 months ago

Glad it's working for you. The usage numbers are in hours. 0,20 hours = 12 minutes, 0,33 hours = 19.8 minutes, 0 hours = 0 minutes. Usage is computed from the difference in sample times, which are not particularly synchronized, and are dependent on query times. If you want minutes, multiply by 60 and optionally round.

0
None
Evan1127coder56

Reply 9 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 9 months 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.