Google Sheets onEdit脚本报错：TypeError: 无法读取undefined的'range'属性

问题描述 #

之前解决了找不到源的错误，修改脚本后又遇到新错误：

TypeError: Cannot read properties of undefined (reading 'range')

以下是我的脚本：

function onEdit(e) {
  addTimeStamp(e);
  senEmail(e);
}

function addTimeStamp(e){
  var sheet = SpreadsheetApp.getActive().getSheetByName("Sheet5") ;
  var range = e.range;
  var row = range.getRow();
  var col = range.getColumn();
  var cellValue = range.getValue();

  if (col == 9 && cellValue === true){
    sheet.getRange(row,10).setValue(new Date());
  }
}

function senEmail(e){
  var sheet = SpreadsheetApp.getActive().getSheetByName("Sheet5") ;
  var range = SpreadsheetApp.getActiveSpreadsheet().getRangeByName('Sheet5');
  var row = range.getRow();
  var col = range.getColumn();
  let cellValue = range.getValue(); // The value of the edited cell

  // Check if the edit occurred in column 5 and the value is true
  if (col == 9 && cellValue === true) {
  
  let email = sheet.getRange(row, 2).getValue();
  let name = sheet.getRange(row, 2);
  let orderID = sheet.getRange(row, 5);
  let orderDate =sheet.getRange(row, 1);
  let methodOfPayment = sheet.getRagne(row, 4);
  let numberOfTickets = sheet.getRagne(row, 6);
  let payableTotal = sheet.getRange(row, 7);
  let html_link ="https://checkout.payableplugins.com/order/"+[row,5];

  const subject = "PHLL Raffle Fundraiser Follow-Up on Order ID #" +row[5];
  const messageBody = 'Dear' + name + ',' + "\n\n" + 'We hope this message finds you well. We wanted to remind you that we have received your order information for the raffle ticket. According to our records, you have selected the cash method for payment, but we have not yet received the payment.' + "\n\n" + 'If you have already provided the payment to the player, please let us know as soon as possible. This will allow us to coordinate with them to ensure that your payment is collected and your raffle ticket is processed accordingly.' + "\n\n" + 'However, if you have not yet provided the payment to the player, we kindly ask you to do so at your earliest convenience. Once the payment is received, we will promptly send you your raffle ticket.' + "\n\n" +
'Please note that if payment is not received within the specified timeframe, we will have to remove your name from the drawing.' + "\n\n" +  'If you wish to change your method of payment, you may do so by following this link:' + html_link +   + "\n\n" + 'Should you have any further questions or concerns, please do not hesitate to reach out to us. We are here to assist you in any way we can.' + "\n\n" +  'Best of luck in the raffle drawing!'  + "\n\n" + 'Warm regards,'
const messageBody2 = 'ORDER DETAILS:' + "\n\n" + 'ORDER DATE:' + orderDate +"\n\n" + 'ORDER ID #' + orderID +"\n\n" + 'METHOD OF PAYMENT:' + methodOfPayment + "\n\n" + 'NUMBER OF TICKETS:' + numberOfTickets +"\n\n" +'PAYABLE TOTAL:' + payableTotal
const respondent = 'Treasurer' + "\n\n" + 'EmailAddress'   + "\n\n" + 'Paradise Hills Little League'

MailApp.sendEmail(email,subject,messageBody,messageBody2,respondent);

console.log(sendEmail)

}
}

错误原因及修正方案 #

1. senEmail函数错误获取编辑范围 #

你在senEmail中没有使用传入的事件对象e，反而用getRangeByName('Sheet5')获取范围——这个方法是用来读取已命名的单元格/区域，不是工作表名称，因此返回undefined，后续调用range.getRow()就会触发range未定义的错误。

修正：和addTimeStamp函数保持一致，从事件对象中获取编辑范围：

var range = e.range;

2. 函数拼写错误 #

脚本里多处把getRange拼写成getRagne，比如：

let methodOfPayment = sheet.getRagne(row, 4);
let numberOfTickets = sheet.getRagne(row, 6);

这类拼写错误会导致函数调用失败，返回undefined，必须修正为getRange。

3. 邮件内容拼接错误 #

• 直接将Range对象（如name = sheet.getRange(row, 2)）拼入字符串，会显示对象的toString结果而非单元格值，需要添加.getValue()：

  let name = sheet.getRange(row, 2).getValue();
  let orderID = sheet.getRange(row, 5).getValue();
  let orderDate = sheet.getRange(row, 1).getValue();
  // 其他变量同理
  

• html_link和subject中的[row,5]是无效语法，应该直接使用已获取值的orderID变量：

  let html_link = "https://checkout.payableplugins.com/order/" + orderID;
  const subject = "PHLL Raffle Fundraiser Follow-Up on Order ID #" + orderID;
  

• MailApp.sendEmail参数格式错误，该函数不支持直接传入多个文本参数，需要将内容合并为一个字符串：

  const fullMessage = messageBody + "\n\n" + messageBody2 + "\n\n" + respondent;
  MailApp.sendEmail(email, subject, fullMessage);
  

• console.log(sendEmail)中的函数名拼写错误，应为senEmail（不影响功能，但会导致日志无效）。

4. 可选优化：增加事件对象校验 #

为避免手动运行onEdit时（无事件对象e）报错，可在函数开头添加校验：

function addTimeStamp(e){
  if (!e || !e.range) return;
  // 后续代码
}

function senEmail(e){
  if (!e || !e.range) return;
  // 后续代码
}

修正后的完整脚本 #

function onEdit(e) {
  addTimeStamp(e);
  senEmail(e);
}

function addTimeStamp(e){
  if (!e || !e.range) return;
  var sheet = SpreadsheetApp.getActive().getSheetByName("Sheet5");
  var range = e.range;
  var row = range.getRow();
  var col = range.getColumn();
  var cellValue = range.getValue();

  if (col == 9 && cellValue === true){
    sheet.getRange(row,10).setValue(new Date());
  }
}

function senEmail(e){
  if (!e || !e.range) return;
  var sheet = SpreadsheetApp.getActive().getSheetByName("Sheet5");
  var range = e.range;
  var row = range.getRow();
  var col = range.getColumn();
  let cellValue = range.getValue();

  if (col == 9 && cellValue === true) {
    let email = sheet.getRange(row, 2).getValue();
    let name = sheet.getRange(row, 2).getValue();
    let orderID = sheet.getRange(row, 5).getValue();
    let orderDate = sheet.getRange(row, 1).getValue();
    let methodOfPayment = sheet.getRange(row, 4).getValue();
    let numberOfTickets = sheet.getRange(row, 6).getValue();
    let payableTotal = sheet.getRange(row, 7).getValue();
    let html_link = "https://checkout.payableplugins.com/order/" + orderID;

    const subject = "PHLL Raffle Fundraiser Follow-Up on Order ID #" + orderID;
    const messageBody = 'Dear ' + name + ',' + "\n\n" + 'We hope this message finds you well. We wanted to remind you that we have received your order information for the raffle ticket. According to our records, you have selected the cash method for payment, but we have not yet received the payment.' + "\n\n" + 'If you have already provided the payment to the player, please let us know as soon as possible. This will allow us to coordinate with them to ensure that your payment is collected and your raffle ticket is processed accordingly.' + "\n\n" + 'However, if you have not yet provided the payment to the player, we kindly ask you to do so at your earliest convenience. Once the payment is received, we will promptly send you your raffle ticket.' + "\n\n" +
    'Please note that if payment is not received within the specified timeframe, we will have to remove your name from the drawing.' + "\n\n" +  'If you wish to change your method of payment, you may do so by following this link:' + html_link + "\n\n" + 'Should you have any further questions or concerns, please do not hesitate to reach out to us. We are here to assist you in any way we can.' + "\n\n" +  'Best of luck in the raffle drawing!'  + "\n\n" + 'Warm regards,';
    const messageBody2 = 'ORDER DETAILS:' + "\n\n" + 'ORDER DATE:' + orderDate +"\n\n" + 'ORDER ID #' + orderID +"\n\n" + 'METHOD OF PAYMENT:' + methodOfPayment + "\n\n" + 'NUMBER OF TICKETS:' + numberOfTickets +"\n\n" +'PAYABLE TOTAL:' + payableTotal;
    const respondent = 'Treasurer' + "\n\n" + 'EmailAddress'   + "\n\n" + 'Paradise Hills Little League';

    const fullMessage = messageBody + "\n\n" + messageBody2 + "\n\n" + respondent;
    MailApp.sendEmail(email, subject, fullMessage);

    console.log(senEmail);
  }
}

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