Skip to content
Skip to chapter

Chapter 42

Advanced integrations and data

18 min read

Authenticate with a Cloud service account

Read a spreadsheet as a service account. The Apps Script user requests a five-minute token through IAM, with no key file or domain-wide delegation.

By the end

Set up a service-account read without a key, tell which account each request runs as and which permission it needs, and remove token and file access, knowing that neither revokes a token already issued.

Chapter navigation

Read two fictional tasks from a private spreadsheet as a service account. The Apps Script user requests a five-minute access token for that account, and the script uses the token for one Sheets REST read and logs a count. You do not download a private key file, and the service account does not impersonate any Workspace user.

For a workflow owned by a user, the built-in Sheets service or a REST request authorized by the user is usually the better fit. Choose a service account when an application needs its own identity and access to specific files, independent of a person's file access.

Start after runtime and permissions, HTTP requests, and state across runs. The supplied project is standalone and leaves earlier projects unchanged.

Follow the two tokens

A principal is an identity that Google can grant access to. This run uses two principals and two access tokens:

StepPrincipal presented to GoogleCredentialPermission that matters
Start a manual editor runThe signed-in Apps Script user, as the effective userApps Script manages the user's authorizationThe user must be allowed to run the script and approve its manifest scopes.
Request a service-account tokenThat same userScriptApp.getOAuthToken()The user needs iam.serviceAccounts.getAccessToken on the target service account, plus an accepted OAuth scope.
Read the spreadsheetThe dedicated service accountThe new accessToken returned by IAM CredentialsThe account needs access to the file through sharing, and the token needs a Sheets read scope.

ScriptApp.getOAuthToken() returns the effective user's token. Calling it does not make the execution run as the service account. Only the HTTP request whose Authorization header carries the new service-account token runs as the service account. A SpreadsheetApp call in the same execution would still run as the Apps Script user. This project makes no such call.

The first request uses Google's IAM Credentials generateAccessToken method. IAM (Identity and Access Management) controls who may request that credential. The second request uses Sheets spreadsheets.values.get. Google's Workspace credential guide explains that file sharing and Cloud IAM are separate, and that Cloud roles do not grant access to a Google spreadsheet.

Your own access to the practice spreadsheet lets you prepare it, but it does not let the service account read it. Sharing the file with the service account does not let you request its tokens. Both grants must exist.

This design still needs a person to run it. It does not give Apps Script an attached service identity, a way to sign in as the service account, or a way to run unattended. Moving it to a trigger or web app would need a separate review of who the script runs as and who can cause a token to be requested.

Approve a small practice environment

Use a new standalone script, a new blank spreadsheet, and a dedicated service account in an isolated standard Cloud project. Do not use a default Compute Engine service account, a production automation account, or a spreadsheet that contains business data.

Ask the relevant owners to approve each of these setup jobs:

Setup jobPermission neededNeeded by the caller at run time?
Supply or create the isolated standard Cloud projectAn approved Cloud project owner or project-creation administratorNo.
Enable the three named APIsserviceusage.services.enable, commonly through Service Usage AdminNo. The setup administrator needs it.
Create the dedicated service accountCreate Service Accounts, roles/iam.serviceAccountCreator, or equivalent permissionsNo. The setup administrator needs it.
Define the project custom roleRole Administrator, roles/iam.roleAdmin, or equivalent permissionsNo. The role administrator needs it.
Grant and later remove access on this service accountService Account Admin on that account, or equivalent policy-management permissionsNo. The policy administrator needs it.
Associate the script with the standard project and configure consentProject Browser and OAuth Config Editor, or equivalent permissionsOnly the person doing this setup.
Share the practice spreadsheetIts owner or an authorized sharer, subject to Workspace policyOnly the data owner.
Request one tokenThe custom one-permission role on this service accountYes. Only the exact caller user.

An administrator can do the Cloud setup without giving those setup permissions to the caller. See Google's guides to creating service accounts, custom roles, and managing access to service accounts. The caller's user token carries that user's IAM permissions within the granted scopes, so prefer a caller account with no unrelated IAM administrator roles.

Ask the Cloud and Workspace administrators to check their policies before you continue. Any of these can block the exercise:

  • Organization constraints can prevent creating the service account.

  • IAM policies can prevent granting the role to the caller.

  • OAuth application controls can block consent.

  • Drive sharing settings can block sharing with the service account.

A service account is not a user in your Workspace domain, even when your organization owns its Cloud project. Drive's external-sharing indicator also treats service-account access as external.

Policies that restrict key creation can stay enabled, because this flow uses no user-managed key. The five-minute token lifetime also needs no policy that allows longer token lifetimes.

Create the service account and grant only minting permission

In this lesson, minting a token means asking IAM Credentials to issue a new short-lived access token for the service account.

Record four values for your setup in a private note. They are identifiers, and you can write them down without exposing a credential:

  • PRACTICE_CLOUD_PROJECT_ID: the isolated project's alphanumeric ID.

  • PRACTICE_CLOUD_PROJECT_NUMBER: that project's numeric project number, which Apps Script uses.

  • SETUP_ADMIN_EMAIL: the approved account that does the Cloud setup.

  • CALLER_USER_EMAIL: the exact Google account that will run the Apps Script function.

An administrator can supply an existing isolated project. If a new project is approved, open the Cloud console's project selector, choose New project, select the approved organization and parent folder, and create it with a recognizable practice name. Record its ID and number from the project settings. Do not reuse the inaccessible default Cloud project that Apps Script creates at first.

The approved administrator runs the following commands. The script does not. Use a Google Cloud CLI environment that is already authorized, such as Cloud Shell opened for the selected practice project. Check which account is signed in first, and do not turn on an impersonation configuration. Replace every uppercase placeholder, then run one command at a time. Do not print access tokens or turn on HTTP debug logging.

Enable these APIs in that one project:

Source
gcloud services enable iam.googleapis.com iamcredentials.googleapis.com sheets.googleapis.com \
  --project=PRACTICE_CLOUD_PROJECT_ID --account=SETUP_ADMIN_EMAIL

The IAM API handles the account and role setup. The IAM Service Account Credentials API handles the token request. The Sheets API handles the data read. Enabling an API does not grant an IAM role or share a file. This project calls the REST APIs directly, so you add nothing under Apps Script Services.

Create the account with the ID practice-sheet-reader:

Source
gcloud iam service-accounts create practice-sheet-reader \
  --display-name="Practice sheet reader" \
  --project=PRACTICE_CLOUD_PROJECT_ID --account=SETUP_ADMIN_EMAIL

Its email has the form practice-sheet-reader@PRACTICE_CLOUD_PROJECT_ID.iam.gserviceaccount.com. Copy the actual email from IAM. Do not give the service account any project data role, do not add a key, and do not configure domain-wide delegation.

A service account can hold access to other resources, and it is also a resource that other principals can be given access to. The next command treats the service account as a resource and controls who may request its tokens.

Download iam-role.yaml. It defines a custom role that contains only the permission the token endpoint requires. Google's custom-role support table marks iam.serviceAccounts.getAccessToken as supported.

iam-role.yaml
title: Practice access token caller
description: Generate OAuth access tokens for the dedicated practice service account.
stage: GA
includedPermissions:
  - iam.serviceAccounts.getAccessToken

Download iam-role.yaml

Create the role in the practice project, then bind it on this service account for the caller only:

Source
gcloud iam roles create practiceAccessTokenCaller \
  --file=iam-role.yaml \
  --project=PRACTICE_CLOUD_PROJECT_ID --account=SETUP_ADMIN_EMAIL

gcloud iam service-accounts add-iam-policy-binding \
  practice-sheet-reader@PRACTICE_CLOUD_PROJECT_ID.iam.gserviceaccount.com \
  --member="user:CALLER_USER_EMAIL" \
  --role="projects/PRACTICE_CLOUD_PROJECT_ID/roles/practiceAccessTokenCaller" \
  --condition=None \
  --project=PRACTICE_CLOUD_PROJECT_ID --account=SETUP_ADMIN_EMAIL

The role is defined in the project, but its binding is on one service account. Do not replace it with a project-wide binding. --condition=None means the grant never expires on its own, so the administrator must remove it during cleanup. New accounts and policy changes can take several minutes to take effect. Do not grant broader roles while you wait.

Google's general impersonation guide uses the predefined Service Account Token Creator role. That role can do more than request access tokens. For example, it can sign JWTs and blobs. The custom role avoids granting those extra permissions. Service Account User, which lets a principal attach or act as an account in other workflows, does not include getAccessToken.

The custom role still lets the caller request any token that IAM Credentials allows for this account. It does not force every token to use this script's read-only scope, five-minute lifetime, or spreadsheet ID. Someone with this role can use other code to request other permitted scopes and lifetimes. For that reason, the dedicated account must have no unrelated Cloud grants, no inherited data access, and no other shared files.

Share only the fictional spreadsheet

  1. Create a new blank spreadsheet named Service identity practice - test under an approved account. Do not copy a production workbook.

  2. Rename the first tab Practice.

  3. Download practice-rows.csv, paste it into A1, and use Data > Split text to columns > Comma if the values stay in one column. Confirm that A1:C3 contains the three headers and the two rows below them.

  4. Open Share. Leave General access set to Restricted. Add the dedicated service account's email as Viewer, clear Notify people, and save the grant. Service accounts have no inbox for the notification.

  5. Record the spreadsheet file ID from the URL, between /d/ and /edit.

practice-rows.csv
Task,Owner,Status
Confirm room booking,Ana,Open
Prepare agenda,Ben,In progress

Download practice-rows.csv

All nine cells in A1:C3 should contain plain text, with no formulas, extra rows, or blank trailing cells.

Viewer sharing applies to the whole spreadsheet, not only A1:C3. The spreadsheets.readonly scope in the new token is not limited to one file either. A token with that scope can read any spreadsheet the service account can access. The script requests only nine cells, but nothing in IAM restricts the account to that range, so use a dedicated file and account. This values read needs no Drive API scope.

Configure the standalone Apps Script project

Create a new project from the Apps Script dashboard and name it Service identity practice - test. Do not create it from the spreadsheet's Extensions menu, because the exercise does not need a bound script.

In the standard Cloud project, open Google Auth platform and configure the application's Branding, Audience, and Data Access. Use an approved support and contact address and the practice application name. Choose the audience based on the caller:

  • Use Internal only if it is available and the caller belongs to that organization.

  • Otherwise, keep the external app in Testing and add only the approved caller under Test users.

Register the two manifest scopes below wherever the consent configuration requires them. This consent screen authorizes the script for the user. The service account has no consent screen. See Google's consent configuration guide.

In Apps Script Project Settings, under Google Cloud Project, choose Change project, enter the numeric PRACTICE_CLOUD_PROJECT_NUMBER, and choose Set project. Check that it is the same standard project you used for the service account and APIs. Google's project-association guide requires Project Browser and OAuth Config Editor permissions or equivalents. Switching projects affects authorization, and switching back to the original default project does not undo that. This is another reason to use a new script.

Replace the new project's Code.gs with the complete source in the next section. In Project Settings, turn on Show "appsscript.json" manifest file in editor and replace that manifest with the supplied file:

appsscript.json
{
  "timeZone": "Etc/UTC",
  "runtimeVersion": "V8",
  "oauthScopes": [
    "https://www.googleapis.com/auth/iam",
    "https://www.googleapis.com/auth/script.external_request"
  ]
}

Download appsscript.json

The caller requests the iam scope, which the token endpoint accepts, instead of the broader cloud-platform scope. script.external_request lets UrlFetchApp make the two HTTPS requests. Neither scope gives the user an IAM role. The manifest has no Sheets scope for the user, because the second token carries the Sheets permission. The script sets that token's scope in the body of the token request.

Apps Script manages the user's OAuth flow. Do not create a separate OAuth client secret, add an OAuth library, download a service-account JSON key, or save a refresh token.

Under Project Settings > Script Properties, add:

PropertyValue
PRACTICE_SERVICE_ACCOUNT_EMAILThe dedicated account's email, copied from Cloud IAM
PRACTICE_SPREADSHEET_IDThe practice spreadsheet's file ID, without the surrounding URL

Save the properties. The code checks that the email has the standard <name>@<project>.iam.gserviceaccount.com format and URL-encodes the spreadsheet ID into the fixed sheets.googleapis.com path.

Script Properties are not a safe place for secrets. Project editors can read the settings or change the code to read the values, and an editor's changed code can run with an authorized caller's permissions. This example uses properties only to keep your real identifiers out of the distributed code. Never store a private key or either access token there.

Read the complete program before authorizing it

Code.gs
/** @typedef {{ accessToken: string, expireTime: string, expiresAtMs: number }} PracticeAccessToken */

const PRACTICE_RANGE = 'Practice!A1:C3';
const PRACTICE_READ_SCOPE = 'https://www.googleapis.com/auth/spreadsheets.readonly';

/**
 * Mints one five-minute service-account token and reads nine practice cells.
 * Run manually in the standalone editor; no deployment or trigger is needed.
 * @returns {void}
 */
function readSharedPracticeSheet() {
  const serviceAccountEmail = getPracticeProperty_('PRACTICE_SERVICE_ACCOUNT_EMAIL');
  const spreadsheetId = getPracticeProperty_('PRACTICE_SPREADSHEET_ID');
  if (!/^[a-z0-9-]+@[a-z0-9-]+\.iam\.gserviceaccount\.com$/.test(serviceAccountEmail)) {
    throw new Error('Use the dedicated practice service account email from Cloud IAM.');
  }

  const token = mintPracticeAccessToken_(serviceAccountEmail);
  if (token.expiresAtMs - Date.now() < 30000) {
    throw new Error('The service account token has less than 30 seconds remaining. No read was attempted.');
  }

  const url = 'https://sheets.googleapis.com/v4/spreadsheets/' +
    encodeURIComponent(spreadsheetId) + '/values/' + encodeURIComponent(PRACTICE_RANGE) +
    '?majorDimension=ROWS&valueRenderOption=FORMATTED_VALUE';
  const body = fetchPracticeJson_(url, {
    method: 'get',
    headers: { Authorization: 'Bearer ' + token.accessToken },
    followRedirects: false,
    muteHttpExceptions: true
  }, 'Sheets read');
  const rows = parsePracticeRows_(body);
  let openCount = 0;
  for (let index = 1; index < rows.length; index += 1) {
    if (rows[index][2] === 'Open') openCount += 1;
  }

  Logger.log('Service account read: tasks: ' + (rows.length - 1) + ', open: ' + openCount + '.');
  Logger.log('Service account token expires at: ' + token.expireTime);
}

/**
 * Reads a non-secret identifier without printing its value.
 * @param {string} name
 * @returns {string}
 */
function getPracticeProperty_(name) {
  const value = PropertiesService.getScriptProperties().getProperty(name);
  if (value === null || value.trim() === '') {
    throw new Error('Set the ' + name + ' script property before running.');
  }
  return value.trim();
}

/**
 * Uses the effective Apps Script user's token only at IAM Credentials.
 * No delegate chain, domain user subject, private key or refresh token is used.
 * @param {string} serviceAccountEmail
 * @returns {PracticeAccessToken}
 */
function mintPracticeAccessToken_(serviceAccountEmail) {
  const callerToken = ScriptApp.getOAuthToken();
  if (callerToken === '') throw new Error('No Apps Script user access token is available.');
  const url = 'https://iamcredentials.googleapis.com/v1/projects/-/serviceAccounts/' +
    encodeURIComponent(serviceAccountEmail) + ':generateAccessToken';
  const body = fetchPracticeJson_(url, {
    method: 'post',
    contentType: 'application/json',
    headers: { Authorization: 'Bearer ' + callerToken },
    payload: JSON.stringify({ scope: [PRACTICE_READ_SCOPE], lifetime: '300s' }),
    followRedirects: false,
    muteHttpExceptions: true
  }, 'IAM token mint');

  if (!isPracticeRecord_(body) || typeof body.accessToken !== 'string' ||
      body.accessToken.trim() === '' || typeof body.expireTime !== 'string' ||
      !/^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}(?:\.\d{3}|\.\d{6}|\.\d{9})?Z$/.test(body.expireTime)) {
    throw new Error('IAM token mint returned an unexpected response shape.');
  }
  const expiresAtMs = Date.parse(body.expireTime);
  if (!Number.isFinite(expiresAtMs) || expiresAtMs <= Date.now()) {
    throw new Error('IAM token mint returned an invalid or expired expiration time.');
  }
  return { accessToken: body.accessToken, expireTime: body.expireTime, expiresAtMs };
}

/**
 * Accepts HTTP 200 and at most 20000 UTF-16 code units before parsing JSON.
 * UrlFetchApp has already buffered the response; this is not a network byte cap.
 * Error messages contain only this fixed operation label and an HTTP status.
 * @param {string} url
 * @param {UrlFetchApp.URLFetchRequestOptions} options
 * @param {string} label
 * @returns {unknown}
 */
function fetchPracticeJson_(url, options, label) {
  let response;
  try {
    response = UrlFetchApp.fetch(url, options);
  } catch {
    throw new Error(label + ' transport failed. No automatic retry was made.');
  }
  const status = response.getResponseCode();
  if (status !== 200) {
    throw new Error(label + ' failed with HTTP ' + status + '. Response content was not logged.');
  }
  const text = response.getContentText();
  if (text.length > 20000) throw new Error(label + ' response exceeded the practice size limit.');
  try {
    return JSON.parse(text);
  } catch {
    throw new Error(label + ' returned invalid JSON. Response content was not logged.');
  }
}

/**
 * Validates this fixture's three rows of three nonempty text cells, headers
 * and known statuses. Extra response fields are ignored, not extra rows.
 * @param {unknown} value
 * @returns {string[][]}
 */
function parsePracticeRows_(value) {
  if (!isPracticeRecord_(value) || typeof value.range !== 'string' ||
      value.majorDimension !== 'ROWS' || !isPracticeArray_(value.values) ||
      value.values.length !== 3) {
    throw new Error('Sheets read requires the complete Practice!A1:C3 fixture.');
  }
  /** @type {string[][]} */
  const rows = [];
  for (const row of value.values) {
    if (!isPracticeArray_(row) || row.length !== 3) {
      throw new Error('Each practice row must contain exactly three text cells.');
    }
    /** @type {string[]} */
    const cells = [];
    for (const cell of row) {
      if (typeof cell !== 'string' || cell.trim() === '' || cell.length > 200) {
        throw new Error('Practice cells must contain 1 to 200 characters of nonblank text.');
      }
      cells.push(cell);
    }
    rows.push(cells);
  }
  if (rows[0][0] !== 'Task' || rows[0][1] !== 'Owner' || rows[0][2] !== 'Status') {
    throw new Error('Restore the Task, Owner and Status headers in Practice!A1:C1.');
  }
  for (let index = 1; index < rows.length; index += 1) {
    const status = rows[index][2];
    if (status !== 'Open' && status !== 'In progress' && status !== 'Closed') {
      throw new Error('Practice statuses must be Open, In progress or Closed.');
    }
  }
  return rows;
}

/** @param {unknown} value @returns {value is Record<string, unknown>} */
function isPracticeRecord_(value) {
  return typeof value === 'object' && value !== null && !Array.isArray(value);
}

/** @param {unknown} value @returns {value is unknown[]} */
function isPracticeArray_(value) {
  return Array.isArray(value);
}

Download Code.gs

readSharedPracticeSheet is the only function to run. Its helpers keep the two Authorization headers apart:

  1. mintPracticeAccessToken_ gets the effective user's token and sends it only to iamcredentials.googleapis.com. The resource path contains the literal projects/-/serviceAccounts/. The API reference requires - in that position, not the Cloud project ID.

  2. The POST body requests scope: [spreadsheets.readonly] and lifetime: '300s'. It names no chain of delegate accounts and no user subject. The response must contain a nonempty accessToken and an expireTime in the future, in the documented UTC format.

  3. Before the Sheets GET, readSharedPracticeSheet requires the token to have at least 30 seconds left. This check rejects a nearly expired token early. It does not guarantee that the request finishes before the token expires. If the token expires during the request, the request fails, and the script does not request another token.

  4. The GET sends the new service-account token only to sheets.googleapis.com, requests Practice!A1:C3, and asks for rows of formatted values. Both requests turn off redirects so that no credential is forwarded to another destination.

  5. The response check accepts only this lesson's three-by-three grid of text, with the exact headers and known statuses. It counts Open in the two data rows and logs only the count and the token's expiration time. It does not log names, tasks, file IDs, token values, or response bodies.

The script rejects a successful response longer than 20,000 JavaScript UTF-16 code units before parsing the JSON. UrlFetchApp has already downloaded the whole response by then, so this does not limit network bytes. Each accepted cell can hold at most 200 characters. Both limits come from this lesson, not from the Sheets API.

A ValueRange response leaves out empty trailing rows and columns. As a result, if you clear the last status cell, the last row arrives with two cells, and the script rejects the row as incomplete. General Sheets code would need to handle missing trailing cells. This example rejects any unexpected shape instead of guessing at the missing data. JSON.parse checks only the syntax. The helper's field and array checks confirm the types the count depends on.

The token method normally allows at most one hour unless an organization policy permits longer tokens. This script requests only five minutes and uses the expireTime that the response returns. The token stays only in the execution's memory. The script keeps no token cache and no refresh token, so each later approved run makes a new token request.

Predict, run, change, and restore

Before you run the function, predict the count. There are two tasks, and only Confirm room booking has status Open.

Sign in as CALLER_USER_EMAIL, select readSharedPracticeSheet in the standalone project's Apps Script editor, and click Run. Review the application name and requested permissions before you approve consent. If authorization finishes without starting the function, click Run again.

For the supplied data, the execution log should show:

Source
Service account read: tasks: 2, open: 1.
Service account token expires at: <the returned UTC expiration timestamp>

The expiration time differs for each token request.

Now change only Practice!C3 from In progress to Open. Predict open: 2, then run the function again. You do not need to change code or deploy anything, because the input is in the spreadsheet. Set C3 back to In progress and run once more to confirm open: 1.

To see a data error, clear C3. Predict that the response check will reject the incomplete last row, then run once. Expect an error saying that the row must have exactly three text cells, and no count. This run still requests a token and reads the range, because the error happens after the response arrives. Set C3 back to In progress. The script never repairs cells.

Find which request failed

The error message names the request that failed, IAM token mint or Sheets read. The HTTP status alone does not always tell you why, because different policy, account, and API problems can produce the same status.

Observed stage or errorCheck before another approved run
Missing property or invalid practice-account emailThe two script properties. The script has not made any HTTP request.
Authorization prompt or blocked applicationThe signed-in caller, the standard-project association, the consent audience, the test-user entry, the manifest, and the administrator's OAuth controls. IAM grants cannot fix missing user consent.
IAM token mint with HTTP 401Whether the user's current authorization and token are accepted. Review and reauthorize as needed. Do not paste a token into the source.
IAM token mint with HTTP 403That the IAM Credentials API is enabled, the exact account email, and the caller's effective getAccessToken permission on that account, including any deny policies. An account that does not exist can also produce a permission error.
Sheets read with HTTP 403 or 404The Sheets API, the file ID, the service account's Viewer share, and Workspace restrictions. A successful token request does not show that the account can open the file, and the status does not show whether the file exists.
Sheets read with HTTP 400The Practice tab and range. Keep the endpoint and fixed range unchanged while you check the spreadsheet data.
Sheets read with HTTP 401, or not enough remaining token lifetimeToken expiry or whether the credential was accepted. Review the failure before the next manual run. Do not add code that writes automatically or falls back to a broader scope.
HTTP 429, 5xx, or a transport failureQuota, or a temporary service or network failure. Stop this run and try again later with approval. The script has no retries or polling.
Invalid JSON, unexpected shape, or a response that is too largeThe selected test file and its data. Do not log the body to debug it, because a token response contains a bearer credential.

On a transport failure, the script replaces the exception with a fixed message naming the request, so it does not echo request details that may be sensitive. On an HTTP error, it reports only the request name and the status. The script sets muteHttpExceptions so that it can read the status of a failed response. That setting does not turn a failed response into a success.

Investigate through the account and project settings, with administrators where needed. Do not turn on raw HTTP logging, paste an access token into a token-inspection website, or include a response body in an issue report. Both tokens are bearer credentials. Anyone who has one can use it, within its permissions, until it expires.

Keep domain-wide delegation a separate design

In this lesson, impersonation means that you are allowed to get a credential for the service account itself. Workspace domain-wide delegation means that an administrator lets a service account get credentials on behalf of domain users without each user giving consent. With the first, requests run as the service account. With the second, they run as a domain user.

Google's domain-wide delegation documentation requires a Workspace super administrator to approve the service account's client ID and an explicit list of scopes in the Admin console. Being a Cloud project owner does not give you that approval. A delegated request also names the domain user, for example in the JWT sub claim or a library's subject or delegated-user setting. A service account's email is not that user's email.

This lesson does not use domain-wide delegation. generateAccessToken has no subject field. Its optional delegates field lists a chain of service accounts and has nothing to do with Workspace domain-wide delegation. Do not put a user email in either field, and do not fix a sharing failure with a domain-wide grant. Sharing the file directly is enough for this one-file workflow.

If a real project needs data from many domain users, stop and design that integration separately with the Workspace security administrator. Decide which users and operations are allowed, choose only the scopes you need, decide who owns the code and the audit records, and agree on how grants are removed and how long credentials last. The user OAuth lesson covers another option for when each user's own consent is appropriate.

A JSON key file adds risk and does not fix a missing permission. Google's key-management guidance recommends avoiding user-managed keys when there is an alternative. Anyone with a leaked private key can request new tokens until the key is disabled, deleted, or expires, or until the service account itself is disabled or deleted.

Storing the key in Script Properties instead of the source does not hide it from project editors or their changed code. Requesting tokens without a key avoids that long-lived secret. You still need to protect the caller account, the IAM bindings, and the list of script editors.

Withdraw access and retire only the practice resources

Each step below stops a different kind of access:

ControlWhat it changesWhat it does not prove
Remove the caller's role binding for token requestsAfter IAM propagation, future token requests lose this grant, unless another grant still allows them.That tokens already issued for the service account were revoked.
Remove the service account's Viewer sharingFuture file requests should fail once the change takes effect, unless another sharing path still grants access.That the token itself is invalid or cannot access other resources.
Wait until the token's returned expiration timeThat token can no longer authorize new requests.That the caller cannot request another token, or that data already read has been erased.
Delete the two Script PropertiesThe script no longer knows which account and spreadsheet to use.That any IAM or sharing grant was removed.
warning

Google documents that service-account access tokens are not revocable and stay valid until they expire. IAM access changes propagate gradually and typically take several minutes to take effect. Drive sharing changes have their own delay.

When you finish, restore the data and stop running the function. Remove the dedicated service account in the practice spreadsheet's Share dialog. Ask the administrator to remove the caller's Practice access token caller role binding from the practice service account in IAM (Principals with access). To see each failure on its own, remove the Viewer sharing first and keep the role binding, so that the Sheets read fails. Then remove the role binding, so that the token request fails once the change takes effect.

Finally, delete PRACTICE_SERVICE_ACCOUNT_EMAIL and PRACTICE_SPREADSHEET_ID from the standalone script's Script Properties, and move the practice spreadsheet and script project to the trash. Delete the dedicated service account, the custom role, or the isolated Cloud project if you no longer need them.

Next, read Audit Workspace users and events with Admin SDK to inspect domain-wide user posture and external Drive sharing with read-only Admin SDK services.

Project files

Complete source files for this chapter's examples.

Keyless service-account read of a shared practice spreadsheet

Code.gs
/** @typedef {{ accessToken: string, expireTime: string, expiresAtMs: number }} PracticeAccessToken */

const PRACTICE_RANGE = 'Practice!A1:C3';
const PRACTICE_READ_SCOPE = 'https://www.googleapis.com/auth/spreadsheets.readonly';

/**
 * Mints one five-minute service-account token and reads nine practice cells.
 * Run manually in the standalone editor; no deployment or trigger is needed.
 * @returns {void}
 */
function readSharedPracticeSheet() {
  const serviceAccountEmail = getPracticeProperty_('PRACTICE_SERVICE_ACCOUNT_EMAIL');
  const spreadsheetId = getPracticeProperty_('PRACTICE_SPREADSHEET_ID');
  if (!/^[a-z0-9-]+@[a-z0-9-]+\.iam\.gserviceaccount\.com$/.test(serviceAccountEmail)) {
    throw new Error('Use the dedicated practice service account email from Cloud IAM.');
  }

  const token = mintPracticeAccessToken_(serviceAccountEmail);
  if (token.expiresAtMs - Date.now() < 30000) {
    throw new Error('The service account token has less than 30 seconds remaining. No read was attempted.');
  }

  const url = 'https://sheets.googleapis.com/v4/spreadsheets/' +
    encodeURIComponent(spreadsheetId) + '/values/' + encodeURIComponent(PRACTICE_RANGE) +
    '?majorDimension=ROWS&valueRenderOption=FORMATTED_VALUE';
  const body = fetchPracticeJson_(url, {
    method: 'get',
    headers: { Authorization: 'Bearer ' + token.accessToken },
    followRedirects: false,
    muteHttpExceptions: true
  }, 'Sheets read');
  const rows = parsePracticeRows_(body);
  let openCount = 0;
  for (let index = 1; index < rows.length; index += 1) {
    if (rows[index][2] === 'Open') openCount += 1;
  }

  Logger.log('Service account read: tasks: ' + (rows.length - 1) + ', open: ' + openCount + '.');
  Logger.log('Service account token expires at: ' + token.expireTime);
}

/**
 * Reads a non-secret identifier without printing its value.
 * @param {string} name
 * @returns {string}
 */
function getPracticeProperty_(name) {
  const value = PropertiesService.getScriptProperties().getProperty(name);
  if (value === null || value.trim() === '') {
    throw new Error('Set the ' + name + ' script property before running.');
  }
  return value.trim();
}

/**
 * Uses the effective Apps Script user's token only at IAM Credentials.
 * No delegate chain, domain user subject, private key or refresh token is used.
 * @param {string} serviceAccountEmail
 * @returns {PracticeAccessToken}
 */
function mintPracticeAccessToken_(serviceAccountEmail) {
  const callerToken = ScriptApp.getOAuthToken();
  if (callerToken === '') throw new Error('No Apps Script user access token is available.');
  const url = 'https://iamcredentials.googleapis.com/v1/projects/-/serviceAccounts/' +
    encodeURIComponent(serviceAccountEmail) + ':generateAccessToken';
  const body = fetchPracticeJson_(url, {
    method: 'post',
    contentType: 'application/json',
    headers: { Authorization: 'Bearer ' + callerToken },
    payload: JSON.stringify({ scope: [PRACTICE_READ_SCOPE], lifetime: '300s' }),
    followRedirects: false,
    muteHttpExceptions: true
  }, 'IAM token mint');

  if (!isPracticeRecord_(body) || typeof body.accessToken !== 'string' ||
      body.accessToken.trim() === '' || typeof body.expireTime !== 'string' ||
      !/^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}(?:\.\d{3}|\.\d{6}|\.\d{9})?Z$/.test(body.expireTime)) {
    throw new Error('IAM token mint returned an unexpected response shape.');
  }
  const expiresAtMs = Date.parse(body.expireTime);
  if (!Number.isFinite(expiresAtMs) || expiresAtMs <= Date.now()) {
    throw new Error('IAM token mint returned an invalid or expired expiration time.');
  }
  return { accessToken: body.accessToken, expireTime: body.expireTime, expiresAtMs };
}

/**
 * Accepts HTTP 200 and at most 20000 UTF-16 code units before parsing JSON.
 * UrlFetchApp has already buffered the response; this is not a network byte cap.
 * Error messages contain only this fixed operation label and an HTTP status.
 * @param {string} url
 * @param {UrlFetchApp.URLFetchRequestOptions} options
 * @param {string} label
 * @returns {unknown}
 */
function fetchPracticeJson_(url, options, label) {
  let response;
  try {
    response = UrlFetchApp.fetch(url, options);
  } catch {
    throw new Error(label + ' transport failed. No automatic retry was made.');
  }
  const status = response.getResponseCode();
  if (status !== 200) {
    throw new Error(label + ' failed with HTTP ' + status + '. Response content was not logged.');
  }
  const text = response.getContentText();
  if (text.length > 20000) throw new Error(label + ' response exceeded the practice size limit.');
  try {
    return JSON.parse(text);
  } catch {
    throw new Error(label + ' returned invalid JSON. Response content was not logged.');
  }
}

/**
 * Validates this fixture's three rows of three nonempty text cells, headers
 * and known statuses. Extra response fields are ignored, not extra rows.
 * @param {unknown} value
 * @returns {string[][]}
 */
function parsePracticeRows_(value) {
  if (!isPracticeRecord_(value) || typeof value.range !== 'string' ||
      value.majorDimension !== 'ROWS' || !isPracticeArray_(value.values) ||
      value.values.length !== 3) {
    throw new Error('Sheets read requires the complete Practice!A1:C3 fixture.');
  }
  /** @type {string[][]} */
  const rows = [];
  for (const row of value.values) {
    if (!isPracticeArray_(row) || row.length !== 3) {
      throw new Error('Each practice row must contain exactly three text cells.');
    }
    /** @type {string[]} */
    const cells = [];
    for (const cell of row) {
      if (typeof cell !== 'string' || cell.trim() === '' || cell.length > 200) {
        throw new Error('Practice cells must contain 1 to 200 characters of nonblank text.');
      }
      cells.push(cell);
    }
    rows.push(cells);
  }
  if (rows[0][0] !== 'Task' || rows[0][1] !== 'Owner' || rows[0][2] !== 'Status') {
    throw new Error('Restore the Task, Owner and Status headers in Practice!A1:C1.');
  }
  for (let index = 1; index < rows.length; index += 1) {
    const status = rows[index][2];
    if (status !== 'Open' && status !== 'In progress' && status !== 'Closed') {
      throw new Error('Practice statuses must be Open, In progress or Closed.');
    }
  }
  return rows;
}

/** @param {unknown} value @returns {value is Record<string, unknown>} */
function isPracticeRecord_(value) {
  return typeof value === 'object' && value !== null && !Array.isArray(value);
}

/** @param {unknown} value @returns {value is unknown[]} */
function isPracticeArray_(value) {
  return Array.isArray(value);
}

appsscript.json

Download file
appsscript.json
{
  "timeZone": "Etc/UTC",
  "runtimeVersion": "V8",
  "oauthScopes": [
    "https://www.googleapis.com/auth/iam",
    "https://www.googleapis.com/auth/script.external_request"
  ]
}

practice-rows.csv

Download file
practice-rows.csv
Task,Owner,Status
Confirm room booking,Ana,Open
Prepare agenda,Ben,In progress

iam-role.yaml

Download file
iam-role.yaml
title: Practice access token caller
description: Generate OAuth access tokens for the dedicated practice service account.
stage: GA
includedPermissions:
  - iam.serviceAccounts.getAccessToken