Build a private request-status web app

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

You need the runtime and permissions and menus and interfaces chapters first. A bound sidebar reads the active spreadsheet selection. A web app has no active selection, so the browser submits an ID and the server opens a configured spreadsheet by its file ID. This standalone project uses the New, Ready, Hold, and Done states from the request tracker on its own sample sheet.

Choose who the app runs as and who can open it

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. Check both settings in the deployment dialog when you create the URL.

The other execution mode, User accessing the web app, runs as each visitor. Each visitor then needs permission on the target spreadsheet and must grant OAuth consent. Google's web app permissions guide compares both modes.

An email address typed into the browser is not proof of who the visitor is. Session.getActiveUser().getEmail() is not proof either. In a web app that runs as the owner without visitor authorization, it can return an empty string. Session.getEffectiveUser() returns the account the script runs as, which is the deployer when Execute as is Me. See the Session reference. This example uses no OAuth token. If you later add code that does, never send the 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. Confirm that the table is five rows by five columns and that E2 is blank.

3. From the Apps Script home page, choose New project and name it Private request lookup. This creates a standalone project. Do not open 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 practice 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 checks Requester during validation but never returns it to the browser. Formatting A:E as plain text stops 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. A bound script can use the narrower spreadsheets.currentonly scope. SpreadsheetApp.openById needs the full spreadsheets scope even when the code only reads, and that grant covers every spreadsheet the account can open. A web app serves its own HTML page, not a sidebar inside a file, so it does not need script.container.ui.

After saving the project, select checkSetup in the Apps Script 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 fails when openById runs. Running doGet in the editor returns an HtmlOutput object without opening a browser tab, so use checkSetup to check spreadsheet access before you deploy.

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

Opening a web app URL in a browser sends an HTTP GET, which runs doGet. doGet returns an HtmlOutput object. The web app guide documents the required doGet function and its return type.

HtmlService.createHtmlOutputFromFile('Index') loads Index.html without evaluating template scriptlets or querying the spreadsheet. If the file name does not match an HTML file in the project, Apps Script throws No HTML file named ... was found. 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, where it can use SpreadsheetApp and WEB_APP_CONFIG. The script inside Index.html runs in the visitor's browser, where it can use the page's DOM. Browser and server code share no memory. Each lookup calls google.script.run. That call sends the arguments to the server and returns the result to the browser as serialized copies in an asynchronous request. See the communication guide.

Validate the ID and sheet data 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.

readRequestTable_ opens the spreadsheet and tab named in WEB_APP_CONFIG with openById and getSheetByName. If the Requests tab is missing, it throws an error.

It checks getLastRow() and getLastColumn() (Sheet dimension and range methods) and rejects a table with more than 100 request rows or with data beyond column E. It then reads at most 101 rows by five columns in one getDisplayValues() call.

The same function validates every row before lookupRequest searches the table. The headers must match, and each row needs a unique ID. Requester must be 1 to 80 characters, Summary must be 1 to 200 characters, and Status must be one of the four allowed values. A duplicate ID or a blank row causes an error before the lookup can return a result.

The browser can call doGet, checkSetup, and lookupRequest through google.script.run. It cannot call helper functions whose names end with _, such as readRequestTable_. See private server functions.

Return display strings instead of 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 }. An invalid ID, malformed sheet data, or a service failure throws an error instead.

getDisplayValues() returns formatted strings, with "" for an empty cell. Because every value is a string, the result contains no Date objects, which google.script.run cannot serialize.

Through google.script.run, return primitives (string, number, boolean, null) and plain arrays or objects built from them. google.script.run rejects Range, Sheet, Date, functions, circular objects, and DOM elements. The one exception is a single <form> element passed as the only argument. 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. The input's required, pattern, minlength, and maxlength attributes make the browser check the value before submission. Anyone who can open the page can remove those attributes in developer tools, so lookupRequest repeats the same format check on the server.

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

withSuccessHandler and withFailureHandler register callbacks for the asynchronous response. The page has four states:

• 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 blocks a second submission from the same tab until the first one answers. Concurrent google.script.run calls can finish in any order. Without the flag, a slow lookup could overwrite a newer result. See the communication guide.

Render data as text

The page writes every dynamic string, including error messages and spreadsheet values, with textContent. The browser then shows a value such as <b>Urgent</b> as literal text instead of parsing it as HTML. See Node.textContent.

If a page instead evaluates server-side scriptlets with HtmlService.createTemplateFromFile('Index').evaluate(), use the escaping form <?= value ?>. Do not force-print with <?!= value ?>. See the HTML templates guide.

HTML Service shows the page inside an iframe. <base target="_top"> makes links open outside that 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. A local copy of Index.html opened from disk does not work, because only HtmlService provides google.script.run.

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 a snapshot of the project code (Code.gs, Index.html, and appsscript.json) that cannot change. A deployment points to one version and keeps a stable /exec URL. Saving an edit in the editor updates /dev at once. To update /exec:

1. Choose Deploy > Manage deployments.

2. Click Edit and select New version.

3. Click Deploy.

4. Reload any open /exec tab to load the new HTML.

Spreadsheet rows are not part of the version, so both /dev and /exec read the current 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. lookupRequest only reads, so reloading and resubmitting after a dropped connection repeats the read and changes nothing. If you later add an action that changes data, check authorization on the server, make a repeated request safe to run twice, and use a lock. LockService.getDocumentLock() returns null in a standalone project such as this one. Use LockService.getScriptLock() to coordinate shared state across executions of a standalone project, as documented in the LockService reference.

Troubleshoot the lookup

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.

Archive the deployment and remove practice files

Close the web app browser tabs. In Deploy > Manage deployments, archive each versioned deployment you created for this exercise. Deleting doGet in the editor does not stop a deployed version. See archive a versioned deployment. When you no longer need the practice files, move the Private request lookup script project and the practice spreadsheet to Trash.

Next, read HTTP endpoints and webhooks with doPost.