Chapter 21

User interfaces

Menus, sidebars, and modals

Build a custom Sheets menu with onOpen() and createMenu(), then open an alert, a sidebar, and a modal dialog with HtmlService in Google Apps Script.

By the end

Build a read-only bound Sheets menu that reads one selected row on the server, loads it into a sidebar or modal, and shows cell text without parsing it as HTML.

Add a custom menu to a spreadsheet and open three kinds of interface from it: an alert, a sidebar, and a modal dialog. Use a bound Sheets menu for actions an editor starts by hand in that spreadsheet. This project reads the selected request row in an alert or a sidebar, then lets you prepare a plain-text handoff note in a modal dialog. It does not write cells, send the note, create files, or install a schedule.

The alert shows the selected request. The sidebar stays beside the sheet while you move between requests. The modal shows one request and lets you add your own context before you copy the note. None of the three needs a deployment or a public URL.

You need runtime and permissions and the request tracker first.

Create a separate interface practice sheet

This practice spreadsheet has four columns and does not touch the tracker spreadsheet. Its Status column holds the display values Open and In progress. It does not use the tracker's five-column layout or its status transitions.

Create a blank spreadsheet named Interface practice and rename its tab Requests.
Copy the text of requests.csv into cell A1. Select column A, choose Data > Split text to columns, and set the separator to Comma. Check that you have three rows by four columns, with the headers Request, Owner, Status, and Notes in A1:D1.
Open Extensions > Apps Script from this spreadsheet. Replace Code.gs with the server file below. Add two HTML files named Sidebar and Handoff, which the Apps Script editor shows as Sidebar.html and Handoff.html.
In Project Settings, enable Show "appsscript.json" manifest file in editor, then replace appsscript.json with the manifest below.
Save the project and reload the spreadsheet. Select A2, then choose Request Tracker > Show selected request. Approve the consent request for your account. If the consent flow interrupts the action, select A2 and choose the menu item again.

requests.csv

Request,Owner,Status,Notes
Confirm room booking,Alex,Open,Check accessibility requirements
Prepare agenda,Sam,In progress,Collect topics before Friday

Download requests.csv

The CSV is sample data for the sheet. It does not go in the editor.

A script created through the spreadsheet's Extensions menu is bound to that spreadsheet, which is called its container. When an editor runs the script from the spreadsheet, the script can read the current selection and add menus and panels to that spreadsheet without a file ID. A standalone project that opens a spreadsheet by ID gets none of that. Google's bound-script guide explains what a bound script can do and how to create one.

Review the account and scopes

appsscript.json

{
  "timeZone": "Etc/UTC",
  "runtimeVersion": "V8",
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets.currentonly",
    "https://www.googleapis.com/auth/script.container.ui"
  ]
}

Download appsscript.json

The manifest declares two scopes:

spreadsheets.currentonly limits Sheets access to the bound spreadsheet. It is not a read-only scope. This script only reads because it calls no write methods.
script.container.ui allows the custom sidebar and modal. Both showSidebar and showModalDialog require it.

Menu functions and their HTML panels run as the editor who clicked the menu, as the authorization guide describes.

Code.gs

const REQUESTS_SHEET = 'Requests';
const REQUEST_HEADERS = ['Request', 'Owner', 'Status', 'Notes'];

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Request Tracker')
    .addItem('Show selected request', 'showSelectedRequest')
    .addItem('Open request sidebar', 'openRequestSidebar')
    .addItem('Prepare handoff note', 'openRequestDialog')
    .addToUi();
}

function showSelectedRequest() {
  const ui = SpreadsheetApp.getUi();
  let request;
  try {
    request = getSelectedRequest();
  } catch (error) {
    ui.alert('Cannot show request', error instanceof Error ? error.message : String(error), ui.ButtonSet.OK);
    return;
  }
  ui.alert(
    'Selected request',
    request.sheetName + ' row ' + request.rowNumber + '\n' + request.request +
      '\nOwner: ' + (request.owner || '(not set)') + '\nStatus: ' + (request.status || '(not set)'),
    ui.ButtonSet.OK,
  );
}

function openRequestSidebar() {
  const html = HtmlService.createHtmlOutputFromFile('Sidebar').setTitle('Selected request');
  SpreadsheetApp.getUi().showSidebar(html);
}

function openRequestDialog() {
  const html = HtmlService.createHtmlOutputFromFile('Handoff')
    .setWidth(520)
    .setHeight(640);
  SpreadsheetApp.getUi().showModalDialog(html, 'Prepare handoff note');
}

function getSelectedRequest() {
  const rangeList = SpreadsheetApp.getActiveRangeList();
  if (!rangeList) {
    throw new Error('Open the sample spreadsheet and select a request row.');
  }
  const ranges = rangeList.getRanges();
  if (ranges.length !== 1 || ranges[0].getNumRows() !== 1) {
    throw new Error('Select one continuous range within one request row, not multiple rows or separate ranges.');
  }

  // Capture the selected range once; do not reread the selection after the data.
  const selection = ranges[0];
  const sheet = selection.getSheet();
  if (sheet.getName() !== REQUESTS_SHEET) {
    throw new Error('Select a row on the Requests sheet before opening Request Tracker.');
  }
  const row = selection.getRow();
  if (row < 2) {
    throw new Error('Select a request row, not the header row.');
  }

  const headers = sheet.getRange(1, 1, 1, REQUEST_HEADERS.length).getDisplayValues()[0];
  if (REQUEST_HEADERS.some(function (header, index) { return headers[index] !== header; })) {
    throw new Error('The Requests sheet must start with Request, Owner, Status, and Notes.');
  }

  const values = sheet.getRange(row, 1, 1, REQUEST_HEADERS.length).getDisplayValues()[0];
  if (!values[0].trim()) {
    throw new Error('The selected row has no request title.');
  }

  return {
    request: values[0],
    owner: values[1],
    status: values[2],
    notes: values[3],
    sheetName: sheet.getName(),
    rowNumber: row,
  };
}

Download Code.gs

onOpen is a simple trigger. When an editor opens the spreadsheet, Apps Script recognizes the function name and runs it, so you do not install a trigger. It does not run for a viewer or commenter. A simple trigger runs without asking for authorization and cannot call services that need consent, so keep onOpen to building the menu. See simple triggers.

The second argument to addItem is a function name as a string, such as 'showSelectedRequest' (see Script function not found). onOpen registers the names and calls addToUi() to show the menu. Apps Script runs the named function later, when you click the menu item.

A spreadsheet can have only one menu with a given name. Adding a menu with the same name replaces the earlier one instead of merging their items, and a menu cannot be removed while the file is open. The menu guide documents both menu rules.

All files in a project share one global scope, so a second onOpen replaces the first and builds only one of the two menus. The capstone project already defines onOpen. Keep this practice project in its own spreadsheet, and do not paste it into the capstone project.

Choose what the interface should block

The word modal says what the user can click in the spreadsheet editor while the interface is open. It says nothing about whether the server function is still running.

Interface	Can you keep interacting with this spreadsheet editor?	Does the opening server execution wait for dismissal?
Built-in `Ui.alert` or `Ui.prompt`	No, while the dialog is open	Yes. The execution is suspended
HTML modal opened by `showModalDialog`	No, until the modal closes	No. The opener continues and finishes
HTML sidebar opened by `showSidebar`	Yes	No. The opener continues and finishes

showSelectedRequest reads the selected request, then calls ui.alert. The server function is suspended until you dismiss the alert. If the selection is invalid, the error appears in a Cannot show request alert instead.

A built-in ui.prompt adds one text input and returns a PromptResponse. Check getSelectedButton() before you read getResponseText(), because the response can still carry typed text after Cancel or the close button. Like an alert, a prompt suspends the server execution. JDBC connections and script locks do not survive that suspension, so release locks before you prompt. See the Ui reference.

openRequestDialog works differently. It builds an HtmlOutput and calls showModalDialog. The server function finishes as soon as the modal opens, and the HTML page handles everything after that. While the modal is open, it blocks clicks in this user's spreadsheet tab. It does not lock the spreadsheet for collaborators or other executions.

The modal opens at 520 by 640 pixels, set with setWidth and setHeight. A sidebar is always 300 pixels wide, so setWidth on a sidebar has no effect. See HtmlOutput sizing and Ui sidebar behavior.

Read the selection on the server

All three interfaces call the same server function, getSelectedRequest. It takes no arguments from the browser.

The function first checks the selection. It calls getActiveRangeList() and RangeList.getRanges() and requires one continuous range on one row. A2 or A2:D2 passes. If you select rows 2 and 3 together, or several separate ranges, the function throws an error instead of picking one row.

The function then checks that the range is on the Requests tab below the header row. It reads two small ranges: A1:D1 for the headers, and columns A:D of the selected row. The headers must be Request, Owner, Status, and Notes, and the selected row must have a nonblank request title. The panels show an empty owner or status as (not set) and empty notes as (none). Columns outside A:D are ignored.

Google checks spreadsheet access and OAuth consent on the server. The browser can call any public top-level function through google.script.run, even one the HTML has no button for. It cannot call helper functions whose names end with _, as the communication guide documents.

Return values the browser can receive

The server reads cell text with getDisplayValues() and returns a plain object with the string fields request, owner, status, notes, and sheetName, plus a numeric rowNumber.

getDisplayValues returns formatted cell text, so the panel receives strings rather than Date objects. getValues() returns Date objects for date cells, and google.script.run rejects a Date as a parameter or a return value.

Pass primitives (string, number, boolean, null) and arrays or plain objects built from them. Functions, circular references, and most DOM elements fail serialization even when nested inside an object. See parameters and return values.

Sidebar.html

<!doctype html>
<html lang="en">
  <head>
    <base target="_top">
    <style>
      body { margin: 0; padding: 1rem; color: #202124; background: #fff; font: 0.95rem/1.5 system-ui, sans-serif; }
      h1 { font-size: 1.25rem; }
      button { padding: 0.5rem; font: inherit; }
      :focus-visible { outline: 3px solid #174ea6; outline-offset: 2px; }
      #result { white-space: pre-wrap; overflow-wrap: anywhere; }
      #message, #source { overflow-wrap: anywhere; }
    </style>
  </head>
  <body>
    <h1>Selected request</h1>
    <p>Select a request row in Sheets, then use Reload. The displayed result is a snapshot, not a live selection indicator.</p>
    <button type="button" id="reload" disabled>Reload</button>
    <button type="button" id="close">Close</button>
    <p id="message" role="status" aria-live="polite">Loading the selected row.</p>
    <p id="source"></p>
    <p id="result"></p>
    <p>If a lost connection leaves this panel busy, close it and reopen it from the menu. Repeating this read changes no cells.</p>
    <script>
      const result = requireElement('result');
      const source = requireElement('source');
      const message = requireElement('message');
      const reload = requireElement('reload');
      let pending = false;

      reload.addEventListener('click', loadRequest);
      requireElement('close').addEventListener('click', function () {
        google.script.host.close();
      });

      /** @param {string} id */
      function requireElement(id) {
        const element = document.getElementById(id);
        if (!element) throw new Error('Missing sidebar element: ' + id);
        return element;
      }

      /** @param {boolean} value */
      function setPending(value) {
        pending = value;
        reload.toggleAttribute('disabled', value);
      }

      function loadRequest() {
        if (pending) return;
        setPending(true);
        result.textContent = '';
        source.textContent = '';
        message.textContent = 'Loading the selected row.';
        try {
          google.script.run
            .withSuccessHandler(renderRequest)
            .withFailureHandler(showError)
            .getSelectedRequest();
        } catch (error) {
          showError(error instanceof Error ? error : new Error(String(error)));
        }
      }

      /** @param {{request: string, owner: string, status: string, notes: string, sheetName: string, rowNumber: number}} request */
      function renderRequest(request) {
        setPending(false);
        source.textContent = 'Snapshot from ' + request.sheetName + ' row ' + request.rowNumber + '.';
        result.textContent = request.request + '\nOwner: ' + (request.owner || '(not set)') +
          '\nStatus: ' + (request.status || '(not set)') + '\nNotes: ' + (request.notes || '(none)');
        message.textContent = 'Request loaded. Check the source row before using this result.';
      }

      /** @param {Error} error */
      function showError(error) {
        setPending(false);
        result.textContent = '';
        source.textContent = '';
        message.textContent = 'Cannot load request: ' + error.message;
      }

      loadRequest();
    </script>
  </body>
</html>

Download Sidebar.html

Select A2 and choose Request Tracker > Open request sidebar. The sidebar loads the selection once and shows a source label for Requests row 2:

Source

Confirm room booking
Owner: Alex
Status: Open
Notes: Check accessibility requirements

Select A3 in the sheet without clicking Reload. The sidebar still shows row 2. Click Reload to fetch row 3: Prepare agenda, Sam, In progress, and Collect topics before Friday.

The script inside Sidebar.html runs in the browser, where it can use document and the page's elements. Code.gs runs on Apps Script's servers, where it can use SpreadsheetApp. HtmlService.createHtmlOutputFromFile('Sidebar') loads the HTML file without the .html extension (see No HTML file named X was found).

google.script.run calls a server function from the browser and returns at once, before the server answers. withSuccessHandler(renderRequest) and withFailureHandler(showError) register the callbacks before getSelectedRequest() sends the request. The response is handled inside those callbacks. The communication guide explains this pattern.

Make waiting, errors, and stale data visible

loadRequest sets pending, disables Reload, and clears the previous result and source label before it sends the request. The success handler re-enables the button and renders the response. The failure handler re-enables the button and shows the error.

The pending flag stops a second request from the same panel while the first is in flight, because the responses to two concurrent requests could arrive out of order. The try and catch around the call catch errors thrown in the browser, and withFailureHandler catches failures on the server.

A reload has four steps:

You click Reload in the sidebar.
Apps Script starts getSelectedRequest and reads the active selection.
The server reads the captured sheet and row.
The browser receives the response and renders it.

If you move to another cell before step 2 captures the selection, the server reads the newer cell. The response includes sheetName and rowNumber, so the sidebar can label the row the server read.

The sidebar does not poll or listen for selection or cell changes. Sorting or inserting rows shifts row numbers, so treat rowNumber as the row the snapshot came from. Do not use it to find the same request for a later write.

Handoff.html

<!doctype html>
<html lang="en">
  <head>
    <base target="_top">
    <style>
      body { margin: 0; padding: 1rem; color: #202124; background: #fff; font: 0.95rem/1.5 system-ui, sans-serif; }
      h1 { font-size: 1.25rem; }
      fieldset { margin: 1rem 0; padding: 0.75rem; border: 1px solid #aaa; }
      label { display: block; margin-top: 0.5rem; font-weight: 600; }
      textarea { box-sizing: border-box; width: 100%; font: inherit; }
      button { margin-top: 0.5rem; padding: 0.5rem; font: inherit; }
      :focus-visible { outline: 3px solid #174ea6; outline-offset: 2px; }
      #request { white-space: pre-wrap; }
      p { overflow-wrap: anywhere; }
    </style>
  </head>
  <body>
    <h1>Prepare a handoff note</h1>
    <p>Review the selected request and prepare plain text for manual copying. Nothing is sent or saved to Sheets.</p>
    <p id="message" role="status" aria-live="polite">Loading the selected row.</p>
    <p id="source"></p>
    <p id="request"></p>
    <fieldset id="editor" disabled>
      <legend>Local handoff note</legend>
      <label for="context">Additional context (optional, up to 1,000 characters)</label>
      <textarea id="context" rows="3" maxlength="1000"></textarea>
      <button id="prepare" type="button">Prepare note</button>
      <label for="preview">Plain-text note</label>
      <textarea id="preview" rows="8" readonly aria-describedby="copy-help"></textarea>
      <p id="copy-help">Select note highlights this text. Use Ctrl+C on Windows/Linux or Command+C on macOS to copy it yourself. The button does not access the clipboard.</p>
      <button id="select-note" type="button" disabled>Select note</button>
    </fieldset>
    <p>Reload discards your local context and prepared note. Close also discards them. To choose another row in this editor, close the dialog first.</p>
    <button id="reload" type="button" disabled>Reload and discard note</button>
    <button id="close" type="button">Close</button>
    <script>
      /** @typedef {{request: string, owner: string, status: string, notes: string, sheetName: string, rowNumber: number}} SelectedRequest */
      const message = requireElement('message');
      const source = requireElement('source');
      const requestText = requireElement('request');
      const editor = requireElement('editor');
      const reload = requireElement('reload');
      const selectNote = requireElement('select-note');
      const context = requireTextArea('context');
      const preview = requireTextArea('preview');
      /** @type {SelectedRequest | null} */
      let currentRequest = null;
      let pending = false;

      requireElement('prepare').addEventListener('click', prepareNote);
      reload.addEventListener('click', loadRequest);
      requireElement('close').addEventListener('click', function () {
        google.script.host.close();
      });
      context.addEventListener('input', function () {
        preview.value = '';
        selectNote.toggleAttribute('disabled', true);
        message.textContent = 'Context changed. Prepare the note again before copying.';
      });
      selectNote.addEventListener('click', function () {
        if (!preview.value) return;
        preview.focus();
        preview.select();
        message.textContent = 'Text selected, not copied or sent. Use your keyboard copy command if needed.';
      });

      /** @param {string} id */
      function requireElement(id) {
        const element = document.getElementById(id);
        if (!element) throw new Error('Missing dialog element: ' + id);
        return element;
      }

      /** @param {string} id */
      function requireTextArea(id) {
        const element = requireElement(id);
        if (!(element instanceof HTMLTextAreaElement)) throw new Error('Expected text area: ' + id);
        return element;
      }

      /** @param {boolean} value */
      function setPending(value) {
        pending = value;
        reload.toggleAttribute('disabled', value);
        editor.toggleAttribute('disabled', value || currentRequest === null);
      }

      function loadRequest() {
        if (pending) return;
        currentRequest = null;
        context.value = '';
        preview.value = '';
        selectNote.toggleAttribute('disabled', true);
        source.textContent = '';
        requestText.textContent = '';
        setPending(true);
        message.textContent = 'Loading the selected row.';
        try {
          google.script.run
            .withSuccessHandler(renderRequest)
            .withFailureHandler(showError)
            .getSelectedRequest();
        } catch (error) {
          showError(error instanceof Error ? error : new Error(String(error)));
        }
      }

      /** @param {SelectedRequest} request */
      function renderRequest(request) {
        currentRequest = request;
        setPending(false);
        source.textContent = 'Snapshot from ' + request.sheetName + ' row ' + request.rowNumber + '. Not a live view.';
        requestText.textContent = request.request + '\nOwner: ' + (request.owner || '(not set)') +
          '\nStatus: ' + (request.status || '(not set)') + '\nNotes: ' + (request.notes || '(none)');
        message.textContent = 'Check the request, add any local context, then prepare the note.';
        context.focus();
      }

      function prepareNote() {
        if (pending || !currentRequest || !context.reportValidity()) return;
        const request = currentRequest;
        const extra = context.value.trim();
        preview.value = 'Request: ' + request.request +
          '\nSource: ' + request.sheetName + ' row ' + request.rowNumber + ' (snapshot)' +
          '\nOwner: ' + (request.owner || '(not set)') +
          '\nStatus: ' + (request.status || '(not set)') +
          '\nNotes: ' + (request.notes || '(none)') +
          (extra ? '\nAdditional context: ' + extra : '');
        selectNote.toggleAttribute('disabled', false);
        message.textContent = 'Note prepared in this dialog only. Review it before copying.';
      }

      /** @param {Error} error */
      function showError(error) {
        currentRequest = null;
        setPending(false);
        source.textContent = '';
        requestText.textContent = '';
        preview.value = '';
        selectNote.toggleAttribute('disabled', true);
        message.textContent = 'Cannot load request: ' + error.message + ' Close to fix the selection, or reload to retry this read.';
      }

      loadRequest();
    </script>
  </body>
</html>

Download Handoff.html

The handoff modal loads the selected request once and lets you write a plain-text note in the browser. It does not edit the sheet.

Select A2 and choose Request Tracker > Prepare handoff note.
Wait for the request to load. While it loads, the note controls stay disabled.
In Additional context, enter Ask about step-free access. This text stays in the browser.
Choose Prepare note. The read-only preview displays:

Source

Request: Confirm room booking
Source: Requests row 2 (snapshot)
Owner: Alex
Status: Open
Notes: Check accessibility requirements
Additional context: Ask about step-free access.

Choose Select note to focus and highlight the text area, then copy it with Ctrl+C (Windows and Linux) or Command+C (macOS).
Choose Close. The Requests sheet remains unchanged.

When the modal opens, it calls getSelectedRequest and stores the returned snapshot in currentRequest. prepareNote joins currentRequest and the text area value in browser memory. Editing Additional context clears the preview and disables Select note until you click Prepare note again.

Reload and discard note clears the context and the preview, then fetches the current selection. If loading fails, Prepare note stays disabled, and Reload and discard note and Close still work.

google.script.host.close() closes an HTML dialog or sidebar from browser code, where window.close() does not work. The dialogs guide describes this host API. Closing the modal does not cancel a google.script.run request that is still running.

Keep text out of the HTML parser

Both panels write dynamic strings with textContent and set the note preview through the text area's value. Neither parses cell text such as <b>Urgent</b> as HTML, so the browser shows the angle brackets. See textContent, text area values, and text area selection. To keep line breaks in plain text, use the CSS white-space: pre-wrap. Do not use innerHTML or unescaped template scriptlets.

HTML Service shows each page inside an iframe. <base target="_top"> makes links open outside the frame, and external scripts and stylesheets must load over HTTPS. setSandboxMode has no effect, because every HTML Service page uses the IFRAME sandbox mode. See HTML restrictions.

Exercise: distinguish selection, snapshots, and local edits

Load A2 in the sidebar. Click A3 without reloading and confirm the panel still shows row 2. Click Reload and check that the source label and details change to row 3.
Select A1 and click Reload to see the header-row error. Select A2:A3 and reload to see the single-row error. Select A2 and reload to recover.
Change D2 to <b>Check the ramp</b> and reload the sidebar. Confirm that the angle brackets appear as text, then set D2 back to Check accessibility requirements.
Open the handoff modal for A2, add some context, and click Prepare note. Edit the context and confirm that the preview clears. Click Prepare note again, then click Reload and discard note. The context and preview reset, and the sheet's Notes cell does not change.
Close the modal, select A3, and reopen it. The snapshot shows Prepare agenda with empty context.

What you see	Start here
The menu is missing	Save the bound project, reload the spreadsheet, and confirm that you have edit access. The function must be named `onOpen` and must call `addToUi`.
The capstone's Request tracker menu disappears, or this menu is missing items	The code was pasted into the capstone project, so one `onOpen` replaced the other. Keep this project in its own spreadsheet.
A selected-row or wrong-tab error	Select one cell in a data row on `Requests`. The header row, several rows, and separate ranges are rejected.
A header error	Compare `A1:D1` with the sample headers `Request`, `Owner`, `Status`, `Notes`.
A blank-title error	Select a data row that has text in its `Request` cell.
The HTML panel opens, but loading fails	Opening the page and reading the sheet are separate executions. Check the error in the panel, the authorization, and the Executions entry for `getSelectedRequest`.
The panel stays busy	Check the browser console for page errors and Executions for server failures. Close and reopen the panel to retry the read.
The modal shows a different request than you meant	Check its source-row label. Close it, select the intended row, and reopen it.
Select note does not put text on the clipboard	The button only selects the text. Copy it with Ctrl+C or Command+C.
Saved HTML changes do not appear	Close and reopen the sidebar or modal so that Apps Script serves the updated HTML file.

Close the practice interface and choose what to build next

Close the sidebar and modal when you are done. Any context or note you did not copy is lost.

To keep the practice spreadsheet without its custom menu, delete onOpen, save the script, and reload the spreadsheet.

When people need a URL that works without opening the sheet, build a standalone web app instead. A web app has its own list of who can open it and its own setting for which account it runs as. It cannot read a bound file's selection or use its UI, as the bound-script guide notes.

When you want one interface across many files, or want to distribute it through the Workspace side panel, build a Workspace add-on. See the add-ons overview.

Project files

Complete source files for this chapter's examples.

Download file

Code.gs

const REQUESTS_SHEET = 'Requests';
const REQUEST_HEADERS = ['Request', 'Owner', 'Status', 'Notes'];

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Request Tracker')
    .addItem('Show selected request', 'showSelectedRequest')
    .addItem('Open request sidebar', 'openRequestSidebar')
    .addItem('Prepare handoff note', 'openRequestDialog')
    .addToUi();
}

function showSelectedRequest() {
  const ui = SpreadsheetApp.getUi();
  let request;
  try {
    request = getSelectedRequest();
  } catch (error) {
    ui.alert('Cannot show request', error instanceof Error ? error.message : String(error), ui.ButtonSet.OK);
    return;
  }
  ui.alert(
    'Selected request',
    request.sheetName + ' row ' + request.rowNumber + '\n' + request.request +
      '\nOwner: ' + (request.owner || '(not set)') + '\nStatus: ' + (request.status || '(not set)'),
    ui.ButtonSet.OK,
  );
}

function openRequestSidebar() {
  const html = HtmlService.createHtmlOutputFromFile('Sidebar').setTitle('Selected request');
  SpreadsheetApp.getUi().showSidebar(html);
}

function openRequestDialog() {
  const html = HtmlService.createHtmlOutputFromFile('Handoff')
    .setWidth(520)
    .setHeight(640);
  SpreadsheetApp.getUi().showModalDialog(html, 'Prepare handoff note');
}

function getSelectedRequest() {
  const rangeList = SpreadsheetApp.getActiveRangeList();
  if (!rangeList) {
    throw new Error('Open the sample spreadsheet and select a request row.');
  }
  const ranges = rangeList.getRanges();
  if (ranges.length !== 1 || ranges[0].getNumRows() !== 1) {
    throw new Error('Select one continuous range within one request row, not multiple rows or separate ranges.');
  }

  // Capture the selected range once; do not reread the selection after the data.
  const selection = ranges[0];
  const sheet = selection.getSheet();
  if (sheet.getName() !== REQUESTS_SHEET) {
    throw new Error('Select a row on the Requests sheet before opening Request Tracker.');
  }
  const row = selection.getRow();
  if (row < 2) {
    throw new Error('Select a request row, not the header row.');
  }

  const headers = sheet.getRange(1, 1, 1, REQUEST_HEADERS.length).getDisplayValues()[0];
  if (REQUEST_HEADERS.some(function (header, index) { return headers[index] !== header; })) {
    throw new Error('The Requests sheet must start with Request, Owner, Status, and Notes.');
  }

  const values = sheet.getRange(row, 1, 1, REQUEST_HEADERS.length).getDisplayValues()[0];
  if (!values[0].trim()) {
    throw new Error('The selected row has no request title.');
  }

  return {
    request: values[0],
    owner: values[1],
    status: values[2],
    notes: values[3],
    sheetName: sheet.getName(),
    rowNumber: row,
  };
}

Download file

Sidebar.html

<!doctype html>
<html lang="en">
  <head>
    <base target="_top">
    <style>
      body { margin: 0; padding: 1rem; color: #202124; background: #fff; font: 0.95rem/1.5 system-ui, sans-serif; }
      h1 { font-size: 1.25rem; }
      button { padding: 0.5rem; font: inherit; }
      :focus-visible { outline: 3px solid #174ea6; outline-offset: 2px; }
      #result { white-space: pre-wrap; overflow-wrap: anywhere; }
      #message, #source { overflow-wrap: anywhere; }
    </style>
  </head>
  <body>
    <h1>Selected request</h1>
    <p>Select a request row in Sheets, then use Reload. The displayed result is a snapshot, not a live selection indicator.</p>
    <button type="button" id="reload" disabled>Reload</button>
    <button type="button" id="close">Close</button>
    <p id="message" role="status" aria-live="polite">Loading the selected row.</p>
    <p id="source"></p>
    <p id="result"></p>
    <p>If a lost connection leaves this panel busy, close it and reopen it from the menu. Repeating this read changes no cells.</p>
    <script>
      const result = requireElement('result');
      const source = requireElement('source');
      const message = requireElement('message');
      const reload = requireElement('reload');
      let pending = false;

      reload.addEventListener('click', loadRequest);
      requireElement('close').addEventListener('click', function () {
        google.script.host.close();
      });

      /** @param {string} id */
      function requireElement(id) {
        const element = document.getElementById(id);
        if (!element) throw new Error('Missing sidebar element: ' + id);
        return element;
      }

      /** @param {boolean} value */
      function setPending(value) {
        pending = value;
        reload.toggleAttribute('disabled', value);
      }

      function loadRequest() {
        if (pending) return;
        setPending(true);
        result.textContent = '';
        source.textContent = '';
        message.textContent = 'Loading the selected row.';
        try {
          google.script.run
            .withSuccessHandler(renderRequest)
            .withFailureHandler(showError)
            .getSelectedRequest();
        } catch (error) {
          showError(error instanceof Error ? error : new Error(String(error)));
        }
      }

      /** @param {{request: string, owner: string, status: string, notes: string, sheetName: string, rowNumber: number}} request */
      function renderRequest(request) {
        setPending(false);
        source.textContent = 'Snapshot from ' + request.sheetName + ' row ' + request.rowNumber + '.';
        result.textContent = request.request + '\nOwner: ' + (request.owner || '(not set)') +
          '\nStatus: ' + (request.status || '(not set)') + '\nNotes: ' + (request.notes || '(none)');
        message.textContent = 'Request loaded. Check the source row before using this result.';
      }

      /** @param {Error} error */
      function showError(error) {
        setPending(false);
        result.textContent = '';
        source.textContent = '';
        message.textContent = 'Cannot load request: ' + error.message;
      }

      loadRequest();
    </script>
  </body>
</html>

Download file

Handoff.html

<!doctype html>
<html lang="en">
  <head>
    <base target="_top">
    <style>
      body { margin: 0; padding: 1rem; color: #202124; background: #fff; font: 0.95rem/1.5 system-ui, sans-serif; }
      h1 { font-size: 1.25rem; }
      fieldset { margin: 1rem 0; padding: 0.75rem; border: 1px solid #aaa; }
      label { display: block; margin-top: 0.5rem; font-weight: 600; }
      textarea { box-sizing: border-box; width: 100%; font: inherit; }
      button { margin-top: 0.5rem; padding: 0.5rem; font: inherit; }
      :focus-visible { outline: 3px solid #174ea6; outline-offset: 2px; }
      #request { white-space: pre-wrap; }
      p { overflow-wrap: anywhere; }
    </style>
  </head>
  <body>
    <h1>Prepare a handoff note</h1>
    <p>Review the selected request and prepare plain text for manual copying. Nothing is sent or saved to Sheets.</p>
    <p id="message" role="status" aria-live="polite">Loading the selected row.</p>
    <p id="source"></p>
    <p id="request"></p>
    <fieldset id="editor" disabled>
      <legend>Local handoff note</legend>
      <label for="context">Additional context (optional, up to 1,000 characters)</label>
      <textarea id="context" rows="3" maxlength="1000"></textarea>
      <button id="prepare" type="button">Prepare note</button>
      <label for="preview">Plain-text note</label>
      <textarea id="preview" rows="8" readonly aria-describedby="copy-help"></textarea>
      <p id="copy-help">Select note highlights this text. Use Ctrl+C on Windows/Linux or Command+C on macOS to copy it yourself. The button does not access the clipboard.</p>
      <button id="select-note" type="button" disabled>Select note</button>
    </fieldset>
    <p>Reload discards your local context and prepared note. Close also discards them. To choose another row in this editor, close the dialog first.</p>
    <button id="reload" type="button" disabled>Reload and discard note</button>
    <button id="close" type="button">Close</button>
    <script>
      /** @typedef {{request: string, owner: string, status: string, notes: string, sheetName: string, rowNumber: number}} SelectedRequest */
      const message = requireElement('message');
      const source = requireElement('source');
      const requestText = requireElement('request');
      const editor = requireElement('editor');
      const reload = requireElement('reload');
      const selectNote = requireElement('select-note');
      const context = requireTextArea('context');
      const preview = requireTextArea('preview');
      /** @type {SelectedRequest | null} */
      let currentRequest = null;
      let pending = false;

      requireElement('prepare').addEventListener('click', prepareNote);
      reload.addEventListener('click', loadRequest);
      requireElement('close').addEventListener('click', function () {
        google.script.host.close();
      });
      context.addEventListener('input', function () {
        preview.value = '';
        selectNote.toggleAttribute('disabled', true);
        message.textContent = 'Context changed. Prepare the note again before copying.';
      });
      selectNote.addEventListener('click', function () {
        if (!preview.value) return;
        preview.focus();
        preview.select();
        message.textContent = 'Text selected, not copied or sent. Use your keyboard copy command if needed.';
      });

      /** @param {string} id */
      function requireElement(id) {
        const element = document.getElementById(id);
        if (!element) throw new Error('Missing dialog element: ' + id);
        return element;
      }

      /** @param {string} id */
      function requireTextArea(id) {
        const element = requireElement(id);
        if (!(element instanceof HTMLTextAreaElement)) throw new Error('Expected text area: ' + id);
        return element;
      }

      /** @param {boolean} value */
      function setPending(value) {
        pending = value;
        reload.toggleAttribute('disabled', value);
        editor.toggleAttribute('disabled', value || currentRequest === null);
      }

      function loadRequest() {
        if (pending) return;
        currentRequest = null;
        context.value = '';
        preview.value = '';
        selectNote.toggleAttribute('disabled', true);
        source.textContent = '';
        requestText.textContent = '';
        setPending(true);
        message.textContent = 'Loading the selected row.';
        try {
          google.script.run
            .withSuccessHandler(renderRequest)
            .withFailureHandler(showError)
            .getSelectedRequest();
        } catch (error) {
          showError(error instanceof Error ? error : new Error(String(error)));
        }
      }

      /** @param {SelectedRequest} request */
      function renderRequest(request) {
        currentRequest = request;
        setPending(false);
        source.textContent = 'Snapshot from ' + request.sheetName + ' row ' + request.rowNumber + '. Not a live view.';
        requestText.textContent = request.request + '\nOwner: ' + (request.owner || '(not set)') +
          '\nStatus: ' + (request.status || '(not set)') + '\nNotes: ' + (request.notes || '(none)');
        message.textContent = 'Check the request, add any local context, then prepare the note.';
        context.focus();
      }

      function prepareNote() {
        if (pending || !currentRequest || !context.reportValidity()) return;
        const request = currentRequest;
        const extra = context.value.trim();
        preview.value = 'Request: ' + request.request +
          '\nSource: ' + request.sheetName + ' row ' + request.rowNumber + ' (snapshot)' +
          '\nOwner: ' + (request.owner || '(not set)') +
          '\nStatus: ' + (request.status || '(not set)') +
          '\nNotes: ' + (request.notes || '(none)') +
          (extra ? '\nAdditional context: ' + extra : '');
        selectNote.toggleAttribute('disabled', false);
        message.textContent = 'Note prepared in this dialog only. Review it before copying.';
      }

      /** @param {Error} error */
      function showError(error) {
        currentRequest = null;
        setPending(false);
        source.textContent = '';
        requestText.textContent = '';
        preview.value = '';
        selectNote.toggleAttribute('disabled', true);
        message.textContent = 'Cannot load request: ' + error.message + ' Close to fix the selection, or reload to retry this read.';
      }

      loadRequest();
    </script>
  </body>
</html>

Download file

appsscript.json

{
  "timeZone": "Etc/UTC",
  "runtimeVersion": "V8",
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets.currentonly",
    "https://www.googleapis.com/auth/script.container.ui"
  ]
}

Download file

requests.csv

Request,Owner,Status,Notes
Confirm room booking,Alex,Open,Check accessibility requirements
Prepare agenda,Sam,In progress,Collect topics before Friday

Menus, sidebars, and modals

Create a separate interface practice sheet

Review the account and scopes

Use onOpen only to add the menu

Choose what the interface should block

Read the selection on the server

Return values the browser can receive

Keep the sidebar open while moving between requests

Make waiting, errors, and stale data visible

Keep text out of the HTML parser

Exercise: distinguish selection, snapshots, and local edits

Troubleshoot the menu and panels

Close the practice interface and choose what to build next

Project files

Code.gs

Sidebar.html

Handoff.html

appsscript.json

requests.csv

Create a separate interface practice sheet

Review the account and scopes

Use onOpen only to add the menu

Choose what the interface should block

Read the selection on the server

Return values the browser can receive

Keep the sidebar open while moving between requests

Make waiting, errors, and stale data visible

Prepare a handoff note in the modal

Keep text out of the HTML parser

Exercise: distinguish selection, snapshots, and local edits

Troubleshoot the menu and panels

Close the practice interface and choose what to build next

Code.gs

Sidebar.html

Handoff.html

appsscript.json

requests.csv