Up until our previous lesson, we dove deep into the “trigger” features that power automated execution in Google Apps Script (GAS). Being able to run your programs automatically at set times or in response to events opens up so many exciting possibilities for workflow automation!
Starting with this brand new phase, we are going to build mechanisms that let programs interact directly with users working in Google Sheets!
Think of handy interactive features like showing a warning popup that asks “Are you sure you want to delete this?” when a button is clicked, prompting the user for text input, or adding your very own custom menu right to the Google Sheets menu bar.
The foundational command that acts as the “command center” for all on-screen interactions is SpreadsheetApp.getUi(), which we will explore in detail today.
Whenever you want to display something on the screen in GAS, you will always start with getUi(). In this tutorial, using our Sapporo office’s “Hokkaido Soup Curry Tour List” as our hands-on sample data, I’ll walk you through how it works, how to use it, and the classic pitfalls every beginner faces—step by step!
1. What Is SpreadsheetApp.getUi() in the First Place?
It might look like a long, intimidating incantation at first glance, but breaking it down reveals how intuitive it actually is.
Breaking Down the Code Structure
In programming, we connect objects and actions with a dot (.) to specify “who (target)” and “what to do (command)”.
SpreadsheetApp.getUi()
- SpreadsheetApp: As introduced in Series #2, this is the top-level parent class that oversees and manages Google Sheets as a whole.
- .getUi():
Uistands for User Interface. Literally translated, this method says: “Please retrieve the visual interface environment where the user interacts!”
In short, you are telling GAS: “Fetch the interface controller (Ui object) for the currently open spreadsheet so my script can interact with the screen!”
Picture It Like a “Movie Theater Information Desk”!
A great analogy for on-screen interactions in Google Sheets is a movie theater.
| Google Sheets Concept | Movie Theater Analogy | Main Role in GAS |
| Spreadsheet (Entire Screen) | The Theater Building | The workspace where the user is operating |
| Ui Object (Retrieved via getUi()) | Main Information Desk | The service counter that issues popups and menus |
| alert() / prompt(), etc. | Announcements & Ticket Checks | Interactions like “The movie is starting!” or “Please enter your name.” |
If you want to broadcast announcements to guests or handle ticket checks inside a theater, you first need to open the main information desk (Ui).
The action of setting up this information desk is precisely what SpreadsheetApp.getUi() does.
Once you have this desk (the Ui object), you can perform tasks like:
- Displaying message boxes (
Ui.alert()) - Displaying input dialogs (
Ui.prompt()) - Adding custom menus to the top toolbar (
Ui.createMenu()) - Showing custom sidebars or modal dialogs (
Ui.showSidebar()/Ui.showModalDialog())
2. STEP 1: Preparation! Create the “Hokkaido Soup Curry Tour List”
Let’s open Google Sheets and prepare our practice spreadsheet together!
Steps
- Create a new Google Sheet: Go to Google Drive, click “New” > “Google Sheets”.
- Rename the file: Change the title at the top left to “Hokkaido Soup Curry Tour List”.
- Enter sample data into the sheet: Place headers in Row 1, and add some popular restaurants below it.
| A | B | C | |
| 1 | Restaurant Name | Area | Spice Level Recommendation |
| 2 | GARAKU | Sapporo – Chuo Ward | ★★★★★ |
| 3 | Okushiba Shoten | Sapporo – Station Area | ★★★★☆ |
| 4 | RAMAI | Sapporo – Toyohira Ward | ★★★★★ |
- Open the Apps Script editor: In the top menu, click Extensions > Apps Script.
Once the code editor opens in a new tab, you are ready to go!
3. STEP 2: Hands-on! Getting the UI Object with getUi()
Let’s write a simple script that grabs the UI object using getUi() and adds a custom menu and message popup directly inside Google Sheets.
1. Enter the Code
Delete any existing default code in the editor, and paste the following snippet:
function setupUserInterface() {
// 1. Get the UI service window (UI object) of the spreadsheet
var ui = SpreadsheetApp.getUi();
// 2. Check the retrieved object in the execution log
Logger.log("Retrieved UI object: " + ui);
// 3. Use the UI object to create a custom menu in the top menu bar
ui.createMenu("★ Soup Curry Club")
.addItem("Check Recommended Restaurant", "showNotification")
.addToUi();
}
// Function called when the menu item is clicked
function showNotification() {
var ui = SpreadsheetApp.getUi();
ui.alert("Today's pick is the Japanese dashi broth soup curry at GARAKU!");
}
2. Step-by-Step Code Breakdown
var ui = SpreadsheetApp.getUi();: The star of today’s lesson! We callgetUi()fromSpreadsheetAppand store our screen command center inside the variableui.ui.createMenu("★ Soup Curry Club"): Calling.createMenu()on ouruiobject builds a brand new menu along the top menu bar alongside “File”, “Edit”, and “View”..addItem("Check Recommended Restaurant", "showNotification"): Specifies the label text for the menu item and the function name (showNotification) that executes when clicked..addToUi(): The final command that attaches the assembled menu directly to the spreadsheet’s live UI.
3. Save and Run the Script!
- Click the Save icon (floppy disk) at the top of the editor.
- Verify that the selected function is set to
setupUserInterface, then click the Run button. - If this is your first run, a Google authorization dialog will appear. Follow the on-screen prompts to grant permission (just like we covered in Series #2).
Once execution finishes, switch back to your Google Sheets tab!
You should now see a brand-new menu named “★ Soup Curry Club” added to the far right side of the menu bar.
Click that menu and select “Check Recommended Restaurant”. A neat alert dialog will pop up in the middle of your screen reading: “Today’s pick is the Japanese dashi broth soup curry at GARAKU!”
4. Common Traps for Beginners and How to Fix Them
While SpreadsheetApp.getUi() is super convenient, there are a few classic pitfalls that almost every GAS learner trips over. Let’s look at the solutions so you never get stuck!
Trap 1: The “Cannot call SpreadsheetApp.getUi() from this context” Error
This is easily the #1 most frequent error encountered by GAS beginners!
The Cause:
getUi() inherently assumes that a human user currently has the spreadsheet open and is interacting with it.
Therefore, if your script triggers in a headless background environment where no active UI session exists, getUi() fails and throws this error:
- Time-driven triggers (e.g., automated runs every morning at 8:00 AM)
- Form-submit triggers running silently in the background
- Web App deployments (executed via
doGetordoPost)
The Solution:
Never include UI dialogs like getUi() or ui.alert() inside automated background scripts!
For background notification needs, the standard practice is to send an email via GmailApp (planned for Series #31) or push an alert to Slack or LINE (planned for Series #75–#76) instead.
Trap 2: Running Inside a Standalone Script
The Cause:
As touched upon in Series #2 and #3, GAS projects come in two varieties:
- Container-bound scripts (created via “Extensions > Apps Script” within a sheet)
- Standalone scripts (independent project files created directly from Google Drive)
SpreadsheetApp.getUi() only works inside container-bound scripts where a parent spreadsheet UI exists. In a standalone script, there is no attached interface, resulting in errors or null.
The Solution:
When building UI controls or custom menus, always make it a habit to open your script editor via Extensions > Apps Script from the target Google Sheet!
Trap 3: Calling getUi() Alone Produces No Visual Change
Beginners often ask: “I ran getUi(), but nothing appeared on screen… why?”
The Cause:
getUi() merely sets up the information desk (prepares the object).
Setting up the desk doesn’t broadcast any message (alert) or ask any questions (prompt) by itself. It is completely normal for nothing visible to change on screen yet.
The Solution:
After preparing the object with var ui = SpreadsheetApp.getUi();, be sure to chain actual action methods like ui.alert(...) or ui.createMenu(...).
Trap 4: Casing Typos (getUI vs. getui)
JavaScript and GAS are strictly case-sensitive.
- ✕
SpreadsheetApp.getUI()(uppercase ‘I’) - ✕
SpreadsheetApp.getui()(lowercase ‘ui’) - ◯
SpreadsheetApp.getUi()(capital ‘U’, lowercase ‘i’)
The Solution:
Avoid typing the whole expression by hand. Instead, type SpreadsheetApp.get and use the editor’s autocomplete suggestion by hitting Enter. It’s the safest way to avoid typos!
Trap 5: Confusing the Spreadsheet Object with the Ui Object
// ✕ Incorrect usage
var ss = SpreadsheetApp.getActiveSpreadsheet();
ss.alert("Hello"); // This causes an error!
// ◯ Correct usage
var ui = SpreadsheetApp.getUi();
ui.alert("Hello");
Methods like alert() and prompt() do not belong to the Spreadsheet object (ss); they belong strictly to the Ui object (ui).
Keep the division of labor clear: “Manipulate cells and tabs with Spreadsheet / Sheet; manage menus and dialog boxes with Ui.” This mental model will keep your code bug-free!
5. Pro Tip: Auto-Load Custom Menus on Open with onOpen(e)
Remember the simple trigger onOpen(e) we covered back in Series #21?
It is a special built-in function that fires automatically the exact moment a file is opened.
By combining SpreadsheetApp.getUi() inside onOpen(e), you can build a seamless tool where custom menus are automatically mounted right when the user opens the spreadsheet!
Practical Code
// Function automatically triggered when opening the spreadsheet
function onOpen(e) {
var ui = SpreadsheetApp.getUi();
// Build custom menu
ui.createMenu("Sushi & Curry Navigator")
.addItem("Show Today's Recommendation", "showRecommendation")
.addSeparator() // Adds a horizontal divider line
.addItem("Clear Input Data on Sheet", "clearSheetData")
.addToUi();
}
function showRecommendation() {
var ui = SpreadsheetApp.getUi();
ui.alert("Sapporo Office Lunch Recommendation: Chicken Curry at Spice Kennos!");
}
function clearSheetData() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// Clear cell values using clearContent() from Series #8!
sheet.getRange("A2:C10").clearContent();
var ui = SpreadsheetApp.getUi();
ui.alert("Specified range data has been cleared!");
}
Once you save this script, the “Sushi & Curry Navigator” menu will appear automatically whenever anyone opens this sheet.
When you build internal office tools or shared team sheets with this setup, teammates can trigger automations with a single click—even non-engineers will love it!
6. Summary and What’s Coming Next
In this lesson, we explored SpreadsheetApp.getUi(), the crucial first step for building UI extensions and handling on-screen user interactions in GAS.
Key Takeaways
SpreadsheetApp.getUi()retrieves the UI command center (Ui object) for on-screen operations.- It serves as the launching point for alerts (
alert), prompts (prompt), and custom menus. - It throws errors when executed in headless background environments like time-based triggers.
- Always use it inside container-bound scripts attached to a spreadsheet.
- Combine it with
onOpen(e)to automatically load custom menus the moment the sheet opens.
Coming Up Next!
Now that we know how to summon the UI command center, our next article will dive straight into popping up interactive message dialogs on screen!
