Skip to content
Skip to chapter

Chapter 43

Advanced integrations and data

11 min read

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.

By the end

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.

Chapter navigation

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:

  1. OAuth scope consent on the script token: When appsscript.json omits oauthScopes, Apps Script inspects your code and can request read-write Directory scopes such as https://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. Pinning oauthScopes to admin.directory.user.readonly and admin.reports.audit.readonly prevents the script's OAuth token from mutating directory objects even if a bug calls a write method.

  2. 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.readonly still receives 403 Not Authorized to access this resource/api when calling AdminDirectory.Users.list with the default admin_view or when calling AdminReports.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.

warning

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:

CapabilityAdminDirectory (directory_v1)AdminReports (reports_v1)
Manifest serviceId and version"admin", "directory_v1""admin", "reports_v1"
Question answeredCurrent directory snapshot of users, org units, groups, and rolesHistorical audit events and usage metrics across Workspace apps
Read method in this projectAdminDirectory.Users.list(optionalArgs)AdminReports.Activities.list(userKey, applicationName, optionalArgs)
Read-only OAuth scopehttps://www.googleapis.com/auth/admin.directory.user.readonlyhttps://www.googleapis.com/auth/admin.reports.audit.readonly
maxResults limit per page1 to 500 (default 100)1 to 1000 (default 1000, this project passes 50)
Retention and propagation lagReflects live account settings. New user creation and role changes propagate within minutesRetains 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.

  1. At script.google.com, create a standalone project named Workspace Admin posture audit.

  2. Replace Code.gs with the complete source below.

  3. Open Project Settings, turn on Show "appsscript.json" manifest file in editor, return to Editor, replace appsscript.json with the supplied manifest, and save both files.

  4. Confirm Services lists both AdminDirectory and AdminReports. Do not add duplicate entries through Services > Add a service.

  5. To test normalization without Workspace admin rights, select runLocalAuditNormalizationCheck and click Run.

  6. 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.

  7. Under Project Settings > Script Properties, add AUDIT_SPREADSHEET_ID with that spreadsheet ID. If your Workspace tenant owns secondary domains, add AUDIT_INTERNAL_DOMAINS as a comma-separated list such as acme.example,subsidiary.example.

appsscript.json
{
  "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"
  ]
}

Download appsscript.json

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).

Code.gs
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);
}

Download Code.gs

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.

tip

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 on UserPosture with Account State set to suspended and Posture Flags set to ok.

  • 2SV policy enforcement (user.isEnforcedIn2Sv !== true): The User resource exposes both isEnrolledIn2Sv (the user registered a second factor) and isEnforcedIn2Sv (organizational policy requires 2SV at sign-in). Checking isEnforcedIn2Sv catches 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 whose lastLoginTime is older than 90 days before referenceTimeMs receive inactive_over_90d. compareUserPosturePriority_ sorts flagged active accounts to the top of UserPosture.

note

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, and visibility).

  • getEventParamMultiValue_ reads repeated parameters such as new_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_:

Source
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:

  1. In the Apps Script editor, select runLocalAuditNormalizationCheck in 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.

  2. Confirm Execution log prints a passing summary:

Source
{
  "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:

  1. Confirm AUDIT_SPREADSHEET_ID is saved under Project Settings > Script Properties.

  2. Select exportWorkspaceSecurityAudit and 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.

  3. Inspect the logged live_audit JSON object: directoryUsersFetched and directoryFlaggedUsers report user counts, externalShareEventsWritten counts active external Drive grants, and directoryTruncatedAtCap or activityTruncatedAtCap turns true when your tenant has more pages than MAX_USER_PAGES (5) or MAX_ACTIVITY_PAGES (2).

  4. Open the audit spreadsheet. Check UserPosture (A1:I) to review flagged accounts at the top, and check ExternalShares (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 symptomCause and fix
AdminDirectory is not defined or AdminReports is not definedSave 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 formatStore 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 tiersDrive 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 formulaPoint 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:

  1. In runLocalAuditNormalizationCheck, add an active user object with suspended: false, isEnrolledIn2Sv: true, isEnforcedIn2Sv: false, and lastLoginTime: '2026-11-17T12:00:00.000Z'. What does normalizeDirectoryUser_ return in flags, and why does enrollment alone leave the account flagged?

  2. In fetchBoundedDriveAccessActivities_, add startTime: new Date(Date.now() - 7 * MS_PER_DAY).toISOString() to requestOptions. Which timestamp format does Activities.list require, and what happens if a clock skew or typo sets startTime after the current UTC time?

  3. Suppose your organization owns both acme.example and subsidiary.example, and an analyst in acme.example grants can_view on a private file to finance@subsidiary.example. Compare what extractExternalDriveShareRows_ outputs when AUDIT_INTERNAL_DOMAINS is unset versus when AUDIT_INTERNAL_DOMAINS is set to acme.example,subsidiary.example.

Check your predictions:

  1. flags contains ['2sv_not_enforced'] and needsReview is true. normalizeDirectoryUser_ checks user.isEnforcedIn2Sv === true because a user enrolled in an optional-2SV organizational unit can still turn off 2SV until enforcement is turned on.

  2. Activities.list requires an RFC 3339 timestamp such as toISOString() (YYYY-MM-DDTHH:mm:ss.sssZ). If startTime is later than endTime or the current server time, the Reports API rejects the request with a 400 invalid-parameter error.

  3. When AUDIT_INTERNAL_DOMAINS is unset, extractExternalDriveShareRows_ knows only activity.ownerDomain (acme.example) and flags finance@subsidiary.example as external. Setting AUDIT_INTERNAL_DOMAINS to acme.example,subsidiary.example recognizes 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
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);
}

appsscript.json

Download file
appsscript.json
{
  "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"
  ]
}