0

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

}
2
  • Welcome to Web Applications. Instead of using Range.getValues() use Range.getValue(). This because the first returns and Array of Arrays of numbers, strings, Booleans or Date objects and comparing an Array to another Array as is done in always returns false. The second will return a number, string, Boolean or Date object. If you need further help, please read developers.google.com/apps-script/guides/sheets, add some sample data, the corresponding expected result and a brief description of your search efforts as is suggested in How to Ask.
    – Rubén
    Jun 11, 2022 at 1:30
  • Hi, welcome to WebApps. Something for your future reference when drafting questions... include as much information about your scenario as possible, including some sample data. For example, you said "and it worked only for the first 2 names in my list..." what do we make of that? - if you had three records, it might mean that it is just skipping the last record; if you had 100 records, then the implications are more serious. I think your question has been answered, but next time around please provide the full/complete story.
    – Tedinoz
    Jun 25, 2022 at 1:22

1 Answer 1

0

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

missing handoff


Contacts

contacts


Message text

message text


Output

output1


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)

handoffs2


Output

output2


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.

7
  • This is incredible, thanks! I have a follow up question: with the second example, I am trying to understand how to adjust the range for the 'Missing Handoff (Google Form)' sheet to get the missing data from it. It has 43 columns (with the missing item on column 12), so I put it as: var missingRange = missing.getRange(2,1,missingLR-1,43). With this, I am not sure if I should adjust the number of e's in the map function that you used: var missingNames = missingValues.map(function(e){return e[0];});//[[e],[e],[e]]=>[e,e,e]
    – Tony Call
    Jun 28, 2022 at 20:29
  • 2 unrelated issues: #1) map converts missingValues from a 2D array into new 1D array=missingNames. In the example, User Name is Column A so return e[0] (zero-based array index). Your sheet, as output of a form, Column A probably = timestamp and User Name might be, say, Column D; so you would modify this code to, say, return e[3]. #2) In the example, Missing Items is Column B so newitems = missingValues[d][1] and newitems = newitems+","+missingValues[d][1]. But your data is in Column L(=11 on a zero-based index) so you would change the code to missingValues[d][11].
    – Tedinoz
    Jun 30, 2022 at 8:37
  • I've added the specific lines for you to edit to reflect your data; refer to the amended answer. BTW, if the answer was helpful, you might consider "accepting it".
    – Tedinoz
    Jun 30, 2022 at 8:43
  • So I updated the code, and for some reason it just says execution started and then execution completed without sending the emails. I troubleshooted it with the logger.log statements, and those work, but the other code doesn't seem to work. What could I be doing wrong? Note, I have var missingNameCol = 5 (column E). I also updated the return as suggested, to 'return e[4]', since this is the column with the names. (thanks for your help by the way, I appreciate it. Once we get this figured out, I'll accept your answer because it has been very insightful)
    – Tony Call
    Jul 1, 2022 at 16:48
  • It's because this line ` // MailApp.sendEmail(missingEmail,emailSubject,eMailmessage);` is commented out. Remove the "//", save and run and it should be OK.
    – Tedinoz
    Jul 2, 2022 at 11:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.