Build custom user interfaces in Google Sheets using Apps Script

I've written a series of tutorials on building custom user interfaces (UIs) in Google Sheets using Google Apps Script.

With just a little bit of coding, you can build custom menus, alerts, prompt the user for input and more. If you do not know how to code, I've written a series of tutorials on learning to code using Google Sheets and Google Apps Script.

Why build custom user interfaces in Google Sheets?

Spreadsheets are ultimately about helping you and other users make good decisions and solve problems. They're a powerful tool and with this power comes complexity and the ability to make inadvertent errors. A custom user interface that is optimized for your use case can help reduce errors, improve productivity and even make your spreadsheets more fun to use!

Prerequisites

In this series of posts, I'm going to assume that you know the basics of coding using Apps Script. If you don't know coding or if you've never used Apps Script before, I've written a series of posts to teach you how to code using Google Sheets and Apps Script.

Custom user interfaces in Google Sheets

In this tutorial series, you'll learn how to build the following user interfaces in Google Sheets:

Custom menus in Google Sheets

Custom menus in Google Sheets to make it easy for end users to access the functionality that you've developed using Google Apps Script. These custom menus will be displayed in the menu bar right next to the Help menu. The screenshot below shows a custom menu called ⚙️ Admin Settings.

Screenshot of a Google Sheets spreadsheet showing a custom menu with two menu items.

Displaying notifications in Google Sheets using toasts

A toast is a notification that provides some contextual information to the user in an unobtrusive manner. A toast notification will automatically disappear after a few seconds. In Google Sheets, toast notifications are displayed in the bottom right corner of the screen.

A screenshot of Google Sheets showing a toast notification in the lower right corner.

Pop up alert messages in Google Sheets

A pop up alert message is used to display important information that the user must pay attention to. They can also be used to confirm with the user before taking some action.

Screenshot of Google Sheets with an alert message displayed.

Getting user input in Google Sheets using prompts

A prompt is used to get input from the user in a Google Sheets application. A prompt is a popup dialog that has a text field for the user to enter some text.

Screenshot of a Google Sheets spreadsheet displaying a prompt that is asking the user to enter their name.

Buttons in Google Sheets

You can create buttons in Google Sheets to run custom scripts. Clicking the button will run a Google Apps Script function that you specify.

Screenshot of a button in Google Sheets.

This can be very useful to build simple applications within the Google Sheets UI itself. For example, you can build a simple expense report approval workflow where clicking the Send Emails button in the top right corner will send out emails to let people know the approval status of their expense reports.

Screenshot of a Google Sheets spreadsheet.

Radio buttons in Google Sheets using Apps Script

A radio button is a user interface element that lets users select a single option from a number of different (and usually mutually exclusive) options. Learn how to build radio-button-like functionality in Google Sheets using Google Apps Script.

Custom sidebar in Google Sheets

A Sidebar is a UI widget that appears on the right hand side of Google Sheets. If you have added conditional formatting to your Google Sheets spreadsheet, you've seen sidebars in action. You can build your own custom sidebars using Apps Script.

Screenshot of a Google Sheets spreadsheet with a custom sidebar.

Custom dialog in Google Sheets

A custom dialog is a UI widget that is displayed over the Google Sheet. Unlike alerts and prompts, you can build your own user interfaces within a custom dialog using HTML, CSS and Javascript. You can build custom dialogs using Apps Script. These modal dialogs enable you to build custom prompts and alerts to get contextual information from the user.

A screenshot of a custom data entry form in a custom dialog in Google Sheets.

Have feedback for me?

I'd appreciate any feedback you can give me regarding this post.

Was it useful? Are there any errors or was something confusing? Would you like me to write a post about a related topic? Any other feedback is also welcome. Thank you so much!