javascript - getValue 不适用于工作表

Question

我正在尝试根据我的团队在工作中使用的项目跟踪表来设置电子邮件警报系统。当 K 列中的任务状态更改为“完成”时，我需要它发送电子邮件。我得到了可以在测试表上运行的代码，但是当我将其复制到实时表时， getValue() 代码停止工作？由于电子邮件是根据 if() 语句发送的，因此脚本会运行，但实际上不起作用。我不确定这是否是权限问题，因为我不是实时工作表的所有者？我希望这具有足够的描述性——我已经自学了 javascript，以便让它正常工作，它看起来很接近，但我被困住了!这是screenshot项目跟踪表的样子。

function emailUpdate(e) { 
 
var emailInfoRange = sheet.getRange("B:O");
  
var edit = e.range.getA1Notation(); // Gets edited cell location
 
var editColumn = edit.substring(0,1)  // Gets column of edited cell 
  
var editRow = edit.substring(1,3)  // Gets row of edited cell
  
 if(editColumn == "K") {   // gets all relevent information needed for email
   
    var taskTypeCell = emailInfoRange.getCell(editRow,1);
    var taskType = taskTypeCell.getValue();
   
    var requestedByCell = emailInfoRange.getCell(editRow,3);
    var requestedBy = requestedByCell.getValue();
   
    var emailRequestCell = emailInfoRange.getCell(editRow,4);
    var emailRequest = emailRequestCell.getValue();
   
    var projectIdCell = emailInfoRange.getCell(editRow,5);
    var projectID = projectIdCell.getValue();
   
    var taskDescriptionCell = emailInfoRange.getCell(editRow,6);
    var taskDescription = taskDescriptionCell.getValue();
   
    var claimedByCell = emailInfoRange.getCell(editRow,9);
    var claimedBy = claimedByCell.getValue();
    
    var taskStatusCell = emailInfoRange.getCell(editRow,10);
    var taskStatus = taskStatusCell.getValue(); 


    if(taskStatus == "Done") {
      if(emailRequest == "Yes" || emailRequest == "yes") {  // Determines if status is "Done", and email notification is "Yes" or "yes"
        var emailAddress; 
        var getEmailAddress = function(personelArray)  {  // Defines function to search email address arrays for the one that belongs to requestedBy
        for (var i = 0; i < personelArray.length; i++) {
        if(requestedBy === personelArray[i]) {
          emailAddress = personelArray[i+1];

        } } }

// Searches through all email arrays to find the one belonging to requester         
      getEmailAddress(specialistsAndEmails)
      getEmailAddress(coordinatorsAndEmails)
      getEmailAddress(managersAndEmails)

// Sends email      
      MailApp.sendEmail(emailAddress,
                       "AUTOGEN: " + taskType + " for " + projectID + " " + taskDescription + " completed by " + claimedBy + ".", "This email has been automatically generated by an edit to the work available sheet. \n"
                           + "PLEASE DO NOT REPLY");             

         
      } else (Logger.log("No email requested"))
      } else (Logger.log("Status not changed to done"))
      } else (Logger.log("Update not to status cell"))
}

Answer 1

我将进行以下更改以帮助防止字符串操作出现问题。这可能是导致 getValues() 出现问题的原因。

function emailUpdate(e) { 
var emailInfoRange = sheet.getRange("B:O");
var edit = e.range // Gets edited cell location
var editColumn = edit.getColumn()  // Gets column of edited cell 
var editRow = edit.getRow()  // Gets row of edited cell

 if(editColumn == 11)  // Column K should correspond to column number 11, if i can count correctly. 
{
/// Remainder of the code should be the same as above

}
}

因此，您应该使用 getColumn and getRow() 获取列号和行号，而不是将范围转换为 A1 表示法。在范围对象上。这将防止文本到数字操作的问题，并且可能是导致问题的原因。