Send Email to Different Addresses Based on Form Responses in Google Script

That title’s SEO gold, Jerry!

I’ve used this pattern several times recently so I figured I’d write it up and break up the endless stream of “Weekly Web Harvest” posts.

Google Forms are great but the options for doing more with emails are limited. Often people want to be alerted to a submission or to have emails go to multiple, or different, people based on form submissions. This need gives you the opportunity to do some Google Script work.¹

Get All the Data

One frequent request is to get all the responses. That’s what we’re doing in the function below. In this case, there were a number of conditional fields and we didn’t want a bunch of empty spaces or “no information submitted” responses so we check with if(answer != ”). If there’s something there, we’ll include it. If not, the else statement occurs. I left it in there as other people have requested it say “not submitted” or something like that.

function betterAllData(){
  const sheet = SpreadsheetApp.getActiveSheet();//get the sheet
  const rows = sheet.getDataRange();
  const lastRow = rows.getLastRow();
  const newEntry = sheet.getRange('A'+lastRow+':AJ'+lastRow).getValues();//Set range you want to get data from
  let body = '';
  let titleCount = 1;//if you're not starting with column A, you can use this to get the right titles
  for (var row in newEntry) {
    for (var col in newEntry[row]) {
    let question = sheet.getRange(1,titleCount+parseInt([col])).getValue();
    let answer = newEntry[row][col];
    if(answer != ''){
      body = body +`<strong>${question}</strong><br><p>${answer}</p></br>`;//add form question and response if filled in
    } else {
      body = body +``;//don't add anything if no response
    }
  }
}
return body;
}

Email Someone Particular

Depending on what kind of thing someone wants to buy, we want to email a different librarian. So we had to do two things. First, we had to see what the media type was and then we had to change the email addresses to reflect the associated librarian.

function getLibrarian(){
  const sheet = SpreadsheetApp.getActiveSheet();//get our sheet
  const rows = sheet.getDataRange();
  const lastRow = rows.getLastRow();//get the most recent/last row
  const requestType = sheet.getRange('G'+lastRow).getValue();//get the field value we want to choose by
  if(requestType === 'Ebook'){
    return 'foo@middlebury.edu, bar@middlebury.edu';//note the use of a comma not a semicolon
  }
  if(requestType === 'Print book'){
    return 'buzz@middlebury.edu, bang@middlebury.edu'
  }
  if(requestType === 'CD'){//I guess we still buy enough CDs to justify this
    return 'blitz@midlebury.edu, bar@middlebury.edu';
  }
  if(requestType === 'Video'){
    return 'foo@middlebury.edu, bang@middlebury.edu';
  }
  if(requestType === 'Journal or database'){
    return 'buzz@middlebury.edu, bar@middlebury.edu';
  }
}

Form Values in the Email Subject

In this case there were a couple of values from the form that we wanted in the email subject line. They were in columns B and F. B plus the lastRow would get us something like B14, then we .getValue() and we’ve got the submitted data.

function getlibSubject(){
   const sheet = SpreadsheetApp.getActiveSheet();
   const rows = sheet.getDataRange();
   const lastRow = rows.getLastRow();
   const facName = sheet.getRange('B'+lastRow).getValue();
   const needDate = sheet.getRange('F'+lastRow).getValue();
   return facName + ' - ' + needDate;
}

Send the Email

Finally, we send the email. The functions we’ve written above come together to fill out the data needed for the email.

There are a couple nice things you can do. While you can only authorize your own logged in account to send the email, you can set the replyTo and the visible name to be anything you want. That way when you do this for someone else it can appear to come from a particular name and when they hit reply it doesn’t go to you. This saves me from having to walk someone else through how to activate the script in their own account. Not perfect but pretty nice.

function generalEmail() {
  const recipient = getLibrarian(); //from our earlier logic
  const subject = getlibSubject(); //the combined elements from the form
  const body = betterAllData(); //all the stuff 
  MailApp.sendEmail({
    to: recipient,
    replyTo: 'foo@middlebury.edu',
    name: 'Library Purchase Request Form',
    subject: subject,
    htmlBody: body,
  });
}