Send email to specific address listed on another sheet using Google Apps Script

Question

I am stuck at a point in some code and need some expert help. I want to reference column "E" on the sheet "Form Responses", which will return a name of a person. The same name can be found in column "A" of sheet "Email". In column "B" of sheet "Email" will be the email address that I want to send data to. I am stuck at how to produce this email address. Here is what I have so far...

function emailData(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var responses = ss.getSheetByName("Form Responses");
var lastRow = responses.getLastRow();
var values = responses.getRange("A"+(lastRow)+":AK"+(lastRow)).getValues();// get the range and values in one step
var headers = responses.getRange("A1:AK1").getValues();// do the same for headers
var recipient = responses.getRange("E"+(lastRow)).getValues();

var emailSheet = ss.getSheetByName("Email");
var names = emailSheet.getRange("A2:A20").getValues();
var emails = emailSheet.getRange("B2:B20").getValues();


var subject = "Capacity Campaign Form";
var message = composeMessage(headers,values);// call the function with 2 arrays as arguments

Logger.log(message);// check the result and then send the email with message as text body
MailApp.sendEmail(recipient,subject,message);
}

function composeMessage(headers,values){
var message = 'Here is the data from the form submission:'
for(var c=0;c<values[0].length;++c){
message+='\n'+headers[0][c]+' : '+values[0][c]
}
return message;
}

I must give props to @Serge for helping me with the array. Alas, any help that you could provide would be awesome!

Mogsdad Mogsdad · Accepted Answer · 2013-07-15T19:59:00

You've got the names and email addresses you need to search, in two 2-dimensional arrays. Each of those arrays is an array of rows. A simple search would be this:

var email = ''; // If we don't find a match, we'll fail the send
for (var row=0; row < names.length; row++) {
  if (names[row][0] == recipient) {
    email = emails[row][0];
    break; // end the search, we're done
  }
}

...
MailApp.sendEmail(email,subject,message);

There are more elegant ways to do this, I'm sure, but this should work for you.

Send email to specific address listed on another sheet using Google Apps Script

1 Answers