Skip to content
Skip to chapter

Chapter 21

User interfaces

Menus, sidebars, and modals

Use onOpen(), SpreadsheetApp.getUi().createMenu(), and HtmlService in Google Apps Script to open custom menus, sidebars, and modal dialogs in Sheets.

By the end

Build a read-only bound Sheets interface with explicit selection, asynchronous loading, safe text output, and a useful modal interaction.

Chapter navigation

Use a bound Sheets menu when the action belongs to one Workspace file and an editor starts it by hand. This project reads one selected request in an alert or 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 three interfaces serve different needs. The alert is a quick check, the sidebar stays beside the sheet while you move between requests, and the modal gives you a place to review one request and add local context before copying a note yourself. None needs a deployment or a public URL.

Create a separate interface practice sheet

This four-column practice sheet builds on runtime and permissions and the request tracker without modifying the tracker spreadsheet. It uses Open and In progress display statuses instead of running the tracker's five-column state transitions.

  1. Create a blank spreadsheet named Interface practice and rename its tab Requests.

  2. Copy the comma-separated text from requests.csv into cell A1. Select column A, choose Data > Split text to columns, set the separator to Comma, and verify three rows by four columns with headers Request, Owner, Status, and Notes in A1:D1.

  3. Open Extensions > Apps Script from this spreadsheet. Replace Code.gs with the server file below. Add two HTML files named Sidebar and Handoff; the editor displays them as Sidebar.html and Handoff.html.

  4. In Project Settings, enable Show "appsscript.json" manifest file in editor, then replace appsscript.json with the manifest below.

  5. Save 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 Sheets, not a file in the script editor. A script created through the spreadsheet's Extensions menu is bound to that spreadsheet, its container. In a bound editor execution, it can obtain the current selection and the container's UI without a file ID. Creating a standalone project and opening a spreadsheet by ID does not attach that project to the spreadsheet's UI. Google's bound-script guide explains the supported contexts and creation steps.

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 authorization to the current bound spreadsheet. It is not a read-only scope; this script is read-only because it calls no write methods.

  • script.container.ui permits the custom sidebar and modal. Both showSidebar and showModalDialog require it.

Menu functions and their HTML interfaces run as the active editor, using the user-at-the-keyboard model described in the authorization guide.

Let opening the file add the menu, not perform the work

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: Apps Script recognizes its name and invokes it when an editor opens the spreadsheet. No trigger installation is needed. It does not run for a viewer or commenter. Simple triggers run without prompting for authorization and cannot call services that require consent, so keep onOpen limited to building the menu. See simple triggers.

The second argument to addItem is a function name string such as 'showSelectedRequest'. onOpen registers that callback and calls addToUi() to display the menu; Apps Script runs the function later when you click the menu item.

A document can have only one menu with a given name. Adding another Request Tracker menu replaces the earlier one in that open editor instead of merging entries. Keep this practice project in its own spreadsheet rather than pasting it into the capstone project, which already defines onOpen. The menu guide documents menu replacement and notes that menus stay visible until the file is reloaded.

Choose what the interface should block

The word modal describes what the user can interact with in the spreadsheet editor, not whether the server function is still running.

InterfaceCan you keep interacting with this spreadsheet editor?Does the opening server execution wait for dismissal?
Built-in Ui.alert or Ui.promptNo, while the dialog is openYes; the execution is suspended
HTML modal opened by showModalDialogNo, until the modal closesNo; the opener continues and finishes
HTML sidebar opened by showSidebarYesNo; the opener continues and finishes

showSelectedRequest reads the selected request, then calls ui.alert. Dismissing the alert resumes that server function. Validation errors appear in a Cannot show request alert instead.

A built-in ui.prompt adds one text input and returns a PromptResponse: check getSelectedButton() before reading getResponseText(), because Cancel or the close button can still return entered text. Like an alert, a prompt suspends the server execution. JDBC connections and script locks do not persist across that suspension, so release locks before prompting. See the Ui reference.

By contrast, openRequestDialog constructs an HtmlOutput and calls showModalDialog. The server function finishes as soon as the modal opens, and the HTML page handles later actions. While open, the modal blocks clicks in this user's spreadsheet tab, but it does not lock the spreadsheet against collaborators or other executions.

The modal starts at 520 by 640 pixels using setWidth and setHeight. Sidebars have a fixed width of 300 pixels, so calling setWidth on a sidebar has no effect. See HtmlOutput sizing and Ui sidebar behavior.

Read the server's selection, not a browser guess

Each interface calls the getSelectedRequest server function, which takes no arguments from the browser.

Selection still requires validation. The function calls getActiveRangeList() and checks RangeList.getRanges() to require a single continuous range on one row. Selecting A2 or A2:D2 passes; selecting rows 2 and 3 or multiple disjoint ranges throws an error rather than guessing a row.

The server then checks that the range is on the Requests tab below the header row. It reads two small rectangles: A1:D1 for the headers and columns A:D on the selected row. Headers must match Request, Owner, Status, and Notes, and the selected row must have a nonblank request title. Empty owner or status cells format as (not set), and empty notes format as (none). Columns outside A:D are ignored.

Google enforces spreadsheet access and OAuth consent on the server. Public top-level functions are callable through google.script.run even if the HTML has no button for them; helper functions whose names end with _ are excluded from google.script.run, as documented in the communication guide.

Return values the browser can receive

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

getDisplayValues returns formatted cell text, which fits a display panel and avoids Date instances. getValues() returns Date objects for date cells, and google.script.run rejects Date parameters and return values. Across the bridge, use 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. See parameters and return values.

Keep the sidebar open while moving between requests

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 keeps the row-2 snapshot. 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 and has access to document and DOM elements, while Code.gs runs on Apps Script's servers with access to SpreadsheetApp. HtmlService.createHtmlOutputFromFile('Sidebar') loads the HTML file (omitting .html).

google.script.run bridges the two environments asynchronously. withSuccessHandler(renderRequest) and withFailureHandler(showError) register callbacks before getSelectedRequest() dispatches the request. Because the call returns immediately in the browser, handle the server response inside the callbacks, as explained in the communication guide.

Make waiting, errors, and stale data visible

loadRequest sets pending before dispatching, disables Reload, and clears the previous result and source label. The success handler re-enables the button and renders the response; the failure handler re-enables the button and displays the error.

The pending flag prevents overlapping requests from the same panel, which could otherwise resolve out of order. A try/catch block around dispatch catches synchronous browser errors, while withFailureHandler catches asynchronous server failures.

A reload crosses four steps:

  1. You click Reload in the sidebar.

  2. Apps Script starts getSelectedRequest and reads the active selection.

  3. The server reads the captured sheet and row.

  4. The browser receives the response and renders it.

If you change the active cell before step 2 captures the selection, the server reads the newer cell. Returning sheetName and rowNumber with the payload lets the sidebar label which row the server actually read.

The sidebar does not poll or listen for selection or cell changes. Because sorting or inserting rows shifts row numbers, treat rowNumber as the source location of that snapshot rather than a persistent identifier for a later write.

Prepare a handoff note in the modal

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 compose a plain-text note in the browser without editing the sheet.

  1. Select A2 and choose Request Tracker > Prepare handoff note.

  2. Wait for the request to load. While loading, the note controls remain disabled.

  3. In Additional context, enter Ask about step-free access. This text stays in the browser.

  4. 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.

  1. Choose Select note to focus and highlight the text area, then copy it with Ctrl+C (Windows/Linux) or Command+C (macOS).

  2. Choose Close. The Requests sheet remains unchanged.

The modal calls getSelectedRequest on open and stores the returned snapshot in currentRequest. prepareNote combines currentRequest with the text area value in browser memory. Editing Additional context clears the prepared preview and disables Select note until you click Prepare note again.

Reload and discard note clears both the local context and the preview before fetching the current selection. If loading fails, preparation stays disabled while reload and close remain available.

google.script.host.close() closes the HTML dialog or sidebar from browser code; do not use window.close(). The dialogs guide describes this host API. Closing the modal does not cancel an in-flight google.script.run request.

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 markup. See textContent, text area values, and text area selection. To preserve line breaks in plain text, use CSS white-space: pre-wrap rather than innerHTML or unescaped template scriptlets.

HTML Service isolates pages in an iframe sandbox. <base target="_top"> directs links to open outside the frame, and external scripts or stylesheets must load over HTTPS. Calling setSandboxMode has no effect because all HTML Service pages use the IFRAME sandbox mode; see HTML restrictions.

Exercise: distinguish selection, snapshots, and local edits

  1. Load A2 in the sidebar. Click A3 without reloading and confirm the panel still displays row 2. Click Reload and verify the source label and details update to row 3.

  2. Select A1 and reload to trigger the header-row error, then select A2:A3 and reload to trigger the single-row error. Select A2 and reload to recover.

  3. Change D2 to <b>Check the ramp</b> and reload the sidebar. Confirm the angle brackets render as literal text, then restore D2 to Check accessibility requirements.

  4. Open the handoff modal for A2, add local context, and click Prepare note. Edit the context and verify the preview clears. Click Prepare note again, then click Reload and discard note and confirm the context and preview reset while the sheet's Notes cell stays unchanged.

  5. Close the modal, select A3, and reopen it. Verify the snapshot loads Prepare agenda with empty context.

If you change selection while the sidebar is still loading, check the returned source label: whichever row was active when getSelectedRequest captured the selection is the row reported in the label.

Troubleshoot without widening permissions

What you seeStart here
The menu is missingSave the bound project, reload the spreadsheet, and confirm you have edit access. The function must be named onOpen and call addToUi.
The capstone's Request tracker menu disappears or this menu is missing entriesThis 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 errorSelect one cell in a data row on Requests. Header rows, multiple rows, and disjoint ranges are rejected.
A header errorCompare A1:D1 with the four-column sample (Request, Owner, Status, Notes).
A blank-title errorSelect a populated data row or restore its Request cell.
The HTML panel opens but loading failsOpening the page and reading Sheets are separate executions. Check the displayed error, authorization, and the Executions entry for getSelectedRequest.
The panel stays busyInspect the browser console for page errors and Apps Script Executions for server failures. Close and reopen the panel to retry the read.
The modal shows the wrong request for your intentionCheck its source-row label, close it, select the intended row, and reopen.
Select note does not put text on the clipboardThe button only selects the text. Copy it with Ctrl+C or Command+C.
Saved HTML changes do not appearClose and reopen the sidebar or modal so Apps Script serves the updated HTML file.

Close the practice interface and choose the next surface

Close the sidebar and modal when finished. Any local context or note you did not copy is discarded.

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

Use a bound menu, sidebar, or modal when editors work inside a single spreadsheet. When callers need a standalone URL without opening the sheet, build a standalone web app instead; a web app has its own deployment audience and execution identity and cannot access a container selection or UI, as noted in the bound-script guide. When you want to install an interface across files or distribute it through the Workspace toolbar, build a Workspace add-on; see the add-ons overview.

Project files

Complete source files for this chapter’s examples.

Inspect a selected request and prepare a local handoff note

Code.gs

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,
  };
}

Sidebar.html

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>

Handoff.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>

appsscript.json

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"
  ]
}

requests.csv

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