Automated Email from Google Form Submissions

If you’ve ever created a Google Form to capture the information you need, great! However you’ll soon get to a point where you need to keep checking your form responses to see if there have been any new submissions. Well there’s a simple way to get the form response sheet to email you once a response is submitted, we’ll use Google App Script to automate this process for us.

Creating your Google Form

First thing’s first, you’ll need to create your Google Form, for this example I’ve just put together a quick Customer Experience Survey form:

Creating your Google Form Responses Sheet

Now you’ve created your form, you’ll need to make your Form Responses sheet, you can do this by clicking on the “Responses” tab, and clicking on the Google Sheet’s icon, this will give you a prompt to select your response destination, by leaving the defaults you’ll store the response sheet with the Google Form in the same location in Google Drive, and click Create.

Google App Script

We’ve got everything that we need now to apply some google app script to our form responses sheet. In your google sheet, click Tools > Script Editor…

This will launch the script editor interface, with the default Untitled project view.

Give your project an appropriate name, i.e. Customer Experience Email

Time to add some scripting:

//Creates a variable called emailSent with the string text Email Sent
var emailSent = "Email Sent";

function email_Sent() {
  //Sets a variable called spreadsheet which get's the active sheet
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName("Form responses 1");
  
  // Tell's the script which row of data to process first
  var startRow = 2;
  var lastRow = sheet.getLastRow() -1;
  
  // Fetch the range of data
  var dataRange = sheet.getRange(startRow, 1, lastRow, 6);
  
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();

Breaking down the script:

1. Firstly, I created a variable called emailSent with some text we want to update on the spreadsheet after script has been executed
2. Then, I renamed my function to something a little more appropriate, i.e. email_Sent
3. Now, I told the script that the spreadsheet is the form responses sheet as it’s the only active spreadsheet
4. Then, I told the script that we’ll start processing data from row number 2
5. Next, we’ll need to tell the script what rows and columns the data data we’re looking for is
6. Finally, we’ll tell the script to go and get the values for those row’s and column’s

Next, we need to create some variables for the data that requires capturing:

  for (var i = 0; i < data.length; ++i) {
    var column = data[i];
    //Timestamp Variable - Autocreated by Google Forms in Column 1
    var timeStamp = column[0];
    //Name Variable - Captures the Name in Column 2
    var name = column[1];
    //Email Address Variable - Captures the Email Address in Column 3
    var emailAddress = column[2];
    //Service Rating Variable - Captures the Service Rating in Column 4
    var serviceRating = column[3];
    //Recommendation Variable - Captures the Recommendation in Column 5
    var recommendation = column[4];
    //Email Sent Variable - Captures the Email Sent Notifications in Column 6
    var emailNotification = column[5];

Now let’s set up our email notification using our variables and our email checking mechanism which checks the “Email Sent” Column and if it can’t find the presence of the emailSent variable in our case the text Email Sent, then it’ll action the email. Once processed the email we’ll tell the script to update the email sent column with the emailSent variable:

    //Email Checking Mechanism - Check's Column 6 if an email has already been sent, if the text "Email Sent" is present in Column 6 it will not process this row
    if (emailNotification != emailSent)
    {
      //Your email
       MailApp.sendEmail({
        to: "[email protected]", 
        subject: "Customer Experience Form Submission",
        htmlBody: "Hi," + "<br><br>" + 
         "A submission has been made on the Customer Exprience Survey, please find the details below: " + "<br><br>" +  
        "What's your name?" + "<br><br>" + 
        name +  "<br><br>" + 
        "What's your email address?" + "<br><br>" + 
        emailAddress + "<br><br>" + 
        "How did you find our service today?" + "<br><br>" +
        serviceRating + "<br><br>" + 
        "Would you recommend us?" + "<br><br>" +
        recommendation + "<br><br><br>" +
         "Timestamp: " + timeStamp + "<br><br>" +
        "Thanks"
       });
      //Updating Column 6 once the email has been set with the emailSent variable
       sheet.getRange(startRow + i, 6).setValue(emailSent);
    }
  }
}

Setting up your script trigger

Now that we’ve got our script just how we want we’ll need to tell it when to run and give it the permissions to be able to send email and update the form responses sheet in Google Drive.

To set up your script trigger, click Resources > Current project triggers…

Click, No triggers set up. Click here to add one now

And set your project trigger, to the following:

• Run – Your Script Function, i.e. email_Sent
• Event – From spreadsheet
• Event – On form submit

Giving your script permission to access your Google Mail and Google Files

Now you’ve set your script trigger click, Save. You’ll now see a  “Authorisation required” prompt, click Continue.

When the Google Authentication window appears it’ll state what it requires permissions too, if you’re happy then click Allow

Now close your Google App Script window.

The final result

Now we’ve got everything set up all that’s left to do is fill out your Google form and wait for the email to arrive!

This guide was the basic intro into Google App Script and hopefully it let’s you apply this to whatever your scenario may be. Let me know how you get on and if you need any assistance, drop a comment below.