Custom Email based on Cell values in google sheets

Question

I need help while building a Request Approval Flow in google Forms/Sheets

I have a data response sheet similar to like below, Column A to J headers are "Timestamp" "EmailAddress" "Name" "Targets" "Account#" "Reason" "Access(Days)" "Approver" "Approved" "CaseID"

I have already setup a form submit email trigger to Approval body through formMule AddOn, Now I want to trigger email when Approval body approve or disapprove the request in Data response sheet.

Everytime when anyone select "Y" or "N" to column "I", script should suppose to trigger an email based on the data present in that row.

I am not an expert but tried to do it with following script and unfortunately not getting desired outcome, I set it up Current project trigger to OnEdit

 function sendNotification1(e) {
      var ss = SpreadsheetApp.getActiveSpreadsheet();   
      var sheet = ss.getSheetByName("Form Responses 1");

      if(e.range.getColumn() == 9 && e.value == "Y")
      {
        var name = e.range.offset(0,-6).getValue();
        var comment = e.range.offset(0,-3).getValue();
        var email = e.range.offset(0,-7).getValue();
        var case1 = e.range.offset(0,1).getValue();
        var approver1 = e.range.offset(0,-1).getValue(); 
        var subject = "Request has been Approved";
        var body = "Hi " + name + ", your Change Request number " + case1 + " has been approved by " + approver1 + " with following comments: " + "\n\r" + comment;
        MailApp.sendEmail(email, subject, body);

        }

        if(e.range.getColumn() == 9 && e.value == "N")
       {
        var name = e.range.offset(0,-6).getValue();
        var comment = e.range.offset(0,-3).getValue();
        var email = e.range.offset(0,-7).getValue();
        var case1 = e.range.offset(0,1).getValue();
        var approver1 = e.range.offset(0,-1).getValue(); 
        var subject = "Request has been Disapproved";
        var body = "Hi " + name + ", your Change Request number " + case1 + " has been Dis-Approved by " + approver1 + " with following comments: " + "\n\r" + comment;
        MailApp.sendEmail(email, subject, body);

      }   

    }

StackDriver logging showed the following error, but couldn't identify where the issue exist while referring the cell addresses.

2018-09-14 08:19:06.336 PKT The coordinates or dimensions of the range are invalid. at sendNotification1(Code:6)

I am able to trigger an email on any edit event with following code, but no luck with conditional edit event trigger.

function sendNotification1(e) {
  MailApp.sendEmail("[email protected]", "Sample subject", "Sample body");
}

While debugging the code, I can see the following error TypeError: Cannot read property "range" from undefined. (line 6, file "Code")

Any help will be highly appreciated

Lines added as below function sendNotification1(e) { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName("Form Responses 1"); if(e.range.getColumn() == 9 && e.value == "Y") but still not getting email, when I ran debugger it shows TypeError: Cannot read property "range" from undefined. (line 6, file "Code") — Aatif Ali

Ron Kloberdanz Ron Kloberdanz · Accepted Answer · 2018-09-14T19:39:29

You are likely running into the issue of the code not knowing what range you are referring to, because you never tell it where to look.

Add the following to the top of the function:

var ss = SpreadsheetApp.getActiveSpreadsheet();   
var sheet = ss.getSheetByName("YOUR SHEET NAME");

And it should work perfectly.

EDIT: Here's the email I sent to myself when I tested it.

Imgur

Custom Email based on Cell values in google sheets

1 Answers