Google Sheets脚本复制当前工作表至多表异常：始终复制首表

Hey there! I get it, it's frustrating when the script doesn't behave like you expect—let's break down why your code is copying the first sheet instead of the active one, and how to fix it.

Root Cause of the Issue #

The most likely problem isn't with how you're getting the active sheet (your attempts like SpreadsheetApp.getActiveSpreadsheet().getActiveSheet() are actually correct!), but with how the script is using that variable later on.

Chances are the original script you found hardcodes the first sheet somewhere (like using getSheets()[0]) and you didn't update that part to reference your activeSheet variable. So even if you correctly grab the active sheet, the script still falls back to copying the first sheet.

Correct Working Script #

Here's a revised, tested script that properly copies the currently active sheet to multiple target spreadsheets:

function copyActiveSheetToMultipleTargets() {
  // 1. Get the currently active sheet (this part is correct!)
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var activeSheet = activeSpreadsheet.getActiveSheet();

  // Check if a sheet is actually active
  if (!activeSheet) {
    SpreadsheetApp.getUi().alert("Please select a sheet first before running the script!");
    return;
  }

  // 2. List the IDs of the spreadsheets you want to copy to
  // Replace these with your actual target spreadsheet IDs
  var targetSpreadsheetIds = [
    "your-target-spreadsheet-id-1",
    "your-target-spreadsheet-id-2",
    "your-target-spreadsheet-id-3"
  ];

  // 3. Loop through each target and copy the active sheet
  targetSpreadsheetIds.forEach(function(id) {
    try {
      var targetSpreadsheet = SpreadsheetApp.openById(id);
      // Copy the active sheet to the target spreadsheet (adds to the end by default)
      activeSheet.copyTo(targetSpreadsheet);
      SpreadsheetApp.getUi().alert(`Successfully copied sheet to: ${targetSpreadsheet.getName()}`);
    } catch (error) {
      SpreadsheetApp.getUi().alert(`Failed to copy to spreadsheet ID ${id}: ${error.message}`);
    }
  });
}

Key Checks to Ensure It Works #

• Verify variable usage: Go back to your original script and make sure every place that references the "source sheet" uses your activeSheet variable, not getSheets()[0] or any other call that grabs the first sheet.
• Test with an active sheet: Always click on the sheet you want to copy before running the script—if no sheet is active, the script will throw an alert (which we added for safety).
• Confirm permissions: When you run the script for the first time, it will ask for authorization to access your Google Sheets. Make sure you grant these permissions, otherwise it won't be able to access the target spreadsheets.

Why Your Previous Changes Didn't Work #

Your attempts to update the activeSheet line were correct, but if the script later used a different variable (like sourceSheet = getSheets()[0]) to perform the copy, those changes wouldn't have any effect. The fix requires updating both the sheet retrieval and the copy operation to use the same active sheet variable.

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