1

I have a registration form that we are using for my company. We have 6 locations. I want to have the registration form that is filled out on Google forms to go to specific emails based on their answer for what location they are at. I have tried a few scripts, and have had no luck.

0

You'd write an if then statement that covers your 6 locations. It'd be something like this . . . though this isn't exact.

function sendEmails() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();

  var lastRow = getLastRow();
  var range = sheet.getRange('C'. lastRow);//location cell
  var location = range.getValue();
  if (location === 'heaven'){
     emailAddress = 'heaven@location.com'
     } 
    elseif (location === 'hell'){
     emailAddress = 'hades@location.com'
   } elseif (location === 'purgatory') {
     emailAddress = 'purg@location.com' 
   }else { 
     emailAddress ='void@location.com'
   }


MailApp.sendEmail({
     to: emailAddress,
     subject: subject,
     htmlBody: message     
   });
1

Alternatively, if you expect that the individuals to whom the email is to be sent for each location might change occasionally, you might consider creating another sheet with a table which can be modified without touching the script:

| Location 1 | john.doe@email.com |

| Location 2 | jane.smith@email.com |

Then you could use something like this to pull values from the table (forgive my naming conventions):

  var location = range.getValue();

  var sheet2 = ss.getSheetByName('NameOfSheetWithTable');
  var range2 = sheet2.getRange(1, 1, 2, 2); // adjust to your table's range
  var data2 = range.getValues();
    for (var i=0; i < data2.length; i++) {
    for (var j=0; j < data2[i].length; j++) {
      var location1 = data2[0][0];
      var location2 = data2[1][0];
      var email1 = data2[0][1];
      var email2 = data2[1][1];
      }
      } 
  if (location == location1) {emailAddress = email1;}
  else if (location == location2) {emailAddress = email2;}
  // etc etc

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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