Build a private request-status web app

Build a form that looks up a request by ID and displays its summary, status, and last update. The browser sends the ID to Apps Script; Apps Script reads a private sample spreadsheet and returns only the selected request's display fields. Submitting the form does not create a request, change a status, or send a notification.

This chapter builds on runtime and permissions and menus and interfaces. Unlike a bound sidebar that reads the active spreadsheet selection, a web app has no active selection: the browser submits an ID and the server opens a configured spreadsheet by ID. This standalone project uses the New, Ready, Hold, and Done states from the request tracker on its own sample sheet.

Choose the identity and audience first

A web app has two separate permission settings:

Use one account to create the sample sheet, own the standalone script, authorize it, and deploy it. The web app manifest reference names these settings USER_DEPLOYING and MYSELF. Verify both settings in the deployment dialog when creating the URL.

This is an owner-only lookup tool. Knowing a request ID such as R-300 selects a row, not a caller identity; the script does not check per-requester permissions.

The alternative execution mode, User accessing the web app, runs with each visitor's identity and requires each visitor to have permission on the target spreadsheet and grant OAuth consent. Google's web app permissions guide compares both modes.

Do not trust a browser-submitted email address as caller identity, and do not assume Session.getActiveUser().getEmail() returns an address: in owner-executed web apps without visitor authorization, it can return an empty string. Session.getEffectiveUser() returns the account whose authority executes the script (the deployer when set to Me). See the Session reference. Never send an OAuth token to the browser.

Prepare the sheet and standalone project

1. Create a blank spreadsheet for this exercise and name its tab Requests.

2. Select columns A:E and choose Format > Number > Plain text. Copy requests.csv below into A1. Select column A, choose Data > Split text to columns, and set the separator to Comma. Verify five rows by five columns; E2 is blank.

3. From the Apps Script home page, choose New project and name it Private request lookup. Create a standalone project rather than opening Extensions > Apps Script from the sheet.

4. Replace Code.gs with the server source below. Add an HTML file named Index (Index.html) and paste the HTML source below into it.

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

6. In Code.gs, set WEB_APP_CONFIG.spreadsheetId to the sample spreadsheet's ID (the segment between /d/ and /edit in its URL). Leave sheetName as Requests and maxRequests as 100.

Request ID,Requester,Summary,Status,Last updated
R-300,Ada Example,Laptop setup,New,
R-301,Bo Example,Guest account,Hold,2026-09-01 09:30 UTC
R-302,Cam Example,Project folder,Ready,2026-09-02 10:00 UTC
R-303,Dee Example,Meeting room display,Done,2026-09-03 11:15 UTC

The headers match the tracker: Request ID, Requester, Summary, Status, and Last updated. The lookup reads Requester during validation without returning it to the browser. Formatting A:E as plain text prevents Sheets from reformatting the sample timestamps.

Review the scope before granting consent

{
  "timeZone": "Etc/UTC",
  "dependencies": {},
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets"
  ],
  "webapp": {
    "access": "MYSELF",
    "executeAs": "USER_DEPLOYING"
  }
}

This manifest requests https://www.googleapis.com/auth/spreadsheets. Unlike a bound script's spreadsheets.currentonly, SpreadsheetApp.openById requires full spreadsheets scope even when the code only calls read methods, and that OAuth grant covers any spreadsheet the account can access. Because a web app serves its own HTML page rather than opening a container sidebar, it does not need script.container.ui.

After saving the project, select checkSetup in the editor, click Run, and approve the consent prompt. The execution log prints:

Setup checked: 4 requests; no cells changed.

checkSetup validates the configured spreadsheet and logs the request count. An empty spreadsheetId fails before any Sheets call; an invalid or inaccessible ID throws when openById runs. Running doGet directly in the editor returns an HtmlOutput object without opening a browser tab, so use checkSetup to verify spreadsheet access before deploying.

Serve the page, then answer its requests

const WEB_APP_CONFIG = {
  // Copy only the sandbox spreadsheet ID between /d/ and /edit in its URL.
  spreadsheetId: '',
  sheetName: 'Requests',
  maxRequests: 100,
};

const REQUEST_HEADERS = ['Request ID', 'Requester', 'Summary', 'Status', 'Last updated'];
const REQUEST_STATUSES = ['New', 'Ready', 'Hold', 'Done'];

function doGet() {
  return HtmlService.createHtmlOutputFromFile('Index')
    .setTitle('Request status')
    .addMetaTag('viewport', 'width=device-width, initial-scale=1');
}

// Run once from the editor to authorize and check the sandbox configuration.
function checkSetup() {
  const table = readRequestTable_();
  const message = 'Setup checked: ' + (table.length - 1) + ' requests; no cells changed.';
  console.log(message);
  return message;
}

// This project is for a self-only deployment, not requester-level access control.
/** @param {string} requestId */
function lookupRequest(requestId) {
  if (!isRequestId_(requestId)) {
    throw new Error('Enter a request ID such as R-300: R- followed by three digits.');
  }

  const table = readRequestTable_();
  const row = table.find(function (values) {
    return values[0] === requestId;
  });

  if (!row) {
    return { found: false, requestId: requestId };
  }

  // Return plain data, not a Sheet, Range, Date, or the whole table.
  return {
    found: true,
    requestId: row[0],
    summary: row[2],
    status: row[3],
    lastUpdated: row[4],
  };
}

/** @param {unknown} value @returns {value is string} */
function isRequestId_(value) {
  return typeof value === 'string' && value.length === 5 && /^R-[0-9]{3}$/.test(value);
}

function readRequestTable_() {
  const spreadsheetId = WEB_APP_CONFIG.spreadsheetId;
  if (typeof spreadsheetId !== 'string' || !/^[A-Za-z0-9_-]+$/.test(spreadsheetId)) {
    throw new Error('Set WEB_APP_CONFIG.spreadsheetId to the sandbox spreadsheet ID, not its URL.');
  }

  const spreadsheet = SpreadsheetApp.openById(spreadsheetId);
  const sheet = spreadsheet.getSheetByName(WEB_APP_CONFIG.sheetName);
  if (!sheet) {
    throw new Error('Requests sheet is missing. Create the sample tab before looking up requests.');
  }

  const lastRow = sheet.getLastRow();
  if (lastRow < 1 || lastRow > WEB_APP_CONFIG.maxRequests + 1) {
    throw new Error('Requests must have a header and at most 100 request rows.');
  }
  if (sheet.getLastColumn() !== REQUEST_HEADERS.length) {
    throw new Error('Requests must contain exactly the five sample columns A:E.');
  }

  const table = sheet.getRange(1, 1, lastRow, REQUEST_HEADERS.length).getDisplayValues();
  if (REQUEST_HEADERS.some(function (header, index) { return table[0][index] !== header; })) {
    throw new Error('Requests headers must be: ' + REQUEST_HEADERS.join(', ') + '.');
  }

  const seenIds = new Set();
  for (let index = 1; index < table.length; index++) {
    const row = table[index];
    const rowNumber = index + 1;
    if (!isRequestId_(row[0])) {
      throw new Error('Request row ' + rowNumber + ' needs an ID such as R-300.');
    }
    if (seenIds.has(row[0])) {
      throw new Error('Request row ' + rowNumber + ' repeats an earlier ID. Give each request a unique ID.');
    }
    seenIds.add(row[0]);
    if (!row[1].trim() || row[1].length > 80 || !row[2].trim() || row[2].length > 200) {
      throw new Error('Request row ' + rowNumber + ' needs a requester (1-80 characters) and summary (1-200 characters).');
    }
    if (!REQUEST_STATUSES.includes(row[3])) {
      throw new Error('Request row ' + rowNumber + ' must use New, Ready, Hold, or Done.');
    }
    if (row[4].length > 80) {
      throw new Error('Request row ' + rowNumber + ' has a Last updated value longer than 80 characters.');
    }
  }
  return table;
}

Visiting a web app URL with an HTTP GET invokes doGet, which returns an HtmlOutput object. The web app guide documents the entrypoint and return-type contract.

HtmlService.createHtmlOutputFromFile('Index') loads Index.html without evaluating template scriptlets or querying the spreadsheet. setTitle sets the document title, and addMetaTag sets the mobile viewport (HtmlService strips <meta> tags written directly in the HTML file). See the HtmlService and HtmlOutput references.

Code.gs runs on Apps Script's servers with access to SpreadsheetApp and WEB_APP_CONFIG, while the script inside Index.html runs in the visitor's browser with access to the DOM. Each lookup calls google.script.run, which serializes arguments and return values across an asynchronous request rather than sharing memory between browser and server. See the communication guide.

Keep the read narrow and validate it on the server

lookupRequest accepts a single request ID string such as R-300. isRequestId_ checks that the value is a five-character string matching /^R-[0-9]{3}$/ before opening the spreadsheet.

The server selects the spreadsheet and tab from WEB_APP_CONFIG using openById and getSheetByName, throwing an error if Requests is missing. Using Sheet dimension and range methods, it checks getLastRow() and getLastColumn() first, rejecting tables with more than 100 request rows or data beyond column E, and then reads at most 101 rows by five columns in a single getDisplayValues() call.

Every row in the table is validated before lookupRequest returns: headers must match, each row must have a unique ID, Requester and Summary must be nonblank and within length limits, and Status must be one of the four allowed values. Duplicate IDs and blank interior rows throw an error rather than returning a position-dependent match.

Helper functions ending with _ are hidden from google.script.run, while public functions (doGet, checkSetup, lookupRequest) remain callable; see private server functions.

Return display data, not service objects

When a request ID matches, lookupRequest returns { found: true, requestId, summary, status, lastUpdated }. When a valid ID has no matching row, it returns { found: false, requestId }. Invalid IDs, malformed sheet data, and service failures throw an error instead.

getDisplayValues() returns formatted strings (and "" for empty cells), which avoids Date objects that google.script.run cannot serialize. Across google.script.run, return primitives (string, number, boolean, null) and plain arrays or objects built from them; Range, Sheet, Date, functions, circular objects, and DOM elements (other than a single <form> argument) are rejected. See Range.getDisplayValues and parameters and return values.

Submit the form without leaving the page

<!DOCTYPE html>
<html lang="en">
  <head>
    <base target="_top">
    <style>
      body { margin: 0; color: #202124; background: #fff; font: 1rem/1.5 system-ui, sans-serif; }
      main { max-width: 38rem; margin: 0 auto; padding: 1.5rem; }
      label { display: block; font-weight: 600; }
      input, button { box-sizing: border-box; padding: 0.6rem; font: inherit; }
      input { width: 100%; max-width: 16rem; }
      button { margin-top: 0.5rem; cursor: pointer; }
      button:disabled { cursor: wait; }
      :focus-visible { outline: 3px solid #174ea6; outline-offset: 3px; }
      dt { font-weight: 600; }
      dd { margin: 0 0 1rem; white-space: pre-wrap; overflow-wrap: anywhere; }
      #message { min-height: 3rem; overflow-wrap: anywhere; }
    </style>
  </head>
  <body>
    <main>
      <h1>Request status</h1>
      <p>Look up one request in your private sample spreadsheet. This form does not create or update requests.</p>
      <form id="lookup-form">
        <label for="request-id">Request ID</label>
        <p id="id-help">Enter R- followed by three digits, for example R-300.</p>
        <input id="request-id" name="requestId" type="text" required
          pattern="R-[0-9]{3}" minlength="5" maxlength="5"
          autocomplete="off" spellcheck="false" aria-describedby="id-help">
        <button id="lookup-button" type="submit" disabled>Look up</button>
      </form>
      <p id="message" role="status" aria-live="polite">Loading form...</p>
      <section id="result" hidden tabindex="-1" aria-labelledby="result-heading">
        <h2 id="result-heading">Request details</h2>
        <dl>
          <dt>Request ID</dt><dd id="result-id"></dd>
          <dt>Summary</dt><dd id="result-summary"></dd>
          <dt>Status</dt><dd id="result-status"></dd>
          <dt>Last updated (as displayed in Sheets)</dt><dd id="result-updated"></dd>
        </dl>
      </section>
      <p>Results do not refresh automatically. If the connection is lost and the form stays busy, reload the page and look up the ID again. Repeating this read does not change cells.</p>
      <noscript>Enable JavaScript to use this form through the Apps Script web app URL.</noscript>
    </main>
    <script>
      /**
       * @template {HTMLElement} T
       * @param {string} id
       * @param {new () => T} expectedType
       * @returns {T}
       */
      function requireElement_(id, expectedType) {
        const element = document.getElementById(id);
        if (!(element instanceof expectedType)) {
          throw new Error('The page is missing its required ' + id + ' element.');
        }
        return element;
      }

      /** @typedef {{found:false,requestId:string}|{found:true,requestId:string,summary:string,status:string,lastUpdated:string}} LookupResponse */
      const form = requireElement_('lookup-form', HTMLFormElement);
      const input = requireElement_('request-id', HTMLInputElement);
      const button = requireElement_('lookup-button', HTMLButtonElement);
      const message = requireElement_('message', HTMLParagraphElement);
      const result = requireElement_('result', HTMLElement);
      const resultId = requireElement_('result-id', HTMLElement);
      const resultSummary = requireElement_('result-summary', HTMLElement);
      const resultStatus = requireElement_('result-status', HTMLElement);
      const resultUpdated = requireElement_('result-updated', HTMLElement);
      let pending = false;

      /** @param {boolean} value */
      function setPending(value) {
        pending = value;
        input.disabled = value;
        button.disabled = value;
        form.setAttribute('aria-busy', String(value));
      }

      /** @param {unknown} error */
      function showFailure(error) {
        setPending(false);
        result.hidden = true;
        const detail = error instanceof Error
          ? error.message : 'No response was available. Reload the page and try this read again.';
        message.textContent = 'Lookup failed: ' + detail;
        input.focus();
      }

      form.addEventListener('submit', function (event) {
        event.preventDefault();
        if (pending || !form.reportValidity()) return;

        const requestId = input.value;
        result.hidden = true;
        message.textContent = 'Looking up ' + requestId + '...';
        setPending(true);

        try {
          google.script.run
            .withSuccessHandler(/** @param {LookupResponse} response */ function (response) {
              setPending(false);
              if (!response.found) {
                message.textContent = 'No request found for ' + response.requestId + '. No cells changed.';
                input.focus();
                return;
              }
              resultId.textContent = response.requestId;
              resultSummary.textContent = response.summary;
              resultStatus.textContent = response.status;
              resultUpdated.textContent = response.lastUpdated || 'Not recorded';
              result.hidden = false;
              message.textContent = 'Lookup complete. No cells changed.';
              result.focus();
            })
            .withFailureHandler(showFailure)
            .lookupRequest(requestId);
        } catch (error) {
          // A local bridge error is separate from an asynchronous server error.
          showFailure(error);
        }
      });

      if (typeof google === 'undefined' || !google.script || !google.script.run) {
        message.textContent = 'Open this form using its Apps Script web app URL, not a local HTML file.';
      } else {
        button.disabled = false;
        message.textContent = 'Ready to look up a request.';
      }
    </script>
  </body>
</html>

The <label for="request-id"> element pairs with the input's id, and the input's required, pattern, and length attributes provide browser-side validation before submission. Because anyone with access to the page can bypass browser constraints from developer tools, lookupRequest enforces the same format check on the server.

The submit listener calls event.preventDefault() to stop full-page navigation, reads input.value, and dispatches lookupRequest through google.script.run. Because submissions go through google.script.run, the project needs no doPost handler.

withSuccessHandler and withFailureHandler register callbacks for the asynchronous response:

• Waiting: hides the previous result, shows the loading status, and disables the input and button while pending is true.

• Found: populates the four display fields, reveals the result section, and moves focus to it.

• Not found: reports that the ID has no record and returns focus to the input.

• Failure: hides any previous result, displays the error text, and re-enables the form controls.

The pending flag prevents overlapping submissions from the same tab, since concurrent google.script.run calls are not guaranteed to resolve in order; see the communication guide.

Render data as text

Every dynamic string, including error messages and spreadsheet values, is written with textContent so the browser treats values such as <b>Urgent</b> as literal text rather than parsed HTML; see Node.textContent.

When a page instead evaluates server-side scriptlets with HtmlService.createTemplateFromFile('Index').evaluate(), use contextual escaping (<?= value ?>) rather than force-printing (<?!= value ?>); see the HTML templates guide. HTML Service sandboxes pages in an iframe where <base target="_top"> directs links outside the frame and setSandboxMode has no effect; see HTML Service restrictions.

Create a private deployment and distinguish its URLs

1. Choose Deploy > New deployment and select Web app.

2. Enter a description such as Private request lookup, set Execute as to Me, and set Who has access to Only myself.

3. Click Deploy and copy the /exec URL.

4. Open the /exec URL in a browser signed in as the deploying account and submit R-300. Opening a local copy of Index.html from disk will not work because google.script.run is only injected by HtmlService.

5. Choose Deploy > Test deployments, select Web app, and copy the /dev URL for development testing.

Google documents both deployment and test-deployment URLs:

A version is an immutable snapshot of project code (Code.gs, Index.html, and appsscript.json), while a deployment points to a selected version and keeps a stable /exec URL. Saving an edit in the editor updates /dev immediately, but /exec continues running its pinned version until you choose Deploy > Manage deployments, click Edit, select New version, and deploy. Reload any open /exec tab after updating its version so the browser loads the new HTML. Because spreadsheet rows live outside the script project, both /dev and /exec read current sheet cells on each lookup. See deployments and versions.

Recognize the response and handle repeats

Looking up R-300 displays:

Request ID: R-300
Summary: Laptop setup
Status: New
Last updated (as displayed in Sheets): Not recorded

Not recorded is the browser's fallback label when E2 is empty. Looking up R-301 displays Guest account, Hold, and 2026-09-01 09:30 UTC. Looking up R-999 shows the no-match message and hides the previous result.

The page does not poll; each submission rereads the sheet. Because lookupRequest is read-only, reloading and resubmitting after a dropped connection only repeats the read. If you later add a mutating action, enforce authorization, idempotency, and locking on the server. Note that LockService.getDocumentLock() returns null in standalone scripts and web apps; use LockService.getScriptLock() when coordinating shared state across executions of a standalone project, as documented in the LockService reference.

Troubleshoot the boundary that failed

Exercise: change data, then bypass a browser rule

1. Look up R-300.

2. In the sheet, change C2 to <b>Urgent laptop setup</b> and D2 from New to Ready, leaving E2 blank. Confirm the open browser tab still shows the earlier snapshot, then submit R-300 again. Verify the angle brackets render as literal text, Status shows Ready, and Last updated stays Not recorded.

3. In Index.html, change the input's pattern from R-[0-9]{3} to [A-Z]-[0-9]{3}. Save, reload /dev, and submit X-300. The browser allows submission, and lookupRequest rejects X-300 on the server before reading the spreadsheet.

4. Restore pattern to R-[0-9]{3}, C2 to Laptop setup, and D2 to New. Save, reload /dev, and submit R-300 to confirm the baseline result.

Withdraw the app without touching real requests

Close the web app browser tabs. In Deploy > Manage deployments, archive any versioned deployment you created for this exercise; deleting doGet in the editor alone leaves previously deployed versions active. See archive a versioned deployment. When you no longer need the practice files, move the Private request lookup script project and the sample spreadsheet to Trash.

Next, receive JSON webhooks and verify signatures in doPost(e).