Google表单自动触发邮件脚本的三类异常问题排查求助

Let’s tackle those three problems with your Google Form email automation one by one—here’s what’s going wrong and how to fix it:

1. Duplicate Emails Being Sent #

The main culprit here is a mismatch between the row you’re processing and the row you’re marking as "EMAIL_SENT." Your code correctly fetches the latest submitted row using numRows, but then tries to update row 2 (from startRow + i) instead of the actual row that was just submitted. That means the real submitted row never gets the EMAIL_SENT flag, so every time the trigger runs, it re-processes the same row and sends another email.

Quick secondary check: make sure you don’t have two identical On form submit triggers set up for this function—accidentally duplicating triggers is a super common reason for double emails.

2. Email Sent From Owner ID Instead of Submitter #

By default, MailApp.sendEmail() uses the script owner’s email address. To send from the form submitter’s email, you need to:

• Access the form’s submit event data to grab the respondent’s email
• Use the from parameter in sendEmail()

Important note: This only works if your team uses Google Workspace (G Suite). Regular Gmail accounts block sending from another user’s email for security reasons. If you’re on regular Gmail, you can still add the submitter’s email to the message body (e.g., "This inquiry was submitted by: [submitter email]") instead of using the from parameter.

3. EMAIL_SENT Not Being Recorded in Column 7 #

Your code is currently checking and writing to column 8 (since JavaScript arrays are 0-indexed, row[7] maps to column H). To target column 7, you need to adjust both the index for reading the status and the column number for writing it:

• Use row[6] to check if the email was already sent
• Write the EMAIL_SENT flag to column 7 with sheet.getRange(rowIndex, 7)

Corrected Full Code #

var EMAIL_SENT = "EMAIL_SENT";

// This function uses the form submit event to get accurate row and submitter data
function sendEmails2(e) {
  var sheet = SpreadsheetApp.getActiveSheet();
  // Get the exact row that was just submitted from the event object
  var submittedRow = e.range.getRow();
  // Fetch all columns from the submitted row
  var rowData = sheet.getRange(submittedRow, 1, 1, sheet.getLastColumn()).getValues()[0];
  
  var recipientEmail = rowData[5]; // Column 6 (0-indexed)
  var leadNumber = rowData[1]; // Column 2
  // Template literal makes the message easier to read and edit
  var emailMessage = `Hello,

We have received an inquiry from your customer in Inbound.

Lead No is - ${leadNumber}

Kindly arrange a callback

Regards,
Team Inbound

This is an auto-generated email`;
  
  // Check Column 7 (0-indexed = 6) for existing EMAIL_SENT flag
  var emailStatus = rowData[6];
  if (emailStatus !== EMAIL_SENT) {
    var emailSubject = `Inbound Inquiry - ${leadNumber}`;
    
    // Grab the submitter's email from the event data
    var submitterEmail = e.response.getRespondentEmail();
    var emailOptions = {};
    // Only set the 'from' parameter if we have a valid submitter email (and use G Suite)
    if (submitterEmail) {
      emailOptions.from = submitterEmail;
    }
    
    // Send the email
    MailApp.sendEmail(recipientEmail, emailSubject, emailMessage, emailOptions);
    
    // Mark Column 7 as EMAIL_SENT
    sheet.getRange(submittedRow, 7).setValue(EMAIL_SENT);
    // Ensure the update is saved immediately
    SpreadsheetApp.flush();
  }
}

Critical Setup Steps: #

1. Fix Triggers: Delete any existing triggers for sendEmails2, then create a new On form submit trigger bound to this function. This ensures the function receives the e event object with all the submit details.
2. Verify Column Indices: Double-check that your sheet’s columns match the indices in the code (e.g., rowData[5] is the column with your recipient email).
3. Gmail Limitation: If you’re not on Google Workspace, remove the emailOptions.from line and instead add submitterEmail to the email body if you want to show who submitted the form.

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