Google Sheets订单审批邮件的Apps Script报错：column未定义

核心错误原因 #

你遇到的ReferenceError: column is not defined是因为shouldSendEmail函数误用了未定义的column变量——该函数接收的参数是row，所有列值都应该通过row数组访问。

关键修正点及完整代码 #

1. 修正shouldSendEmail函数 #

• 把所有column[7]替换为row[7]
• 你的审批状态选项是Denied，原代码里写的是Rejected，必须统一匹配，否则逻辑会失效
• 确保列索引和表格实际结构对应（参考你的表格截图）

2. 修正后的完整代码 #

function sendEmailsAndUpdateStatus() {
 //Get the rows in the spreadsheet
 var dataRange = SpreadsheetApp.getActive().getDataRange();
 var data = dataRange.getValues();

 //Remove the header row and add it to a new array.
 //We will write this array back to the spreadsheet at the end.
 var updatedData = [data.shift()];

 //The variable numNotification will track if notifications were sent
 var numNotifications = 0;

 //Process each row using a forEach loop
 data.forEach(function (row) {
   //Check if email notifications should be sent and send them.
   //If the notification is sent, increment numNotifications and also
   //update the "Email sent" column to "Y".
   if(shouldSendEmail(row)) {
     sendApprovalStatusEmail(row);
     numNotifications++;
     row[9] = "Y";
   }

   //Add this row to the new array that we created above
   updatedData.push(row);
 });

 //Write the new array to the spreadsheet. This will update the
 //"Email sent" columns in the spreadsheet.
 dataRange.setValues(updatedData);

 //Display a Toast notification to let the user know if notifications
 //were sent.
 if(numNotifications > 0) {
   SpreadsheetApp.getActive().toast("Successfully sent " + numNotifications + " notifications.");
 } else {
   SpreadsheetApp.getActive().toast("No notifications were sent.");
 }
}

function shouldSendEmail(row) {
 //Don't send email unless the expense report has been processed
 if(row[7] != "Approved" && row[7] != "Denied" && row[7] != "Have Questions")
   return false;
  //Don't send email if email address is empty
 if(row[5] === "")
   return false;
  //Don't send email if already sent
 if(row[9] === "Y")
   return false;
  return true;
}

function sendApprovalStatusEmail(row) {
 //Create the body of the email based on the contents in the row.
 var emailBody = `
EXPENSE REPORT: ${row[7]}
-----------------------------------------------------------------
Note: ${row[8] === "" ? "N/A" : row[8]}
-----------------------------------------------------------------
Department: ${row[1]}
Amount: ${row[2]}
Reason: ${row[4]}
Date: ${(row[3].getMonth() + 1) + "/" + row[3].getDate() + "/" + row[3].getFullYear() }
-----------------------------------------------------------------
 Please contact expensereports@example.com if you have any questions about this email.
`;

 //Create the email message object by setting the to, subject,
 //body, replyTo and name properties.
 var message = {
   to: row[6],
   subject: "[Expense report " + row[7] + "]: " + row[4],
   body: emailBody,
   replyTo: "expensereports@example.com",
   name: "Expense Reports"
 }

 //Send the email notification using the MailApp.sendEmail() API.
 MailApp.sendEmail(message);
}

额外注意事项 #

• 首次运行代码时，需要授权Google Apps Script访问你的邮箱和表格权限
• 确认row的索引完全匹配表格列顺序：比如row[7]对应「Approval Status」列，row[9]对应「Email sent」列，row[6]对应申请人邮箱列
• 可以手动运行sendEmailsAndUpdateStatus测试，或者设置onChange触发器实现自动触发

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