Google Script报错：Spreadsheets服务错误（Code文件第4行）求助

Hey there! Let's break down why your script is throwing that error and get it working again. First, let's look at the root causes and then walk through the fixes step by step.

Common Reasons for the Error #

The error points to line 4 (sheet = ss.getSheetByName('New School Temp');), but it might not be the only issue. Here's what could be going wrong:

• Mismatched Sheet Name: Since you updated the template, double-check that the sheet name is exactly New School Temp—no extra spaces, typos, or capitalization changes. If the sheet was renamed or deleted, this line will fail.
• Global Variable Conflicts: You're using sheet and sheet2 without the var keyword, which makes them global variables. If you have other scripts in the same spreadsheet, this could cause unexpected conflicts.
• Unnecessary Sheet Activation in Loop: Your script activates the new sheet inside the protection loop, which triggers unnecessary service calls and can lead to rate limits or errors.
• Missing Handling for Edge Cases: If your updated template has protections with no assigned editors, the original script might throw errors when trying to add empty editor lists.

Fixed Script with Explanations #

Here's the revised script with fixes for all the above issues:

function duplicateSheetWithProtections() { 
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  // Add var to avoid global variable conflicts
  var sheet = ss.getSheetByName('New School Temp'); 
  
  // Check if the template sheet exists before proceeding
  if (!sheet) {
    SpreadsheetApp.getUi().alert("Template sheet 'New School Temp' not found!");
    return;
  }
  
  var sheet2 = sheet.copyTo(ss).setName('My Copy'); 
  
  // Copy range-level protections
  var rangeProtections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE); 
  for (var i = 0; i < rangeProtections.length; i++) { 
    var originalProtection = rangeProtections[i]; 
    var rangeNotation = originalProtection.getRange().getA1Notation(); 
    var newProtection = sheet2.getRange(rangeNotation).protect(); 
    
    newProtection.setDescription(originalProtection.getDescription()); 
    newProtection.setWarningOnly(originalProtection.isWarningOnly()); 
    
    if (!originalProtection.isWarningOnly()) { 
      // Clear default editors first
      newProtection.removeEditors(newProtection.getEditors()); 
      // Add back original editors (handle empty lists gracefully)
      var originalEditors = originalProtection.getEditors();
      if (originalEditors.length > 0) {
        newProtection.addEditors(originalEditors); 
      }
      // Uncomment this line if you're using a Google Workspace domain
      // newProtection.setDomainEdit(originalProtection.canDomainEdit()); 
    } 
  } 

  // Optional: Copy sheet-level protection if your template uses it
  var sheetProtection = sheet.getProtections(SpreadsheetApp.ProtectionType.SHEET)[0];
  if (sheetProtection) {
    var newSheetProtection = sheet2.protect();
    newSheetProtection.setDescription(sheetProtection.getDescription());
    newSheetProtection.setWarningOnly(sheetProtection.isWarningOnly());
    if (!sheetProtection.isWarningOnly()) {
      newSheetProtection.removeEditors(newSheetProtection.getEditors());
      var sheetEditors = sheetProtection.getEditors();
      if (sheetEditors.length > 0) {
        newSheetProtection.addEditors(sheetEditors);
      }
      // newSheetProtection.setDomainEdit(sheetProtection.canDomainEdit());
    }
  }

  // Activate the new sheet once, after all protections are set up
  sheet2.activate();
}

Key Changes Made #

• Added var to local variables: Prevents global variable conflicts that could cause unexpected behavior.
• Added a template sheet check: Shows a clear alert if the template is missing, so you know exactly why the script fails instead of a vague service error.
• Moved sheet activation outside the loop: Only activates the new sheet once, reducing unnecessary service calls.
• Graceful empty editor handling: Avoids errors if a protection has no specific editors assigned.
• Optional sheet-level protection support: Includes code to copy sheet-wide protections if your updated template uses them (uncomment the domain edit line if you're on Google Workspace).

Quick Troubleshooting Steps #

1. Verify the template sheet name: Go to your spreadsheet and confirm the template is definitely named New School Temp (check for spaces, uppercase letters, etc.).
2. Run the script again: After replacing the code, run it—if you get an alert about the template missing, double-check the name.
3. Check permissions: Ensure you have edit access to the template sheet and can manage protections on the spreadsheet.

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