Google Sheets普通用户无法添加行的权限问题及脚本触发doPost(e)报错的解决请求

Let's break down what's going wrong here and get your script setup working so regular users can insert rows successfully.

First: The Critical Bug in doGet() #

Your biggest issue is a simple but fatal mistake in the doGet() function: you're passing the full Google Sheet URL to SpreadsheetApp.openById(), but this method requires only the sheet's unique ID. The ID is the string between /d/ and /edit in your sheet link: 1HsRwknyZBmZZ9nibDfNpOwqkVsFGThDyrTwspV-5_4U. Using the full URL here throws an error when the script tries to access the sheet, which is almost certainly triggering the unexpected doPost(e)-related message.

Second: Verify Web App Deployment Permissions #

Your Web App needs to run with your owner-level permissions to bypass the B列 protection. Double-check these settings when deploying:

• Execute as: Select your Google account (not "User accessing the web app")
• Who has access: Choose "Anyone, even anonymous" (or "Anyone within [your domain]" if using a work/school account)
  If these are set incorrectly, regular users won't be able to trigger the script properly.

Third: Corrected & Optimized Scripts #

Here's the refined version of both scripts with better error handling, input validation, and fixes for the core issues:

Updated insertRow() Script

function insertRow() { 
  var ss = SpreadsheetApp.getActive();
  var sheetName = ss.getActiveSheet().getName();
  var row = ss.getActiveRange().getRow();
  
  // Validate user input to avoid invalid numbers or cancellations
  var numRowsInput = Browser.inputBox('Insert Rows', 'Enter the number of rows to insert (positive number only)', Browser.Buttons.OK_CANCEL);
  if (numRowsInput === 'cancel') return; // Exit if user cancels
  
  var numRows = parseInt(numRowsInput);
  if (isNaN(numRows) || numRows <= 0) {
    Browser.msgBox('Invalid input! Please enter a positive whole number.');
    return;
  }

  var webAppUrl = "https://script.google.com/macros/s/AKfycbzpnEMhIG-0dMp54q3W4UxoT71-lSdfF7Qxf7rq_j6gJMNIxuCS/exec";
  // Encode sheet name to handle spaces/special characters
  var queryString = "?sheetName=" + encodeURIComponent(sheetName) + "&rowNo=" + row + "&noOfRows=" + numRows;
  var fullUrl = webAppUrl + queryString;

  try {
    var response = UrlFetchApp.fetch(fullUrl, {method: 'GET', muteHttpExceptions: true});
    var responseText = response.getContentText();
    
    if (responseText !== 'Success') {
      Browser.msgBox('Error inserting rows: ' + responseText);
    } else {
      Browser.msgBox(numRows + ' row(s) inserted successfully!');
    }
  } catch (err) {
    Browser.msgBox('Request failed: ' + err.message);
  }
}

Updated doGet() Script

function doGet(e) { 
  // Check for missing required parameters
  if (!e.parameter || !e.parameter.sheetName || !e.parameter.rowNo || !e.parameter.noOfRows) {
    return ContentService.createTextOutput("Invalid query: Missing required parameters");
  }

  try {
    var sheetName = decodeURIComponent(e.parameter.sheetName);
    var row = parseInt(e.parameter.rowNo);
    var numOfRows = parseInt(e.parameter.noOfRows);

    // Validate numeric inputs are positive numbers
    if (isNaN(row) || isNaN(numOfRows) || row <= 0 || numOfRows <= 0) {
      return ContentService.createTextOutput("Invalid parameters: Row number and row count must be positive numbers");
    }

    // Use the correct sheet ID instead of full URL
    var ss = SpreadsheetApp.openById("1HsRwknyZBmZZ9nibDfNpOwqkVsFGThDyrTwspV-5_4U");
    var sheet = ss.getSheetByName(sheetName);
    
    if (!sheet) {
      return ContentService.createTextOutput("Error: Sheet '" + sheetName + "' not found");
    }

    // Insert rows and copy formatting/values from the source row
    sheet.insertRowsAfter(row, numOfRows);
    var sourceRange = sheet.getRange(row, 1, 1, sheet.getLastColumn());
    var targetRange = sheet.getRange(row + 1, 1, numOfRows, sheet.getLastColumn());
    // Copy format first, then values to preserve styling
    sourceRange.copyTo(targetRange, SpreadsheetApp.CopyPasteType.PASTE_FORMAT);
    sourceRange.copyTo(targetRange, SpreadsheetApp.CopyPasteType.PASTE_VALUES);

    return ContentService.createTextOutput("Success");
  } catch (err) {
    return ContentService.createTextOutput("Error: " + err.message);
  }
}

Final Deployment Steps #

1. Replace your existing scripts with the updated versions above
2. Re-deploy your Web App (click "New deployment" instead of reusing an old deployment to ensure changes take effect)
3. Test the insertRow() function as a regular user — it should now insert rows without permission errors, since the Web App runs using your owner-level access.

内容的提问来源于stack exchange，提问作者Piotr Magdziak