Effortlessly Manage and Modify Spreadsheet Permissions Using Google Apps Script: Preparation Guide

function listFilesAndManageAccess() {
  const ui = SpreadsheetApp.getUi();
  const response = ui.prompt(
    'Specify a Folder',
    'Enter "All Files in Drive" or specify a folder name or folder ID:',
    ui.ButtonSet.OK_CANCEL
  );

  if (response.getSelectedButton() === ui.Button.CANCEL) {
    ui.alert('Operation cancelled.');
    return;
  }

  const input = response.getResponseText().trim();
  
  // Handle input for "All Files in Drive" or a specific folder
  if (input === 'All Files in Drive') {
    listAllFiles();
  } else {
    listFilesInFolder(input);
  }
}

function listAllFiles() {
  const sheet = setupSheet();
  const files = DriveApp.getFiles();
  let row = 2;

  while (files.hasNext()) {
    const file = files.next();
    appendFileDataToSheet(sheet, file, row);
    row++;
  }
}

function listFilesInFolder(folderNameOrId) {
  const sheet = setupSheet();
  let folder;
  
  try {
    folder = DriveApp.getFolderById(folderNameOrId);
  } catch (e) {
    const folders = DriveApp.getFoldersByName(folderNameOrId);
    if (folders.hasNext()) {
      folder = folders.next();
    } else {
      SpreadsheetApp.getUi().alert('Folder not found.');
      return;
    }
  }
  
  const files = folder.getFiles();
  let row = 2;

  while (files.hasNext()) {
    const file = files.next();
    appendFileDataToSheet(sheet, file, row);
    row++;
  }
}

function setupSheet() {
  const sheetName = 'Access Management';
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName) || SpreadsheetApp.getActiveSpreadsheet().insertSheet(sheetName);
  
  // Clear and reset the sheet
  sheet.clear();
  
  // Set headers
  const headers = [
    'Folder Name', 
    'File Name', 
    'Current Permissions', 
    'Owner', 
    'Viewers (Including Commenters)', 
    'Editors', 
    'Change Target', 
    'New Permissions', 
    'Remove Viewers', 
    'Remove Editors', 
    'Add Viewers', 
    'Add Editors', 
    'Timestamp'
  ];
  
  sheet.appendRow(headers);
  const headerRange = sheet.getRange(1, 1, 1, headers.length);
  headerRange.setHorizontalAlignment('center');
  headerRange.setVerticalAlignment('middle');
  
  // Color-code headers
  const greenHeadersRange = sheet.getRange(1, 1, 1, 6);
  const orangeHeadersRange = sheet.getRange(1, 8, 1, 6);
  
  greenHeadersRange.setBackground('#B7E1CD'); // Light Green
  orangeHeadersRange.setBackground('#FAD7AC'); // Light Orange
  
  // Add dropdown options for "New Permissions" column
  const permissions = [
    'Anyone with the link (View)', 
    'Anyone with the link (Comment)', 
    'Anyone with the link (Edit)', 
    'Restricted'
  ];
  const validation = SpreadsheetApp.newDataValidation().requireValueInList(permissions).build();
  sheet.getRange(2, 8, sheet.getMaxRows() - 1).setDataValidation(validation);
  
  return sheet;
}

function appendFileDataToSheet(sheet, file, row) {
  const folderName = getFolderName(file);
  const fileName = file.getName();
  const fileLink = file.getUrl();
  const currentPermission = translatePermission(file.getSharingAccess(), file.getSharingPermission());

  // Append file data with a hyperlink for the file name
  sheet.getRange(row, 1).setValue(folderName);
  sheet.getRange(row, 2).setFormula(`=HYPERLINK("${fileLink}", "${fileName}")`);
  sheet.getRange(row, 3).setValue(currentPermission);
  
  // Retrieve member details
  const viewers = file.getViewers().map(user => user.getEmail());
  const editors = file.getEditors().map(user => user.getEmail());
  
  // Safely get the owner's email
  const owner = file.getOwner() ? file.getOwner().getEmail() : 'No Owner';

  // Populate member details in respective columns
  sheet.getRange(row, 4).setValue(owner);
  sheet.getRange(row, 5).setValue(viewers.join(', '));
  sheet.getRange(row, 6).setValue(editors.join(', '));
  sheet.getRange(row, 7).insertCheckboxes(); // Add checkboxes for changes
}

function getFolderName(file) {
  const parents = file.getParents();
  return parents.hasNext() ? parents.next().getName() : 'Root';
}

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'
  };

  if (access === DriveApp.Access.PRIVATE) {
    return accessMap[access];
  } else {
    return `${accessMap[access] || 'Unknown'} (${permissionMap[permission] || 'Unknown'})`;
  }
}