Google Sheet Protection Maintenance

appsscript.json

{
  "timeZone": "America/Los_Angeles",
  "dependencies": {
    "enabledAdvancedServices": [
      {
        "userSymbol": "Drive",
        "version": "v2",
        "serviceId": "drive"
      }
    ]
  },
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8"
}

ProjectSharedDrive.gs

const PROJECT_DRIVE_ID = ".......................";

/**
 * @returns {string[]} Project Managers
 */
function getProjectManagers() {
  return getSharedDriveManagers(PROJECT_DRIVE_ID);
}

ProjectSheet.gs

const PROJECTSHEET_FILE_ID = "...........................................";
const PROJECTSHEET_ADMINSHEET_ID = 123123123;

/**
 * @returns {SpreadsheetApp.Spreadsheet} Project Spreadsheet File
 */
function getProjectSheetFile() {
  return SpreadsheetApp.openById(PROJECTSHEET_FILE_ID);
}

/**
 * @returns {SpreadsheetApp.Sheet} Project Admin Sheet
 */
function getProjectAdminSheet() {
  const file = getProjectSheetFile();
  if (!file) throw new Error("Couldn't get the project sheet file.");

  return file
  .getSheets()
  .filter(sheet => sheet.getSheetId() === PROJECTSHEET_ADMINSHEET_ID)
  .shift();
}

/**
 * 
 */
function setProjectAdmins() {
  const driveAdmins = getWorkloadForecastingManagers();
  if (driveAdmins.length === 0) throw new Error("No workload forecasting managers found.");

  const adminSheet = getProjectAdminSheet();
  if (!adminSheet) throw new Error("Couldn't get the project admin sheet.");

  const file = adminSheet.getParent();
  if (!file) throw new Error("Couldn't get the project sheet file.");

  const projectAdminSheetProtection = adminSheet
  .getProtections(SpreadsheetApp.ProtectionType.SHEET)
  .shift();

  setProtectionEditors(projectAdminSheetProtection, driveAdmins);

  [
    ...(
      file.getProtections(SpreadsheetApp.ProtectionType.SHEET)
      .filter(protection => protection.getRange().getSheet().getSheetId() !== adminSheet.getSheetId())
    ),
    ...file.getProtections(SpreadsheetApp.ProtectionType.RANGE),
  ]
  .filter(protection => !protection.isWarningOnly())
  .forEach(protection => setProtectionEditors(protection, driveAdmins));
}

SharedDrive.gs

/**
 * @param {string} sharedDriveId - Shared Drive ID
 * @returns {string[]} Manager email addresses.
 */
function getSharedDriveManagers(sharedDriveId) {
  let nextPageToken;
  let sharedDriveManagers = [];

  do {
    /** @type {Drive_v2.Drive.V2.Schema.PermissionList} */
    const page = Drive.Permissions.list(sharedDriveId, {
      supportsAllDrives: true,
      pageToken: nextPageToken,
    });
    sharedDriveManagers.push(
      ...page.items
      .filter(permission => !permission.deleted)
      .filter(permission => permission.role === "organizer")
      .filter(permission => permission.type === "user")
      .filter(permission => /^(scjalliance\.com|scj\.io)$/i.test(permission.domain))
      .map(permission => permission.emailAddress.toLowerCase())
    );
    nextPageToken = page.nextPageToken;
  } while(nextPageToken);

  return sharedDriveManagers;
}

SheetProtection.gs

/**
 * @param {SpreadsheetApp.Protection} protection - Sheet or Range protection to update.
 * @param {string[]|null} editors - List of editors to set for this `Protection`.
 */
function setProtectionEditors(protection, editors) {
  if (editors === null) {
    // null means empty array in this case... wipe out the editors list in the Protection
    editors = [];
  } else {
    // ensure that the editors array is trimmed and lowercase
    editors = editors.map(editor => editor.toLowerCase().trim());
  }

  // get the protection's current editors list (as an array of trimmed, lowercase email addresses)
  const protectionEditors = protection
  .getEditors()
  .map(editor => editor.getEmail().toLowerCase().trim());

  // new/missing editors to add to this Protection
  const addEditors = editors
  .filter(editor => !protectionEditors.includes(editor));

  // unwanted editors to remove from this Protection
  const delEditors = protectionEditors
  .filter(sheetAdmin => !editors.includes(sheetAdmin));

  // if no changes, return early
  if (addEditors.length === 0 && delEditors.length === 0) return;

  const sheet = protection.getRange().getSheet();
  const sheetName = sheet.getName();
  const file = sheet.getParent();
  const fileName = file.getName();
  const a1Notation = protection.getRange().getA1Notation();
  const description = protection.getDescription();

  Logger.log(
    "****** %s",
    fileName,
  );
  Logger.log(
    "%s: [%s%s%s]",
    protection.getProtectionType(),
    sheetName,
    a1Notation ? "!" : "",
    a1Notation,
  );
  if (description) Logger.log(" DESC: %s", description);

  addEditors.forEach(member => Logger.log(
    Utilities.formatString(
      "%10s: <%s>",
      "adding",
      member,
    )
  ));
  if (addEditors.length > 0) protection.addEditors(addEditors);

  delEditors.forEach(member => Logger.log(
    Utilities.formatString(
      "%10s: <%s>",
      "removing",
      member,
    )
  ));
  if (delEditors.length > 0) protection.removeEditors(delEditors);
}