How To Send Google Forms Responses In Email Using Google Scripts

How To Send Google Form Responses With Google Scripts

In the blog post, I will show you how to create a Google Script that will email responses to a Google Form. Responses can be emailed to the form admin as well as the form submitter.

I’m assuming you already have a form setup. On the responses spreadsheet go to Tools > Script Editor.Open Google Script Editor

In here is where you will place the Google Script below. I’ve made comments in the script so it should be easy for you to follow what’s going on. Mainly, you will need to change the email recipients, subject, body, and assign values to the correct column.

If you have knowledge of JavaScript you can do amazing things with the script, but I’m not going to into that in this tutorial.

Test Google ScriptBefore launching your script you can test it by using the test function. This will send the first row of the in the responses spreadsheet through our sendEmail() function.

Now there is one very important step, which is to connect the script we made with the form through a Trigger. If you don’t do this, the the form will not know to run any in your script. Click on the littleĀ time bubble icon in the toolbar and add your trigger.

Google Scripts Trigger

The Google Script

// Prepare and send email
function sendEmail() {
  Logger.log("[METHOD] sendEmail");
  
  // FETCH SPREADSHEET //
  var values = SpreadsheetApp.getActiveSheet(); // This accesses all data in the active sheet 
  var row = values.getLastRow(); // This accesses only the last row with data in it 
  
  // EXTRACT VALUES //
  var question = values.getRange(row, 2).getValue(); //Note that column numbering start at 1 instead of at 0
  var email = values.getRange(row, 3).getValue();
  
  // PREPARE EMAIL //
  
  // Email recipient
  var emailRecipients = "info@allurewebsolutions.com";
  
  // Email subject
  var emailSubject = "[Allure Web Solutions] New Test Form Submission";
  
  // Email body
  var emailBody = "<h2>Results of Submission</h2>" +
    "<strong>Test Question:</strong> " + question + "<br />" +
    "<strong>Email:</strong> " + email;
  
  // SEND EMAIL TO ADMIN //
  MailApp.sendEmail({
    to: emailRecipients,
    subject: emailSubject,
    htmlBody: emailBody
  });
  
  
  // SEND EMAIL TO FORM SUBMITTER //
  
  // Submitter email details
  submitterEmailSubject = "Thank you for submitting the Test Form";
  submitterEmailBody = "<h2>Results of Submission</h2>" +
    "<strong>Test Question:</strong> " + question + "<br />" +
    "<strong>Email:</strong> " + email;
  
  MailApp.sendEmail({
    to: email,
    replyTo: 'info@allurewebsolutions.com',
    subject: submitterEmailSubject,
    htmlBody: submitterEmailBody
  });

}

// Test function
function test() {
  Logger.log("[METHOD] test");
  
  var ssResponses = SpreadsheetApp.getActive().getSheetByName("Form Responses 1");
  var testRange = ssResponses.getRange(2,1,1,ssResponses.getLastColumn());
  sendEmail(testRange);
}

Test Form

After you submit the form, you should receive a confirmation email.

Mike Doubintchik

Author Mike Doubintchik

More posts by Mike Doubintchik

Leave a Reply