如何允许编辑用户在受保护的Google Sheet中执行增删行脚本

Hey there! Let's work through this problem together—it's a super common scenario with protected Google Sheets and script permissions, so I’ve got you covered.

Why your script works for you but not others #

First, let's break down the root issue:

• As the sheet owner, your script runs using your permissions, which include full access to edit protected ranges.
• When other editors run the script (even simple triggers like onEdit), it uses their limited permissions—and since the sheet is protected, they can't modify those restricted ranges.
• Installable triggers are the fix here because they can run using your (the creator's) identity, bypassing the editors' permission limitations.

Step-by-Step Solutions #

I’ll share two practical approaches, depending on how you want users to trigger the row actions.

Option 1: Trigger actions via a "command cell" (simple setup)

This lets users type a command (like "ADD" or "DELETE") in a specific cell to trigger row changes, with the work done using your permissions.

1. Update your script
   Replace your existing code with something like this (adjust sheet names and row logic to match your needs):

   function onSheetChange(e) {
     // Target the sheet you want to work with
     const targetSheet = e.source.getSheetByName("Your Sheet Name");
     if (!targetSheet || e.range.getSheet().getName() !== targetSheet.getName()) return;
   
     // Define the cell users will use to trigger actions (e.g., cell B1)
     const triggerCell = targetSheet.getRange("B1");
     if (e.range.getA1Notation() !== triggerCell.getA1Notation()) return;
   
     const userAction = e.value.trim().toLowerCase();
     switch(userAction) {
       case "add":
         // Insert a row after the last row (customize this logic!)
         targetSheet.insertRowAfter(targetSheet.getLastRow());
         triggerCell.clearContent(); // Clear the command to avoid repeats
         break;
       case "delete":
         // Delete the last row (skip if only the header remains)
         if (targetSheet.getLastRow() > 1) {
           targetSheet.deleteRow(targetSheet.getLastRow());
         }
         triggerCell.clearContent();
         break;
     }
   }
   

2. Set up the installable trigger

   • Open the Apps Script editor (Extensions > Apps Script)
   • Click the clock icon (Triggers) on the left sidebar
   • Click "Add trigger"
   • Configure these settings:
     • Choose which function to run: onSheetChange
     • Choose which deployment to run: Head
     • Select event source: From spreadsheet
     • Select event type: Change
     • Select run as: Your account (this is critical!)
     • Notification settings: Notify me immediately (on failure)
   • Save the trigger

Now, when editors type "ADD" or "DELETE" in cell B1, the script will run using your permissions to modify the protected ranges, then clear the command cell.

Option 2: Trigger actions via a custom menu (more user-friendly)

If you want editors to click a menu option instead of typing commands, use a Web App paired with a custom menu.

1. Write the script

   // Web App function that handles row actions
   function doGet(e) {
     const action = e.parameter.action;
     const targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Your Sheet Name");
     let resultMessage = "Action failed";
   
     if (!targetSheet) return ContentService.createTextOutput(resultMessage);
   
     switch(action) {
       case "add":
         targetSheet.insertRowAfter(targetSheet.getLastRow());
         resultMessage = "Row added successfully!";
         break;
       case "delete":
         if (targetSheet.getLastRow() > 1) {
           targetSheet.deleteRow(targetSheet.getLastRow());
           resultMessage = "Last row deleted successfully!";
         } else {
           resultMessage = "Can't delete the header row!";
         }
         break;
     }
     return ContentService.createTextOutput(resultMessage);
   }
   
   // Creates the custom menu in the sheet
   function createCustomMenu() {
     const ui = SpreadsheetApp.getUi();
     ui.createMenu("Row Actions")
       .addItem("Add Row", "callAddRowWebApp")
       .addItem("Delete Last Row", "callDeleteRowWebApp")
       .addToUi();
   }
   
   // Functions to call the Web App
   function callAddRowWebApp() {
     const webAppUrl = "YOUR_WEB_APP_URL"; // Replace this later
     const response = UrlFetchApp.fetch(`${webAppUrl}?action=add`);
     SpreadsheetApp.getUi().alert(response.getContentText());
   }
   
   function callDeleteRowWebApp() {
     const webAppUrl = "YOUR_WEB_APP_URL"; // Replace this later
     const response = UrlFetchApp.fetch(`${webAppUrl}?action=delete`);
     SpreadsheetApp.getUi().alert(response.getContentText());
   }
   

2. Deploy as a Web App

   • Click "Deploy" > "New deployment" in the script editor
   • Set type to Web app
   • Under "Execute as", select Your account
   • Under "Who has access", choose "Anyone, even anonymous" (or restrict to your organization if needed)
   • Click "Deploy", copy the generated Web App URL, and paste it into the webAppUrl fields in the script

3. Set up an onOpen trigger

   • Go back to the Triggers page, add a new trigger
   • Choose function: createCustomMenu
   • Event source: From spreadsheet
   • Event type: Open
   • Run as: Your account
   • Save the trigger

Now, every time someone opens the sheet, they’ll see a "Row Actions" menu. Clicking the options will run the Web App using your permissions and show a success/failure alert.

Final Notes #

• Always test with another editor account to make sure the actions work as expected.
• When authorizing triggers or the Web App, you may see a "Google hasn’t verified this app" warning—just click "Advanced" > "Go to [App Name]" to proceed (it’s safe since you built it!).

内容的提问来源于stack exchange，提问作者Lineu Pastana Neto