NestScript.gs

// 1) open the shared spreadsheed, make a copy of it, open tools|script editor, update the settings below, save
//     https://docs.google.com/spreadsheets/d/15bTn9_Cv9IBVkvepINPlBRcK8oty74e6FlAieNbiQMg/edit?usp=sharing
//     for script updates, simply copy this script into your spreadsheet using the script editor, i.e. select all, copy, paste, save
// 2) set the script's timezone (File|Project properties)
// 3) on the menu bar click Current Project's Triggers
//      click add new trigger
//      for Run select the function nestDataCollection, Events: time-driven, and select the rate per your preference (I do every 5 minutes)
//      click add new trigger
//      for Run select the function rollUpAllSheets, Events: time-driven, and select once a month
// 4) in the spreadsheet, set the spreadsheet's timezone to your timezone (File|Spreadsheet settings...)
// 5) duplicate the first tab for each additional nest thermostat managed by your account
// 6) name the tabs "{structure name} {thermostat name}", e.g. "Home Living Room", "Home Office", "Condo Master Bedroom", ...
//
// Based on
//   bmw220        https://www.instructables.com/id/Nest-Thermostat-Data-Logger/
//   michael-pesce https://gist.github.com/michael-pesce/a4ba55d4fc4f4df7d3a6
//   beezly        https://gist.github.com/beezly/9b2de3749d687fdbff3f
//

// user specific settings -- update the values for your nest account, or update the settings tab
var settings = {
  EMAIL:            'EMAIL',   // your nest user id / email
  PASSWORD:         'PASSWORD',         // your nest password
  MINUTESPERSAMPLE: 15,                 // number of minutes between samples -- every 15 minutes seems reasonable, but adjust as you see fit
  SAMPLESHIFT:      0,                  // number of minutes to shift the sample time -- use to delay the sample time by some minutes
  COMPUTEUSAGE:     true,               // set to true if you want to compute usage on data collection
  TEMPDECIMALS:     1,                  // number of decimals in final temperature results
  TZDELTA:          0                   // Difference in hours between the spread sheet's timezone and the first tab's thermostat timezone
};

// main entry point to collect data from nest and update the spreadsheet
// -- create a trigger to run this function as often as you need to collect the desired amount of data.
// -- It should be the same or faster than the minutes per sample.
// -- Triggering faster than MINUTESPERSAMPLE enables retry on nest login failure,
// -- e.g. Trigger every 5 minutes and MINUTESPERSAMPLE = 15 means try every 5 minutes, but collect data only every 15 minutes.
// -- I suggest something between 5 minutes to 1 hour.
function nestDataCollection() {
  // skip if it is not time for a new sample
  return nest.readyForNextSample() ? nest.doDataCollection() : 'skipped';
}

// main entry point to roll up thermostat data -- collapse all daily data to a single row for that day
// -- create a trigger to run this function once a month
function rollUpAllSheets() {
  return nest.rollUpPenultimateMonth({sheets: SpreadsheetApp.getActiveSpreadsheet().getSheets()});
}

// add Nest menu to ui
// -- if you do not want to add this 'Nest' menu to the ui, then rename this function to something other than onOpen
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Nest')
      .addSubMenu(ui.createMenu('Roll Up')
        .addItem('Roll Up Last Month', 'rollUpLastMonth')
        .addItem('Roll Up Penultimate Month', 'rollUpPenultimateMonth')
        .addItem('Roll Up Last Quarter', 'rollUpLastQuarter')
        .addItem('Roll Up All', 'rollUpAll')
        .addSeparator()
        .addItem('Roll Up Until...', 'rollUpUntil')
        .addItem('Roll Up...', 'rollUp'))
      .addItem('Recompute usage', 'recomputeUsage')
      .addItem('Collect data', 'nestDataCollection')
      .addToUi();

  return 'success';
}

// Google app script helpers
function rollUpLastMonth()        { nest.rollUpLastMonth();        }
function rollUpPenultimateMonth() { nest.rollUpPenultimateMonth(); }
function rollUpLastQuarter()      { nest.rollUpLastQuarter();      }
function rollUpAll()              { nest.rollUpAll();              }
function rollUpUntil()            { nest.rollUpUntil();            }
function rollUp()                 { nest.rollUp();                 }
function recomputeUsage()         { nest.recomputeUsage();         }

// get the Nest data collection class
var Nest = requireNest();

// instantiate a Nest data collection object
var nest = new Nest();

// return the nest data collection class
function requireNest() {
  return (function () {
    // Nest data collection class
    function Nest() {
      // compute settings
      this.settings = this.extend({MAXCOLUMN : 13, CRYPTKEY: 13}, settings, this.getSheetSettings());
    };
    
    // roll up data
    Nest.prototype.rollUp = function (options) {
      return this.doRollUp(options);
    }
    
    // roll up without row delete -- useful to debug roll up
    Nest.prototype.rollUpNoDelete = function (options) {
      return this.doRollUp(this.extend({doNotDelete: true}, options));
    }
    
    // roll up all data
    Nest.prototype.rollUpAll = function (options) {
      return this.doRollUp(this.extend({startDate: '1/1/2000', endDate: '1/1/2099'}, options));
    }
    
    // roll up data until
    Nest.prototype.rollUpUntil = function (options) {
      return this.doRollUp(this.extend({startDate: '1/1/2000'}, options));
    }
    
    // roll up data last month
    Nest.prototype.rollUpLastMonth = function (options) {
      var month = (new Date()).getMonth();
      return this.rollUpMonth(month > 0 ? month - 1 : 11, options);
    }
    
    // roll up data penultimate month
    Nest.prototype.rollUpPenultimateMonth = function (options) {
      var month = (new Date()).getMonth();
      return nest.rollUpMonth(month > 1 ? month - 2 : month == 1 ? 11 : 10, options);
    }
    
    // roll up data month
    Nest.prototype.rollUpMonth = function (month, options) {
      var today = new Date();
      return this.doRollUp(this.extend({startDate: new Date((month + 1) + '/1/' + today.getFullYear()), endDate: new Date((month < 11 ? (month + 2) : 1) + '/1/' + today.getFullYear())}, options));
    }
    
    // roll up data last quarter
    Nest.prototype.rollUpLastQuarter = function (options) {
      var today = new Date();
      var endDate = undefined;
      switch(today.getMonth()) {
        case 0: case 1: case 2: default:
          startDate = new Date('10/1/' + (today.getFullYear() - 1));
          endDate = new Date('1/1/' + today.getFullYear());
          break;
        case 3:  case 4: case 5:
          startDate = new Date('1/1/' + today.getFullYear());
          endDate = new Date('4/1/' + today.getFullYear());
          break;
        case 6: case 7: case 8:
          startDate = new Date('4/1/' + today.getFullYear());
          endDate = new Date('7/1/' + today.getFullYear());
          break;
        case 9: case 10: case 11:
          startDate = new Date('7/1/' + today.getFullYear());
          endDate = new Date('10/1/' + today.getFullYear());
          break;
      }
      return this.doRollUp(this.extend({startDate: startDate, endDate: endDate}, options));
    }
    
    // recompute usage
    Nest.prototype.recomputeUsage = function (options) {
      return this.doRecomputeUsage(options);
    }
    
    // collect data from nest and update the spreadsheet
    Nest.prototype.doDataCollection = function () {
      Logger.log('Collect nest data...');
      
      // login
      this.login();
      
      // collect nest information from the cloud
      var nest = this.getSample();
      
      Logger.log('Parse data...');
      //Logger.log(JSON.stringify(nest));
      
      // loop through structures
      for (var sid in nest['structure']) {
        var structure = nest['structure'][sid];
        var devices = structure['devices'];
        
        Logger.log('Parse structure: ' + (structure['name'] || sid) + '...');
        
        // loop through devices
        devices.forEach(function(did) {
          var did = did.split('.')[1];
          
          // current device information
          var device = nest['device'][did];
          var shared = nest['shared'][did];
          
          // get thermostat name
          var name = this.getThermostatName(nest, device, shared, sid);
          
          Logger.log('Parse device: ' + (name || did) + '...');
          
          // get the temperature scale for the thermostat, so we can access the appropriate temps
          var tempUnits = device['temperature_scale'].toLowerCase(); 
          
          // get the weather at the structure
          var weather = this.getStructureWeather(nest, sid);
          var gmtOffset = Math.round(parseFloat(weather['current']['gmt_offset']));
          
          // build the row to append to the sheet
          var newRow = [];
          newRow[newRow.length] = this.getStructureTime(gmtOffset);
          
          newRow[newRow.length] = name;
          newRow[newRow.length] = shared['hvac_ac_state'] ? 'cool' : shared['hvac_heater_state'] ? 'heat' : structure['away'] ? 'away' : 'off';
          
          newRow[newRow.length] = this.degree(shared['target_temperature'], tempUnits);
          newRow[newRow.length] = this.degree(shared['current_temperature'], tempUnits);
          newRow[newRow.length] = device['current_humidity'];
          
          newRow[newRow.length] = weather['current']['temp_' + tempUnits];
          newRow[newRow.length] = weather['current']['humidity'];
          newRow[newRow.length] = weather['current']['condition'];
          
          // get the sheet
          var sheetName = structure['name'] + ' ' + name;
          var sheet = this.getSheet(sheetName);
          
          // write the new data to the sheet
          Logger.log("Appending row to sheet: '" + sheetName + "'");
          sheet.appendRow(newRow);
          
          // compute usage
          var lastRow = sheet.getLastRow();
          if (this.settings.COMPUTEUSAGE && lastRow > 2) {
            Logger.log("Compute usage...");
            
            var row = lastRow - 1;
            var usage = ((newRow[0] - sheet.getRange(row, 1).getValue()) / 1000 / 60 / 60).toFixed(2);
            switch(sheet.getRange(row, 3).getValue()) {
              case 'cool': sheet.getRange(row, 10).setValue(usage); break;
              case 'heat': sheet.getRange(row, 11).setValue(usage); break;
              case 'away': sheet.getRange(row, 12).setValue(usage); break;
            }
          }
        }.bind(this));
      }
      
      // release data
      this.auth = this.headers = this.zip = this.weather = undefined;
      
      Logger.log("Success...");
      
      return 'success';
    }
    
    // roll up data
    Nest.prototype.doRollUp = function (options) {
      // compute options
      options = this.extend({startDate: null, endDate: null, uiEnabled: true, temperatureDecimals: this.settings.TEMPDECIMALS, doNotDelete: false, sheets: [SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()]}, options || {});
      
      // if start and end dates are not given, ask the user
      if ((!options.startDate || !options.endDate) && options.uiEnabled) {
        // get the spreadsheet ui
        var ui = SpreadsheetApp.getUi();
        
        // if not start date
        if (!options.startDate) {
          // ask the user for the start roll up date
          var response = ui.prompt('Nest data roll up', 'Enter a roll up start date (mm/dd/yyyy):', ui.ButtonSet.OK_CANCEL);
          if (response.getSelectedButton() != ui.Button.OK)
            return 'canceled';
          
          options.startDate = response.getResponseText();
        }
        
        // ensure start date is mm/dd/yyyy
        options.startDate = new Date(this.getRowDate(new Date(options.startDate)));
        
        // if no end date
        if (!options.endDate) {
          // ask the user for the end roll up date
          var response = ui.prompt('Nest data roll up', 'Enter a roll up end date (mm/dd/yyyy):', ui.ButtonSet.OK_CANCEL);
          if (response.getSelectedButton() != ui.Button.OK)
            return 'canceled';
          
          options.endDate = response.getResponseText();
        }
        
        // ensure end date is mm/dd/yyyy
        options.endDate = new Date(this.getRowDate(new Date(options.endDate)));
        
        // confirm the roll up dates with the user
        response = ui.alert('Nest data roll up', 'Rolling up: ' + this.getRowDate(options.startDate) + ' to ' + this.getRowDate(options.endDate), ui.ButtonSet.OK_CANCEL);
        if (response != ui.Button.OK || options.startDate >= options.endDate)
          return 'canceled';
      }
      
      // ensure date options are dates with the time stripped -- this covers options passed in
      options.startDate = new Date(this.getRowDate(new Date(options.startDate)));
      options.endDate = new Date(this.getRowDate(new Date(options.endDate)));
      
      Logger.log('rollUp ' + this.getRowDate(options.startDate) + ' to ' + this.getRowDate(options.endDate) + '...');
      
      // for each sheet
      options.sheets.forEach(function(sheet) {
        Logger.log('Sheet ' + sheet.getName() + '...');
        
        // set the current sheet
        this.sheet = sheet;
        
        // skip sheet if it is empty
        if (this.lastRow() < 3)
          return;
        
        // finalize the accumlated row
        function finalize() {
          // if the values are within the date range
          if (v0[0] < options.endDate) {
            Logger.log('Finalize... ' + r0 + ', ' + n);
            
            // if we accumulated values
            if (n > 1) {
              // finalize readings -- compute the averages
              for (var c = 3; c < 8; c++) {
                v0[c] = (v0[c] / n).toFixed(options.temperatureDecimals);
              }
              
              // finalize hvac state
              if (!options.doNotDelete) {
                switch((!this.isz(v0[9]) ? 1 : 0) + (!this.isz(v0[10]) ? 2 : 0) + (!this.isz(v0[11]) ? 4 : 0)) {
                  case 0: v0[2] = 'off';                break;
                  case 1: v0[2] = 'cool';               break;
                  case 2: v0[2] = 'heat';               break;
                  case 3: v0[2] = 'cool & heat';        break;
                  case 4: v0[2] = 'away';               break;
                  case 5: v0[2] = 'cool & away';        break;
                  case 6: v0[2] = 'heat & away';        break;
                  case 7: v0[2] = 'cool & heat & away'; break;
                }
              }
              
              // record number of samples in the average
              v0[12] = n;
              
              // finalize weather
              var mostFrequentWeather = Object.keys(weather)[0];
              for (var w in weather) {
                if (weather[w] > weather[mostFrequentWeather])
                  mostFrequentWeather = w;
              }
              v0[8] = mostFrequentWeather;
              
              // delete the intermediate rows
              if (!options.doNotDelete)
                this.deleteRows(r0 + 1, n - 1);
            }
            else
              // clear number of samples -- there is no average
              v0[12] = '';
            
            // finalize usage
            v0[ 9] = this.isz(v0[ 9]) ? '' : v0[ 9].toFixed(2);
            v0[10] = this.isz(v0[10]) ? '' : v0[10].toFixed(2);
            v0[11] = this.isz(v0[11]) ? '' : v0[11].toFixed(2);
            
            // write the finalized row
            this.updateRow(r0, v0);
          }
        }
        
        var r0 = 2;                             // row 0
        var v0 = this.getRow(r0);               // values for row 0
        
        // find the starting row
        while (v0[0] < options.startDate && r0 < this.lastRow()) {
          v0 = this.getRow(++r0);
        }
        
        // initialize the accumulator
        var t0      = this.getRowDate(v0[0]);   // date for 0
        var n       = 1;                        // number of rows accumulated
        var v1      = undefined;                // values for row 1
        var ti      = v0[0];                    // intermediate time used for accumulating usage
        var si      = v0[2];                    // intermediate state used for accumulating usage
        var weather = {};                       // accumulate weather frequencies
        
        // initialize usage computations
        if (v0[0] < options.endDate) {
          v0[9] = v0[10] = v0[11] = 0;
          weather[v0[8].replace(/mostly |partly /i, '')] = 1;
        }
        
        // scan the sheet, accumulating rows as we go 
        for (var r1 = r0 + 1; r1 <= this.lastRow() && v0[0] < options.endDate; ) {
          // get values for row 1
          v1 = this.getRow(r1);
          
          // accumulate hvac usage
          var usage = (v1[0] - ti) / 1000 / 60 / 60;
          if (/cool/i.test(si)) v0[ 9] += usage;
          if (/heat/i.test(si)) v0[10] += usage;
          if (/away/i.test(si)) v0[11] += usage;
          
          // remember intermediate time and state
          ti = v1[0];
          si = v1[2];
          
          // accumulate weather
          var w = v1[8].replace(/mostly |partly /i, '');
          weather[w] = weather[w] ? weather[w]++ : 1;
          
          // get the date for row 1
          var t1 = this.getRowDate(v1[0]);
          
          // if rows are on the same day
          if (t0 == t1) {
            // accumulate data values
            for (var c = 3; c < 8; c++) {
              v0[c] += v1[c];
            }
            
            // clear intermediate usage numbers -- they are no longer needed
            v1[9] = v1[10] = v1[11] = '';
            
            // number of rows accumulated
            n++;
            
            // next row to accumulate
            r1++;
          } else {
            // a new day
            
            // finalize the current day
            finalize.call(this);
            
            // reinitialize accumulator for the new day
            r0 = options.doNotDelete ? r1 : r0 + 1;
            r1 = r0 + 1;
            v0 = v1;
            t0 = t1;
            n = 1;
            weather = {};
            if (v0[0] < options.endDate) {
              v0[9] = v0[10] = v0[11] = 0;
              weather[v0[8].replace(/mostly |partly /i, '')] = 1;
            }
          }
        }
        
        // finalize the last row
        finalize.call(this);
        
        // update the sheet
        this.updateSheet();
      }.bind(this));
      
      Logger.log("Success...");
      
      return 'success';
    }
    
    // recompute usage
    Nest.prototype.doRecomputeUsage = function (options) {
      Logger.log('Recompute usage...');
      
      // compute options
      options = this.extend({sheets: [SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()]}, options || {});
      
      // for each sheet
      options.sheets.forEach(function(sheet) {
        Logger.log('Sheet ' + sheet.getName() + '...');
        
        // set the current sheet
        this.sheet = sheet;
        
        // skip sheet if it is empty
        if (this.lastRow() < 3)
          return;
        
        // for each row
        var v0 = this.getRow(2);
        for (var row = 3; row <= this.lastRow(); row++) {
          var v1 = this.getRow(row);
          
          // compute usage
          var usage = ((v1[0] - v0[0]) / 1000 / 60 / 60).toFixed(2);
          
          // update usage
          switch(v0[2]) {
            case 'cool': v0[ 9] = usage; v0[10] = v0[11] = ''; break;
            case 'heat': v0[10] = usage; v0[ 9] = v0[11] = ''; break;
            case 'away': v0[11] = usage; v0[ 9] = v0[10] = ''; break;
            default:     v0[ 9] =    ''; v0[10] = v0[11] = ''; break;
          }
          
          // update the row
          this.updateRow(row - 1, v0);
          
          // next row
          v0 = v1;
        }
        
        // clear the usage in the last row
        v0[9] = v0[10] = v0[11] = '';
        
        // update the row
        this.updateRow(row - 1, v0);
        
        // update the sheet
        this.updateSheet();
      }.bind(this));
      
      Logger.log("Success...");
      
      return 'success';
    }
    
    // login to nest
    Nest.prototype.login = function (resolve) {
      Logger.log('Logging in...');
      
      // login options
      var data = {
        'email': this.settings.EMAIL,
        'password': this.settings.PASSWORD
      }
      var options = {
        'method' : 'post',
        'muteHttpExceptions': true,
        'headers': {
          'Accept-Language': 'en-us',
          'Accept':          'application/json',
          'Connection':      'keep-alive'
        },
        'contentType': 'application/json',
        'payload': JSON.stringify(data)
      };
      
      // log in to nest
      // -- login is throttled by nest, my guess is that login fails when nest is 'busy' or otherwise determines too many logins have been made in some time period
      var response = UrlFetchApp.fetch('https://home.nest.com/session', options);
      if (response.getResponseCode() < 300) {
        // successful login
        
        // update settings
        this.auth = JSON.parse(response);
        this.headers = {
          'Authorization':        'Basic ' + this.auth['access_token'],
          'X-nl-user-id':          this.auth['userid'],
          'X-nl-protocol-version': '1',
          'Accept-Language':       'en-us',
          'Accept':                'application/json',
          'Connection':            'keep-alive'
        };
      } else {
        // login failed -- we will try again with the next trigger
        var errorMessage = 'Login failed: ' + response.getContentText() + response.getAllHeaders().toSource();
        Logger.log(errorMessage);
        throw errorMessage;
      }
    }
    
    // get Nest thermostat data sample
    Nest.prototype.getSample = function () {
      Logger.log('Getting nest data...');
      return JSON.parse(UrlFetchApp.fetch(this.auth['urls']['transport_url'] + '/v2/mobile/user.' + this.auth['userid'], {headers: this.headers}));
    }
    
    // get weather at the structure
    Nest.prototype.getStructureWeather = function (nest, sid) {
      var zip = nest['structure'][sid]['postal_code'];
      if (this.zip != zip || !this.weather) {
        Logger.log('Getting weather...');
        this.weather = JSON.parse(UrlFetchApp.fetch(this.auth['urls']['weather_url'] + zip, {headers: this.headers}))[zip.replace(/^0*/, '')];
        if (this.weather)
          this.zip = zip;
        else {
          this.weather= {current: {gmt_offset: "0", temp_f: 0, temp_c: 0, humidity: 0, condition: 'missing'}};
          this.zip = undefined;
        }
      }
      return this.weather;
    }
    
    // get the thermostat name
    Nest.prototype.getThermostatName = function (nest, device, shared, sid) {
      // use the device name given by the user
      var name = shared['name'];
      if (!name) {
        // use the device location
        
        // scan the locations
        var wheres = nest['where'][sid]['wheres'];
        for (var widx = 0; widx < wheres.length; widx++) {
          // if the location is the device location
          if (wheres[widx]['where_id'] == device['where_id']) {
            // use the device location as the name
            name = wheres[widx]['name'];
            break;
          }
        }
      }
      return name;
    }
    
    // get the current time at the structure
    Nest.prototype.getStructureTime = function (gmtOffsetInHours) {
      var d = new Date();
      return new Date(d.getTime() + 60000 * (d.getTimezoneOffset() + (gmtOffsetInHours * 60)));
    }
    
    // convert temp to fahrenheit or celcius
    Nest.prototype.degree = function (temp, tempUnits) {
      return tempUnits == 'f' ? Math.round(9.0 / 5.0 * temp + 32) : temp;
    }
    
    // get the row's date -- strip the time
    Nest.prototype.getRowDate = function (t) {
      return Utilities.formatDate(new Date(t), Session.getScriptTimeZone(), "MM/dd/YYYY");
    }
    
    // return true if we are ready for the next sample
    Nest.prototype.readyForNextSample = function () {
      // assume ready for the next sample
      var readyForNextSample = true;
      
      // commpute when the sample was scheduled to occur
      var now = this.getStructureTime(this.settings.TZDELTA - (new Date()).getTimezoneOffset() / 60);
      var minutesPerSample = this.settings.MINUTESPERSAMPLE;
      var minutes = Math.floor((now.getHours() * 60 + now.getMinutes()) / minutesPerSample) * minutesPerSample + this.settings.SAMPLESHIFT;
      var hours = Math.floor(minutes / 60);
      var sampleTime = new Date(now.getYear(), now.getMonth(), now.getDate(), hours, minutes - hours * 60);
      
      // get the first sheet
      var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
      
      // if we have a previous sample
      var lastRow = sheet.getLastRow();
      if (lastRow > 1) {
        // get the last sample time
        var lastSampleTime = new Date(sheet.getRange(lastRow, 1).getValue());
        
        // we are ready for a new sample if the last sample time is less than the sample time
        readyForNextSample = lastSampleTime < sampleTime;
      }
      
      // return if it is time for a new sample
      return readyForNextSample;
    }
    
    // is number zero
    Nest.prototype.isz = function (n) {
      return Math.abs(n) < 0.01;
    }
    
    // extend implementation
    // usage extend(dest, source1, source2, source3, ...)
    Nest.prototype.extend = function (dest) {
      // iterate over source arguments
      Array.prototype.slice.call(arguments, 1).forEach(function(source) {
        // if we have a source
        if (source) {
          // for each property in source
          for (var prop in source) {
            // if source property is iterable
            if (source[prop].constructor === Object) {
              // if dest property does not yet exist or exists but is iterable
              if (!dest[prop] || dest[prop].constructor === Object) {
                // create dest property if needed
                dest[prop] = dest[prop] || {};
                // extend dest with source
                this.extend(dest[prop], source[prop]);
              } else {
                // just copy source to dest
                dest[prop] = source[prop];
              }
            } else {
              // just copy source to dest
              dest[prop] = source[prop];
            }
          }
        }
      }.bind(this));
      
      // return extended property
      return dest;
    }
    
    // get settings from the settings sheet
    Nest.prototype.getSheetSettings = function () {
      // sheet settings
      var sheetSettings = {settingRow: {}};
      
      // get the settings sheet
      var sheet = this.getSheet('settings');
      if (sheet && sheet.getName().toLowerCase() == 'settings') {
        sheet.getRange(1, 1, sheet.getLastRow(), 2).getValues().forEach(function(setting, index) {
          // setting names are in the first column, values are in the second
          var name = setting[0];
          var value = setting[1];
          
          // if we have a setting name
          if (name) {
            // update the setting
            sheetSettings[name] = value;
            sheetSettings['settingRow'][name] = index + 1;
          }
        }); 
      }
      
      // if the sheet has a password
      if (sheetSettings['PASSWORD']) {
        // get the password
        var pw = sheetSettings['PASSWORD'];
        
        // if we have a password
        if (pw) {
          // passwords that start with '#:' are encrypted 
          if (pw.substring(0, 2) == '#:') {
            // decrypt the password
            sheetSettings['PASSWORD'] = this.decryptString(pw.substring(2))
          } else {
            // encrypt the password
            sheet.getRange(sheetSettings['settingRow']['PASSWORD'], 2).setValue('#:' + this.encryptString(pw));
          }
        }
      }
      
      // return the sheet settings
      return sheetSettings;
    }
    
    // find sheet by name -- default to first sheet
    Nest.prototype.getSheet = function (sheetName) {
      // get the list of sheets
      var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();

      // return the first sheet as a default
      var sheet = sheets[0];

      // match lowercased sheet name
      sheetName = sheetName.toLowerCase();

      // scan sheets
      for (var sheetIdx = 0; sheetIdx < sheets.length; sheetIdx++) {
        // if we found the sheet name
        if (sheetName == sheets[sheetIdx].getName().toLowerCase()) {
          // return the matched sheet name
          sheet = sheets[sheetIdx];
          break;
        }
      }
      return sheet;
    }
    
    // get the row values
    Nest.prototype.getRow = function (r) {
      if (!this.rows && !this.settings.DISABLESHEETCACHE) {
        // sheet operations such as getRange, and get/setValue are REALLY slow
        // it is MUCH faster to read the entire sheet into memory
        Logger.log('Getting all rows...');
        this.rows = this.sheet.getRange(1, 1, this.sheet.getLastRow(), this.settings.MAXCOLUMN).getValues();
        Logger.log('Rows: ' + this.rows.length + ', ' + this.rows[0].length);
      }
      
      // return the row values
      return this.rows ? this.rows[r - 1] : this.sheet.getRange(r, 1, 1, this.settings.MAXCOLUMN).getValues()[0];
    }
    
    // update row
    Nest.prototype.updateRow = function (r, v) {
      if (this.rows)
        // update the row with a copy of V
        this.rows[r - 1] = v.slice(0);
      else
        this.sheet.getRange(r, 1, 1, v.length).setValues([v]);
    }
    
    // delete rows
    Nest.prototype.deleteRows = function (r, n) {
      if (this.rows)
        this.rows.splice(r - 1, n);
      else
        this.sheet.deleteRows(r, n);
    }
    
    // get last row index
    Nest.prototype.lastRow = function () {
      return this.rows ? this.rows.length : this.sheet.getLastRow();
    }
    
    // update sheet
    Nest.prototype.updateSheet = function () {
      // update the sheet en masse
      if (this.rows) {
        var lastRow = this.sheet.getLastRow();

        Logger.log('Setting all rows... ' + this.rows.length + ', ' + this.rows[0].length + ', ' + lastRow);

        // update the values
        this.sheet.getRange(1, 1, this.rows.length, this.rows[0].length).setValues(this.rows);

        // delete extra rows
        if (this.rows.length < lastRow)
          this.sheet.deleteRows(this.rows.length + 1, lastRow - this.rows.length);

        // release data
        this.rows = undefined;
      }

      // release the sheet
      this.sheet = undefined;
    }
    
    // encrypt a string
    Nest.prototype.encryptString = function (s) {
      return Utilities.base64Encode(this.xorEncode(s));
    }
    
    // decrypt a string
    Nest.prototype.decryptString = function (s) {
      return this.xorEncode(String.fromCharCode.apply(undefined, Utilities.base64Decode(s)));
    }
    
    Nest.prototype.xorEncode = function (s) {
      // optionaly provide key for XOR obfuscation -- the key must not occur in s, otherwise an intermediate null is generated
      // -- use settings because this.settings is not yet available
      var key = settings.CRYPTKEY ? settings.CRYPTKEY : 13;
      
      // convert string to array of characters, then numbers, then xor encode
      s = s.split('')
           .map(function(c){return c.charCodeAt(0);})               
           .map(function(i){return i ^ key;})
      
      // return encoded string
      return String.fromCharCode.apply(undefined, s);
    };
    
    // return the Nest data collection class
    return Nest;
  })();
}

This is great. Thanks for this. One question, what are the units for the "Heater Usage" column? Not really sure what this number represents.

Is it possible to use this after migrating my Nest account to "Sign in with Google"?