Manage and Modify Spreadsheet Permissions in Bulk with Google Apps Script: The Update Guide

function updateAccessPermissions() {
  const sheetName = 'Access Management';
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const lastRow = sheet.getLastRow();
  
  for (let row = 2; row <= lastRow; row++) {
    const checkCell = sheet.getRange(row, 7); // Checkbox to select target
    const newPermission = sheet.getRange(row, 8).getValue(); // New permissions to apply
    const fileUrl = sheet.getRange(row, 2).getFormula().match(/"(.*?)"/)[1]; // Extract file URL from hyperlink

    if (checkCell.isChecked() && newPermission) {
      const fileId = getFileIdFromUrl(fileUrl);
      const file = DriveApp.getFileById(fileId);

      // Update access permissions based on the selected option
      if (newPermission === 'Anyone with the link (View)') {
        file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
      } else if (newPermission === 'Anyone with the link (Comment)') {
        file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.COMMENT);
      } else if (newPermission === 'Anyone with the link (Edit)') {
        file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.EDIT);
      } else if (newPermission === 'Restricted') {
        file.setSharing(DriveApp.Access.PRIVATE, DriveApp.Permission.NONE); // Set to restricted
      }

      // Remove viewers and editors based on email addresses in specific columns
      let existingViewers = sheet.getRange(row, 5).getValue().split(',').map(email => email.trim()).filter(email => email);
      let existingEditors = sheet.getRange(row, 6).getValue().split(',').map(email => email.trim()).filter(email => email);

      const removeViewers = sheet.getRange(row, 9).getValue().split(',').map(email => email.trim());
      removeViewers.forEach(email => {
        if (email) {
          try {
            file.removeViewer(email); // Remove as viewer
            existingViewers = existingViewers.filter(e => e !== email); // Update viewer list
          } catch (e) {
            Logger.log(`Failed to remove ${email} as viewer: ${e.message}`);
          }
        }
      });

      const removeEditors = sheet.getRange(row, 10).getValue().split(',').map(email => email.trim());
      removeEditors.forEach(email => {
        if (email) {
          try {
            file.removeEditor(email); // Remove as editor
            existingEditors = existingEditors.filter(e => e !== email); // Update editor list
          } catch (e) {
            Logger.log(`Failed to remove ${email} as editor: ${e.message}`);
          }
        }
      });

      // Add viewers and editors based on input in specific columns
      const addViewers = sheet.getRange(row, 11).getValue().split(',').map(email => email.trim());
      addViewers.forEach(email => {
        if (email && !existingViewers.includes(email)) {
          file.addViewer(email); // Add as viewer
          existingViewers.push(email); // Update viewer list
        }
      });

      const addEditors = sheet.getRange(row, 12).getValue().split(',').map(email => email.trim());
      addEditors.forEach(email => {
        if (email && !existingEditors.includes(email)) {
          file.addEditor(email); // Add as editor
          existingEditors.push(email); // Update editor list
        }
      });

      // Update the sheet with the latest viewer and editor lists
      sheet.getRange(row, 5).setValue(existingViewers.filter(email => email).join(', '));
      sheet.getRange(row, 6).setValue(existingEditors.filter(email => email).join(', '));

      // Update the "Current Permissions" column
      const updatedPermission = translatePermission(file.getSharingAccess(), file.getSharingPermission());
      sheet.getRange(row, 3).setValue(updatedPermission);

      // Add a timestamp in the last column
      sheet.getRange(row, 13).setValue(new Date());
      
      // Clear data in columns H–L
      sheet.getRange(row, 8, 1, 5).setValue('');
      
      // Uncheck the checkbox
      checkCell.setValue(false);
    }
  }
}

// Helper function to extract the file ID from a file URL
function getFileIdFromUrl(fileUrl) {
  const regex = /[-\w]{25,}/;
  const match = fileUrl.match(regex);
  return match ? match[0] : null;
}

// Helper function to translate access and permission levels to readable format
function translatePermission(access, permission) {
  const accessMap = {
    [DriveApp.Access.ANYONE]: 'Anyone',
    [DriveApp.Access.ANYONE_WITH_LINK]: 'Anyone with the link',
    [DriveApp.Access.PRIVATE]: 'Restricted'
  };
  const permissionMap = {
    [DriveApp.Permission.VIEW]: 'View',
    [DriveApp.Permission.COMMENT]: 'Comment',
    [DriveApp.Permission.EDIT]: 'Edit'
  };

  // For restricted access, display only "Restricted"
  if (access === DriveApp.Access.PRIVATE) {
    return accessMap[access];
  } else {
    return `${accessMap[access] || 'Unknown'} (${permissionMap[permission] || 'Unknown'})`;
  }
}