Using HTML Service in Apps Script for Custom Dialogs in Sheets (Google Sheet)

Want to make your Google Sheets more interactive? Google Apps Script’s HTML Service lets you build custom pop-up dialogs and sidebars using regular HTML. These custom dialogs can collect user input, display messages, or provide handy tools right inside your spreadsheet. It’s like turning your sheet into a mini web app—perfect for beginners who want to make Sheets smarter without any fancy software.

In this guide, we’ll walk through how to create a custom dialog using HTML Service in Apps Script. You’ll learn step-by-step how to set it up and even use a real-life example to see it in action.

What Is HTML Service in Apps Script?

The HTML Service in Apps Script allows you to build custom user interfaces—like pop-ups and sidebars—inside Google Sheets using HTML, CSS, and JavaScript. In simple terms, it helps you build input forms or dialogs that interact with your spreadsheet data.

Real-Life Example: Leave Request Form in Google Sheets

Let’s say your team tracks leave requests in a Google Sheet. Instead of entering data manually into the cells, you want a pop-up form where employees can enter their name, leave date, and reason. When they click “Submit”, the data gets added to the sheet automatically.

Sample Sheet Layout

Employee Name	Leave Date	Reason
John Doe	2025-04-25	Medical
Jane Smith	2025-04-28	Family

Step-by-Step Instructions: Creating a Custom Dialog

1. Open Apps Script

Open your Google Sheet
Click Extensions > Apps Script

2. Add the Script Code


function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("Leave Tools")
    .addItem("Request Leave", "openLeaveDialog")
    .addToUi();
}

function openLeaveDialog() {
  const html = HtmlService.createHtmlOutputFromFile("LeaveForm")
    .setWidth(400)
    .setHeight(300);
  SpreadsheetApp.getUi().showModalDialog(html, "Leave Request Form");
}

function submitLeave(name, date, reason) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.appendRow([name, date, reason]);
}

3. Create the HTML File

Click the + icon in the script editor and choose HTML
Name it LeaveForm
Paste the following HTML code:


<!DOCTYPE html>
<html>
  <body>
    <h3>Leave Request Form</h3>
    <label>Employee Name:</label><br>
    <input type="text" id="name"><br><br>

    <label>Leave Date:</label><br>
    <input type="date" id="date"><br><br>

    <label>Reason:</label><br>
    <input type="text" id="reason"><br><br>

    <button onclick="submit()">Submit</button>

    <script>
      function submit() {
        const name = document.getElementById("name").value;
        const date = document.getElementById("date").value;
        const reason = document.getElementById("reason").value;
        google.script.run.submitLeave(name, date, reason);
        google.script.host.close();
      }
    </script>
  </body>
</html>

4. Save and Run

Save all files
Run onOpen() once to activate the menu (authorize if needed)
In the sheet, click Leave Tools > Request Leave
Fill out the form and click “Submit” — it’s that easy!

Key Benefits of Using Custom Dialogs

Improves Data Entry: Users don’t need to find the right cell
Cleaner Interface: Forms feel more user-friendly and professional
Custom Validation: Add checks or confirmations before writing data

Pro Tips

You can return messages back to the dialog using withSuccessHandler()
Use showSidebar() for longer forms or extra tools
Group related scripts under one menu for better navigation

Quick Reference Cheat Sheet

Code	What It Does
`HtmlService.createHtmlOutputFromFile()`	Loads your custom HTML file
`showModalDialog()`	Opens a pop-up dialog in Sheets
`google.script.run`	Runs a function in your Apps Script from the HTML form
`SpreadsheetApp.getUi()`	Gives access to UI features like menus and dialogs

With HTML Service and Google Apps Script, you can create powerful, user-friendly tools directly inside your spreadsheets. Whether you’re collecting leave requests, sending feedback, or gathering any kind of input, custom dialogs make it easy—and fun—for users to interact with your data. Try building one today and see how it transforms your workflow!