getValue not working on sheets

Question

I'm trying to set up an email alert system based on a project tracking sheet my team uses at work. I need it to send an email when a task's status is changed to "Done" in column K. I got the code to work on a test sheet, but when I copy it to the live sheet the getValue() code stops working? Since the email is sent based on if() statements, the script runs, but doesn't actually work. I'm not sure if it's a permissions issue since I am not the owner of the live sheet? I hope that is descriptive enough -- I have taught myself javascript in order to get this working and it seems so close, but I am stuck!! Here is a screenshot of what the project tracking sheet looks like.

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"))
}

What do you see in the execution transcript (view > execution transcript) in the script editor when you edit the sheet? As for permissions, if you have edit access to the sheet you should be able to run this script. Finally, there is a quota on the number of emails that can be sent per day, do know if you have exceeded that quota? — Jack Brown, May 08 '17 at 18:53
The execution transcript says it's going through the whole script. And I'm unaware of any email quotas, but I haven't gotten it to send any out so it shouldn't be exceeded anyway. — Erin, May 08 '17 at 19:13

score 0 · Answer 1 · answered May 08 '17 at 19:50

I would make the following changes to help prevent issues with string manipulations. Which could be the cause for your issues with 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

}
}

So instead of converting the range to A1 notation, you should get column number and row number using getColumn and getRow() on the range object. This will prevent issues with text to number manipulation and could be the cause of your problems.

I made this change to my test sheet and the live one I've been having issues with. The test sheet still works, the live one still doesn't. No changes in logs — Erin, May 16 '17 at 20:40

getValue not working on sheets

1 Answers1