Building Custom Menus and Sidebars in Google Sheets with Apps Script

Ever wanted to add your own buttons or side tools inside Google Sheets? With Google Apps Script, you can create custom menus and sidebars that appear right inside your spreadsheet! This helps you run your favourite scripts, input data, or display helpful tools—all with just a click. It’s perfect for beginners who want to make spreadsheets feel more like an app.

In this guide, we’ll show you how to build a custom menu and sidebar in Google Sheets using Apps Script. You don’t need any special software—just your sheet, a bit of code, and a little creativity!

What Are Custom Menus and Sidebars in Google Sheets?

A custom menu is a dropdown that appears in the top bar of your Google Sheet. When clicked, it can run any script function you define.

A custom sidebar is a small window that opens on the right side of your sheet, perfect for forms, dashboards, or extra tools.

Real-Life Example: Task Tracker with Sidebar Entry Form

Imagine you’re managing a simple task tracker. You want an easy way to:

Click a menu to open a sidebar
Enter task details like name, due date, and priority
Automatically add the task to your sheet

Sheet Layout

Task	Due Date	Priority
Finish report	2025-04-30	High
Email client	2025-04-21	Medium

Step-by-Step: Create a Custom Menu and Sidebar

1. Open Apps Script

Open your Google Sheet
Click Extensions > Apps Script

2. Add This Script


function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu("Task Tools")
    .addItem("Add New Task", "showSidebar")
    .addToUi();
}

function showSidebar() {
  const html = HtmlService.createHtmlOutputFromFile("Sidebar")
    .setTitle("Add Task")
    .setWidth(300);
  SpreadsheetApp.getUi().showSidebar(html);
}

function addTask(task, date, priority) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.appendRow([task, date, priority]);
}

3. Add the Sidebar File

Click the + icon in the Apps Script editor and choose HTML
Name it Sidebar
Paste the following HTML:


<!DOCTYPE html>
<html>
  <body>
    <h3>Add New Task</h3>
    <label>Task Name:</label><br>
    <input type="text" id="task"><br><br>

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

    <label>Priority:</label><br>
    <select id="priority">
      <option>High</option>
      <option>Medium</option>
      <option>Low</option>
    </select><br><br>

    <button onclick="submitTask()">Add Task</button>

    <script>
      function submitTask() {
        const task = document.getElementById("task").value;
        const due = document.getElementById("due").value;
        const priority = document.getElementById("priority").value;
        google.script.run.addTask(task, due, priority);
        google.script.host.close();
      }
    </script>
  </body>
</html>

4. Save and Run

Click Save (disk icon)
Run the onOpen function to initialize the menu (authorize if prompted)
Go back to your sheet and you’ll see a new menu called Task Tools!
Click it → Add New Task → Fill in the form → Done!

Key Benefits of Custom Menus and Sidebars

User-Friendly: No need to open the script editor to run actions
Professional Feel: Create forms and tools right inside the sheet
Fully Customizable: Add dropdowns, buttons, or dashboards

Pro Tips

Use HTML forms in the sidebar to collect any kind of input
Use onOpen() to always show the custom menu when the sheet opens
You can create multiple menus and link different scripts to each item

Quick Reference Cheat Sheet

Function	What It Does
`onOpen()`	Runs when the sheet opens and adds the custom menu
`showSidebar()`	Displays the sidebar with HTML content
`HtmlService.createHtmlOutputFromFile()`	Loads and displays your custom HTML in the sidebar
`google.script.run`	Lets your HTML sidebar call Apps Script functions

Building custom menus and sidebars with Apps Script is one of the easiest ways to level up your Google Sheets. It transforms your basic spreadsheet into an interactive tool that’s easier to use and looks far more polished.

Try it out with a task manager, report generator, or even a mini dashboard. Once you start, you’ll be amazed at how far you can take your spreadsheets!