Nest Thermostat History Data Logger

70,849

74

196

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

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.



1 Person Made This Project!

Recommendations

  • Cheese Challenge

    Cheese Challenge
  • Halloween Contest

    Halloween Contest
  • Microcontroller Contest

    Microcontroller Contest

196 Comments

3
djmills32
djmills32

2 years ago

Hi, I had this working for some time (months), but it stopped logging Oct 23 last year. I have double checked everything. When I try to run after the publish I get a 406 error. Is there any way to resolve this. Or does this relate to Google's change of policy regarding interactions between Nest devices and 3rd party?
Thanks,
David.

2
armstrongwh
armstrongwh

Reply 2 years ago

I am also getting a 406 error when trying to run the code after making the modifications instructed. I never had the script running before, so a little less confidence that I have already coded right, but it seems like this could be a general issue?

0
cpit1988
cpit1988

Reply 8 weeks ago

I know this is 3 years later, but did you ever get a fix for this? I also can't get mine going because of this error.

0
brunoplus
brunoplus

Reply 2 years ago

Same problem for me.
This is the error:
Login failed: {"error":"access_denied","error_description":"invalid user credentials","instance_id":"46c04938-2aad-4736-b2e7-9d4cdf497ee5"} (riga 583, file "NestScript")
How to make it works again?

0
alistairlow
alistairlow

9 months ago

Another way to get (some of) this data is by exporting your Nest data from https://takeout.google.com. I can see all of my thermostat data (temperature and humidity) every 15 minutes for the past 2 years, but not any external temperature or weather data. It might be an easier way for some users that don't need everything

0
nicklausw27
nicklausw27

Question 10 months ago

I am encountering this error:

Exception: Request failed for https://home.nest.com returned code 401. Truncated server response: provide authorization (use muteHttpExceptions option to examine full response)
performLogin

Any ideas how to fix this in order to login?

0
outdoor720
outdoor720

Question 11 months ago on Step 6

I have no experience with Google Project Editor so forgive me if I ask a stupid question/

I copied/recreated this Nest google sheet and Project editor. When i run it, the Head, i get no errors. When i set the trigger for the active deployment, i now have it where there are no errors yet my Nest google sheet is not being updated? Any idea as to why?

Also, can this project editor be used to read Nest Thermestat Sensors?

0
dancase
dancase

1 year ago

Wow! This would have been perfect - just what I was looking for.

Am I right in thinking this has now been killed by Google? I've seen other methods of pulling log data but this was the nicest.

0
matthew.reynolds99
matthew.reynolds99

Question 1 year ago

I got it all working perfectlty until google moved to SDM and traits, any tips how to rewrite>

0
bmw220
bmw220

Answer 1 year ago

I have abandoned this method due the reason you mentioned, and currently using Home Assistant to track that information. Home Assistant has couple sensor to track nest usage and I combine that with weather information and write that in google sheets. Plus, that is a much more accurate way to track and no more errors due to failed attempts.

0
matthew.reynolds99
matthew.reynolds99

Reply 1 year ago

Great, so I now have Home assistance how do you link it to the nets. I have put in the Client ID and Secret into the configuration.yaml file.

When you got to authoise the account for a PIN it takes you to Nest where you cant log in with you google account. Seem it is trying the old way. please help

Link Nest Account

To link your Nest account, authorize your account.
After authorization, copy-paste the provided pin code below.

Pin code

Required

SUBMIT

0
matthew.reynolds99
matthew.reynolds99

Reply 1 year ago

Thanks, that is was I used, but it points to Works with Nest (WWN`). I did manage the create a client id and secret via Works with Google Assistant (WWGA). I think ill have to wait. I did find something that may work if you use the Home Asstance Cloud or expose H.A to the outside https://www.home-assistant.io/integrations/google_...

I guess yu are using the old way, which gave you a nest PIN after authorising. That way no long works as I have migrated my account to. google.

0
bmw220
bmw220

Reply 1 year ago

Yes, I have not moved my nest to google yet :)

0
matthew.reynolds99
matthew.reynolds99

Reply 1 year ago

There we go then. Good old google! Thanks for your help, i shall hunt for another way :)

0
schmidtbaby
schmidtbaby

2 years ago

Hi,
I get the following error _ can you help? PS Password is not actual for safety

Missing ) after argument list. (line 53, file
"NestScript")





var login_auth =
performLogin(schmidtbaby9@gmail.com,Lovwbaby177);

Thanks






1
TheCalebG
TheCalebG

Question 2 years ago

@coder56

I'm working with your script and I'm getting an error when trying to run (see below). Are you familiar with this and do you know a solution?

@coder56

I'm working with your script and I'm getting an error when trying to run (see below). Are you familiar with this and do you know a solution?

Login failed:406 Not Acceptablenginx(line 583, file "NestScript")

Capture.JPG
2
thierry.sombreffe
thierry.sombreffe

Question 2 years ago

The script doesn't login with my gmail account
I have 100% errors. Any idea?
For information, I can't login to Home.nest.com with my google email/password... I must click on "Connect with Google" button

5
dennisgb2006
dennisgb2006

Question 2 years ago

Does this still work after google account migration? I follow all the steps and did not seem to work for me. Any thoughts?

0
gwright215
gwright215

Answer 2 years ago

Just set this up and the login routine with POST is failing, even though I changed it to the be the 'login with nest' URL. Anyone have a fix with this?