Google-sheets – Send a Google Forms notification to two differenz email addresses based on

google sheetsgoogle-apps-scriptgoogle-formsjavascript

How can I send an automatic email notification to two different email addresses depending on whether a woman or a man has filled in the form? Please can someone help me with a script to check this situation? I have tried with the following script, but it seems missing a ';' and I don't find the problem. And so I don't know if it works.

function sendEmails() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = SpreadsheetApp.getActiveSheet();
      var lastRow = getLastRow();
      var range = sheet.getRange('A'. lastRow);//location cell
      var gender = range.getValue();

      if (gender === 'female'){
         emailAddress = 'email1@gmail.com'
         }

      elseif (gender === 'male'){
         emailAddress = 'email2@gmail.com'
       }

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

Best Answer

There is an error on elseif. The correct syntax is else if

Related Solutions

Google Sheets – Sending Email Notifications Based on Timeframe and Cell Data

Here's a pseudocode version of what he was saying:

What he wrote...

counter = 0

loop rows {
    if something {
        add to message
        increment counter
    }
}

if counter {
    send message to one person
}

What you need to write:

counter = 0

loop rows {
    if something {
        make message
        send message to person in row
    }
}

You will need to access the data from the email column as well. I'll leave the rest to you to work out, except to say that you should read this article on how to get the data out of your sheet efficiently.

Google Forms – Can It Send Emails Automatically?

Please note: Under Settings --> Presentation one can set the Confirmation message that will only be shown on the browser screen upon submission.

What you ask for is that:

... the Google Form will automatically send an e-mail ...

You will find that there is a number of ways to make this possible.

Edit Begin

(By now Google has integrated an option for such an action in the form itself)

Using the form

Click on the Settings icon (top right hand side, just in front of the SEND button).
Under GENERAL select Collect email address.
You will be presented with the Response receipts message that gives two options: If respondent requests it and Always.
Select accordingly.

When the If respondent requests it option is selected, the respondent will see the following message upon submitting the form:

Edit End

Using a script

For your convenience I have created a form including but not limited to the fields Event , FirstName , LastName , Email. Please have a look at it.

To write the script we will use the Script Editor on the answers spreadsheet linked to the form (not the script editor in the form).

Go to Tools -> Script editor...
When in the script editor, erase all the presented code and replace it with either of the following:

A really minimal code:

// Auto-Confirmation Email to submitter

// THE SIMPLEST WAY

   function AutoConfirmation(e){
      var theirFirst = e.values[2];
      var theirEmail = e.values[5];
      var theEvent = e.values[1];
      var subject = "Form Submitted";
      var message = "Thank you, " + theirFirst + " for the expressed interest in our " + theEvent;

   MailApp.sendEmail (theirEmail, subject, message);
   }

These very few lines of code are enough for a basic reply.
The e.values 2, 5 and 1 correspond to the columns on the spreadsheet where the answers are submitted. Keep in mind that the numbering for the columns start with 0. Zero being the column reserved for the timestamp.
The code being column depended may be a bit difficult to understand, especially when we have more than a few answer fields.

On the other hand you may want to use a more advanced but also user friendly code. The following code does not depend on the columns numbers but on the headers of the spreadsheet (questions of the form).
Since the code is easier to follow we can make it so we get a more personalized message enriched with more fields.

A more user friendly, personal, enriched code

// Auto-Confirmation Email to submitter WITH appended answers

//  A PERSONALIZED, ENRICHED WAY 

function AutoConfirmation(e) {

    try {

        var theirEmail, subject, message; // must have
        var bcc, ourName, theirName; // optional
        var theEvent, anyAdults, anyKids; // depending on various form fields

        // This is the submitter's email address
        theirEmail = e.namedValues["Email"].toString();

        // We could sent a copy to our email address using the BCC or CC field,
        // OR to any other email by using = "mail-01@test.com, mail-02@domain.com"
        bcc = Session.getActiveUser().getEmail();

        // This will be the sender's name
        // If omitted, it will be replaced by our email
        ourName = "Our Company";

        // Optional but more personal
        theirName = e.namedValues["FirstName"].toString();

        //This has to go over the subject since it is used by it
        theEvent = e.namedValues["Event"].toString();

        // vars can be used at the email subject as well.
        subject = "Confirmation for " + theirName + " at " + theEvent;

        // The following depend on the various other form fields
        // and can be omitted
        anyAdults = e.namedValues["Adults"].toString();
        anyKids = e.namedValues["Kids"].toString();

        // This is the body of the auto-reply message
        message =  
          "Dear " + theirName + 
          ", <br> Thank you for your expressed interest in our " + theEvent + 
          ". <br> Places for " + anyAdults + " and " + anyKids + 
          " will be reserved for you. <br> We will all have a great time." + 
          "<br>---------------<br>" + 
          "Please also find a copy of your submitted details. <br><br>";

        // This part is for appending ONLY the answers that are NOT BLANK
        // OR, to include ALL of the answers, modify 
        // the if ( e.namedValues[key] && (e.namedValues[key] != "") ) 
        // to: ( e.namedValues[key] )

        var ss, columns; // needed if we want to include the answers as well

        ss = SpreadsheetApp.getActiveSheet();
        columns = ss.getRange(1, 1, 1, ss.getLastColumn()).getValues()[0];

        for ( var keys in columns ) {
            var key = columns[keys];
            if ( e.namedValues[key] && (e.namedValues[key] != "") ) {
                message += key + ' :: '+ e.namedValues[key] + "<br>"; 
            }
        }

        textbody = message.replace("<br>", "\n\n");

        // before emailing set up one last var
        var cosmetics = {bcc: bcc, name: ourName, htmlBody: message};

        // You can use MailApp.sendEmail as well
        GmailApp.sendEmail(theirEmail, subject, message, cosmetics );

    } catch (e) {
        Logger.log(e.toString());
    }

}

The above code is scalable and very flexible. You can easily add or omit variables, send copies to other emails or to yourself for debugging, append the answers and in general create a more personalized message for the submitter.

Still. If you simply do not want to code any, there is an alternative method.

Using an add-on

A number of add-ons can be found in the Google Web Store.

Best Answer

Related Solutions

Google Sheets – Sending Email Notifications Based on Timeframe and Cell Data

Google Forms – Can It Send Emails Automatically?

Edit Begin

Using the form

Edit End

Using a script

Using an add-on

Related Topic