Sidebars keep lightweight tools beside the grid without deploying a full web app.
onOpen creates an Ops Tools menu; showOpsSidebar loads Sidebar.html at 320px width.
fillActiveWithStatus is invoked from the sidebar to write a status string into the active cell and return coordinates.
Simple installable or simple onOpen both work for menus bound to the spreadsheet container.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Item | Name | Purpose |
|---|---|---|
| Menu | Ops Tools | Shown in the Sheets UI |
| Item | Open assistant | Calls showOpsSidebar |
| Item | Insert today's date | Calls insertToday |
| Html | Sidebar.html | Sidebar UI |
| Server | fillActiveWithStatus | Writes active cell |
What this script does
The menu entry points open UI or run quick actions; the sidebar bridges HTML controls to sheet cells.
Prerequisites
Container-bound script; Sidebar.html in the project.
- Reload the spreadsheet after adding onOpen
- Authorize on first menu click
- Active cell selected before fill
Walkthrough
Reload the Sheet, open Ops Tools → Open assistant, click a status button wired to fillActiveWithStatus.
Edge cases
onOpen simple triggers cannot call services that need authorization until the user runs a menu item once.
- Sidebar width is limited
- Multiple sidebars replace each other
How to test
Select A2, set status to Done, confirm A2 value and returned row/col.
Hardening for production
Validate allowed status enums server-side; log actions to an Audit tab.
Variations
Use showModalDialog for wider forms; add submenus with addSubMenu.
Full code: onOpen + showOpsSidebar()
Add Sidebar.html, reload the spreadsheet, then use Ops Tools → Open assistant.
/**
* Custom menu opens an HtmlService sidebar for quick tools.
*/
function onOpen() {
SpreadsheetApp.getUi()
.createMenu("Ops Tools")
.addItem("Open assistant", "showOpsSidebar")
.addItem("Insert today's date", "insertToday")
.addToUi();
}
function showOpsSidebar() {
const html = HtmlService.createHtmlOutputFromFile("Sidebar")
.setTitle("Ops assistant")
.setWidth(320);
SpreadsheetApp.getUi().showSidebar(html);
}
function insertToday() {
const cell = SpreadsheetApp.getActiveSpreadsheet().getActiveCell();
cell.setValue(new Date());
}
/** Called from Sidebar.html */
function fillActiveWithStatus(status) {
const cell = SpreadsheetApp.getActiveSpreadsheet().getActiveCell();
cell.setValue(status);
return { row: cell.getRow(), col: cell.getColumn(), status: status };
}- Line 6: Adds Ops Tools to the Sheets menu bar.
- Line 16: Opens HtmlService output in the sidebar.
- Line 15: Sidebar width in pixels.
- Line 8: Writes a Date into the active cell.
- Line 25: Server entry point for sidebar buttons.
- Line 20: Targets whatever cell the user selected.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before you run: sidebar menu
- 1Script is bound to the spreadsheet
- 2Sidebar.html exists
- 3Reload Sheet to register onOpen
- 4Run a menu item once to authorize
- 5Train users to select the target cell first
- 6Keep sidebar actions idempotent when possible
Frequently asked questions
Reload the spreadsheet. onOpen runs on open — it does not appear until refresh after saving the script.
Sidebars stay open beside the grid; modal dialogs block until closed and can be wider.
Not until authorized. Move network calls to menu items users click after auth.
Call a server function on sidebar load that returns getActiveRange().getA1Notation().
Yes if you bundle static assets into HtmlService, but keep payloads small.
Custom menus/sidebars are desktop-oriented; mobile support is limited.
Sheets custom menus do not support icons; use emoji in item labels if needed.