Email address selector in Google Apps script

Question

I have a script to select an email address depending on the result of a dropdown selector on a form which is working as intended.

But I have another dropdown which if "08-Maintenance" is selected I want it to override the email selector. This bit doesn't work. Any help greatly appreciated.

//select THIS spreadsheet
var LogSheet = SpreadsheetApp.getActiveSpreadsheet(); 

//select correct worksheet  
var sheet = LogSheet.getSheetByName("LOG"); 

//declare the row to look at (the last row)
var lastRowNumber = sheet.getDataRange().getNumRows();

//declare and obtain the variable for the Source Code in column 3  
var SourceValue = sheet.getRange(lastRowNumber, 3).getValue(); 

//declare and obtain the variable for the Location Code in column 4 
var LocationValue = sheet.getRange(lastRowNumber,4).getValue();

//declare and obtain the variable for the Hazard being reported in column 6 
var HazardValue = sheet.getRange(lastRowNumber,6).getValue();

//declare the correct email addresses to use
  var email01 = LogSheet.getSheetByName("LocationCodes").getRange(3, 5).getValue();
  var email02 = LogSheet.getSheetByName("LocationCodes").getRange(4, 5).getValue();
  var email03 = LogSheet.getSheetByName("LocationCodes").getRange(5, 5).getValue();
  var email04 = LogSheet.getSheetByName("LocationCodes").getRange(6, 5).getValue();
  var email05 = LogSheet.getSheetByName("LocationCodes").getRange(7, 5).getValue();
  var email06 = LogSheet.getSheetByName("LocationCodes").getRange(8, 5).getValue();
  var email07 = LogSheet.getSheetByName("LocationCodes").getRange(9, 5).getValue();
  var email08 = LogSheet.getSheetByName("LocationCodes").getRange(10, 5).getValue();
  var email09 = LogSheet.getSheetByName("LocationCodes").getRange(11, 5).getValue();
  var email10 = LogSheet.getSheetByName("LocationCodes").getRange(12, 5).getValue();
  var email11 = LogSheet.getSheetByName("LocationCodes").getRange(13, 5).getValue();
  var email12 = LogSheet.getSheetByName("LocationCodes").getRange(14, 5).getValue();
  var email13 = LogSheet.getSheetByName("LocationCodes").getRange(15, 5).getValue();
  var email14 = LogSheet.getSheetByName("LocationCodes").getRange(16, 5).getValue();
  var email15 = LogSheet.getSheetByName("LocationCodes").getRange(17, 5).getValue();
  var email16 = LogSheet.getSheetByName("LocationCodes").getRange(18, 5).getValue();
  var email17 = LogSheet.getSheetByName("LocationCodes").getRange(19, 5).getValue();
  var email18 = LogSheet.getSheetByName("LocationCodes").getRange(20, 5).getValue();
  var email19 = LogSheet.getSheetByName("LocationCodes").getRange(21, 5).getValue();  
  var email20 = LogSheet.getSheetByName("LocationCodes").getRange(22, 5).getValue(); 

//declare the correct LocationCodes to check
  var depot01 = LogSheet.getSheetByName("LocationCodes").getRange(3, 4).getValue();
  var depot02 = LogSheet.getSheetByName("LocationCodes").getRange(4, 4).getValue();
  var depot03 = LogSheet.getSheetByName("LocationCodes").getRange(5, 4).getValue();
  var depot04 = LogSheet.getSheetByName("LocationCodes").getRange(6, 4).getValue();
  var depot05 = LogSheet.getSheetByName("LocationCodes").getRange(7, 4).getValue();
  var depot06 = LogSheet.getSheetByName("LocationCodes").getRange(8, 4).getValue();
  var depot07 = LogSheet.getSheetByName("LocationCodes").getRange(9, 4).getValue();
  var depot08 = LogSheet.getSheetByName("LocationCodes").getRange(10, 4).getValue();
  var depot09 = LogSheet.getSheetByName("LocationCodes").getRange(11, 4).getValue();
  var depot10 = LogSheet.getSheetByName("LocationCodes").getRange(12, 4).getValue();
  var depot11 = LogSheet.getSheetByName("LocationCodes").getRange(13, 4).getValue();
  var depot12 = LogSheet.getSheetByName("LocationCodes").getRange(14, 4).getValue();
  var depot13 = LogSheet.getSheetByName("LocationCodes").getRange(15, 4).getValue();
  var depot14 = LogSheet.getSheetByName("LocationCodes").getRange(16, 4).getValue();
  var depot15 = LogSheet.getSheetByName("LocationCodes").getRange(17, 4).getValue();
  var depot16 = LogSheet.getSheetByName("LocationCodes").getRange(18, 4).getValue();
  var depot17 = LogSheet.getSheetByName("LocationCodes").getRange(19, 4).getValue();
  var depot18 = LogSheet.getSheetByName("LocationCodes").getRange(20, 4).getValue();
  var depot19 = LogSheet.getSheetByName("LocationCodes").getRange(21, 4).getValue();
  var depot20 = LogSheet.getSheetByName("LocationCodes").getRange(22, 4).getValue();


//send the email to the 'recipient' 
//if SourceValue is recorded as 08 - Mantenance Request System, the recipient is engineering.support@websiteaddress
//otherwise check location code and send to corresponding H&S Co-ordinator's email address

if (SourceValue == "08 - Mantenance Request System") {
    var recipient = "engineering.support@websiteaddress";

//Start with first depot in list

    } else if (LocationValue == depot01) {
    var recipient = email01;

    } else if (LocationValue == depot02) {
    var recipient = email02;

    } else if (LocationValue == depot03) {
    var recipient = email03;

    } else if (LocationValue == depot04) {
    var recipient = email04;

    } else if (LocationValue == depot05) {
    var recipient = email05;

    } else if (LocationValue == depot06) {
    var recipient = email06;

    } else if (LocationValue == depot07) {
    var recipient = email07;

    } else if (LocationValue == depot08) {
    var recipient = email08;

    } else if (LocationValue == depot09) {
    var recipient = email09;

    } else if (LocationValue == depot10) {
    var recipient = email10;

    } else if (LocationValue == depot11) {
    var recipient = email11;

    } else if (LocationValue == depot12) {
    var recipient = email12;

    } else if (LocationValue == depot13) {
    var recipient = email13;

    } else if (LocationValue == depot14) {
    var recipient = email14;

    } else if (LocationValue == depot15) {
    var recipient = email15;

    } else if (LocationValue == depot16) {
    var recipient = email16;

    } else if (LocationValue == depot17) {
    var recipient = email17;

    } else if (LocationValue == depot18) {
    var recipient = email18;

    } else if (LocationValue == depot19) {
    var recipient = email19;

    } else if (LocationValue == depot20) {
    var recipient = email20;

} else {

//and send to Engineering Support if there is no code as a catch all.
    var recipient = "engineering.support@websiteaddress";  //CHANGE TO ENG SUPP
}

//with the 'LocationCode' & 'SourceCode' as the subject
var subject = SourceValue ;  

//then write the'body' of the email including the link to see the changes
var body = 'A new [' + SourceValue + '] log entry has been recorded at [' + LocationValue  + '],  listed as [' + HazardValue + '], please click the link > > http://goo.gl/shortcode < < to view details.';

{ 
//then send the email 
MailApp.sendEmail(recipient,subject,body); 
}

I have changed the website address and goo.gl short code for obvious reasons!

`Mantenance` is spelled wrong. Is that the problem? And I'd use triple equal signs for comparison. — Alan Wells, Jan 20 '15 at 14:09
Thanks Sandy - that seems to have nailed it. Can you expand on where the triple equals go? Do you mean instead of a double? And why...? — witham, Jan 20 '15 at 15:14

score 1 · Accepted Answer · edited May 23 '17 at 12:20

1

It looks like your comparison value is misspelled:

Should it be Maintenance instead of Mantenance:

if (SourceValue == "08 - Mantenance Request System") {
    var recipient = "engineering.support@websiteaddress";

//Start with first depot in list

    } else if (LocationValue == depot01) {
    var recipient = email01;

And I'd use triple equal signs for comparison:

if (SourceValue === "08 - Maintenance Request System") {

You can search a post like this one for the difference:

Stack Overflow Link

edited May 23 '17 at 12:20

Community

1
1

answered Jan 20 '15 at 15:30

Alan Wells

30,746
15
104
152

Thanks again. It was a spelling mistake and it is sorted. Can you explain why you would use three = instead of two? Both seem to work. – witham Jan 21 '15 at 15:43
Triple equal signs are a "Strict" comparison, both the value and the type must be the same. The triple equals signs produce results that are more intuitive; they give you what you expect. Sometimes the double equal signs give you a result that is unexpected because people don't understand what it's really doing. [Comparison Operators - Mozilla Documentation](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Operators/Comparison_Operators) – Alan Wells Jan 21 '15 at 16:01

Email address selector in Google Apps script

1 Answers1