为何Google Apps Script的OnChange触发器会多次执行？

Hey there! Let's sort out that frustrating issue where your OnChange trigger is running multiple times even though your core functionality works fine. Here's a breakdown of why this happens and how to fix it:

Why Your Trigger Runs Multiple Times #

The OnChange trigger fires for any change to the spreadsheet—this includes edits, formatting changes, inserting/deleting rows/columns, or even changes made by your script itself. If your workflow (or the script) triggers more than one of these events, the trigger will execute multiple times. Additionally, there's no built-in guardrail in your current code to prevent duplicate runs.

Solutions to Stop Duplicate Executions #

1. Switch to a More Targeted Trigger (If Possible) #

If your goal is to run the script only when specific cells (like the email or name fields) are edited, replace the OnChange trigger with an installable OnEdit trigger (or use a simple OnEdit if your script doesn't need advanced permissions). This will only fire when a cell is edited, reducing unnecessary triggers.

2. Add a "Execution Guard" to Your Code #

If you need to keep using OnChange, add a mechanism to track whether the script has already run for the current change. Here are two reliable methods:

Option A: Use a Hidden Cell as a Marker

Pick a cell in your sheet (e.g., a hidden column) to act as a flag. Check this flag before running the main logic, set it to "in progress" while running, and clear it afterward (or after a short delay to avoid immediate re-runs).

Option B: Use PropertiesService to Track Execution State

Store a temporary flag in the script's properties to indicate that the script is already running. This works even if the sheet is modified elsewhere.

Modified Code with Execution Guard #

Here's your updated code using PropertiesService to prevent duplicate runs, plus some cleanup (like removing unused variables):

function emailPoaAsPDF() {
  // Prevent duplicate executions using script properties
  const scriptProps = PropertiesService.getScriptProperties();
  const isRunning = scriptProps.getProperty('isRunning');
  
  if (isRunning === 'true') {
    console.log('Script already running, exiting to avoid duplicate execution');
    return;
  }
  
  // Mark script as running
  scriptProps.setProperty('isRunning', 'true');
  
  try {
    const ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1xEEEiLfil1qfetSwZRhr02Q9uoXvWtCxq22JywTu5mo/edit#gid=1872480652").getSheetByName("POA Temp");
    const email = ss.getRange("A37").getValue();
    const cc_email = "xxxxxx@gmail.com";
    const name = ss.getRange("A34").getValue();
    const sub = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxx of " + name;
    const body = "Hello " + name + "," + "xxxxxxxxxxxxxxxxxxxx";
    
    const url = 'https://docs.google.com/spreadsheets/d/1xEEEiLfil1qfetSwZRhr02Q9uoXvWtCxq22JywTu5mo/export?';
    const exportOptions = 'exportFormat=pdf&format=pdf' +
      '&size=a4' +
      '&scale=2' +
      '&top_margin=1' +
      '&bottom_margin=1' +
      '&left_margin=1.25' +
      '&right_margin=1.25' +
      '&portrait=true' +
      '&fitw=false' +
      '&sheetnames=false&printtitle=false' +
      '&pagenumbers=false&gridlines=false' +
      '&fzr=false' +
      '&gid=1872480652';
    
    const params = {
      method: "GET",
      headers: {"authorization": "Bearer " + ScriptApp.getOAuthToken()}
    };
    
    const response = UrlFetchApp.fetch(url + exportOptions, params).getBlob();
    GmailApp.sendEmail(email, sub, body, {
      htmlBody: body,
      cc: cc_email,
      attachments: [{
        fileName: "xxx for " + name.toString() + ".pdf",
        content: response.getBytes(),
        mimeType: "application/pdf"
      }]
    });
    
    console.log('PDF email sent successfully to ' + email);
  } catch (error) {
    console.error('Error executing script:', error);
    throw error; // Re-throw to ensure errors are logged
  } finally {
    // Mark script as not running, even if an error occurs
    scriptProps.setProperty('isRunning', 'false');
  }
}

Additional Tips #

• Test the Trigger: After updating the code, test the trigger by making a single change to the sheet and check the script logs (View > Logs) to confirm it only runs once.
• Limit Trigger Scope: If you stick with OnChange, you can add checks at the start of the script to only run if the change is relevant (e.g., only run if the edited range includes cells A34 or A37).
• Clean Up Unused Code: I removed the unused nameFile variable from your original code to keep things tidy.

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