OAuth for separate accounts

Read three fictional requests from a private spreadsheet through the Sheets REST API. You will authorize a dedicated Google OAuth client, reuse its saved grant on later runs, and remove that access when you finish. The script counts ready requests and totals their points without changing spreadsheet cells.

This exercise adds a separate OAuth grant. Start after runtime and permissions, connecting services, state across runs, and menus and interfaces. Keep their projects unchanged. The new project is bound to its own practice spreadsheet so it can display an authorization link; the data read itself uses UrlFetchApp, not SpreadsheetApp.getValues().

Decide whether you need a separate grant

For ordinary Google integrations, prefer a built-in service or an advanced service when it supplies the operation. You can also request the required scope in the Apps Script manifest and send ScriptApp.getOAuthToken() to a Google REST API. Apps Script then manages the effective user's token. You do not need to create your own OAuth client secret or save a refresh token for that design.

Here you use a dedicated client to learn the authorization-code lifecycle needed when your application owns a separate provider connection. Google serves as the provider because it has documented consent, callback, refresh, and revocation contracts; see the OAuth2 library README.

There are two authorizations in this project:

The Sheets scope is read-only, but Google classifies it as sensitive and describes it as access to all your Google Sheets spreadsheets. The code's fixed spreadsheet ID and twelve-cell range narrow its operation, not the token's authority. Sheets scopes cannot be limited to an individual tab or cell range. A per-file drive.file design needs a different file-selection/access workflow and is not a drop-in permission for an arbitrary existing spreadsheet ID. See the Sheets scope reference.

Choose a user grant or keyless service-account impersonation

There are three distinct ways this kind of script can obtain a Google API access token. Choose by execution identity and permission boundary.

Keyless service-account impersonation uses two tokens and two principals. First, ScriptApp.getOAuthToken() supplies the effective user's token to authenticate a call to the IAM Service Account Credentials API. That user must have iam.serviceAccounts.getAccessToken on the target service account. The API then returns a new, short-lived OAuth access token whose identity is the service account. The user token authorizes minting; it is not the token sent to the final resource API.

To set up that path:

1. Associate the Apps Script project with the intended standard Google Cloud project and enable the IAM Service Account Credentials API.

2. Create or select the service account. Grant it only the resource permissions it needs for the final API.

3. Grant the approved human caller iam.serviceAccounts.getAccessToken on that service account. The predefined Service Account Token Creator role includes this permission and other credential-creation permissions, so prefer a reviewed custom role containing only iam.serviceAccounts.getAccessToken when that is all the workflow needs.

4. Add script.external_request and an IAM-authorizing scope such as cloud-platform to the Apps Script manifest. Review the breadth of both permissions.

5. Call projects/-/serviceAccounts/SERVICE_ACCOUNT_EMAIL:generateAccessToken with the target API scope and a short lifetime. The dash in projects/- is required.

6. Send the returned service-account token to the final Google API, and discard it when the call or bounded batch finishes.

function mintServiceAccountToken(serviceAccountEmail, targetScope) {
  const name = 'projects/-/serviceAccounts/' +
    encodeURIComponent(serviceAccountEmail);
  const response = UrlFetchApp.fetch(
    'https://iamcredentials.googleapis.com/v1/' + name +
      ':generateAccessToken',
    {
      method: 'post',
      contentType: 'application/json',
      headers: {
        Authorization: 'Bearer ' + ScriptApp.getOAuthToken()
      },
      payload: JSON.stringify({
        scope: [targetScope],
        lifetime: '3600s'
      }),
      muteHttpExceptions: true
    }
  );

  const status = response.getResponseCode();
  if (status < 200 || status >= 300) {
    throw new Error('Token minting failed with HTTP ' + status + '.');
  }
  const body = JSON.parse(response.getContentText());
  if (typeof body.accessToken !== 'string' || typeof body.expireTime !== 'string') {
    throw new Error('IAM Credentials returned an unexpected response.');
  }
  return body;
}

The default and ordinary maximum lifetime is one hour. An organization policy can allow longer lifetimes for named service accounts, but increasing lifetime expands exposure and is not needed for a bounded Apps Script call. Request the narrow final-API scope, refresh only after expiry or an authorization error, and never log either token.

This flow avoids downloading a service-account JSON key and storing a private key in Script Properties. It also does not create domain-wide delegation and does not make the service account act as a Workspace user. To read a spreadsheet as the service account, share that file with the service account or grant access through another authorized path. The target API checks the service account's resource permission as well as the token's scopes.

The exercise below does not use impersonation. It teaches a separate user consent, callback, refresh token, and revocation lifecycle. Use impersonation when a stable non-human principal is the intended actor; use a user grant when the remote action must belong to the consenting person.

Keep the identities separate

The account running a menu action or editor function supplies the Apps Script execution identity. getUserProperties() selects that script user's store. The browser then chooses an account at Google's authorization screen. That authorizing account supplies the Sheets permission carried by the separate access token. The browser account selection does not update Script Properties or guarantee that both identities match.

For this exercise, use the same approved account throughout: spreadsheet owner, script user, OAuth test user, and account selected at consent. Use a browser profile with only that account signed in. The source asks Google to show an account chooser; it does not request email or OpenID identity scopes and does not prove that the selected provider account equals the Apps Script user. If you choose another account, its file permissions govern the REST request.

The callback is an Apps Script execution reached through Google's authenticated /usercallback handling, not a web app configured to run as its deployer. Keep the same script user and browser session through that round trip so the later editor read finds that user's stored grant. If the callback cannot be opened or the subsequent inspection reports no local token, check the script and account context before creating another grant.

Install the complete project and its pinned library

1. Create a blank private spreadsheet named OAuth practice. Rename its only tab OAuth sample.

2. Copy the supplied CSV into A1. If it lands in one column, select that column and choose Data > Split text to columns > Comma. Keep the default automatic number format for the numeric points; do not format those values as plain text.

3. Confirm the exact table below. Leave cells outside A1:C4 out of the exercise.

4. From this spreadsheet, choose Extensions > Apps Script. Name the new bound project OAuth practice script.

5. Replace Code.gs, add an HTML file named Authorization with the Authorization.html contents, and in Project Settings, enable Show "appsscript.json" manifest file in editor and replace the manifest.

Request,Status,Points
REQ-001,Ready,3
REQ-002,Hold,2
REQ-003,Ready,5

{
  "timeZone": "Etc/UTC",
  "dependencies": {
    "libraries": [
      {
        "userSymbol": "OAuth2",
        "libraryId": "1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF",
        "version": "43",
        "developmentMode": false
      }
    ]
  },
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "oauthScopes": [
    "https://www.googleapis.com/auth/script.external_request",
    "https://www.googleapis.com/auth/script.container.ui"
  ]
}

The OAuth2 symbol comes from Libraries, not Services or npm. The public library ID is 1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF. The manifest pins Apps Script version 43 with developmentMode: false. This ID and numeric version appear together in Google's own consumer manifest; the library repository separately identifies its latest tagged release as v1.43.0. Do not paste the GitHub release name into the manifest's numeric version field.

After saving, inspect the Libraries entry. If adding it through the UI, click + beside Libraries, paste the ID, choose Look up, select version 43, retain identifier OAuth2, and click Add. Do not select HEAD (Development Mode). Google's dependency reference says development mode ignores the pinned version.

No advanced Sheets service is required. The manifest has neither a webapp resource nor a deployment to create. Its two scopes allow the external fetches and the modal HTML link; they do not include a spreadsheet data scope. Save and reload the spreadsheet to see OAuth Practice. The simple onOpen handler only adds that menu; it never starts consent or fetches data.

Register the callback and configure the client

A client ID identifies your application to the authorization server. A client secret is the server-side credential of this web client. The redirect URI tells Google where to return the browser after consent.

First obtain the real redirect URI from your own script. Choose OAuth Practice > Show redirect URI with the spreadsheet open. Complete the Apps Script consent prompt if asked. This function uses the library's OAuth2.getRedirectUri() and needs no client configuration. It displays a URI with this structure:

https://script.google.com/macros/d/{YOUR_SCRIPT_ID}/usercallback

Copy the displayed URI. The library's redirect implementation uses the including script's ID and /usercallback rather than /exec or /dev, and selects the callback function through the state token without a web-app deployment.

In Google Cloud Console:

1. Create or select a dedicated practice Cloud project for API configuration and OAuth credentials. Leave the Apps Script project's default Cloud project unchanged; the OAuth client can live in a separate Cloud project.

2. In that dedicated Cloud project, open APIs & Services > Library, find Google Sheets API, and enable it where the OAuth client lives.

3. Open Google Auth platform > Branding. If it is not configured, choose Get Started. Set an app name such as OAuth practice reader, select your support contact, enter your developer contact, review the data-policy agreement, and complete creation.

4. Set Audience to External and leave the publishing status Testing. Under Test users, add your practice account.

5. Under Data Access > Add or Remove Scopes, add only https://www.googleapis.com/auth/spreadsheets.readonly and save. Confirm it appears in the sensitive scope category.

6. Under Clients > Create Client, select application type Web application and name it OAuth practice web client. Add the exact /usercallback URI under Authorized redirect URIs and create the client. This server flow does not need an authorized JavaScript origin.

7. Copy the client ID and client secret into the Script Properties described next. Keep any downloaded credentials JSON out of Code.gs, the HTML file, and version control.

These steps follow Google's current consent-screen setup and web-client credential instructions. Redirect matching is exact, including HTTPS, case, and trailing slash. A copied script has a different script ID and therefore needs its own registered redirect URI and credential setup.

Understand Testing before you consent

External Testing limits the app to listed test users and displays an unverified/test warning. Current Google documentation says those authorizations expire after seven days; an issued offline refresh token expires too. The exception for requests containing only basic name, email, profile, or OpenID scopes does not apply to this Sheets scope. A refresh token is therefore not a permanent unattended credential, even when the first refresh succeeds. See Manage App Audience and refresh token expiration.

Removing that seven-day Testing limit requires moving the app to In production, which brings separate consent-screen and verification requirements; keep this practice app in Testing for the exercise despite the seven-day expiry. Organization policy or Advanced Protection can still block a test user from authorizing.

Put configuration and tokens in different stores

In Apps Script Project Settings > Script Properties, add these four keys and save:

Run checkSetup from the editor. Expect Configuration format checked; no provider request or consent link created. The guard checks required values and their syntax before creating an authorization link or making a configured token/resource request. Provider checks validate the secret, redirect registration, and file access when you authorize and read. The redirect display and local cleanup actions remain usable before client configuration is complete.

Script Properties are shared project configuration. The OAuth library instead receives PropertiesService.getUserProperties() for its saved token, so one user's token is not kept in a store shared with every script user. The stable service name book-sheets-read identifies this connection within that store. The library storage source serializes the token as JSON under its service prefix and reconstructs it in later executions. Leave those library-managed values alone; do not hand-edit an expiry or paste in a token.

Both stores are server-side storage, not a vault that excludes project maintainers. PropertiesService defines the sharing scopes, not a separate editor-proof encryption policy. A script editor can read shared configuration and change code that later runs with a user's access to their token store. For a bound script, protecting the parent spreadsheet's editor list is part of protecting the script. Keep this project private and review the pinned dependency as code that runs with the project's capabilities.

The project does not use CacheService. For heavier use the library supports a cache with the same user scope as the property store, but a cache is an optional acceleration, not durable token storage or evidence of current authorization. Eviction must fall back to properties. Adding a shared script cache beside private user properties would choose the wrong isolation boundary. Library objects also retain in-memory values during one execution; this project creates them inside its locked operation rather than retaining a stale global service.

Never log or return the service object, property contents, authorization URL, callback request, token object, request headers, or provider response body. Although the client ID travels in the authorization URL, keep the client secret, tokens, state parameter, and authorization code out of logs and exports.

Follow one consent round trip

const OAUTH_SERVICE_NAME_ = 'book-sheets-read';
const OAUTH_SCOPE_ = 'https://www.googleapis.com/auth/spreadsheets.readonly';
const OAUTH_SAMPLE_RANGE_ = "'OAuth sample'!A1:C4";

/** @type {Record<string, string>} */
const OAUTH_MESSAGES_ = {
  CONFIG: 'Set the four OAuth Script Properties exactly as described in the chapter. No provider request was made.',
  BUSY: 'Another operation holds this user lock. Wait for it to finish before trying again.',
  STORED: 'A local token already exists. Read or inspect it; do not start another consent flow. Revoke and forget it before reconnecting.',
  CALLBACK: 'This callback is incomplete or belongs to another service. Close this tab. Use a fresh authorization link from your own spreadsheet.',
  CLIENT: 'Google rejected the OAuth client. Check the dedicated web client ID and secret privately; repeated consent will not fix them.',
  GRANT: 'Google rejected the authorization code or refresh grant (invalid_grant). Do not replay this callback. Review expiry, revocation and client setup before reconnecting.',
  TOKEN: 'No usable access token is available. Inspect local status and review grant expiry, provider availability and Apps Script quotas. No automatic consent or token retry was attempted.',
  SCOPE: 'The stored token does not report the required read-only Sheets scope. Do not read with it. Review the grant, then revoke and reconnect.',
  OFFLINE: 'The token response lacks a refresh token. The refresh exercise is not ready. Review offline access, revoke this grant and reconnect.',
  TOKEN_SHAPE: 'The stored token has an unexpected shape. Do not print it. Remove this app access in Google Account settings, then forget its local tokens.',
  RESOURCE_AUTH: 'Sheets rejected the bearer token (401). A locally unexpired token can be revoked. Do not force refresh or loop consent; review the grant first.',
  RESOURCE_PERMISSION: 'Sheets refused access (403). Check the granted scope, Sheets API enablement, chosen account, spreadsheet permission and organization policy.',
  RESOURCE_MISSING: 'Sheets did not expose this spreadsheet (404). Check the configured ID and the authorizing account permission; do not make the sheet public.',
  RESOURCE_INPUT: 'Sheets rejected the request (400). Check the spreadsheet ID, tab name and exact sample range.',
  RATE_WAIT: 'The provider requested a wait or returned 429. Stop this run. Review quota and retry timing before a later manual read.',
  RESOURCE_TRANSIENT: 'Sheets still returned a server error after the bounded read attempts. Wait before a later manual read; keep the stored grant.',
  RESOURCE_STATUS: 'Sheets returned an unexpected HTTP status. The response was not accepted and no redirect was followed.',
  RESOURCE_TRANSPORT: 'The Sheets request did not produce a usable response. Check connectivity, Apps Script authorization and quotas before a later manual read.',
  DATA: 'The sample response is not the expected four rows by three columns. Restore the supplied headers and valid sample values; no cells were changed.',
  NO_REVOKE_TOKEN: 'No local token is available for revocation. Local absence does not prove remote removal. Check this app in Google Account settings.',
  REVOKE_UNCERTAIN: 'The revocation request has an uncertain outcome. Local tokens were retained. Check this app access in Google Account settings before doing more work.',
  REVOKE_REJECTED: 'Google did not confirm revocation with HTTP 200. Local tokens were retained. Check or remove this app access in Google Account settings.',
  CLEANUP: 'Google accepted revocation, but local cleanup did not finish. Do not authorize again yet. Check remote app access, then use Forget local tokens.',
  UNEXPECTED: 'The operation stopped unexpectedly. No raw error or credential was logged. Check project setup and Apps Script execution status before proceeding.'
};

function onOpen() {
  SpreadsheetApp.getUi().createMenu('OAuth Practice')
    .addItem('Show redirect URI', 'showRedirectUri')
    .addItem('Authorize sample read', 'showAuthorization')
    .addSeparator()
    .addItem('Revoke grant and forget tokens', 'revokeGrant')
    .addItem('Forget local tokens only', 'forgetLocalTokens')
    .addToUi();
}

function showRedirectUri() {
  runSafely_(function () {
    // This public library method needs no client credentials and sends no fetch.
    SpreadsheetApp.getUi().alert('Register this exact redirect URI',
      OAuth2.getRedirectUri(), SpreadsheetApp.getUi().ButtonSet.OK);
  });
}

function checkSetup() {
  runSafely_(function () {
    readConfig_();
    Logger.log('Configuration format checked; no provider request or consent link created.');
  });
}

function showAuthorization() {
  runSafely_(function () {
    withUserLock_(function (lock) {
      const service = configuredService_(readConfig_(), lock);
      // Do not use hasAccess here: it can refresh, and false is not a consent instruction.
      if (oauthToken_(service)) fail_('STORED');
    });
    const page = HtmlService.createHtmlOutputFromFile('Authorization').setWidth(430).setHeight(230);
    SpreadsheetApp.getUi().showModalDialog(page, 'Authorize the practice client');
  });
}

function getAuthorizationUrl() {
  return withUserLock_(function (lock) {
    const service = configuredService_(readConfig_(), lock);
    if (oauthToken_(service)) fail_('STORED');
    return service.getAuthorizationUrl();
  });
}

/** @param {*} request */
function authCallback(request) {
  let message;
  try {
    message = withUserLock_(function (lock) {
      const config = readConfig_();
      if (!request || !request.parameter ||
          request.parameter.serviceName !== OAUTH_SERVICE_NAME_ ||
          (typeof request.parameter.code !== 'string' &&
           typeof request.parameter.error !== 'string')) fail_('CALLBACK');
      const service = configuredService_(config, lock);
      // A second callback must not overwrite the first saved grant.
      if (oauthToken_(service)) fail_('STORED');
      if (!service.handleCallback(request)) {
        return 'Authorization declined. No new token was saved. Close this tab.';
      }
      validateToken_(oauthToken_(service));
      return 'Authorization saved. Close this tab and the dialog, then run readSample in the Apps Script editor.';
    });
  } catch (error) {
    message = safeMessage_(error);
  }
  // Only application-owned text reaches the callback page. No provider body or URL.
  return HtmlService.createHtmlOutput('').appendUntrusted(message);
}

function readSample() {
  runSafely_(function () {
    const summary = withUserLock_(function (lock) {
      const config = readConfig_();
      const service = configuredService_(config, lock);
      let accessToken;
      try {
        // Internally checks expiry and may attempt one refresh. Never refresh on each GET.
        accessToken = service.getAccessToken();
      } catch (error) {
        throw service.getLastError() || new Error('TOKEN');
      }
      validateToken_(oauthToken_(service));
      const values = fetchSample_(config, accessToken);
      return summarizeSample_(values);
    });
    Logger.log('Requests: ' + summary.requests + '; ready: ' + summary.ready +
      '; ready points: ' + summary.readyPoints + '. No cells changed.');
  });
}

function inspectLocalGrant() {
  runSafely_(function () {
    const status = withUserLock_(function (lock) {
      const token = oauthToken_(storedService_(lock));
      if (!token) return 'Local token: absent. Remote authorization: not checked.';
      const expires = accessExpiry_(token);
      const expirationText = expires === null ? 'unknown' :
        Utilities.formatDate(new Date(expires * 1000), 'Etc/UTC', "yyyy-MM-dd'T'HH:mm:ss'Z'");
      return 'Local token: present; refresh token stored: ' +
        (typeof token.refresh_token === 'string' && token.refresh_token.length > 0) +
        '; access expiry UTC: ' + expirationText + '. Remote authorization: not checked.';
    });
    Logger.log(status);
  });
}

function revokeGrant() {
  runSafely_(function () {
    const ui = SpreadsheetApp.getUi();
    // Alerts suspend the execution and do not preserve locks. Confirm before acquiring one.
    if (ui.alert('Revoke this practice grant?',
      'This removes the Google authorization represented by this stored token, not just this range. Close all consent tabs first. Continue?',
      ui.ButtonSet.YES_NO) !== ui.Button.YES) return;
    withUserLock_(function (lock) {
      const service = storedService_(lock);
      const token = oauthToken_(service);
      const value = token && (token.refresh_token || token.access_token);
      if (typeof value !== 'string' || !value) fail_('NO_REVOKE_TOKEN');
      let response;
      try {
        response = UrlFetchApp.fetch('https://oauth2.googleapis.com/revoke', {
          method: 'post',
          contentType: 'application/x-www-form-urlencoded',
          payload: 'token=' + encodeURIComponent(value),
          muteHttpExceptions: true,
          followRedirects: false
        });
      } catch (error) {
        fail_('REVOKE_UNCERTAIN');
      }
      if (!response) fail_('REVOKE_UNCERTAIN');
      if (response.getResponseCode() !== 200) fail_('REVOKE_REJECTED');
      try {
        service.reset();
      } catch (error) {
        fail_('CLEANUP');
      }
    });
    ui.alert('Google accepted revocation; this user local tokens were removed. Check the app access in Google Account settings.');
  });
}

function forgetLocalTokens() {
  runSafely_(function () {
    const ui = SpreadsheetApp.getUi();
    if (ui.alert('Forget local tokens only?',
      'This does NOT revoke Google access. Remove the practice app access in Google Account settings first. Close all consent tabs. Forget this user local tokens?',
      ui.ButtonSet.YES_NO) !== ui.Button.YES) return;
    withUserLock_(function (lock) {
      storedService_(lock).reset();
    });
    ui.alert('Local tokens removed for this user. Remote authorization was not checked or changed.');
  });
}

function readConfig_() {
  const properties = PropertiesService.getScriptProperties();
  const config = {
    clientId: properties.getProperty('OAUTH_CLIENT_ID'),
    clientSecret: properties.getProperty('OAUTH_CLIENT_SECRET'),
    spreadsheetId: properties.getProperty('OAUTH_SPREADSHEET_ID'),
    range: properties.getProperty('OAUTH_RANGE')
  };
  if (!config.clientId || !/^[A-Za-z0-9_-]+\.apps\.googleusercontent\.com$/.test(config.clientId) ||
      !config.clientSecret || config.clientSecret.length < 10 || config.clientSecret.length > 512 ||
      /\s/.test(config.clientSecret) ||
      !config.spreadsheetId || !/^[A-Za-z0-9_-]{20,200}$/.test(config.spreadsheetId) ||
      config.range !== OAUTH_SAMPLE_RANGE_) fail_('CONFIG');
  return config;
}

/** @param {LockService.Lock} lock @returns {*} */
function storedService_(lock) {
  return OAuth2.createService(OAUTH_SERVICE_NAME_)
    .setPropertyStore(PropertiesService.getUserProperties())
    .setLock(lock);
}

/** @param {*} config @param {LockService.Lock} lock @returns {*} */
function configuredService_(config, lock) {
  return storedService_(lock)
    .setAuthorizationBaseUrl('https://accounts.google.com/o/oauth2/v2/auth')
    .setTokenUrl('https://oauth2.googleapis.com/token')
    .setClientId(config.clientId)
    .setClientSecret(config.clientSecret)
    .setCallbackFunction('authCallback')
    .setScope(OAUTH_SCOPE_)
    .setParam('access_type', 'offline')
    .setParam('prompt', 'consent select_account');
}

/** @param {*} service @returns {*} */
function oauthToken_(service) {
  return service.getToken();
}

/** @param {*} token */
function validateToken_(token) {
  if (!token || typeof token.access_token !== 'string' || !token.access_token ||
      token.token_type !== 'Bearer' || accessExpiry_(token) === null) fail_('TOKEN_SHAPE');
  if (typeof token.scope !== 'string' || !token.scope.split(/\s+/).includes(OAUTH_SCOPE_)) fail_('SCOPE');
  if (typeof token.refresh_token !== 'string' || !token.refresh_token) fail_('OFFLINE');
}

/** @param {*} token @returns {number|null} */
function accessExpiry_(token) {
  const expires = token.expiresAt !== undefined ? token.expiresAt :
    Number(token.granted_time) + Number(token.expires_in);
  return typeof expires === 'number' && Number.isFinite(expires) && expires > 0 &&
    expires <= 8640000000000 ? expires : null;
}

/** @param {*} config @param {string} accessToken */
function fetchSample_(config, accessToken) {
  const url = 'https://sheets.googleapis.com/v4/spreadsheets/' +
    encodeURIComponent(config.spreadsheetId) + '/values/' + encodeURIComponent(config.range) +
    '?majorDimension=ROWS&valueRenderOption=UNFORMATTED_VALUE&fields=' +
    encodeURIComponent('range,majorDimension,values');
  for (let attempt = 0; attempt < 2; attempt += 1) {
    let response;
    try {
      response = UrlFetchApp.fetch(url, {
        method: 'get',
        headers: { Authorization: 'Bearer ' + accessToken, Accept: 'application/json' },
        muteHttpExceptions: true,
        followRedirects: false
      });
    } catch (error) {
      fail_('RESOURCE_TRANSPORT');
    }
    if (!response) fail_('RESOURCE_TRANSPORT');
    const status = response.getResponseCode();
    const headers = response.getAllHeaders();
    const headerNames = Object.keys(headers);
    if (status === 200) {
      const contentTypeKey = headerNames.find(/** @param {string} name */ function (name) { return name.toLowerCase() === 'content-type'; });
      if (!contentTypeKey || String(headers[contentTypeKey]).split(';')[0].trim().toLowerCase() !== 'application/json') fail_('DATA');
      const text = response.getContentText('UTF-8');
      if (text.length > 8192) fail_('DATA');
      let body;
      try {
        body = JSON.parse(text);
      } catch (error) {
        fail_('DATA');
      }
      if (!body || body.range !== config.range || body.majorDimension !== 'ROWS' ||
          !Array.isArray(body.values) || body.values.length !== 4) fail_('DATA');
      return body.values;
    }
    if (status === 401) fail_('RESOURCE_AUTH');
    if (status === 403) fail_('RESOURCE_PERMISSION');
    if (status === 404) fail_('RESOURCE_MISSING');
    if (status === 400) fail_('RESOURCE_INPUT');
    if (status === 429 || headerNames.some(/** @param {string} name */ function (name) { return name.toLowerCase() === 'retry-after'; })) fail_('RATE_WAIT');
    if (![500, 502, 503, 504].includes(status)) fail_('RESOURCE_STATUS');
    if (attempt === 1) fail_('RESOURCE_TRANSIENT');
    Utilities.sleep(1000 + Math.floor(Math.random() * 1001));
  }
  fail_('RESOURCE_TRANSIENT');
}

/** @param {*} rows */
function summarizeSample_(rows) {
  if (!rows.every(/** @param {*} row */ function (row) { return Array.isArray(row) && row.length === 3; }) ||
      rows[0][0] !== 'Request' || rows[0][1] !== 'Status' || rows[0][2] !== 'Points') fail_('DATA');
  const seen = new Set();
  const summary = { requests: 0, ready: 0, readyPoints: 0 };
  for (const row of rows.slice(1)) {
    if (typeof row[0] !== 'string' || !/^REQ-\d{3}$/.test(row[0]) || seen.has(row[0]) ||
        !['Ready', 'Hold'].includes(row[1]) || !Number.isInteger(row[2]) ||
        row[2] < 0 || row[2] > 20) fail_('DATA');
    seen.add(row[0]);
    summary.requests += 1;
    if (row[1] === 'Ready') {
      summary.ready += 1;
      summary.readyPoints += row[2];
    }
  }
  return summary;
}

/** @param {function(LockService.Lock): *} operation */
function withUserLock_(operation) {
  const lock = LockService.getUserLock();
  if (!lock.tryLock(5000)) fail_('BUSY');
  try {
    return operation(lock);
  } finally {
    lock.releaseLock();
  }
}

/** @param {string} code @returns {never} */
function fail_(code) {
  throw new Error(code);
}

/** @param {unknown} error */
function safeMessage_(error) {
  const message = error instanceof Error ? error.message : '';
  if (Object.prototype.hasOwnProperty.call(OAUTH_MESSAGES_, message)) return OAUTH_MESSAGES_[message];
  // This library exposes token errors as text, not a typed HTTP result.
  // Recognize only these leading provider codes; never echo the rest of the message.
  if (/^Error retrieving token: invalid_grant(?:,|$)/.test(message)) return OAUTH_MESSAGES_.GRANT;
  if (/^Error retrieving token: (invalid_client|unauthorized_client|deleted_client)(?:,|$)/.test(message)) return OAUTH_MESSAGES_.CLIENT;
  return OAUTH_MESSAGES_.UNEXPECTED;
}

/** @param {function(): void} operation */
function runSafely_(operation) {
  try {
    operation();
  } catch (error) {
    // Do not retain the original error as a cause: it may contain a provider body.
    throw new Error(safeMessage_(error));
  }
}

Choose OAuth Practice > Authorize sample read. The source checks configuration and refuses to replace any existing local token. It displays the link in Authorization.html; displaying the link does not exchange a code or read Sheets. Click Review Google Sheets read access to open Google's authorization screen in a separate browser tab. Check the app name, account, and read-only Sheets permission before consenting.

The authorization URL contains response_type=code, the client ID, the scope, the exact redirect URI, access_type=offline, and library-generated state. The connection action also requests prompt=consent select_account. Consent is requested only when you start a connection, not on every read. Offline access asks for a refresh-capable grant; it does not mean the script works without a network connection.

State ties an authorization attempt to its callback routing and helps defend against cross-site request forgery, where someone tries to make your app accept an unsolicited authorization response. The library uses Apps Script's encrypted StateTokenBuilder, records the callback method and service name, and sets a one-hour validity period. Google returns that state to the /usercallback endpoint; Apps Script processes it and invokes authCallback(request). Rely on the library's StateTokenBuilder integration rather than a static string or doGet(e).

handleCallback() handles error=access_denied by returning false. When Google returns a code, the library sends it from Apps Script to https://oauth2.googleapis.com/token, with grant_type=authorization_code, the configured client ID and secret, and the same redirect URI. The code is a short-lived credential for the exchange, not the token you send to Sheets. Google's authorization-code contract defines this single-use exchange; reloading a completed callback tab cannot exchange the same code again.

On a successful exchange the library saves the token before returning. The callback then verifies the fields needed by this lesson: a bearer access token, expiry information, the required granted scope, and a refresh token. It displays either a short success message or an application-owned error. A scope or refresh-token failure after exchange can therefore leave a local token and a remote grant even though the page does not say success. Inspect the local status and use the removal procedure; do not assume an error undid consent.

<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="utf-8">
    <title>Authorize the practice client</title>
  </head>
  <body>
    <p>Use the same approved practice account as this spreadsheet and script.</p>
    <p id="link-status">Preparing the authorization link...</p>
    <p>The grant can read spreadsheets available to that account, not only this sample. Continue only for your dedicated practice client.</p>
    <p>After the callback, close that tab and this dialog. Run <code>readSample</code> in the Apps Script editor.</p>
    <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 dialog is missing its required ' + id + ' element.');
        }
        return element;
      }

      const linkStatus = requireElement_('link-status', HTMLParagraphElement);
      google.script.run
        .withSuccessHandler(/** @param {string} authorizationUrl */ function (authorizationUrl) {
          const link = document.createElement('a');
          link.href = authorizationUrl;
          link.target = '_blank';
          link.rel = 'noopener noreferrer';
          link.textContent = 'Review Google Sheets read access';
          linkStatus.replaceChildren(link);
        })
        .withFailureHandler(function () {
          linkStatus.textContent = 'Authorization link could not be prepared. Close this dialog and inspect the local grant.';
        })
        .getAuthorizationUrl();
    </script>
  </body>
</html>

The dialog is served with createHtmlOutputFromFile, not a template: no server value is printed into the markup. The link is built in the browser: google.script.run.getAuthorizationUrl() asks the server for the URL, and its success handler creates the anchor with document.createElement('a'), setting href, target, rel, and textContent through DOM APIs rather than HTML strings. The link opens a new tab rather than trying to embed Google's consent page inside the Apps Script dialog. It carries neither the client secret nor an access/refresh token. The callback includes no images, analytics, redirects, or raw provider text; appendUntrusted also escapes its message as text.

After success, close the callback tab and the spreadsheet dialog. A second callback is rejected if a token is already saved, rather than overwriting a completed connection. Close abandoned consent tabs before revoking or forgetting a connection: finishing a still-open consent flow later grants access again.

Read, repeat, and change a result

In the Apps Script editor, select readSample and click Run. The script obtains the access token on the server and sends it only in an Authorization: Bearer header to sheets.googleapis.com. The client secret and refresh token go to the token endpoint, not to Sheets. No token appears in the resource URL.

The request calls spreadsheets.values.get for exactly 'OAuth sample'!A1:C4, requests majorDimension=ROWS and UNFORMATTED_VALUE, and selects only range,majorDimension,values through the partial-response fields parameter. Unformatted values keep the points numeric rather than reading locale-formatted text.

The response must have status 200, a JSON content type, at most 8,192 characters of body text, the requested range and row orientation, and the expected four-by-three table. Headers must match; request IDs must be distinct; statuses must be Ready or Hold; points must be integers from zero through twenty. A Sheets ValueRange omits empty trailing rows and columns, so clearing a required sample cell is a data error. The script does not count the missing value as zero. Extra cells outside the fixed range are not imported.

With the supplied data, expect this execution-log message:

Requests: 3; ready: 2; ready points: 8. No cells changed.

1. Run readSample again without changing data. Predict the same counts and no consent page. A successful repeat shows that the stored connection can be reused; it does not by itself prove a refresh occurred.

2. In the sample spreadsheet, change only B3 from Hold to Ready. Predict three ready requests and ten ready points. Run readSample and compare the new message.

3. Restore B3 to Hold. The next read should return to two ready requests and eight points. The script does not restore data for you.

4. To observe a data boundary, change only C3 to the text two, then read again. Expect a sample-data error rather than a partial total. Restore the numeric value 2 before continuing. A data failure does not require another OAuth grant.

The GET helper permits one retry for 500, 502, 503, or 504, after one to two seconds of jitter, provided the error response has no Retry-After header. A retry repeats only the read with the same access token. Two unsuccessful attempts stop. A 429 or a non-success response asking for a wait stops without trying to fit an unknown delay into the execution. Google's Sheets limits guide describes time-based quotas and truncated backoff. Because UrlFetchApp downloads the response before the body-size check runs, locks, fetches, and platform quotas still govern total execution time. Standard Sheets API use incurs no additional cost within quota, though the limits page notes planned changes for over-quota usage later in 2026.

Observe expiry without editing a token

An access token is the short-lived bearer credential used on the resource request. A refresh token is the longer-lived credential the server can use to obtain another access token without asking the user to consent again. Anyone who obtains usable bearer credentials may be able to exercise their access; token storage and logging are therefore authorization boundaries, not only preferences.

The library's hasAccess() implementation first examines the stored access token's expiry. With a still-usable token it returns locally; it does not ask Google whether the grant was revoked. Near expiry it can acquire the configured lock, reread the token, and refresh it. getAccessToken() calls that check internally and throws when no usable token is available. Calling hasAccess() can therefore refresh and mutate stored token state, so status checks that should not contact Google use getToken() instead.

inspectLocalGrant calls getToken(). It reports only local presence, whether a refresh token is stored, and the access-expiry time in UTC. It never fetches or refreshes, and its message says remote authorization was not checked.

To observe a real automatic refresh:

1. After a successful authorization and read, run inspectLocalGrant and note the displayed expiry time and whether a refresh token is stored.

2. Leave the grant connected, close any consent tabs, and wait until after that access-expiry time while the refresh grant remains valid. The library treats access tokens within sixty seconds of expiry as expired.

3. Run readSample once. If it succeeds without a new consent flow, run inspectLocalGrant again to confirm the updated access-expiry time.

4. If the call fails, check the error category in the table below.

Google's refresh contract uses a POST to the token endpoint containing grant_type=refresh_token, the refresh token, and this web client's credentials. A refresh response supplies a new access token and expiry; it is not a promise to issue a different refresh token each time. The library preserves the old refresh token when a refresh response omits one and saves a replacement when one is returned. It also carries forward known refresh-expiry metadata when omitted by the new response. Do not implement your own token rotation or assume the old credential was remotely invalidated whenever local JSON changed.

The initial code exchange has a different persistence rule: handleCallback() saves its response as the new token rather than merging it with an old connection. Google documents offline refresh-token issuance for the first code exchange, and re-consent can be necessary when a previous authorization did not supply offline access. The project requests offline access and consent at connection time, checks for a returned refresh token, and refuses to overwrite a stored grant. Neither prompt=consent nor a missing local record guarantees a new refresh token under every account policy. Creating grant after grant does not fix a missing refresh token; Google imposes refresh-token issuance limits that can invalidate older tokens.

A refresh token can also stop working after revocation, prolonged disuse, token limits, time-limited consent, or policy changes. Not every rule applies to this scope: for example, Google's password-change invalidation condition names tokens containing Gmail scopes, which this project does not request. The seven-day external Testing rule does apply. The absence of an explicit refresh-expiry field does not mean the grant lasts forever. See Google's expiration conditions.

Use a lock for coordination, not for trust

withUserLock_ acquires a user lock before reading or changing this user's connection and passes that same lock object to the library. It releases the lock in finally, including when a callback, refresh, or storage operation throws. Authorization-link preparation, callback persistence, the read, inspection, revocation, and forgetting all cooperate with that scope. Another account is not forced to wait for this user's connection. The LockService contract describes that per-script, per-user boundary.

Confirmation dialogs happen before acquiring the lock because an Apps Script alert suspends execution and does not preserve locks. The lock is acquired after confirmation and the token is then read afresh. It is held during this tiny read so a cooperating revoke/forget action cannot interleave with it.

A lock is not a permission check, does not hide secrets from editors, and does not lock Google's authorization server or the spreadsheet against human edits. There is no transaction covering token issuance and a property write. Google can issue or refresh a token and the local save can fail; Google can accept revocation and the script can stop before local cleanup. Stopping a run or closing its browser tab cannot recall a request already sent. Do not infer exactly-once refresh, immediate access removal, or a complete rollback from a local flag.

Diagnose the failed boundary

All application entrypoints replace raw exceptions with approved messages. The library exposes the last refresh failure through getLastError(), but its token errors are text containing provider fields rather than a typed HTTP result. The wrapper recognizes only allowlisted leading invalid_grant and client-error codes from the inspected library format. It never prints the rest of a token error, provider description, or response body. Unclassified failures remain unclassified; they do not trigger consent, token deletion, or an automatic retry.

Only the read has an automatic retry. Authorization-code exchange, refresh, and revocation do not. In particular, replaying a code after an uncertain token exchange can fail even if Google already issued a token. If inspection cannot establish a usable local connection, review or remove the dedicated app's access in Google Account settings before reconnecting. That independent removal path still works when local storage is damaged or missing.

Deny, revoke, and remove the local record

For a denial exercise, start with no grant for this dedicated client and inspectLocalGrant reporting absent. Open one authorization link, decline Google's requested access, and observe the denial page. Inspect again; no new local token should exist. Running readSample should stop without a resource result. Do not use a previously connected client and assume declining an additional request revokes its older grant. If you now want the read, create a fresh link and consent once.

Revocation asks Google to remove access. Forgetting removes the library's local record.

The menu action Revoke grant and forget tokens asks for confirmation and makes one POST to https://oauth2.googleapis.com/revoke. It sends the stored refresh token, or the access token if that is all that is available, in a form-encoded body, never a query string. It does not refresh first. Google documents either token type as acceptable; revoking an access token with a corresponding refresh token revokes that refresh token too. Only an HTTP 200 confirmation permits local service.reset(). See Google token revocation.

A 400, another status, or a transport failure is not treated as confirmed removal. The source retains local tokens and directs you to Google Account settings. If Google returned 200 but local deletion failed, the message says so. A local record can outlive its grant; a missing local record can also leave a remote grant behind. Revocation affects the authorization represented by the token. It is not limited to the twelve cells this code chose, which is another reason to use a dedicated client and Cloud project.

To remove or inspect access independently, open your Google Account's linked apps page, choose Access to your Google Account, select OAuth practice reader, then See details. Review the actual name and permissions. To revoke that connection, select Remove access > Confirm. These are the current steps in Google Account Help. Select only this practice app; removing access revokes future token use without erasing data or logs already recorded.

Use the following sequence to observe a revoked grant before local cleanup:

1. Begin with a successful readSample, a present local token, and no open consent tabs. Note the access-expiry time from inspectLocalGrant.

2. Revoke this dedicated client's access through Google Account settings without running Forget local tokens yet.

3. Run inspectLocalGrant. Predict that it can still report a local token, because it did not contact Google.

4. Run readSample once. A token that still passes the local expiry check reaches Sheets and receives 401; an expired access token fails refresh with invalid_grant, and a known refresh expiry stops locally. If a read still succeeds, recheck the selected app and account in Google Account settings.

5. To observe the refresh rejection specifically, wait past the access-expiry time without reconnecting, then run readSample once.

6. After confirming remote removal, choose Forget local tokens only and confirm. Run inspectLocalGrant again: it should report absent. This action calls reset() for only the current user's named service; it does not erase unrelated properties or send a revocation request.

After a successful programmatic Revoke grant and forget tokens, local absence is also expected. A subsequent readSample cannot demonstrate that Google's token rejection works, because there is no longer a local token to send. Use the remote-first sequence above when you want that observation.

To restore the exercise, verify the same private configuration, authorize again, and rerun the supplied read. Before replacing a client ID, secret, script, or service name, close consent tabs, remove the old dedicated app's grant, and clear its old local tokens. Changing a configuration value alone does not migrate a connection or revoke the previous credentials.

When finished, revoke the remote grant, run Forget local tokens only, and delete the four practice Script Properties in Project Settings > Script Properties. Delete any downloaded client-credentials file from your workstation.

Next, classify and summarize spreadsheet rows with AI models and validated JSON.