OAuth for separate accounts

Read three fictional requests from a private spreadsheet through the Sheets REST API. 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 show an authorization link in a dialog. It reads the data with UrlFetchApp and the Sheets REST API instead of 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 approach.

Here you use a dedicated OAuth client to learn the authorization-code flow. In that flow, the user approves access in the browser and Google returns a short-lived code. The script exchanges the code for tokens, which it stores, refreshes, and later revokes. You need this flow when your script keeps its own connection to a provider. This exercise uses Google as the provider because Google documents each step: consent, callback, refresh, and revocation. 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 reads only one fixed spreadsheet ID and a twelve-cell range, but the token itself can read every spreadsheet the authorizing account can open. Sheets scopes cannot be limited to an individual tab or cell range. The per-file drive.file scope needs a different way to pick and open files, so it cannot replace this scope for an arbitrary existing spreadsheet ID. See the Sheets scope reference.

Choose a user grant or keyless service-account impersonation

This kind of script can get a Google API access token in three ways. Choose by which account the API call should act as, and what that account may reach.

Keyless service-account impersonation uses two tokens and two identities. First, the script sends the effective user's token from ScriptApp.getOAuthToken() 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 script uses the user's token only for the request that creates the service-account token. The final API call uses the service-account token.

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. When the workflow needs only iam.serviceAccounts.getAccessToken, prefer a reviewed custom role that contains only that permission.

4. Add script.external_request and an IAM-authorizing scope such as cloud-platform to the Apps Script manifest. Check how much access each scope allows.

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 short batch of calls 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. A longer lifetime gives a leaked token more time to be used, and a short Apps Script call does not need it. Request only the scope the final API needs, and request a new token only after expiry or an authorization error. Never log either token.

This flow avoids downloading a service-account JSON key and storing a private key in Script Properties. It does not use domain-wide delegation, so the service account does not act as a Workspace user. To read a spreadsheet as the service account, share that file with the service account or give it access in another authorized way. The target API checks both the service account's permission on the resource and the token's scopes.

The exercise below does not use impersonation. It shows how a separate user grant is approved, stored, refreshed, and revoked. Use impersonation when a fixed non-human account should make the call. Use a user grant when the remote action must belong to the person who consented.

Keep the identities separate

The account that runs a menu action or editor function is the Apps Script user for that execution. getUserProperties() opens that user's property store. You then choose an account in the browser on Google's authorization screen. That authorizing account supplies the Sheets permission in the separate access token. Choosing an account in the browser does not update Script Properties, and it does not ensure that the two accounts match.

For this exercise, use the same approved account for every role: spreadsheet owner, script user, OAuth test user, and the account you select at consent. Use a browser profile with only that account signed in. The script asks Google to show an account chooser. It does not request email or OpenID scopes, so it cannot check that the account you chose is the Apps Script user. If you choose another account, that account's file permissions decide what the REST request can read.

The callback is an Apps Script execution that Google reaches through the authenticated /usercallback address. It is not a web app that runs as the person who deployed it. Use the same script user and browser session from the link to the callback, so that the later read in the Apps Script editor finds that user's stored grant. If the callback page does not open, or a later inspection reports no local token, check which script and account you are using before you create 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. Do not use cells outside A1:C4.

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

5. Replace Code.gs and add an HTML file named Authorization with the Authorization.html contents. 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 a library, which the editor lists under Libraries. The public library ID is 1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF. The manifest pins library version 43 with developmentMode: false. This ID and numeric version appear together in Google's own consumer manifest.

The library repository separately names its latest tagged release v1.43.0. Do not paste that GitHub release name into the manifest's numeric version field.

After saving, inspect the Libraries entry. To add the library through the editor instead, click + beside Libraries, paste the ID, choose Look up, select version 43, keep the 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. You do not need to deploy anything, because the manifest has no webapp section. Its two scopes allow the external fetches and the HTML dialog that shows the link. Neither scope gives access to spreadsheet data. Save and reload the spreadsheet to see the OAuth Practice menu. The simple onOpen handler only adds that menu.

Register the callback and configure the client

A client ID identifies your application to the authorization server. A client secret is this web client's private credential, which only the server sends to Google. 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 builds the URI from the ID of the script that includes the library and /usercallback. It does not use the /exec or /dev web-app addresses. The state token names the callback function, so no web-app deployment is needed.

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.

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. Google matches the redirect URI exactly, 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 shows a warning that the app is unverified and in testing. Current Google documentation says those authorizations expire after seven days, and an issued offline refresh token expires with them. The exception for requests that contain only basic name, email, profile, or OpenID scopes does not apply to this Sheets scope. As a result, a refresh token from this app cannot keep an unattended script working past that limit, even when the first refresh succeeds. See Manage App Audience and refresh token expiration.

To remove the seven-day limit, you would move the app to In production, which adds consent-screen and verification requirements. Keep this practice app in Testing for the exercise. 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. This check confirms that the required values are present and correctly formatted. It runs before the script creates an authorization link or sends any request to Google. It does not prove that the credentials work. Google checks the secret, the redirect registration, and file access when you authorize and read. You can still show the redirect URI and clear local tokens before the client configuration is complete.

Script Properties are shared by every user of the script. The OAuth library stores its token in PropertiesService.getUserProperties(), so each user's token stays out of the shared store. The fixed service name book-sheets-read identifies this connection within that store. The library storage source saves the token as JSON under its service prefix and reads it back in later executions. Leave those values alone. Do not hand-edit an expiry or paste in a token.

Both stores are on Google's servers, and neither hides values from script editors. An editor can read Script Properties. An editor can also change the code, and when a user later runs that code, it can access that user's token store. PropertiesService describes who can read each store. It adds no encryption that hides values from editors. Anyone who can edit the parent spreadsheet can edit a bound script, so keep that spreadsheet's editor list short. Keep this project private, and review the pinned library as code that runs with the project's permissions.

The project does not use CacheService. For heavier use, the library supports a cache with the same per-user scope as the property store. Google can clear a cache at any time, so the token must still be saved in properties. A cached token also does not prove that the grant is still valid. A script cache is shared by all users of the script, so it is the wrong place for one user's token. The library also keeps values in memory during one execution. This project creates its library objects inside the locked operation instead of keeping one in a global variable, where it could hold outdated token data.

Never log or return the service object, property contents, authorization URL, callback request, token object, request headers, or provider response body. The client ID appears in the authorization URL, but keep the client secret, every token, the state parameter, and the 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 script checks configuration and refuses to replace any existing local token. It shows the link in Authorization.html. Showing 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. Google asks for consent only when you start a connection, not on every read. Offline access asks for a grant that includes a refresh token. It does not mean the script works without a network connection.

State links an authorization attempt to the callback that should handle it. It also helps defend against cross-site request forgery, where someone tries to make your app accept an authorization response it did not ask for. The library uses Apps Script's encrypted StateTokenBuilder, records the callback function and service name, and makes the state valid for one hour. Google returns that state to the /usercallback address. Apps Script checks it and calls authCallback(request). Use the library's StateTokenBuilder support. Do not replace it with a fixed string or a doGet(e) handler.

handleCallback() returns false when Google returns error=access_denied. 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 used only for this exchange. The script never sends it to Sheets. Google's authorization-code steps allow each code to be exchanged once, so reloading a finished callback tab cannot exchange the same code again.

After a successful exchange the library saves the token before returning. The callback then checks the fields this exercise needs: a bearer access token, expiry information, the required granted scope, and a refresh token. It shows either a short success message or an error message from this script. If the scope or refresh-token check fails after the exchange, a local token and a remote grant can still exist even though the page does not say success. An error does not undo consent. Inspect the local status and follow the steps in Deny, revoke, and remove the local record.

<!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>

createHtmlOutputFromFile serves the dialog without inserting server values into its markup. The page builds the link in the browser. google.script.run.getAuthorizationUrl() asks the server for the URL. Its success handler creates the link with document.createElement('a') and sets href, target, rel, and textContent as DOM properties, so the URL is never inserted as an HTML string. The link opens Google's consent page in a new tab instead of inside the Apps Script dialog. The link holds no client secret or token.

The callback page shows only a short message, with no images, analytics, redirects, or raw provider text. appendUntrusted also escapes that message as text.

After success, close the callback tab and the spreadsheet dialog. If a token is already saved, the script rejects a second callback, so that callback cannot overwrite a completed connection. Before you revoke or forget a connection, close any consent tabs you abandoned. If you finish an open consent flow later, it grants access again.

Read, repeat, and change a result

In the 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 only to the token endpoint. No token appears in the resource URL.

The request calls spreadsheets.values.get for exactly 'OAuth sample'!A1:C4 and requests majorDimension=ROWS and UNFORMATTED_VALUE. Through the partial-response fields parameter, it selects only range,majorDimension,values. Unformatted values keep the points as numbers instead of locale-formatted text.

The script accepts the response only when all of these checks pass:

• The status is 200 and the content type is JSON.

• The body has at most 8,192 characters.

• The range and row orientation match the request.

• The table has four rows and three columns.

• The headers match.

• Each request ID is different.

• Each status is Ready or Hold.

• Each point value is an integer from zero through twenty.

A Sheets ValueRange omits empty trailing rows and columns, so clearing a required sample cell causes a data error. The script does not count the missing value as zero. It does not read cells outside the fixed range.

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 prove that a refresh happened.

2. In the practice 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 see how the script handles bad data, change only C3 to the text two, then read again. Expect a sample-data error instead of a partial total. Restore the numeric value 2 before continuing. A data failure does not require another OAuth grant.

The GET helper retries once after a 500, 502, 503, or 504 response that has no Retry-After header. It waits a random one to two seconds before the retry. A retry repeats only the read, with the same access token. If both attempts fail, the script stops. A 429, or any failed response with a Retry-After header, stops the run at once, because the script does not try to fit a wait of unknown length into one execution. Google's Sheets limits guide describes time-based quotas and truncated backoff.

The body-size check runs after UrlFetchApp has downloaded the response, so it does not shorten the run. Locks, fetches, and platform quotas still set the 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 a usable token may be able to use its access. Token storage and logs therefore affect who can use this grant.

The library's hasAccess() implementation first checks the stored access token's expiry. If the token is still usable, it returns without contacting Google, so it does not ask whether the grant was revoked. Near expiry it can take the configured lock, read the token again, and refresh it. getAccessToken() runs that check itself and throws when no usable token is available. Because hasAccess() can refresh and change the stored token, 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 steps use 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 does not always include a new refresh token. The library keeps the old refresh token when a refresh response omits one and saves the replacement when one is returned. It also keeps known refresh-expiry metadata when the new response omits it. Do not write your own token rotation. A change in the local JSON does not mean Google invalidated the old credential.

The library saves the result of the first code exchange differently. handleCallback() saves its response as the new token and does not merge it with an old connection. Google documents that the first code exchange issues the offline refresh token. If an earlier authorization did not include offline access, you may need to consent again. The project requests offline access and consent when it connects, checks that a refresh token came back, 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 more grants does not fix a missing refresh token. Google limits how many refresh tokens it issues, and going over that limit can invalidate older tokens.

A refresh token can also stop working after revocation, a long period without use, token limits, time-limited consent, or policy changes. Not every rule applies to this scope. For example, Google's password-change rule names tokens that contain Gmail scopes, which this project does not request. The seven-day Testing limit described earlier does apply. A missing refresh-expiry field does not mean the grant lasts forever. See Google's expiration conditions.

Use a lock so two runs do not change the token at once

withUserLock_ takes a user lock before it reads or changes this user's connection and passes the same lock object to the library. It releases the lock in finally, including when a callback, refresh, or storage step throws. Preparing the authorization link, saving the callback result, the read, inspection, revocation, and forgetting all use the same user lock. Another account does not wait for this user's lock. The LockService reference describes user locks, which apply to one user of one script.

Confirmation dialogs appear before the script takes the lock, because an Apps Script alert pauses execution and does not keep locks. After you confirm, the script takes the lock and reads the token again. It holds the lock during this short read, so a revoke or forget action cannot run in the middle of it.

The lock only coordinates this user's runs of this script. It does not check permissions, hide secrets from editors, hold anything at Google's authorization server, or stop people from editing the spreadsheet. No transaction covers the token request and the property write. Google can issue or refresh a token and then the local save can fail. Google can accept a revocation and then the script can stop before local cleanup. Stopping a run or closing its browser tab cannot recall a request already sent. As a result, a local flag cannot show that a refresh happened exactly once, that access was removed at once, or that a failed step was fully undone.

Find out which step failed

Every menu action, editor function, and callback replaces raw exceptions with messages written for this script. The library reports the last refresh failure through getLastError(), but its token errors are text containing provider fields, not a typed HTTP result. The script recognizes only four codes at the start of that text: invalid_grant, invalid_client, unauthorized_client, and deleted_client. It never prints the rest of a token error, a provider description, or a response body. Any other failure gets a general message. The script does not start consent, delete tokens, or retry because of it.

Only the read retries automatically. The code exchange, refresh, and revocation do not. Replaying a code after an uncertain token exchange can fail even if Google already issued a token. If inspection cannot show a usable local connection, review or remove the dedicated app's access in Google Account settings before reconnecting. Google Account settings work even 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. Run inspectLocalGrant again. No new local token should exist. Running readSample should stop without reading any data. If the client was already connected, declining a new request does not revoke 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 accepts either token type. Revoking an access token with a corresponding refresh token revokes that refresh token too. The script calls the local service.reset() only after Google confirms with HTTP 200. See Google token revocation.

The script does not treat a 400, another status, or a transport failure as confirmed removal. It keeps the local tokens and directs you to Google Account settings. If Google returned 200 but local deletion failed, the message says so. Local tokens can remain after remote revocation, and deleting local tokens can leave the remote grant active. Revocation removes the whole authorization that the token represents: every spreadsheet the account can open, not only the twelve cells this code reads. Use a dedicated client and Cloud project to keep this revocation separate from other applications.

To inspect or remove access without the script, open your Google Account's linked apps page, choose Access to your Google Account, select OAuth practice reader, then See details. Review the name and permissions shown there. 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. A known refresh expiry stops the read before it contacts Google. 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 Revoke grant and forget tokens from the menu, inspection also reports absent. A later readSample cannot show Google rejecting the token, because there is no local token left to send. To see that rejection, use the sequence above, which revokes access in Google Account settings first.

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, Use AI models from Apps Script.