if and for statements on Google Sheets with multiple tabs

Question

I am trying to figure out how to send an email to a bunch of people on my mailing list, but with it being individualized for each person. I have their names and email addresses on one Google Sheet (the email tab). On the same file, I have their name and the item that they are missing on another sheet tab (the master sheet). Basically, I would like to have an email sent to each person with their name and missing item (s) listed so that they know what they need to turn into the warehouse.

Below you will see my Apps Script code. I was able to test it, and it worked only for the first 2 names in my list, when the name in the cell on one tab equaled the name on the other one that was on the master list. There are a couple of problems that I've come across with this:

1. It only emails for the name when it equals the name on the master sheet. If names are not in the exact same order, then it doesn't work. This is an issue because the master sheet is continuously updating, so the email sheet doesn't have the names in the exact same order as the master sheet.
2. There are also multiple of the same names on the master sheet. This is because some people have more than one missing item. So I am trying to figure out how to have the code list all the missing items for that person in the same email. (I also have a message I've written on another tab to be sent in the email).

So based on these problems, I am wondering if I am using the if else statement right. Or any of this. I am still pretty new to Apps Script, so any advice would be appreciated.

---

function sendEmail() {
  
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheetname = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Missing Handoff (Google Form)');
    var sheet1 = ss.getSheetByName('Email Messaging');
    var sheet2 = ss.getSheetByName('Emails');
    var sheet3 = ss.getSheetByName('Missing Handoff (Google Form)')
    var subject = sheet1.getRange(2,1).getValue();
    var n = sheet2.getLastRow();
    
    for (var i = 2; i < n + 1; i++) {
      
      var emailAddress = sheet2.getRange(i,2).getValues();
      var name1 = sheet2.getRange(i,1).getValues();
      var name2 = sheet3.getRange(i,5).getValues();
      var itemMissing = sheet3.getRange(i,12).getValues();
      var message = sheet1.getRange(2,2).getValues();
      
      if (name1 == name2) {

        message = message.replace("<name>",name2).replace("<item>", itemMissing);
        MailApp.sendEmail(emailAddress,subject,message);
      } else {
        message = ""
      }
    
    }

}

Answer 1

Please note that I've also renamed key sheets and variables to create more meaningful and intuitive names. Also, it is certain that there are more elegant, and probably efficient, ways of executing certain tasks but I will leave others to point those out.

---

Problem#1: it worked only for the first 2 names in my list

The existing code contains some issues that affect outcomes &/or efficiency.

• getValue (a time-costly method) is executed inside the loop
• The logic assumes that all the names on the sheet:"Emails" are in the same order as the names on sheet: 'Missing Handoff (Google Form)' AND that all those names will receive an email.
• the loop starts with i=2 which means that the first two rows of any data (Row#0 & Row#1) are ignored.

---

// a script to send email based on list of users no dups
function sendEmail() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  // get the sheet containing the missing data
  var missing = ss.getSheetByName('Missing Handoff (Google Form)')
  
  // test for whether there is any "missing" data
  // get the range for missing stuff
  var missingLR = missing.getLastRow()
  // Logger.log("DEBUG: Missing data last row = "+missingLR)
  // test for existing of any missing values
  if (missingLR===1){
    // LR is the header row, so no data = no email
    Logger.log("DEBUG: No data, so no email to send")
    return
  }
  
  // there is Missing data so continue processing

  // establish the other sheets
  var emailMessage = ss.getSheetByName('Email Messaging');
  var contacts = ss.getSheetByName('Emails');

  // get missing data
  var missingRange = missing.getRange(2,1,missingLR-1,2)
  // Logger.log("DEBUG: Range for the missing data = "+missingRange.getA1Notation())
  var missingValues= missingRange.getValues()
  // Logger.log("DEBUG: Missing data - number of records = "+missingValues.length)
  
  // get the contacts list
  var contactsLR = contacts.getLastRow()
  var contactsRange = contacts.getRange(2,1,contactsLR-1,2)
  // Logger.log("DEBUG: Contacts range = "+contactsRange.getA1Notation())
  var contactsValues = contactsRange.getValues()
    
  // get the email message and subject
  var emailMessageTemplate = emailMessage.getRange(2,2).getValue()
  // Logger.log("DEBUG: Message template: "+emailMessageTemplate)
  var emailMessageSubject = emailMessage.getRange(2,1).getValue()
  // Logger.log("DEBUG: Message subject: "+emailMessageSubject)     

  //loop through the missing data
  for (var i = 0; i <missingValues.length; i++) {
    var missingName = missingValues[i][0]
    var missingItem = missingValues[i][1]
    // Logger.log("DEBUG: Missing stuff#"+(i+1)+" Name:"+missingName+", Item:"+missingItem)
    
    // now loop through the contact names to find a match
    for (var c = 0;c<contactsValues.length;c++){
      //Logger.log("DEBUG: Contacts#:"+c+" Name:"+contactsValues[c][0]+", Email:"+contactsValues[c][1])

      if (missingName === contactsValues[c][0]) {
        // found a match, get the email address
        // Logger.log("DEBUG: c:"+c+" "+missingName+" matched "+contactsValues[c][0])
        var missingEmail = contactsValues[c][1]

        // build the email
        var emailMessage = emailMessageTemplate.replace("<name>",missingName).replace("<item>", missingItem)
        Logger.log("DEBUG: Message: "+emailMessage)
        // Send email
        // MailApp.sendEmail(missingEmail,emailSubject,eMailmessage);
        // Logger.log("DEBUG: sent email")
        break
      }
      else{
          // no match
          //Logger.log("DEBUG: c:"+c+" "+missingName+" did NOT match "+contactsValues[c][0])
      }
    
    }

  }

}

---

Missing handoff

---

Contacts

---

Message text

---

Output

---

Problem#2: There are multiple of the same names on the master sheet because some people have more than one missing item

Much of the first lot of code is re-used here. But there are three new issues to be addressed in order to produce a useful list of names and missing items:

• build a list of any duplicate names on Missing handoff
• add their Missing Items to the list
• add the names and missing items for non-duplicated persons to the list

H/T @MattKing for the elegant/efficient answer in StackOverflow Duplicate values on column A to column B - Google Apps Script for finding the duplicated name

---

// a script to send email based on list of users containing duplicate names
function sendEmailwithDups() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()

  // get the missing containing the missing data
  var missing = ss.getSheetByName('Missing Handoff2 (Google Form)')
  
  // test for whether there is any "missing" data
  // get the range for missing stuff
  var missingLR = missing.getLastRow()
  // Logger.log("DEBUG: Missing data last row = "+missingLR)
  // test for existing of any missing values
  if (missingLR===1){
    // LR is the header row, so no data = no email
    Logger.log("DEBUG: No data, so no email to send")
    return
  }
  
  // there is Missing data so continue processing

  // get missing data
  var missingRange = missing.getRange(2,1,missingLR-1,2)
  // Logger.log("DEBUG: Range for the missing data = "+missingRange.getA1Notation())
  var missingValues= missingRange.getValues()
  // Logger.log("DEBUG: Missing data - number of records = "+missingValues.length)
  var missingNames = missingValues.map(function(e){return e[0];});//[[e],[e],[e]]=>[e,e,e]
  //Logger.log("DEBUG: Missing Names: "+missingNames)

  // create an array for the list
  var dups = new Array()
  // create a temporary array
  var tempArray = new Array()

  // identify the duplicate names
  missingNames.forEach(function (e,i,arr){if(missingNames.indexOf(e)!=i && tempArray.indexOf(e)==-1){tempArray.push(e)}});

  // now get the missing items for the duplicate names
  var tempArrayLen = tempArray.length

  // loop through the duplicate list
  for (var i=0;i<tempArrayLen;i++){
   var newitems = ""
   var rowdata = new Array()
   
    // loop through the Missing Values
    for (var d=0;d<missingValues.length;d++){
     
      if (tempArray[i]=== missingValues[d][0]){
        if (newitems.length==0){
          newitems = missingValues[d][1]
        }else{
          newitems = newitems+","+missingValues[d][1]
        }
      }
    }

    // push the values onto an array
    rowdata.push(tempArray[i])
    rowdata.push(newitems)
    // push the row data onto the duplicate list
    dups.push(rowdata)
  }
 
  // now get the missing values for the NON-duplicates
  // loop through the missing Values looking for a match in the dups
  // if there is a match, ignore it
  // if no match, add missing values to dups
  var dupNames = dups.map(function(e){return e[0]})  //[[e],[e],[e]]=>[e,e,e]
  
  for (var z=0;z<missingValues.length;z++){

  var result = dupNames.indexOf(missingValues[z][0])
  // Logger.log("DEBUG: Name: "+missingValues[z][0]+", result = "+result)
    if (result === -1){
      var newline = new Array()
      // indexOf = -1 means not matched
      // so add details to duplicates list
      newline.push(missingValues[z][0])
      newline.push(missingValues[z][1])
      dups.push(newline)
       
    }
    else{
      // found a match so ignore it.
      // Logger.log("DEBUG: Matched Name: "+missingValues[z][0]+", result = "+result)
    }
  }
  // this completes getting all the names and items for "Missing handoff" 
  // now continue with generating emails

  // establish the other sheets
  var emailMessage = ss.getSheetByName('Email Messaging')
  var contacts = ss.getSheetByName('Emails')

  // get the contacts list
  var contactsLR = contacts.getLastRow()
  var contactsRange = contacts.getRange(2,1,contactsLR-1,2)
  // Logger.log("DEBUG: Contacts range = "+contactsRange.getA1Notation())
  var contactsValues = contactsRange.getValues()

  // get the email message and subject
  var emailMessageTemplate = emailMessage.getRange(2,2).getValue()
  // Logger.log("DEBUG: Message template: "+emailMessageTemplate)
  var emailMessageSubject = emailMessage.getRange(2,1).getValue()
  // Logger.log("DEBUG: Message subject: "+emailMessageSubject)


  //loop through the missing data
  for (var i = 0; i <dups.length; i++) {
    var missingName = dups[i][0]
    var missingItem = dups[i][1]
    // Logger.log("DEBUG: Missing stuff#"+(i+1)+" Name:"+missingName+", Item:"+missingItem)
    
    // now loop through the contact names to find a match
    for (var c = 0;c<contactsValues.length;c++){
      //Logger.log("DEBUG: Contacts#:"+c+" Name:"+contactsValues[c][0]+", Email:"+contactsValues[c][1])

      if (missingName === contactsValues[c][0]) {
        // found a match, get the email address
        // Logger.log("DEBUG: c:"+c+" "+missingName+" matched "+contactsValues[c][0])
        var missingEmail = contactsValues[c][1]

        // build the email
        var emailMessage = emailMessageTemplate.replace("<name>",missingName).replace("<item>", missingItem)
        Logger.log("DEBUG: Message: "+emailMessage)
        // Send email
        // MailApp.sendEmail(missingEmail,emailSubject,eMailmessage);
        // Logger.log("DEBUG: sent email")
        break
      }
      else{
          // no match
          //Logger.log("DEBUG: c:"+c+" "+missingName+" did NOT match "+contactsValues[c][0])
      }
    
    }

  }

}

---

Missing handoff (with dups)

---

Output

---

Edit: apply to OP live data

• add the following two lines between rows 21 and 22
  • var missingNameCol = 3 //Column C
  • var missingStuffCol = 12 // Column L
• edit Line#23 var missingRange as follows"
  • var missingRange = missing.getRange(2,1,missingLR-1,missingStuffCol)
• edit Line#26 var missingNames as follows:
  • var missingNames = missingValues.map(function(e){return e[(+missingNameCol-1)]}) //[[e],[e],[e]]=>[e,e,e]
• edit Line#48 if (tempArray[i]=== missingValues[d][0]){ as follows:
  • if (tempArray[i]=== missingValues[d][missingNameCol-1]){
• edit Line#50 newitems = missingValues[d][1] as follows:
  • newitems = missingValues[d][missingStuffCol-1]
• edit Line#52 newitems = newitems+","+missingValues[d][1] as follows:
  • newitems = newitems+","+missingValues[d][missingStuffCol-1]
• edit Line#72 var result = dupNames.indexOf(missingValues[z][0]) as follows:
  • var result = dupNames.indexOf(missingValues[z][missingNameCol-1])
• edit Lines#78 as follows:
  • newline.push(missingValues[z][missingNameCol-1])
• edit Line#79 as follows:
  • newline.push(missingValues[z][missingStuffCol-1])

Change any Logger statements to reflect any changes in the relevant variables/lines.