/** @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} */ 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); }