Technology

How to Send Confirmation Emails With Google Forms

July 9, 2024

Google Forms is a versatile tool for creating surveys, quizzes, and data collection forms. One useful feature is the ability to send confirmation emails to respondents after they submit a form. This can be helpful for thanking them, providing additional information, or confirming their responses. In this article, we will explore how to set up automatic confirmation emails using Google Forms and Google Apps Script.

Prerequisites:

A Google account.
A Google Form.
Basic understanding of Google Sheets and Google Apps Script.

Step 1: Create Your Google Form

Create a Google Form:
- Go to Google Forms and create a new form.
- Design your form with the necessary questions.
Collect Email Addresses:
- Ensure your form includes a field for collecting respondents’ email addresses. You can add this by selecting the “Email” question type.
Link the Form to a Google Sheet:
- Click on the “Responses” tab in your Google Form.
- Click on the green Sheets icon to create a linked Google Sheet where responses will be recorded.

Step 2: Set Up the Google Sheet

Open the Google Sheet:
- Open the Google Sheet that is linked to your form responses.
Add a Confirmation Column:
- Add a new column header, for example, “Confirmation Sent”, to track if confirmation emails have been sent.

Step 3: Write the Google Apps Script

Open the Script Editor:
- In the Google Sheet, go to Extensions > Apps Script. This will open the Google Apps Script editor.
Write the Script:
- Copy and paste the following script into the Apps Script editor.

javascript

function sendConfirmationEmail(e) {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses 1');

  var row = e.range.getRow();

  var emailAddress = sheet.getRange(row, 2).getValue(); // Adjust column number based on your sheet structure

  var confirmationSent = sheet.getRange(row, 3).getValue(); // Adjust column number based on your sheet structure
  if (emailAddress && confirmationSent !== 'Yes') {

    var subject = 'Thank you for your submission';

    var message = 'Dear participant,\n\nThank you for filling out the form. We have received your response.\n\nBest regards,\nYour Team';
    MailApp.sendEmail(emailAddress, subject, message);

    sheet.getRange(row, 3).setValue('Yes'); // Adjust column number based on your sheet structure

  }

}

function createOnFormSubmitTrigger() { ScriptApp.newTrigger('sendConfirmationEmail') .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet()) .onFormSubmit() .create(); }

Save the Script:
- Save your script by clicking on File > Save. Give your project a name, such as “ConfirmationEmailScript”.

Step 4: Set Up the Trigger

Create the Trigger:
- In the Apps Script editor, go to the clock icon (Triggers) in the left panel.
- Click on “Add Trigger” and set up the trigger as follows:
  - Choose which function to run: sendConfirmationEmail
  - Choose which deployment should run: Head
  - Select event source: From spreadsheet
  - Select type of event: On form submit

Step 5: Customize and Test

Customize the Email:
- Modify the subject and message variables in the script to match your needs.
Test the Script:
- Submit a test response through your Google Form to ensure the script works as expected. Check the “Confirmation Sent” column in your Google Sheet to see if it updates correctly and verify that the confirmation email is received.
Monitor and Troubleshoot:
- Regularly check the Google Sheet to ensure everything is working smoothly. Adjust the script or troubleshoot any issues if necessary.

Additional Tips

Personalize the Email: You can personalize the confirmation email by including responses from the form in the email body. For example, if your form collects the respondent’s name, you can include it in the email message.
Error Handling: Add error handling in your script to manage cases where emails fail to send.
Styling: Enhance the email content by using HTML for better formatting. For instance: