Control Your Nest Thermostat And Build A Temperature Logger In Google Sheets Using Apps Script

If you have a Nest thermostat at home, you can access it from your Google Sheet by using Apps Script to connect to the Smart Device Management API.

It means you can do some cool stuff like build a virtual, working Nest thermostat in your Google Sheet:

Nest Thermostat in Google Sheets
Nest Thermostat in Google Sheets

Zooming in, this is simply a pixel art image of a Nest device, in my Google Sheet:

Nest Thermostat in Google Sheets

(I’ll show you how to create this below.)

More practically, you can log the temperature (first chart) and humidity inside your home:

Home Nest Thermostat Temperature Log
Home Nest Thermostat Temperature Log
Home Nest Thermostat Humidity Log
Home Nest Thermostat Humidity Log

Smart Device Management API

In September 2020, Google launched the Device Access Console for Nest Devices. It meant individuals could connect to their smart home devices via the Smart Device Management API.

In this tutorial, we’ll look at how to connect to the Smart Device Management (SMD) API to access data from Nest Thermostats, using Apps Script.

If you’re new to Apps Script, have a read of my beginner’s guide to Google Apps Script.

API Setup

There are a number of steps you have to complete before you can access the SMD API.

Step 1: Register for the Device Access Program, which incurs a non-refundable $5 fee.

Step 2: In your Google Cloud Console, create a new project.

Step 3: Enable the Smart Device Management API in Google Cloud Console for this project.

Step 4: Create OAuth credentials for this project in your Google Cloud Console.

Step 5: Create a new Google Sheet (pro tip: type sheet.new into your browser!).

Step 6: Add a redirect URI for the project to your Google Cloud Project, which takes the form:

https://script.google.com/macros/d/{SCRIPT_ID}/usercallback

Replace {SCRIPT_ID} with your actual script ID, which can be found under the Settings menu on left hand side of the Apps Script Editor.

You want the ID of the script attached to the Google Sheet you created in step 5 for this project.

Step 7: Back in your Device Access Console, create a new project:

Smart Device Access Console

Step 8: When prompted, add the OAuth client ID from your Google Cloud project to this new Smart Device project.

Step 9: Activate your Nest device(s) when prompted during project creation

One important point is that you need to register for the API with the same Google account as the one you use for your Nest account.

Let’s start by creating creating the temperature logger:

Nest Thermostat Home Temperature and Humidity Logger

Apps Script & OAuth 2.0 Library

We need to use the OAuth2 for Apps Script library.

Step 10: Add the library from setup section here to your Apps Script project and select the most recent version.

Step 11: Add 3 more script files to your project: oauth2.gs, helperFunctions.gs and globalVariables.gs

Apps Script File Structure

Step 12: Add Global Variables:

Open the script editor (Tools > Script editor) and paste the Google Cloud Client ID and Client Secret into your Apps Script file as global variables. (Warning: don’t share this script publicly without either removing them or using a more robust approach such as storing them in your user properties.)

/**
 * Global Variables
 */
const PROJECT_ID = 'XXXXXXXXXXXXXXX';
const OAUTH_CLIENT_ID = 'XXXXXXXXXXXXXXX';
const OAUTH_CLIENT_SECRET = 'XXXXXXXXXXXXXXX';
const DOWNSTAIRS_THERMOSTAT = 'XXXXXXXXXXXXXXX';
const UPSTAIRS_THERMOSTAT = 'XXXXXXXXXXXXXXX';

Code on GitHub: globalVariables.js

Step 13: Add the OAuth scaffolding to the OAuth.gs file.

This example follows the Drive App example in Google’s OAuth2 for Apps Script library, modified for the Smart Device Management API.

/**
 * Create the OAuth 2 service
 */
function getSmartService() {
  // Create a new service with the given name. The name will be used when
  // persisting the authorized token, so ensure it is unique within the
  // scope of the property store.
  return OAuth2.createService('smd')

      // Set the endpoint URLs, which are the same for all Google services.
      .setAuthorizationBaseUrl('https://nestservices.google.com/partnerconnections/' + PROJECT_ID + '/auth')
      .setTokenUrl('https://www.googleapis.com/oauth2/v4/token')

      // Set the client ID and secret, from the Google Developers Console.
      .setClientId(OAUTH_CLIENT_ID)
      .setClientSecret(OAUTH_CLIENT_SECRET)

      // Set the name of the callback function in the script referenced
      // above that should be invoked to complete the OAuth flow.
      .setCallbackFunction('authCallback')

      // Set the property store where authorized tokens should be persisted.
      .setPropertyStore(PropertiesService.getUserProperties())

      // Set the scopes to request (space-separated for Google services).
      .setScope('https://www.googleapis.com/auth/sdm.service')

      // Below are Google-specific OAuth2 parameters.

      // Sets the login hint, which will prevent the account chooser screen
      // from being shown to users logged in with multiple accounts.
      .setParam('login_hint', Session.getEffectiveUser().getEmail())

      // Requests offline access.
      .setParam('access_type', 'offline')

      // Consent prompt is required to ensure a refresh token is always
      // returned when requesting offline access.
      .setParam('prompt', 'consent');
}

/**
 * Direct the user to the authorization URL
 */
function showSidebar() {
  
  const smartService = getSmartService();
  
  if (!smartService.hasAccess()) {

    // App does not have access yet
    const authorizationUrl = smartService.getAuthorizationUrl();

    const template = HtmlService.createTemplate(
        '<a href="<?= authorizationUrl ?>" target="_blank">Authorize</a>. ' +
        'Reopen the sidebar when the authorization is complete.');
    
    template.authorizationUrl = authorizationUrl;
    
    const page = template.evaluate();

    SpreadsheetApp.getUi().showSidebar(page);

  } else {
    // App has access
    console.log('App has access');
    
    // make the API request
    makeRequest();
  }
}

/**
 * Handle the callback
 */
function authCallback(request) {
  
  const smartService = getSmartService();
  
  const isAuthorized = smartService.handleCallback(request);
  
  if (isAuthorized) {
    return HtmlService.createHtmlOutput('Success! You can close this tab.');
  } else {
    return HtmlService.createHtmlOutput('Denied. You can close this tab');
  }
}

Code on GitHub: Oauth2.js

Step 14: Add helper functions to the helperFunctions.gs file

A couple of useful functions to convert from celcius to farenheit and vice versa.

/**
 * function to convert celcius to farenheit
 */ 
const convertCtoF = t => ( (t * 9/5) + 32 );
const convertFtoC = t => ( (t - 32) * 5/9 );

Code on GitHub: helperFunctions.js

Step 15: Finally, add the actual program logic to call the endpoint and get the device IDs.

I run this function once to get a list of the device IDs, which I’ll use elsewhere in my code. The listDevices function in turn calls the makeRequest function to actually call the API.

/**
 * list devices to get thermostat IDs
 */
function listDevices() {

  // specify the endpoint
  const endpoint = '/enterprises/' + PROJECT_ID + '/devices';

  // blank array to hold device data
  let deviceArray = [];

  // make request to smart api
  const data = makeRequest(endpoint);
  const deviceData = data.devices;
  console.log(deviceData);

  deviceData.forEach(device => {
    const name = device.name;
    const type = device.type;
    deviceArray.push([name,type]);
  });

  // get the Sheet
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();

  // output the data
  sheet.getRange(2,1,deviceArray.length,2).setValues(deviceArray);

}

/**
 * function to make request to google smart api
 */
function makeRequest(endpoint) {

  // get the smart service
  const smartService = getSmartService();
  
  // get the access token
  const access_token = smartService.getAccessToken();
  console.log(access_token);

  // setup the SMD API url
  const url = 'https://smartdevicemanagement.googleapis.com/v1';
  //const endpoint = '/enterprises/' + PROJECT_ID + '/devices';

  // setup the headers for the call
  const headers = {
    'Authorization': 'Bearer ' + access_token,
    'Content-Type': 'application/json'
  }
  
  // set up params
  const params = {
    'headers': headers,
    'method': 'get',
    'muteHttpExceptions': true
  }
  
  // try calling API
  try {
    const response = UrlFetchApp.fetch(url + endpoint, params);
    const responseBody = JSON.parse(response.getContentText());
    
    return responseBody;
  }
  catch(e) {
    console.log('Error: ' + e);
  }
}

Code on GitHub: Code.js

Step 16: Add the device IDs to your global variables file, replacing the “XXXXXXXX”.

I have two Nest thermostats, one upstairs and one downstairs, hence why I have those two global variables.

Step 17: Get the device traits, i.e. the temperature, humidity etc.

/**
 * function to make request to google smart api
 */
function logThermostatDataAllDevices() {

  // get the latest weather data
  const weatherDataArray = retrieveWeather('KMRB');
  console.log(weatherDataArray);
  
  // get the smart service
  const smartService = getSmartService();
  
  // get the access token
  const access_token = smartService.getAccessToken();

  // setup the SMD API url
  const url = 'https://smartdevicemanagement.googleapis.com/v1';
  const endpoint = '/enterprises/' + PROJECT_ID + '/devices';

  // setup the headers for the call
  const headers = {
    'Authorization': 'Bearer ' + access_token,
    'Content-Type': 'application/json'
  }
  
  // setup the parameters for url fetch
  const params = {
    'headers': headers,
    'method': 'get',
    'muteHttpExceptions': true
  }

  // empty array to hold device data
  let dataArray = [];
  //let smdWeatherArray = [];
  
  // try calling API
  try {

    // url fetch to call api
    const response = UrlFetchApp.fetch(url + endpoint, params);
    const responseCode = response.getResponseCode();
    const responseBody = JSON.parse(response.getContentText());
    
    // log responses
    console.log(responseCode);
    //console.log(responseBody);

    // get devices
    const devices = responseBody['devices'];
    //console.log(devices);

    // create timestamp for api call
    const d = new Date();

    devices.forEach(device => {
      
      if (device['type'] === 'sdm.devices.types.THERMOSTAT') {

        // get relevant info
        const name = device['name'];
        const type = device['type'];
        let location = '';
        const humidity = device['traits']['sdm.devices.traits.Humidity']['ambientHumidityPercent'];
        const connectivity = device['traits']['sdm.devices.traits.Connectivity']['status'];
        const fan = device['traits']['sdm.devices.traits.Fan']['timerMode'];
        const mode = device['traits']['sdm.devices.traits.ThermostatMode']['mode'];
        const thermostatEcoMode = device['traits']['sdm.devices.traits.ThermostatEco']['mode'];
        const thermostatEcoHeatCelcius = device['traits']['sdm.devices.traits.ThermostatEco']['heatCelsius'];
        const thermostatEcoHeatFarenheit = convertCtoF(thermostatEcoHeatCelcius);
        const thermostatEcoCoolCelcius = device['traits']['sdm.devices.traits.ThermostatEco']['coolCelsius'];
        const thermostatEcoCoolFarenheit = convertCtoF(thermostatEcoCoolCelcius);
        const thermostatHvac = device['traits']['sdm.devices.traits.ThermostatHvac']['status'];
        const tempCelcius = device['traits']['sdm.devices.traits.Temperature']['ambientTemperatureCelsius'];
        const tempFarenheit = convertCtoF(tempCelcius);

        if (name === 'enterprises/' + PROJECT_ID + '/devices/' + DOWNSTAIRS_THERMOSTAT) {
          location = 'Downstairs';
        }
        else {
          location = 'Upstairs';
        }

        dataArray.push(
          [
            d,
            name,
            type,
            location,
            humidity,
            connectivity,
            fan,
            mode,
            thermostatEcoMode,
            thermostatEcoHeatCelcius,
            thermostatEcoHeatFarenheit,
            thermostatEcoCoolCelcius,
            thermostatEcoCoolFarenheit,
            thermostatHvac,
            tempCelcius,
            tempFarenheit
          ].concat(weatherDataArray)
        );
        
        //dataArray = dataArray;

      }

    });
    console.log(dataArray);

    // get the Sheet
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = ss.getSheetByName('thermostatLogs');

    // output the data
    sheet.getRange(sheet.getLastRow()+1,1,dataArray.length,dataArray[0].length).setValues(dataArray);
  }
  catch(e) {
    console.log('Error: ' + e);
  }

}

You’ll notice that this function calls another function, on line 7, to retrieve a local weather forecast to add outside measurements into the mix.

Later the outside weather data array (returned from the weather function) is concatenated to the Nest Thermostat data array, on line 102, before the combined arrays are pasted as rows into the Google Sheet.

Each time the function runs, two rows are created: one for upstairs and one for downstairs.

Code on GitHub: Code.js

Now, let’s create the function to retrieve the outside conditions:

Adding Local Outside Temperature and Humidity

Step 18: Add a function to retrieve the current weather conditions from the nearest weather station to your location.

Use this station list to find out the code for your nearest station.

/**
 * function to retrieve latest weather forecast for nearby area
 * list of stations:
 * https://forecast.weather.gov/stations.php
 */
function retrieveWeather(stationCode) {

  const weatherArray = [];

  //const stationCode = 'KMRB';
  try {
    const weatherUrl = 'https://api.weather.gov/stations/' + stationCode + '/observations/latest';
    const response = UrlFetchApp.fetch(weatherUrl);
    const weatherData = JSON.parse(response.getContentText());

    // parse the data
    console.log(weatherData.properties);
    const textDescription = weatherData['properties']['textDescription'];
    const tempC = weatherData['properties']['temperature']['value'];
    const tempF = convertCtoF(tempC);
    const dewpointC = weatherData['properties']['dewpoint']['value'];
    const dewpointF = convertCtoF(dewpointC);
    const windDirection = weatherData['properties']['windDirection']['value'];
    const windSpeed = weatherData['properties']['windSpeed']['value'];
    const barometricPressure = weatherData['properties']['barometricPressure']['value'];
    const seaLevelPressure = weatherData['properties']['seaLevelPressure']['value'];
    const visibility = weatherData['properties']['visibility']['value'];
    const relativeHumidity = weatherData['properties']['relativeHumidity']['value'];
    const windChill = weatherData['properties']['windChill']['value'];

    // add to array
    weatherArray.push(
      textDescription,
      tempC,
      tempF,
      dewpointC,
      dewpointF,
      windDirection,
      windSpeed,
      barometricPressure,
      seaLevelPressure,
      visibility,
      relativeHumidity,
      windChill
    );
  }
  catch (e) {
    console.log('Error: ' + e);
  }
  console.log(weatherArray);
  
  return weatherArray;

}

The full script for the thermostat temperature and humidity logger, including the outdoor temperature and humidity, can be found here on GitHub: Thermostat Logger Repo

Step 19: Add a simple trigger to run the logThermostatDataAllDevices function on a periodic basis. You could consider once a day, once an hour or even more frequently. I’ve gone for once every 15 minutes.

After a while, your data should look like this:

Nest API data
(Click to enlarge)

Creating a Chart to show the logs

We need to wrangle the data (shown above) from the Thermostats Logs table into something suitable for the Google Sheets chart tool.

Step 20: Create a new sheet in your Google Sheet

Step 21: In this new sheet, use the Google Sheets Query function in cell A1 to parse the thermostat logs data:

=QUERY(thermostatLogs!$A:$AB,"select A, D, N, P, E, S, AA where D = 'Downstairs' label P 'Downstairs Temp, F', E 'Downstairs Humidity'",1)

It looks complicated but it really does two simple tasks:

  1. It filters the data to only include rows related to my downstairs thermostat
  2. It includes only the columns I need to create the chart

The output of this function looks like this:

Nest Thermostat Chart Data

Step 22: Adjacent to this output, use another Query function to output the upstairs temperature.

Step 23: plot a line chart using column A (Timestamp) as the chart X-axis and the downstairs temperature, upstairs temperature and outside temperature as series.

Home Nest Thermostat Temperature Log
Home Nest Thermostat Temperature Log

Step 24: Create the humidity chart in the same way.

Controlling Your Nest Thermostat from a Google Sheet

Set thermostat temperature API endpoint

Step 25: Create a new sheet in your Google Sheet, called “sheetNest”

Step 26: Add the set temperature function to your Code.gs file, which makes use of the ThermostatTemperatureSetpoint endpoint.

When you run this function, it takes the number from cell A1 (your input cell) and sends that to the SMD API as the temperature value to set the Nest to.

/**
 * function to change temperature to value in the Google Sheet
 */
function setTemperature() {
  
  // get temperature from Sheet
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const tempSheet = ss.getSheetByName('sheetNest');
  const tempF = tempSheet.getRange('A1').getValue(); // Change this cell reference to match the temperature input cell of your Google Sheet 
  const tempC = convertFtoC(tempF);

  console.log(tempC.toFixed(1));
  console.log(typeof tempC)

  // get the smart service
  const smartService = getSmartService();
  
  // get the access token
  const access_token = smartService.getAccessToken();
  console.log(access_token);

  // setup the SMD API url
  const url = 'https://smartdevicemanagement.googleapis.com/v1';

  // set the endpoint
  const endpoint = '/enterprises/'  + PROJECT_ID + '/devices/' + DOWNSTAIRS_THERMOSTAT + ':executeCommand';

  // setup the headers for the call
  const headers = {
    'Authorization': 'Bearer ' + access_token,
    'Content-Type': 'application/json'
  }

  const data = {
    'command': 'sdm.devices.commands.ThermostatTemperatureSetpoint.SetHeat',
    'params': {
      'heatCelsius': tempC
    }
  }
  
  const options = {
    'headers': headers,
    'method': 'post',
    'payload': JSON.stringify(data)
  }
  
  try {
    // try calling API
    const response = UrlFetchApp.fetch(url + endpoint, options);

  }
  catch(e) {
    console.log('Error: ' + e);
  }
}

Code on GitHub: Code.js

Make your Google Sheet Virtual Nest Thermostat pretty!

Step 27: To finish the virtual Nest thermostat, I used the Pixel Paintings with Google Sheets tool from fellow GDE Amit Agarwal to turn a picture of my Nest into a pixel image in my Google Sheet.

I uploaded a photo of my Nest thermostat and the Pixel Paintings tool turned it into this:

Nest Thermostat in Google Sheets
Nest Thermostat in Google Sheets

If you change the input cell to the middle of your Nest image (as I’ve done above where the big “71” is now the value I set my temperature to) don’t forget to also change the cell reference in your code file to get the temperature from the new input cell (“DA71” in my example). I.e. replace the “A1” on line 9 of the code above with “DA71” (or whatever cell you’re using to input your temperature).

Step 28: Finally, if you haven’t already, add a custom menu so you can run these functions directly from your Google Sheet:

/**
 * Custom menu to use tool from Sheet UI
 */
function onOpen() {
  
  const ui = SpreadsheetApp.getUi();
  
  ui.createMenu('Smart Device Tool')
    .addItem('Smart Device Tool', 'showSidebar')
    .addSeparator()
    .addItem('Log thermostat data','logThermostatDataAllDevices')
    .addItem('Set temperature','setTemperature')
    .addToUi();
  
}

Have fun!

Let me know in the comments if you build anything cool with this API.

17 thoughts on “Control Your Nest Thermostat And Build A Temperature Logger In Google Sheets Using Apps Script”

  1. Thank you!!! Sadly, I’m getting an error “Access not granted or expired”. When I hard code my access token in as a global variable, it works fine, but then it expires after the hour or so. There’s something happening where it can’t retreive it on its own. Anyone seeing similar?

    1. Found resolution. I bypassed the OAuth library with:

      var token = ScriptApp.getOAuthToken();

      and then added oauthScopes to my appsscript.json:

      “oauthScopes”: [
      “https://www.googleapis.com/auth/script.external_request”,
      “https://www.googleapis.com/auth/userinfo.email”,
      “https://www.googleapis.com/auth/sdm.service”,
      “https://www.googleapis.com/auth/spreadsheets”
      ]

      Now the magic really begins. Thanks Ben

      1. Hey Jared,

        My token times out as well, but after 7 days, so I’d love to find a solution that doesn’t require me to add a token refresh task.

        When I use this token, I can’t find my Nest project, even though it works with the Ben’s auth flow. Any ideas?

        Thanks!

        1. I read that you need to go to your OAuth Consent Screen in Google Cloud Platform and set your publishing status from “Testing” to “In Production”. Hope that works for you.

      2. Hi Jared,

        What precisely did you do to achieve this? (i.e. Which lines of code did you replace with what? (Sorry I’m quite new to all of this))

        1. I’ve figured out how to add the oauthScopes to the appsscript.json, but am unsure on what changes need to be made to Code.gs or oauth2.gs (?) in order for the code to work?

      1. Thanks Ben! Making progress now.
        Are you familiar with issues in retrieving the access token? Error now is occurring on :
        // get the access token
        const access_token = smartService.getAccessToken();

        Error: Property store is required.

  2. Very cool project. This came with some challenges that helped me with my App Script debugging. Keep them coming Ben!!

  3. Hi,
    When I click “Authorize” from the google sheet menu, I get the Google Nest permissions page. I select all the items, click next and get following error:

    Authorization Error
    Error 400: invalid_request
    OAuth 2 parameters can only have a single value: login_hint

    Any suggestions on what could be wrong? Thanks.

    1. I was able to solve it. A web search did not help, but I looked through oauth2.gs and the comment for ‘login_hint’ explained the possible issue with multiple google accounts, which I have. I used a different browser with only one google account and the authorization worked fine.

      Next issue: In sheets the script says it executed, but nothing gets logged. When I execute in the Apps Script editor, the data gets read from the thermostats, but I get: “Error: TypeError: Cannot read property ‘getRange’ of null.” with the last value of the array being NULL.

Leave a Reply

Your email address will not be published. Required fields are marked *