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.
Range.getValues()
useRange.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.