Radio Buttons in Google Sheets: Only One Checkbox Checked

In this article, we’ll see how to make checkboxes in Google Sheets behave like radio buttons. In other words, we’ll ensure that only one can be checked at a time.

It’s impossible to do this with formulas alone, so we use Apps Script to uncheck boxes as required.

Here are the radio buttons in Google Sheets in action:

Radio Button In Google Sheets

You can see that when I check a new checkbox, any other checkboxes on that row are unchecked.

It takes a split second: you can see the row turns orange when the checked checkbox count is briefly 2, but this is simply the script working in the background.

Let’s see how to implement this with Apps Script.

Radio Buttons in Google Sheets Template

Click here to open the Radio Buttons in Google Sheets template

Feel free to make your own copy (File > Make a copy…).

Before you can use the radio buttons, you need to authorize the script to run.

To do this, open the script editor (Tools > Script editor…), select the onEdit function and run from within the Apps Script editor to grant the necessary permissions.

(If you can’t open the file, it’s likely because your G Suite account prohibits opening files from external sources. Talk to your G Suite administrator or try opening the file in an incognito browser.)

Radio Buttons in Google Sheets

To create your own radio buttons in Google Sheets, add this code to your Sheet:

  1. Go to Tools > Script editor…
  2. Delete the existing myFunction() code
  3. Copy in the code below
  4. Select the onEdit function and run from within the Apps Script editor to authorize the script
  5. Return to your Sheet to use the radio buttons
/**
 * onEdit to uncheck checkboxes as required
 */
function onEdit(e) {
  
  // get event object data: sheet name, row number and column number
  const sheet = e.range.getSheet();
  const row = e.range.rowStart;
  const col = e.range.columnStart;
  
  switch(col) {

    // case when column B is checked
    case 2:
      sheet.getRange("C" + row + ":E" + row).uncheck();
      break;

    // case when column C is checked
    case 3:
      sheet.getRangeList(["B" + row,"D" + row + ":E" + row]).uncheck();
      break;

    // case when column D is checked
    case 4:
      sheet.getRangeList(["B" + row + ":C" + row,"E" + row]).uncheck();
      break;
    
    // case when column E is checked
    case 5:
      sheet.getRange("B" + row + ":D" + row).uncheck();
      break;

    // cell is outside of columns B to D
    default:
      return;

  }
}

So how does this script work?

It uses the onEdit trigger in Apps Script to react when the user checks a checkbox. It then uses the information from that event (i.e. which checkbox was clicked) to know which checkboxes to uncheck.

You can see the lines that begin with e.range gather information about which Sheet we’re in and what the row and column coordinates of the checkbox are.

Then we use a switch statement to see if we clicked in column B, C, D, or E (i.e. column 2, 3, 4, or 5).

If we click a checkbox on either end of the row (i.e. column B or E) then we grab the continuous range on that row (i.e. C2:E2 or B2:D2) and use the uncheck method to uncheck any other checkboxes.

If the middle checkboxes are checked (i.e. column C or D) then the range we want to uncheck is no longer continuous, so we use the getRangeList method to get two ranges in A1 notation (e.g. B2 and D2:E2) and uncheck those checkboxes.

Formula To Count Checked Checkboxes

In column F of the GIF image at the top of this post, you’ll notice a formula that counts how many checkboxes are checked. It’s a simple check to ensure that the radio buttons are working correctly.

It’s a simple COUNTIF formula:

=COUNTIF(B2:E2,true)

(More info on the COUNTIF formula in lesson 3 of my free Advanced Formulas course.)

Formula To Return The Answer Column

We also added another formula to return the answer A, B, C, or D corresponding to the checkbox that is checked. (Note, this is not the column.)

It’s a straightforward INDEX and MATCH formula:

=INDEX($B$1:$E$1,1,MATCH(true,B2:E2,0))

(More info on the INDEX and MATCH formulas in lesson 10 of my free Advanced Formulas course.)

Conditional Formatting To Highlight Row Change

Conditional Formatting with Radio Buttons in Google Sheets

To add conditional formatting to highlight the whole row as it changes, we use the fact that the script takes a split second to run, so there are two checkboxes briefly checked.

The conditional formatting checks whether the COUNTIF result in column F is equal to 2, and if so, applies the formatting.

It’s applied to the whole row by using the $ sign in the conditional formatting custom formula:

=$F2=2

The conditional formatting is the orange that shows when a new checkbox is clicked:

Radio Button In Google Sheets

Generalizing The Script

Thanks to my fellow GDE Adam Morris for his extension to this radio button script, which works regardless of changes to the location of the checkboxes.

Google Apps Script: A Beginner’s Guide

What is Google Apps Script?

Google Apps Script is a cloud based scripting language for extending the functionality of Google Apps and building lightweight cloud-based applications.

What does this mean in practice?

It means you write small programs with Apps Script to extend the standard features of Google Workspace Apps. It’s great for filling in the gaps in your workflows.

For example, I used to be overwhelmed with feedback from my courses and couldn’t respond to everyone. Now, when a student submits their feedback, my script creates a draft email in Gmail ready for me to review. It includes all the feedback so I can read it within Gmail and respond immediately.

It made a previously impossible task manageable.

With Apps Script, you can do cool stuff like automating repetitive tasks, creating documents, emailing people automatically and connecting your Google Sheets to other services you use.

Writing your first Google Script

In this Google Sheets script tutorial, we’re going to write a script that is bound to our Google Sheet. This is called a container-bound script.

(If you’re looking for more advanced examples and tutorials, check out the full list of Apps Script articles on my homepage.)

Hello World in Google Apps Script

Let’s write our first, extremely basic program, the classic “Hello world” program beloved of computer teaching departments the world over.

Begin by creating a new Google Sheet.

Then click the menu Tools > Script editor... to open a new tab with the code editor window.

This will open a new tab in your browser, which is the Google Apps Script editor window:

Google Apps Script Editor

By default, it’ll open with a single Google Script file (code.gs) and a default code block, myFunction():

function myFunction() {
  
}

In the code window, between the curly braces after the function myFunction() syntax, write the following line of code so you have this in your code window:

function myFunction() {
  Browser.msgBox("Hello World!");
}

Your code window should now look like this:

Hello World Apps Script

Google Apps Script Authorization

Google Scripts have robust security protections to reduce risk from unverified apps, so we go through the authorization workflow when we first authorize our own apps.

When you hit the run button for the first time, you will be prompted to authorize the app to run:

Google Apps Script Authorization

Clicking Review Permissions pops up another window in turn, showing what permissions your app needs to run. In this instance the app wants to view and manage your spreadsheets in Google Drive, so click Allow (otherwise your script won’t be able to interact with your spreadsheet or do anything):

Google Apps Script Access

❗️When your first run your apps script, you may see the “app isn’t verified” screen and warnings about whether you want to continue.

In our case, since we are the creator of the app, we know it’s safe so we do want to continue. Furthermore, the apps script projects in this post are not intended to be published publicly for other users, so we don’t need to submit it to Google for review (although if you want to do that, here’s more information).

Click the “Advanced” button in the bottom left of the review permissions pop-up, and then click the “Go to Starter Script Code (unsafe)” at the bottom of the next screen to continue. Then type in the words “Continue” on the next screen, click Next, and finally review the permissions and click “ALLOW”, as shown in this image (showing a different script in the old editor):

More information can be found in this detailed blog post from Google Developer Expert Martin Hawksey.

Running a function in Apps Script

Once you’ve authorized the Google App script, the function will run (or execute).

If anything goes wrong with your code, this is the stage when you’d see a warning message (instead of the yellow message, you’ll get a red box with an error message in it).

Return to your Google Sheet and you should see the output of your program, a message box popup with the classic “Hello world!” message:

Message Box Google Sheets

Click on Ok to dismiss.

Great job! You’ve now written your first apps script program.

Rename functions in Google Apps Script

We should rename our function to something more meaningful.

At present, it’s called myFunction which is the default, generic name generated by Google. Every time I want to call this function (i.e. run it to do something) I would write myFunction(). This isn’t very descriptive, so let’s rename it to helloWorld(), which gives us some context.

So change your code in line 1 from this:

function myFunction() {
  Browser.msgBox("Hello World!");
}

to this:

function helloWorld() {
  Browser.msgBox("Hello World!");
}

Note, it’s convention in Apps Script to use the CamelCase naming convention, starting with a lowercase letter. Hence, we name our function helloWorld, with a lowercase h at the start of hello and an uppercase W at the start of World.

Adding a custom menu in Google Apps Script

In its current form, our program is pretty useless for many reasons, not least because we can only run it from the script editor window and not from our spreadsheet.

Let’s fix that by adding a custom menu to the menu bar of our spreadsheet so a user can run the script within the spreadsheet without needing to open up the editor window.

This is actually surprisingly easy to do, requiring only a few lines of code. Add the following 6 lines of code into the editor window, above the helloWorld() function we created above, as shown here:

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('My Custom Menu')
      .addItem('Say Hello', 'helloWorld')
      .addToUi();
}

function helloWorld() {
  Browser.msgBox("Hello World!");
}

If you look back at your spreadsheet tab in the browser now, nothing will have changed. You won’t have the custom menu there yet. We need to re-open our spreadsheet (refresh it) or run our onOpen() script first, for the menu to show up.

To run onOpen() from the editor window, first select then run the onOpen function as shown in this image:

Google Apps Script Function Menu

Now, when you return to your spreadsheet you’ll see a new menu on the right side of the Help option, called My Custom Menu. Click on it and it’ll open up to show a choice to run your Hello World program:

Custom menu

Run functions from buttons in Google Sheets

An alternative way to run Google Scripts from your Sheets is to bind the function to a button in your Sheet.

For example, here’s an invoice template Sheet with a RESET button to clear out the contents:

Button with apps script in google sheets

For more information on how to do this, have a look at this post: Add A Google Sheets Button To Run Scripts

Google Apps Script Examples

Macros in Google Sheets

Another great way to get started with Google Scripts is by using Macros. Macros are small programs in your Google Sheets that you record so that you can re-use them (for example applying standard formatting to a table). They use Apps Script under the hood so it’s a great way to get started.

Read more: The Complete Guide to Simple Automation using Google Sheets Macros

Custom function using Google Apps Script

Let’s create a custom function with Apps Script, and also demonstrate the use of the Maps Service. We’ll be creating a small custom function that calculates the driving distance between two points, based on Google Maps Service driving estimates.

The goal is to be able to have two place-names in our spreadsheet, and type the new function in a new cell to get the distance, as follows:

GAS custom function for maps

The solution should be:

GAS custom map function output

Copy the following code into the Apps Script editor window and save. First time, you’ll need to run the script once from the editor window and click “Allow” to ensure the script can interact with your spreadsheet.

function distanceBetweenPoints(start_point, end_point) {
  // get the directions
  const directions = Maps.newDirectionFinder()
     .setOrigin(start_point)
     .setDestination(end_point)
     .setMode(Maps.DirectionFinder.Mode.DRIVING)
     .getDirections();
  
  // get the first route and return the distance
  const route = directions.routes[0];
  const distance = route.legs[0].distance.text;
  return distance;
}

Saving data with Google Apps Script

Let’s take a look at another simple use case for this Google Sheets Apps Script tutorial.

Here, I’ve setup an importxml function to extract the number of followers a specific social media channel has (e.g. in this case a Reddit channel), and I want to save copy of that number at periodic intervals, like so:

save data in google sheet

In this script, I’ve created a custom menu (as we did above) to run my main function. The main function, saveData(), copies the top row of my spreadsheet (the live data) and pastes it to the next blank line below my current data range as text, thereby “saving” a snapshot in time.

The code for this example is:

// custom menu function
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
      .addItem('Save Data','saveData')
      .addToUi();
}

// function to save data
function saveData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheets()[0];
  const url = sheet.getRange('Sheet1!A1').getValue();
  const follower_count = sheet.getRange('Sheet1!B1').getValue();
  const date = sheet.getRange('Sheet1!C1').getValue();
  sheet.appendRow([url,follower_count,date]);
}

See this post: Saving Data in Google Sheets, for a step-by-step guide to creating and running this script.

Google Apps Script example in Google Docs

Google Apps Script is by no means confined to Sheets only and can be accessed from other Google Workspace tools.

Here’s a quick example in Google Docs, showing a script that inserts a specific symbol wherever your cursor is:

Google Docs Apps Script

We do this using Google App Scripts as follows:

1. Create a new Google Doc

2. Open script editor from the menu: Tools > Script editor...

3. In the newly opened Script tab, remove all of the boilerplate code (the “myFunction” code block)

4. Copy in the following code:

// code to add the custom menu
function onOpen() {
  const ui = DocumentApp.getUi();
  ui.createMenu('My Custom Menu')
      .addItem('Insert Symbol', 'insertSymbol')
      .addToUi();
};

// code to insert the symbol
function insertSymbol() {  
  // add symbol at the cursor position
  const cursor = DocumentApp.getActiveDocument().getCursor();
  cursor.insertText('§§');
  
};

5. You can change the special character in this line

cursor.insertText('§§');

to whatever you want it to be, e.g.

cursor.insertText('( ͡° ͜ʖ ͡°)');

6. Click Save and give your script project a name (doesn’t affect the running so call it what you want e.g. Insert Symbol)

7. Run the script for the first time by clicking on the menu: Run > onOpen

8. Google will recognize the script is not yet authorized and ask you if you want to continue. Click Continue

9. Since this the first run of the script, Google Docs asks you to authorize the script (I called my script “test” which you can see below):

Docs Apps Script Auth

10. Click Allow

11. Return to your Google Doc now.

12. You’ll have a new menu option, so click on it:
My Custom Menu > Insert Symbol

13. Click on Insert Symbol and you should see the symbol inserted wherever your cursor is.

Google Apps Script Tip: Use the Logger class

Use the Logger class to output text messages to the log files, to help debug code.

The log files are shown automatically after the program has finished running, or by going to the Executions menu in the left sidebar menu options (the fourth symbol, under the clock symbol).

The syntax in its most basic form is Logger.log(something in here). This records the value(s) of variable(s) at different steps of your program.

For example, add this script to a code file your editor window:

function logTimeRightNow() {
  const timestamp = new Date();
  Logger.log(timestamp);
}

Run the script in the editor window and you should see:

Google Apps Script Execution Logs

Real world examples from my own work

I’ve only scratched the surface of what’s possible using G.A.S. to extend the Google Apps experience.

Here’s a couple of interesting projects I’ve worked on:

1) A Sheets/web-app consisting of a custom web form that feeds data into a Google Sheet (including uploading images to Drive and showing thumbnails in the spreadsheet), then creates a PDF copy of the data in the spreadsheet and automatically emails it to the users. And with all the data in a master Google Sheet, it’s possible to perform data analysis, build dashboards showing data in real-time and share/collaborate with other users.

2) A dashboard that connects to a Google Analytics account, pulls in social media data, checks the website status and emails a summary screenshot as a PDF at the end of each day.

Marketing dashboard using Google Apps Script

3) A marking template that can send scores/feedback to students via email and Slack, with a single click from within Google Sheets. Read more in this article: Save time with this custom Google Sheets, Slack & Email integration

Send data from Google Sheets to Slack

My own journey into Google Apps Script

My friend Julian, from Measure School, interviewed me in May 2017 about my journey into Apps Script and my thoughts on getting started:

Google Apps Script Resources

For further reading, I’ve created this list of resources for information and inspiration:

Course

Documentation

Official Google Documentation

Google Workspace Developers Blog

Communities

Google Apps Script Group

Stack Overflow GAS questions

Imagination and patience to learn are the only limits to what you can do and where you can go with GAS. I hope you feel inspired to try extending your Sheets and Docs and automate those boring, repetitive tasks!

Related Articles

Sheet Sizer! Build A Tool To Measure Your Google Sheets Size With Apps Script

In this tutorial, you’ll use Apps Script to build a tool called Sheet Sizer, to measure the size of your Google Sheets!

Google Sheets has a limit of 5 million cells, but it’s hard to know how much of this space you’ve used.

Sheet Sizer will calculate the size of your Sheet and compare it to the 5 million cell limit in Google Sheets.

Sheet Sizer

Sheet Sizer: Build a sidebar to display information

Step 1:
Open a blank sheet and rename it to “Sheet Sizer”

Step 2:
Open the IDE. Go to Tools > Script editor

Step 3:
Rename your script file “Sheet Sizer Code”

Step 4:
In the Code.gs file, delete the existing “myFunction” code and copy in this code:

/**
* Add custom menu to sheet
*/
function onOpen() {

  SpreadsheetApp.getUi()
    .createMenu('Sheet Sizer')
    .addItem('Open Sheet Sizer', 'showSidebar')
    .addToUi();
}

/**
* function to show sidebar
*/
function showSidebar() {
 
  // create sidebar with HTML Service
  const html = HtmlService.createHtmlOutputFromFile('Sidebar').setTitle('Sheet Sizer');
 
// add sidebar to spreadsheet UI
  SpreadsheetApp.getUi().showSidebar(html);
}

There are two functions: onOpen, which will add the custom menu to your Sheet, and showSidebar, which will open a sidebar.

The text between the /* ... */ or lines starting with // are comments.

Step 5:
Click the + next to Files in the left menu, just above the Code.gs filename.

Add an HTML file and call it “Sidebar”. It should look like this:

Apps Script HTML files

Step 6:
In the Sidebar file, on line 7, between the two BODY tags of the existing code, copy in the following code:

<input type="button" value="Close" onclick="google.script.host.close()" />

This code adds a “Close” button to the sidebar.

Your Sidebar file should now look like this:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>

    <input type="button" value="Close" onclick="google.script.host.close()" />

  </body>
</html>

Step 7:
Don’t forget to hit Save

Step 8:
Select the Code.gs file, then select the onOpen function in the menu bar (from the drop down next to the word Debug). Then hit Run.

Step 9:
When you run for the first time, you have to accept the script permissions. If you see an “App isn’t verified” screen, click on Advanced, then “Go to…” and follow the prompts. (More info here.)

Step 10:
After authorizing the app in step 8, jump back to your Google Sheet. You should see a new custom menu “Sheet Sizer” in the menu bar, to the right of the Help menu.

Click the menu to open the sidebar.

Step 11:
Close the menu using the button.

Here’s what you’ve built so far:

Google Sheets sidebar with Apps Script

Sheet Sizer: Add a new button and functionality to the sidebar

Step 12:
In the Sidebar file, after the first BODY tag, on line 6, and before the INPUT tag on line 7, add a new line.

Paste in the new button code:

<input type="button" value="Get Sheet Size" onclick="getSheetSize()" />

When clicked, this will run a function called getSheetSize.

Step 13:
Add the getSheetSize function into the Sidebar file with the following code.

Copy and paste this after the two INPUT tags but before the final BODY tag, on line 9.

<script>
function getSheetSize() {
  
  google.script.run.auditSheet();

}
</script>

When the button is clicked to run the getSheetSize function (client side, in the sidebar), it will now run a function called auditSheet in our Apps Script (server side).

Step 14:
Go to the Code.gs file

Step 15:
Copy and paste this new function underneath the rest of your code:

/**
* Get size data for a given sheet url
*/
function auditSheet() {
  // get Sheet
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
 
  // get sheet name
  const name = sheet.getName();

  // get current sheet dimensions
  const maxRows = sheet.getMaxRows();
  const maxCols = sheet.getMaxColumns();
  const totalCells = maxRows * maxCols;

  // output
  SpreadsheetApp.getUi().alert(totalCells);
}

This gets the active Sheet of your spreadsheet and calculates the total number of cells as max rows multiplied by max columns.

Finally, the last line displays an alert popup to show the total number.

Step 16:
Back in your Google Sheet, run Sheet Sizer from the custom Sheet Sizer menu.

When you click on the “Get Sheet Size” button, you should see a popup that shows the number of cells in your Sheet:

Sheet Sizer sidebar

Sheet Sizer: Display the Sheet size in the sidebar

Step 17:
Delete this line of code in the auditSheet function:

SpreadsheetApp.getUi().alert(totalCells);

Step 18:
Paste in this new code, to replace the code you deleted in Step 4:

// put variables into object
const sheetSize = 'Sheet: ' + name +
  '<br>Row count: ' + maxRows +
  '<br>Column count: ' + maxCols +
  '<br>Total cells: ' + totalCells +
  '<br><br>You have used ' + ((totalCells / 5000000)*100).toFixed(2) + '% of your 5 million cell limit.';

return sheetSize;

Now, instead of showing the total number of cells in an alert popup, it sends the result back to the sidebar.

Let’s see how to display this result in your sidebar.

Step 19:
Go to the Sidebar file and copy this code after the two INPUT tags but before the first SCRIPT tag:

<div id="results"></div>

This is a DIV tag that we’ll use to display the output.

Step 20:
Staying in the Sidebar file, replace this line of code:

google.script.run.auditSheet();

with this:

google.script.run.withSuccessHandler(displayResults).auditSheet();

This uses the withSuccessHandler callback function, which we’ve called: displayResults

It runs when the Apps Script function auditSheet successfully executes on the server side. The return value of that auditSheet function is passed to a new function called displayResults, which we’ll create now.

Step 21:
Underneath the getSheetSize function, add this function:

function displayResults(results) {
  // display results in sidebar
  document.getElementById("results").innerHTML = results;
}

When this function runs, it adds the results value (our total cells count) to that DIV tag of the sidebar you added in step 7.

Step 22:
Back in your Google Sheet, run Sheet Sizer from the custom Sheet Sizer menu.

When you click on the “Get Sheet Size” button, you should see a popup that shows the number of cells in your Sheet:

Apps Script to measure Sheet Size

Sheet Sizer: Handle multiple sheets

Step 23:
Modify your auditSheet code to this:

/**
* Get size data for a given sheet url
*/
function auditSheet(sheet) {

  // get spreadsheet object
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  // get sheet name
  const name = sheet.getName();

  // get current sheet dimensions
  const maxRows = sheet.getMaxRows();
  const maxCols = sheet.getMaxColumns();
  const totalCells = maxRows * maxCols;

  // put variables into object
  const sheetSize = {
    name: name,
    rows: maxRows,
    cols: maxCols,
    total: totalCells
  }

  // return object to function that called it
  return sheetSize;

}

Step 24:
In your Code.gs file, copy and paste the following code underneath the existing code:

/**
* Audits all Sheets and passes full data back to sidebar
*/
function auditAllSheets() {

  // get spreadsheet object
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();

  // declare variables
  let output = '';
  let grandTotal = 0;

  // loop over sheets and get data for each
  sheets.forEach(sheet => {

    // get sheet results for the sheet
    const results = auditSheet(sheet);
    
    // create output string from results
    output = output + '<br><hr><br>Sheet: ' + results.name +
      '<br>Row count: ' + results.rows + 
      '<br>Column count: ' + results.cols +
      '<br>Total cells: ' + results.total + '<br>';

    // add results to grand total
    grandTotal = grandTotal + results.total;

  });

  // add grand total calculation to the output string
  output = output + '<br><hr><br>' + 
    'You have used ' + ((grandTotal / 5000000)*100).toFixed(2) + '% of your 5 million cell limit.';

  // pass results back to sidebar
  return output;

}

This adds a new function, auditAllSheets, which loops over all the sheets in your Google Sheet and calls the auditSheet function for each one. The results for each Sheet are joined together into a result string, called output.

Your Code.gs file should now look like this.

Step 25:
Jump back to your Sidebar file and replace this line of code:

google.script.run.withSuccessHandler(displayResults).auditSheet();

with this:

google.script.run.withSuccessHandler(displayResults).auditAllSheets();

The callback function is the general auditAllSheets function, not the specific individual sheet function.

Step 26:
Back in your Google Sheet, add another sheet to your Google Sheet (if you haven’t already) and run Sheet Sizer.

It will now display the results for all the sheets within your Google Sheet!

Sheet Sizer: Add CSS styles

This step is purely cosmetic to make the sidebar more aesthetically pleasing.

Step 27:

Add these CSS lines inside the HEAD tags of the sidebar file:

<!-- Add CSS code to format the sidebar from google stylesheet -->
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">

<style>
  body {
    padding: 20px;
  }
</style>

Step 28:

Add DIV BLOCKS to the elements of the sidebar:

<div class="block">
  <input type="button" class="create" value="Get Sheet Size" onclick="getSheetSize()" /> 
</div>

<div class="block">
  <input type="button" value="Close" onclick="google.script.host.close()" />
</div>

<div class="block">
  <div id="results"></div> 
</div>

Sheet Sizer: Global Variables

Step 29:

One final improvement from me is to move the 5 million cell limit number into a global variable.

Currently, it’s buried in your script so it’s difficult to find and update if and when Google updates the Sheets cell limit.

It’s not good practice to hard-code variables in your code for this reason.

The solution is to move it into a global variable at the top of your Code.gs file, with the following code:

/**
 * Global Variable
 */
const MAX_SHEET_CELLS = 5000000;

And then change the output line by replacing the 5000000 number with the new global variable MAX_SHEET_CELLS:

// add grand total calculation to the output string
output = output + '<br><hr><br>' + 
  'You have used ' + ((grandTotal / MAX_SHEET_CELLS)*100).toFixed(2) + '% of your 5 million cell limit.';

Here is your final Sheet Sizer tool in action:

Sheet Sizer

Click here to see the full code for the Sheet Sizer tool on GitHub.

Next steps: have a go at formatting the numbers shown in the sidebar, by adding thousand separators so that 1000 shows as 1,000 for example.

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.