Nest Thermostat Data Collection in Google Sheets for Upstairs/Downstairs Setups

NestScript.gs

// 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' 
}

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

Hi Michael,

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

Thanks

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

Hi - the script is running fine but no data appears in my spreadsheet - I've double checked everything - anybody got any ideas ? Many thanks for any help you can give me , Max