如何用Google Script基于Google Sheets邮箱数组设置Google Doc共享及更新工作表权限？

Got it, let's tackle this automation problem head-on. You want to sync the editor permissions for your "Area Sheets" directly from the master "Roster" document whenever employee roles change—no more manual sharing updates. Here's a complete, tested solution using Google Apps Script that does exactly that, plus some extra flexibility for adjusting sharing settings.

Solution Breakdown #

We’ll build a script that:

1. Reads your Roster sheet to get each Area Sheet’s URL and its assigned editors
2. Clears existing editor permissions (with safeguards to keep admins/owners intact)
3. Adds the new list of editors from the Roster
4. Lets you tweak sharing settings (like preventing editors from re-sharing) if needed

Step 1: Prep Your Roster Sheet #

First, make sure your Roster has these columns (you can adjust the names, just update the script constants later):

• Column A: Area Name (optional, but helpful for logging)
• Column B: Area Sheet URL (the full link to the individual Area Sheet)
• Column C: Editors (comma-separated email addresses of employees who need edit access)

Example row:

Sales Team | https://docs.google.com/spreadsheets/d/123... | john@company.com,jane@company.com

Step 2: The Script Code #

Open your Roster sheet, click Extensions > Apps Script to open the script editor. Replace the default code with this:

// CONFIGURE THESE CONSTANTS TO MATCH YOUR SETUP
const ROSTER_SHEET_NAME = "Roster"; // Name of your master roster sheet tab
const DOMAIN = "yourcompany.com"; // Optional: restrict editors to your company domain
const PREVENT_RESHARING = true; // Set to false if editors can share the sheet

function updateAreaSheetPermissions() {
  const rosterSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const rosterSheet = rosterSpreadsheet.getSheetByName(ROSTER_SHEET_NAME);
  if (!rosterSheet) {
    throw new Error(`Could not find sheet named "${ROSTER_SHEET_NAME}"`);
  }

  // Get all rows (skip header row if you have one)
  const data = rosterSheet.getDataRange().getValues().slice(1);
  
  data.forEach(row => {
    const areaName = row[0];
    const sheetUrl = row[1];
    const editorEmails = row[2]?.split(",").map(email => email.trim()).filter(email => email);

    if (!sheetUrl || !editorEmails?.length) {
      console.log(`Skipping row for ${areaName}: Missing sheet URL or editors`);
      return;
    }

    try {
      // Open the Area Sheet
      const areaSheet = SpreadsheetApp.openByUrl(sheetUrl);
      const sheetOwner = areaSheet.getOwner();
      const me = Session.getActiveUser().getEmail();

      // Get existing editors and remove everyone except owner and script runner (you)
      const existingEditors = areaSheet.getEditors();
      existingEditors.forEach(editor => {
        const editorEmail = editor.getEmail();
        if (editorEmail !== sheetOwner && editorEmail !== me) {
          areaSheet.removeEditor(editorEmail);
        }
      });

      // Add new editors from the roster
      editorEmails.forEach(email => {
        // Optional: Validate domain if set
        if (DOMAIN && !email.endsWith(`@${DOMAIN}`)) {
          console.log(`Skipping ${email}: Not in ${DOMAIN} domain`);
          return;
        }
        areaSheet.addEditor(email);
      });

      // Set sharing settings (prevent resharing if enabled)
      const sharingSettings = areaSheet.getSharingSettings();
      if (PREVENT_RESHARING && sharingSettings.canShare) {
        areaSheet.setSharing(DriveApp.Access.DOMAIN, DriveApp.Permission.EDIT);
        areaSheet.setShareableByEditors(false);
      }

      console.log(`Successfully updated permissions for ${areaName}`);
    } catch (err) {
      console.error(`Failed to update ${areaName}: ${err.message}`);
    }
  });

  SpreadsheetApp.getUi().alert("Permission update complete! Check the script log for details.");
}

What This Code Does: #

• Safety First: It never removes the sheet owner or the person running the script from editors
• Domain Restriction: If you set your company domain, it skips any emails outside that domain
• Reshare Control: Toggles whether editors can share the sheet with others (adjust the PREVENT_RESHARING constant)
• Error Handling: Logs issues (like invalid URLs) instead of crashing the whole script

Step 3: Run & Automate the Script #

1. Test First: Run the script manually once (click the play button in the script editor) to grant permissions. You’ll need to authorize the script—this is normal, just follow the prompts (you may need to click "Advanced" > "Go to [Script Name]" to proceed).
2. Set Up a Trigger: To automate this, click the clock icon (Triggers) in the script editor. Create a new trigger:
   • Choose updateAreaSheetPermissions as the function
   • Set event source to "Time-driven"
   • Pick a frequency (e.g., weekly, monthly) or use a "On open" trigger to run when someone opens the Roster

Optional: Add a Manual Run Button #

For non-technical team members, add a button directly to the Roster sheet:

1. Go to Insert > Drawing and create a button (e.g., a rectangle with text like "Update Permissions")
2. Save and close the drawing, then click the button > Assign script
3. Enter updateAreaSheetPermissions (exact name) and click OK. Now anyone can click the button to run the update.

Best Practices #

• Test with a Dummy Sheet: Before running on all Area Sheets, test with a test spreadsheet to make sure permissions are updated correctly
• Keep Roster Clean: Ensure emails are formatted correctly (no extra spaces) to avoid errors
• Check Logs: If something goes wrong, open the script editor and click View > Logs to see what failed

内容的提问来源于stack exchange，提问作者C. Olsson