Google-sheets – How to modify this Google script to pull the email from column E in the Google sheet

google sheets

Instead of assigning one static email to the "var email" below (or for that matter multiple emails separated by commas), my question is how do I enter the column E value from the Google Sheet this script is pulling from? Column E contains an email value that varies depending on who is using the form, and we need the script to send to that address specifically each time it is filled out.

Any suggestions would be very helpful. I found similar and related posts, but nothing exactly like this. I also posted in Stack Overflow as I was unsure where this should be housed. 

function sendFormByEmail(e) 
{    
  // Enter email address for form respondent below.
  var email = “example@quest.com"; 

  var s = SpreadsheetApp.getActiveSheet();
  var headers = s.getRange(1,1,1,s.getLastColumn()).getValues()[0];    
  var message = "";
  var subject = "Instructional Rounds: ";

Best Answer

You could use a for loop and iterate down Column E, which is column 5. Then insert the cell values into an array.

var data = s.getDataRange().getValues();
//var data = s.getRange(1,5,s.getLastRow(),1).getValues();
var emails = [];

for (var row in data) {
    var cell_value = data[row][5];
    emails.push(cell_value);
}

for (var e in emails) {
    // TODO: Perform operations on emails[]
}
Related Topic