App Script Dashboard自动执行脚本报错‘请先选择活动工作表’求修复

The Problem #

You’re running a script to duplicate your latest worksheet, set up weekly triggers (Tuesday/Friday) via the App Script Dashboard, but the trigger fails with the error "Please select an active sheet first". Manual runs work perfectly because when you execute the script manually, the spreadsheet is open and a sheet is selected as active—but triggers run in a "headless" context (no user interface), so there’s no default active sheet to rely on.

Looking at your code, the root issue is using duplicateActiveSheet(): this method depends on having an actively selected sheet, which doesn’t exist when the trigger runs automatically.

The Solution #

Instead of relying on the active sheet, explicitly specify which sheet you want to duplicate. You can do this either by the sheet’s name or its position in the spreadsheet. Here’s the revised code:

function createNewSheet() { 
  // Calculate tomorrow's date 
  var today = new Date(); 
  var tomo = new Date(); 
  tomo.setDate(today.getDate() + 1); 
  var SheetName = tomo.toLocaleDateString(); 
  Logger.log(SheetName); 

  // Get the target spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  Logger.log(ss.getName()); 

  // Choose one of these options to specify the sheet to duplicate:
  // Option 1: Duplicate by sheet name (replace "Template" with your actual sheet name)
  var sheetToDuplicate = ss.getSheetByName("Template"); 
  // Option 2: Duplicate the first sheet in the spreadsheet
  // var sheetToDuplicate = ss.getSheets()[0]; 

  // Duplicate the specified sheet (no active sheet context needed)
  var newSheet = sheetToDuplicate.duplicate(); 
  newSheet.setName(SheetName); 
}

Key Changes Explained #

• Removed active sheet dependency: Instead of duplicateActiveSheet(), we use sheetToDuplicate.duplicate() which operates on an explicitly referenced sheet—no active selection required for headless trigger runs.
• Explicit sheet targeting: Using getSheetByName() (or getSheets()[0] for the first sheet) ensures the script knows exactly which sheet to copy, even when no user is interacting with the spreadsheet.
• Cleaned up variable declarations: Fixed the missing var on your original activeSheet variable (renamed to ss for clarity) to follow JavaScript best practices.

Just replace "Template" with the actual name of the sheet you want to duplicate, or use the index option if that’s more suitable for your setup. Test the trigger again, and it should run without the active sheet error.

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