const DIRECTORY_USER_READONLY_SCOPE = 'https://www.googleapis.com/auth/admin.directory.user.readonly'; const REPORTS_AUDIT_READONLY_SCOPE = 'https://www.googleapis.com/auth/admin.reports.audit.readonly'; const SPREADSHEETS_SCOPE = 'https://www.googleapis.com/auth/spreadsheets'; const AUDIT_SPREADSHEET_ID_PROPERTY = 'AUDIT_SPREADSHEET_ID'; const AUDIT_INTERNAL_DOMAINS_PROPERTY = 'AUDIT_INTERNAL_DOMAINS'; const USER_POSTURE_SHEET_NAME = 'UserPosture'; const EXTERNAL_SHARES_SHEET_NAME = 'ExternalShares'; const MAX_USER_PAGES = 5; const USER_PAGE_SIZE = 100; const MAX_ACTIVITY_PAGES = 2; const ACTIVITY_PAGE_SIZE = 50; const STALE_LOGIN_DAYS = 90; const MS_PER_DAY = 24 * 60 * 60 * 1000; const NEVER_LOGGED_IN_SENTINEL = '1970-01-01T00:00:00.000Z'; const DIRECTORY_USER_FIELDS_MASK = 'nextPageToken,users(primaryEmail,name/fullName,orgUnitPath,suspended,isAdmin,isEnforcedIn2Sv,lastLoginTime)'; const REPORTS_ACTIVITY_FIELDS_MASK = 'nextPageToken,items(id/time,actor/email,actor/callerType,ownerDomain,events(name,parameters(name,value,multiValue)))'; const USER_POSTURE_HEADERS = Object.freeze([ 'Primary Email', 'Full Name', 'Org Unit', 'Account State', 'Super Admin', '2SV Enforced', 'Last Login UTC', 'Days Since Login', 'Posture Flags', ]); const EXTERNAL_SHARES_HEADERS = Object.freeze([ 'Event Time UTC', 'Actor Email', 'Document ID', 'Document Title', 'Document Type', 'Owner Email', 'Target Principal', 'Visibility', 'Granted Access', ]); /** * @typedef {{ * spreadsheetId: string, * internalDomains: Set * }} WorkspaceAuditConfig_ */ /** * @typedef {{ * primaryEmail: string, * fullName: string, * orgUnitPath: string, * accountState: 'active' | 'suspended', * isAdmin: boolean, * isEnforcedIn2Sv: boolean, * lastLoginDisplay: string, * daysSinceLogin: number | string, * flags: string[], * needsReview: boolean, * row: (string | number | boolean)[] * }} NormalizedUserPosture_ */ /** * @typedef {{ * eventTimeUtc: string, * actorEmail: string, * documentId: string, * documentTitle: string, * documentType: string, * ownerEmail: string, * targetPrincipal: string, * visibility: string, * grantedAccess: string, * row: string[] * }} ExternalDriveShareRow_ */ /** * Runs a pure in-memory verification of user-posture normalization, Drive * external-share extraction, and spreadsheet formula-injection escaping. * Calls no Workspace or Admin SDK APIs, so any account can execute it. */ function runLocalAuditNormalizationCheck() { const referenceTimeMs = Date.parse('2026-11-18T00:00:00.000Z'); /** @type {AdminDirectory.Schema.User[]} */ const sampleUsers = [ { primaryEmail: 'auditor-reader@acme.example', name: { fullName: 'Morgan Lee' }, orgUnitPath: '/Security', suspended: false, isAdmin: false, isEnforcedIn2Sv: true, lastLoginTime: '2026-11-15T14:30:00.000Z', }, { primaryEmail: 'contractor@acme.example', name: { fullName: '=IMPORTXML("https://evil.example","//a")' }, orgUnitPath: '/Contractors', suspended: false, isAdmin: false, isEnforcedIn2Sv: false, lastLoginTime: NEVER_LOGGED_IN_SENTINEL, }, { primaryEmail: 'seasonal-staff@acme.example', name: { fullName: 'Jordan Patel' }, orgUnitPath: '/Operations', suspended: false, isAdmin: false, isEnforcedIn2Sv: true, lastLoginTime: '2026-07-10T09:00:00.000Z', }, { primaryEmail: 'archived-alumni@acme.example', name: { fullName: 'Taylor Kim' }, orgUnitPath: '/Suspended', suspended: true, isAdmin: false, isEnforcedIn2Sv: false, lastLoginTime: NEVER_LOGGED_IN_SENTINEL, }, ]; const normalizedUsers = sampleUsers.map((user) => normalizeDirectoryUser_(user, referenceTimeMs), ); if (normalizedUsers[0].needsReview !== false) { throw new Error('Compliant active user with enforced 2SV and recent login must not be flagged.'); } if ( !normalizedUsers[1].flags.includes('2sv_not_enforced') || !normalizedUsers[1].flags.includes('never_signed_in') || normalizedUsers[1].lastLoginDisplay !== 'never' ) { throw new Error('Provisioned active user with 1970 epoch sentinel must flag 2sv_not_enforced and never_signed_in.'); } if (!normalizedUsers[1].fullName.startsWith("'=IMPORTXML")) { throw new Error('Leading = in untrusted user fullName must be escaped with an apostrophe.'); } if (!normalizedUsers[2].flags.includes('inactive_over_90d')) { throw new Error('Active user older than 90 days must flag inactive_over_90d.'); } if (normalizedUsers[3].needsReview !== false) { throw new Error('Suspended account must not raise active-account posture findings.'); } /** @type {AdminReports.Schema.Activity[]} */ const sampleActivities = [ { id: { time: '2026-11-17T18:10:00.000Z' }, actor: { email: 'analyst@acme.example' }, ownerDomain: 'acme.example', events: [ { name: 'change_user_access', parameters: [ { name: 'doc_id', value: '1AbCdEfGhIjKlMnOpQrStUvWxYz' }, { name: 'doc_title', value: '+Q4 Forecast Export' }, { name: 'doc_type', value: 'spreadsheet' }, { name: 'owner', value: 'analyst@acme.example' }, { name: 'target_user', value: 'reviewer@external-partner.example' }, { name: 'visibility', value: 'shared_externally' }, { name: 'new_value', multiValue: ['can_view'] }, ], }, { name: 'change_user_access', parameters: [ { name: 'doc_id', value: '1InternalDocOnly987654321' }, { name: 'doc_title', value: 'Team Runbook' }, { name: 'doc_type', value: 'document' }, { name: 'owner', value: 'analyst@acme.example' }, { name: 'target_user', value: 'colleague@acme.example' }, { name: 'visibility', value: 'shared_externally' }, { name: 'new_value', multiValue: ['can_edit'] }, ], }, { name: 'change_user_access', parameters: [ { name: 'doc_id', value: '1RevokedVendorDoc123456789' }, { name: 'doc_title', value: 'Retired Vendor Spec' }, { name: 'doc_type', value: 'document' }, { name: 'owner', value: 'analyst@acme.example' }, { name: 'target_user', value: 'former@external-partner.example' }, { name: 'visibility', value: 'private' }, { name: 'new_value', multiValue: ['none'] }, ], }, ], }, ]; const externalShares = extractExternalDriveShareRows_( sampleActivities, new Set(['acme.example']), ); if (externalShares.length !== 1) { throw new Error( 'Expected exactly 1 active external share (excluding internal grants and access revocations), received ' + externalShares.length + '.', ); } if (externalShares[0].documentTitle !== "'+Q4 Forecast Export") { throw new Error('Leading + in untrusted Drive doc_title must be escaped with an apostrophe.'); } const prefixes = ['=SUM(1,2)', '+1-800-555-0199', '-100', '@admin', '\t=cmd', '\r=cmd', '\n=cmd']; for (const unsafeText of prefixes) { const escaped = escapeSpreadsheetFormulaText_(unsafeText); if (escaped !== "'" + unsafeText) { throw new Error('escapeSpreadsheetFormulaText_ failed to prefix ' + JSON.stringify(unsafeText)); } } const summary = { mode: 'local_check', status: 'passed', usersChecked: normalizedUsers.length, flaggedUserCount: normalizedUsers.filter((item) => item.needsReview).length, externalShareCount: externalShares.length, escapedUserNameSample: normalizedUsers[1].fullName, escapedDocTitleSample: externalShares[0].documentTitle, }; console.log(JSON.stringify(summary, null, 2)); return summary; } /** * Exports a bounded read-only Workspace directory posture and external Drive * sharing audit to the standalone spreadsheet configured in ScriptProperties. */ function exportWorkspaceSecurityAudit() { const config = readAuditConfig_(); ScriptApp.requireScopes(ScriptApp.AuthMode.FULL, [ DIRECTORY_USER_READONLY_SCOPE, REPORTS_AUDIT_READONLY_SCOPE, SPREADSHEETS_SCOPE, ]); const lock = LockService.getScriptLock(); if (!lock.tryLock(5000)) { throw new Error( 'Another audit export holds the script lock. Wait for it to finish before retrying.', ); } try { const spreadsheet = SpreadsheetApp.openById(config.spreadsheetId); const userPostureSheet = prepareAuditTab_(spreadsheet, USER_POSTURE_SHEET_NAME); const externalSharesSheet = prepareAuditTab_(spreadsheet, EXTERNAL_SHARES_SHEET_NAME); const referenceTimeMs = Date.now(); const directoryResult = fetchBoundedDirectoryUsers_(); const normalizedUsers = directoryResult.users .map((user) => normalizeDirectoryUser_(user, referenceTimeMs)) .sort(compareUserPosturePriority_); const activityResult = fetchBoundedDriveAccessActivities_(); const externalShares = extractExternalDriveShareRows_( activityResult.activities, config.internalDomains, ); writeAuditTable_( userPostureSheet, USER_POSTURE_HEADERS, normalizedUsers.map((entry) => entry.row), ); writeAuditTable_( externalSharesSheet, EXTERNAL_SHARES_HEADERS, externalShares.map((entry) => entry.row), ); SpreadsheetApp.flush(); const report = { mode: 'live_audit', generatedAtUtc: new Date(referenceTimeMs).toISOString(), directoryUsersFetched: normalizedUsers.length, directoryFlaggedUsers: normalizedUsers.filter((entry) => entry.needsReview).length, directoryPagesFetched: directoryResult.pagesFetched, directoryTruncatedAtCap: directoryResult.truncated, driveActivitiesFetched: activityResult.activities.length, externalShareEventsWritten: externalShares.length, activityPagesFetched: activityResult.pagesFetched, activityTruncatedAtCap: activityResult.truncated, tabsWritten: [USER_POSTURE_SHEET_NAME, EXTERNAL_SHARES_SHEET_NAME], }; console.log(JSON.stringify(report, null, 2)); return report; } finally { lock.releaseLock(); } } /** * Fetches up to MAX_USER_PAGES pages from AdminDirectory.Users.list using * customer: 'my_customer' and a partial-response fields mask. * @returns {{users: AdminDirectory.Schema.User[], pagesFetched: number, truncated: boolean}} */ function fetchBoundedDirectoryUsers_() { /** @type {AdminDirectory.Schema.User[]} */ const users = []; /** @type {string | null} */ let pageToken = null; let pagesFetched = 0; do { /** @type {{customer: string, maxResults: number, projection: string, fields: string, pageToken?: string}} */ const requestOptions = { customer: 'my_customer', maxResults: USER_PAGE_SIZE, projection: 'basic', fields: DIRECTORY_USER_FIELDS_MASK, }; if (pageToken !== null) { requestOptions.pageToken = pageToken; } const response = AdminDirectory.Users.list(requestOptions); if (!response || typeof response !== 'object') { throw new Error('AdminDirectory.Users.list returned an unexpected response.'); } pagesFetched += 1; const pageUsers = response.users === undefined ? [] : response.users; if (!Array.isArray(pageUsers)) { throw new Error('AdminDirectory.Users.list returned users in an unexpected format.'); } for (const user of pageUsers) { if (user && typeof user === 'object') { users.push(user); } } pageToken = typeof response.nextPageToken === 'string' && response.nextPageToken.length > 0 ? response.nextPageToken : null; } while (pageToken !== null && pagesFetched < MAX_USER_PAGES); return { users: users, pagesFetched: pagesFetched, truncated: pageToken !== null, }; } /** * Fetches up to MAX_ACTIVITY_PAGES pages of Drive change_user_access audit * records across all users via AdminReports.Activities.list. * @returns {{activities: AdminReports.Schema.Activity[], pagesFetched: number, truncated: boolean}} */ function fetchBoundedDriveAccessActivities_() { /** @type {AdminReports.Schema.Activity[]} */ const activities = []; /** @type {string | null} */ let pageToken = null; let pagesFetched = 0; do { /** @type {{eventName: string, maxResults: number, fields: string, pageToken?: string}} */ const requestOptions = { eventName: 'change_user_access', maxResults: ACTIVITY_PAGE_SIZE, fields: REPORTS_ACTIVITY_FIELDS_MASK, }; if (pageToken !== null) { requestOptions.pageToken = pageToken; } const response = AdminReports.Activities.list('all', 'drive', requestOptions); if (!response || typeof response !== 'object') { throw new Error('AdminReports.Activities.list returned an unexpected response.'); } pagesFetched += 1; const items = response.items === undefined ? [] : response.items; if (!Array.isArray(items)) { throw new Error('AdminReports.Activities.list returned items in an unexpected format.'); } for (const activity of items) { if (activity && typeof activity === 'object') { activities.push(activity); } } pageToken = typeof response.nextPageToken === 'string' && response.nextPageToken.length > 0 ? response.nextPageToken : null; } while (pageToken !== null && pagesFetched < MAX_ACTIVITY_PAGES); return { activities: activities, pagesFetched: pagesFetched, truncated: pageToken !== null, }; } /** * Normalizes one Directory user record and evaluates 2SV enforcement and * 90-day login posture for active (non-suspended) accounts. * @param {AdminDirectory.Schema.User} user * @param {number} referenceTimeMs * @returns {NormalizedUserPosture_} */ function normalizeDirectoryUser_(user, referenceTimeMs) { const rawEmail = typeof user.primaryEmail === 'string' ? user.primaryEmail.trim() : ''; const rawFullName = user.name && typeof user.name.fullName === 'string' ? user.name.fullName : ''; const rawOrgUnit = typeof user.orgUnitPath === 'string' && user.orgUnitPath.length > 0 ? user.orgUnitPath : '/'; const suspended = user.suspended === true; /** @type {'active' | 'suspended'} */ const accountState = suspended ? 'suspended' : 'active'; const isAdmin = user.isAdmin === true; const isEnforcedIn2Sv = user.isEnforcedIn2Sv === true; const rawLastLogin = typeof user.lastLoginTime === 'string' ? user.lastLoginTime.trim() : ''; const parsedLoginMs = rawLastLogin.length > 0 ? Date.parse(rawLastLogin) : Number.NaN; const neverSignedIn = rawLastLogin.length === 0 || rawLastLogin === NEVER_LOGGED_IN_SENTINEL || Number.isNaN(parsedLoginMs) || parsedLoginMs <= 0; const lastLoginDisplay = neverSignedIn ? 'never' : new Date(parsedLoginMs).toISOString(); /** @type {number | string} */ const daysSinceLogin = neverSignedIn ? 'never' : Math.max(0, Math.floor((referenceTimeMs - parsedLoginMs) / MS_PER_DAY)); /** @type {string[]} */ const flags = []; if (!suspended) { if (!isEnforcedIn2Sv) { flags.push('2sv_not_enforced'); } if (neverSignedIn) { flags.push('never_signed_in'); } else if (referenceTimeMs - parsedLoginMs > STALE_LOGIN_DAYS * MS_PER_DAY) { flags.push('inactive_over_90d'); } } const primaryEmail = escapeSpreadsheetFormulaText_(rawEmail); const fullName = escapeSpreadsheetFormulaText_(rawFullName); const orgUnitPath = escapeSpreadsheetFormulaText_(rawOrgUnit); const needsReview = flags.length > 0; const flagSummary = needsReview ? flags.join(', ') : 'ok'; return { primaryEmail: primaryEmail, fullName: fullName, orgUnitPath: orgUnitPath, accountState: accountState, isAdmin: isAdmin, isEnforcedIn2Sv: isEnforcedIn2Sv, lastLoginDisplay: lastLoginDisplay, daysSinceLogin: daysSinceLogin, flags: flags, needsReview: needsReview, row: [ primaryEmail, fullName, orgUnitPath, accountState, isAdmin, isEnforcedIn2Sv, lastLoginDisplay, daysSinceLogin, flagSummary, ], }; } /** * Extracts active external Drive sharing grants from AdminReports activity * items while ignoring internal-domain shares and access revocations. * @param {AdminReports.Schema.Activity[]} activities * @param {ReadonlySet} configuredInternalDomains * @returns {ExternalDriveShareRow_[]} */ function extractExternalDriveShareRows_(activities, configuredInternalDomains) { /** @type {ExternalDriveShareRow_[]} */ const rows = []; for (const activity of activities) { if (!activity || !Array.isArray(activity.events)) { continue; } const rawTime = activity.id && typeof activity.id.time === 'string' ? activity.id.time : ''; const rawActor = activity.actor && typeof activity.actor.email === 'string' && activity.actor.email.length > 0 ? activity.actor.email : activity.actor && typeof activity.actor.callerType === 'string' ? activity.actor.callerType : 'unknown'; const internalDomains = new Set(configuredInternalDomains); if ( internalDomains.size === 0 && typeof activity.ownerDomain === 'string' && activity.ownerDomain.trim().length > 0 ) { internalDomains.add(activity.ownerDomain.trim().toLowerCase()); } for (const event of activity.events) { if (!event || event.name !== 'change_user_access') { continue; } const params = event.parameters; const visibility = getEventParamString_(params, 'visibility'); const newValues = getEventParamMultiValue_(params, 'new_value'); const grantedRoles = newValues.filter( (role) => role.length > 0 && role.toLowerCase() !== 'none', ); if (grantedRoles.length === 0) { continue; } /** @type {string} */ let rawTargetPrincipal = ''; const targetUser = getEventParamString_(params, 'target_user').trim(); const targetDomain = getEventParamString_(params, 'target_domain').trim(); if (targetUser.length > 0) { const targetUserDomain = extractEmailDomain_(targetUser); if (targetUserDomain === null || internalDomains.has(targetUserDomain)) { continue; } rawTargetPrincipal = targetUser; } else if (targetDomain.length > 0) { if (internalDomains.has(targetDomain.toLowerCase())) { continue; } rawTargetPrincipal = '@' + targetDomain; } else { continue; } const eventTimeUtc = escapeSpreadsheetFormulaText_(rawTime); const actorEmail = escapeSpreadsheetFormulaText_(rawActor); const documentId = escapeSpreadsheetFormulaText_( getEventParamString_(params, 'doc_id'), ); const documentTitle = escapeSpreadsheetFormulaText_( getEventParamString_(params, 'doc_title'), ); const documentType = escapeSpreadsheetFormulaText_( getEventParamString_(params, 'doc_type'), ); const ownerEmail = escapeSpreadsheetFormulaText_( getEventParamString_(params, 'owner'), ); const targetPrincipal = escapeSpreadsheetFormulaText_(rawTargetPrincipal); const safeVisibility = escapeSpreadsheetFormulaText_(visibility); const grantedAccess = escapeSpreadsheetFormulaText_(grantedRoles.join(', ')); rows.push({ eventTimeUtc: eventTimeUtc, actorEmail: actorEmail, documentId: documentId, documentTitle: documentTitle, documentType: documentType, ownerEmail: ownerEmail, targetPrincipal: targetPrincipal, visibility: safeVisibility, grantedAccess: grantedAccess, row: [ eventTimeUtc, actorEmail, documentId, documentTitle, documentType, ownerEmail, targetPrincipal, safeVisibility, grantedAccess, ], }); } } return rows; } /** * Prefixes a leading apostrophe when text starts with =, +, -, @, tab, * carriage return, or line feed so Google Sheets stores untrusted audit * strings as literal text rather than evaluating a spreadsheet formula. * @param {string} value * @returns {string} */ function escapeSpreadsheetFormulaText_(value) { if (/^[=+\-@\t\r\n]/.test(value)) { return "'" + value; } return value; } /** * @param {{name?: string | undefined, value?: string | undefined, multiValue?: string[] | undefined}[] | undefined} parameters * @param {string} targetName * @returns {string} */ function getEventParamString_(parameters, targetName) { if (!Array.isArray(parameters)) { return ''; } const match = parameters.find((param) => param && param.name === targetName); if (!match) { return ''; } if (typeof match.value === 'string') { return match.value; } if (Array.isArray(match.multiValue) && typeof match.multiValue[0] === 'string') { return match.multiValue[0]; } return ''; } /** * @param {{name?: string | undefined, value?: string | undefined, multiValue?: string[] | undefined}[] | undefined} parameters * @param {string} targetName * @returns {string[]} */ function getEventParamMultiValue_(parameters, targetName) { if (!Array.isArray(parameters)) { return []; } const match = parameters.find((param) => param && param.name === targetName); if (!match) { return []; } if (Array.isArray(match.multiValue)) { return match.multiValue.filter((entry) => typeof entry === 'string'); } if (typeof match.value === 'string' && match.value.length > 0) { return [match.value]; } return []; } /** * @param {string} email * @returns {string | null} */ function extractEmailDomain_(email) { const trimmed = email.trim().toLowerCase(); const atIndex = trimmed.lastIndexOf('@'); if (atIndex <= 0 || atIndex === trimmed.length - 1) { return null; } return trimmed.slice(atIndex + 1); } /** * Sorts flagged active accounts above unflagged and suspended accounts, then * orders alphabetically by primaryEmail. * @param {NormalizedUserPosture_} left * @param {NormalizedUserPosture_} right * @returns {number} */ function compareUserPosturePriority_(left, right) { if (left.needsReview !== right.needsReview) { return left.needsReview ? -1 : 1; } return left.primaryEmail.localeCompare(right.primaryEmail); } /** * Reads AUDIT_SPREADSHEET_ID (required) and AUDIT_INTERNAL_DOMAINS (optional) * from ScriptProperties in one getProperties call. * @returns {WorkspaceAuditConfig_} */ function readAuditConfig_() { const allProperties = PropertiesService.getScriptProperties().getProperties(); const rawSpreadsheetId = allProperties[AUDIT_SPREADSHEET_ID_PROPERTY]; const spreadsheetId = typeof rawSpreadsheetId === 'string' ? rawSpreadsheetId.trim() : ''; if (spreadsheetId.length === 0) { throw new Error( 'Set Script Property ' + AUDIT_SPREADSHEET_ID_PROPERTY + ' to your dedicated audit Google Sheet ID before running exportWorkspaceSecurityAudit.', ); } if (!/^[A-Za-z0-9_-]{20,120}$/.test(spreadsheetId)) { throw new Error( 'Script Property ' + AUDIT_SPREADSHEET_ID_PROPERTY + ' must be a spreadsheet file ID (letters, digits, hyphens, or underscores), not a full URL.', ); } /** @type {Set} */ const internalDomains = new Set(); const rawDomains = allProperties[AUDIT_INTERNAL_DOMAINS_PROPERTY]; if (typeof rawDomains === 'string' && rawDomains.trim().length > 0) { for (const part of rawDomains.split(',')) { const domain = part.trim().toLowerCase(); if (domain.length > 0) { internalDomains.add(domain); } } } return { spreadsheetId: spreadsheetId, internalDomains: internalDomains, }; } /** * Returns the named tab, creating it when absent and refusing to overwrite any * existing tab that contains spreadsheet formulas. * @param {SpreadsheetApp.Spreadsheet} spreadsheet * @param {string} sheetName * @returns {SpreadsheetApp.Sheet} */ function prepareAuditTab_(spreadsheet, sheetName) { const existing = spreadsheet.getSheetByName(sheetName); if (!existing) { return spreadsheet.insertSheet(sheetName); } const formulas = existing.getDataRange().getFormulas(); const hasFormula = formulas.some((formulaRow) => formulaRow.some((formula) => formula !== ''), ); if (hasFormula) { throw new Error( 'Sheet "' + sheetName + '" contains a formula. Use a dedicated plain-data audit spreadsheet before exporting.', ); } return existing; } /** * Clears prior cell values and writes the header row plus normalized rows in a * single batched setValues call. * @param {SpreadsheetApp.Sheet} sheet * @param {readonly string[]} headers * @param {(string | number | boolean)[][]} bodyRows */ function writeAuditTable_(sheet, headers, bodyRows) { const table = [Array.from(headers), ...bodyRows]; sheet.clearContents(); sheet.getRange(1, 1, table.length, headers.length).setValues(table); }