Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Nest Thermostat Data Collection in Google Sheets for Upstairs/Downstairs Setups
// CREDIT TO BEEZLY for a lot of this, I just adapted it a bit and added more documentation :)
// --> BEEZLY's work: https://gist.github.com/beezly/9b2de3749d687fdbff3f
// To make this work:
// 1) create a new Google Sheet (name it whatever you'd like, e.g., "Nest Data")
// 2) on the menu bar click Tools -> Script Editor... to open Script Editor (new window)
// 3) in Script Editor delete all the default scripts/files, and create a new one (I called it "NestScript.gs")
// 4) cut and paste this entire file into NestScript.gs, then SAVE THE SCRIPT
// 5) 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"
// 6) Copy/take note of the link to your new web app
// 7) 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 hourly)
//
// Every hour you should get updated data.
// Note: I have noticed that if the thermostat is not running the heat or the AC it doesn't seem to update the temperature reading...
function runDataCollection() {
//this function is called by a trigger (create one in: Resources -> Current Project's Triggers)
Logger.log("Starting Data Collection")
// fetch calls your webapp, which via Google magic calls the doGet() function which is defined below
//make sure you relpace the placeholder with your web app id (or the entire link you copied in step 6 )
var response = UrlFetchApp.fetch("http://script.google.com/macros/s/YOUR_WEBAPP_ID_HERE/exec");
}
function performLogin(email, password) {
var payload = {
// do not change these:
"username" : email,
"password" : password
};
var options = {
"method" : "post",
"payload" : payload
};
var response = JSON.parse(UrlFetchApp.fetch('https://home.nest.com/user/login', options).getContentText());
if ('error' in response) {
throw "Invalid login credentials";
}
return response
}
function doGet() {
Logger.log("Running web function...")
//change these two variables:
var login_auth = performLogin('YOUR_NEST_USERNAME','YOUR_NEST_PASSWORD');
var headers = {
"Authorization" : 'Basic '+login_auth['access_token'],
"X-nl-user-id" : login_auth['userid'],
"X-nl-protocol-version" : '1',
'Accept-Language': 'en-us',
'Connection' : 'keep-alive',
'Accept' : '*/*',
};
var options = {
'headers' : headers
};
var request=UrlFetchApp.fetch(login_auth['urls']['transport_url']+'/v2/mobile/user.'+login_auth['userid'], options);
var result=JSON.parse(request.getContentText());
var structure_id = result['user'][login_auth['userid']]['structures'][0].split('.')[1]
//I added upstairs and downstairs since I have two thermostats. 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
var device_id = result['structure'][structure_id]['devices'][0].split('.')[1]
var upstairs = 'YOUR_UPSTAIRS_DEVICE_ID' //upstairs
var downstairs = 'YOUR_DOWNSTAIRS_DEVICE_ID' // downstairs
//upstairs
var u_current_temp = result["shared"][upstairs]["current_temperature"] * 1.8000 + 32.00;
var u_target_temp_low = result["shared"][upstairs]["target_temperature_low"]* 1.8000 + 32.00;
var u_target_temp_high = result["shared"][upstairs]["target_temperature_high"]* 1.8000 + 32.00;
var u_target_range = u_target_temp_low.toFixed(1) + "-" + u_target_temp_high.toFixed(1);
var u_humidity = result["device"][upstairs]["current_humidity"];
var u_auto_away = result["shared"][upstairs]["auto_away"];
var u_heater_state = result["shared"][upstairs]["hvac_heater_state"];
var u_ac_state = result["shared"][upstairs]["hvac_ac_state"];
//downstairs
var d_current_temp = result["shared"][downstairs]["current_temperature"] * 1.8000 + 32.00;
var d_target_temp_low = result["shared"][downstairs]["target_temperature_low"]* 1.8000 + 32.00;
var d_target_temp_high = result["shared"][downstairs]["target_temperature_high"]* 1.8000 + 32.00;
var d_target_range = d_target_temp_low.toFixed(1) + "-" + d_target_temp_high.toFixed(1);
var d_humidity = result["device"][downstairs]["current_humidity"];
var d_auto_away = result["shared"][downstairs]["auto_away"];
var d_heater_state = result["shared"][downstairs]["hvac_heater_state"];
var d_ac_state = result["shared"][downstairs]["hvac_ac_state"];
//get outside data
//MAKE SURE TO CHANGE ZIP CODE IN THE NEXT LINE where it says "YOUR_ZIPCODE_HERE"
var wxrequest=UrlFetchApp.fetch('http://api.openweathermap.org/data/2.5/weather?q=YOUR_ZIPCODE_HERE,us');
var wxresult=JSON.parse(wxrequest.getContentText());
var outside_temp = (wxresult["main"]["temp"] - 273) * 1.8000 + 32.00;
var outside_humidity = (wxresult["main"]["humidity"]);
var time = new Date();
//var ss = SpreadsheetApp.getActiveSpreadsheet();
//If you haven't already, create a Google Sheet where this data will be logged, and grab the Sheet id
// 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
var ss = SpreadsheetApp.openById("ENTER_YOUR_SHEET_ID_HERE");
//I have multiple tabs, this puts the data in the first tab. Change the [0] to [1] for the second tab, [2] for the third, etc
var sheet = ss.getSheets()[0];
// Write data to the Google Sheet
// For readability, make sure the sheet has the following column headers:
// Date/Time | Downstairs Temp | Downstairs Target Range | Downstairs Humidity | Upstairs Temp | Upstairs Target Range | Upstairs Humidity | Outside Temp | Outside Humidity | Downstairs AutoAway | Upstairs AutoAway | Downstairs Heater State | Downstairs AC State | Upstairs Heater State | Upstairs AC State
sheet.appendRow([time, d_current_temp, d_target_range, d_humidity, u_current_temp, u_target_range, u_humidity, outside_temp,outside_humidity, d_auto_away, u_auto_away, d_heater_state, d_ac_state, u_heater_state, u_ac_state ]);
return 'success'
}
@rbiffle

This comment has been minimized.

Copy link

@rbiffle rbiffle commented Apr 7, 2017

When I run this script, I get the message "Request failed for https://home.nest.com/user/login returned code 400. Truncated server response". Know what I've done wrong??

@studgeek

This comment has been minimized.

Copy link

@studgeek studgeek commented Jul 22, 2017

I'm getting "too many redirects" on the https://home.nest.com/user/login call to get the login info. I'm wondering if Nest deprecated that API call in favor of their official calls (that require a dev key). Actually, I am able to make a session call from command line, but get "too many redirects" from apps script. I'm wondering if Nest is rate limiting by IP and therefore all the scripts calling from apps script are getting blocked.
Probably the right way to do this is to get a dev key rather than using the username/password auth.

@jacoscar

This comment has been minimized.

Copy link

@jacoscar jacoscar commented Sep 13, 2017

did anyone manage to run it recently? I just picked up this code and it doesn't authenticate

@dhami220

This comment has been minimized.

Copy link

@dhami220 dhami220 commented Dec 19, 2017

I was using this script for almost a year and it suddenly stopped working. Then I realized that adding data into the spreadsheet every 5 minutes triggered google spreadheet max number of cell limit and script was failing to add new rows. I added additional code to only add one row per day (add new if it does not exist or append to the existing row.). Instructable can be referenced below.
https://www.instructables.com/id/Nest-Thermostat-Data-Logger/

@brywhi

This comment has been minimized.

Copy link

@brywhi brywhi commented Aug 28, 2018

Anyone else getting Request failed for https://home.nest.com/user/login returned code 429. Truncated server response: Too many requests (use muteHttpExceptions option to examine full response) (line 40, file "nestscript", project "neststuff")?

@KMacros

This comment has been minimized.

Copy link

@KMacros KMacros commented Aug 17, 2019

When I run the code from Deploy Web App --> Test Code I get the following message.

Request failed for https://home.nest.com returned code 406. Truncated server response: <title>406 Not Acceptable</title>

406 Not Acceptable


nginx</ce... (use muteHttpExceptions option to examine full response) (line 203, file "NestThermScript")

I'll continue to debug and post followup if I find solution.

@possiblyphilip

This comment has been minimized.

Copy link

@possiblyphilip possiblyphilip commented Aug 27, 2019

When I run the code from Deploy Web App --> Test Code I get the following message.

Request failed for https://home.nest.com returned code 406. Truncated server response: <title>406 Not Acceptable</title>

406 Not Acceptable

nginx</ce... (use muteHttpExceptions option to examine full response) (line 203, file "NestThermScript")
I'll continue to debug and post followup if I find solution.

It is related to migrating your nest account over to a google account. I was running a script to pull from Nest and it started throwing those 406's after I changed my account over. If you figure something out let us all know :D

@KMacros

This comment has been minimized.

Copy link

@KMacros KMacros commented Sep 2, 2019

@thomasvandenburg

This comment has been minimized.

Copy link

@thomasvandenburg thomasvandenburg commented Sep 9, 2019

It is related to migrating your nest account over to a google account. I was running a script to pull from Nest and it started throwing those 406's after I changed my account over. If you figure something out let us all know :D

I would be happy to get my spreadsheet working again not the summer is over and the heating will begin with migrated Google nest to Google.

According to the spreadsheet editor it is Based on
// bmw220 [https://www.instructables.com/id/Nest-Thermostat-Data-Logger/]
// michael-pesce [https://gist.github.com/michael-pesce/a4ba55d4fc4f4df7d3a6/] "this thread ;-)
// beezly [https://gist.github.com/beezly/9b2de3749d687fdbff3f/]

Any update on how to fix?

@ktsavoie

This comment has been minimized.

Copy link

@ktsavoie ktsavoie commented Jun 17, 2020

Hi Michael,

Were you able to get this script runnig after Google changed nest to 2 step verification login? I would love to get this working again.

Thanks

@MrMathewRogers

This comment has been minimized.

Copy link

@MrMathewRogers MrMathewRogers commented Aug 11, 2020

Hi Michael,

Like ktsavoie, I have also migrated to a Google account (with 2FA) -- Have you had any luck getting this to work?

Thanks

@MatthewReynolds99

This comment has been minimized.

Copy link

@MatthewReynolds99 MatthewReynolds99 commented Sep 30, 2020

This all worked until Google moved to SDM and traits, any idea how we can address this new challange?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.