Skip to main content

How To Send Google Forms Responses In Email Using Google Scripts

By March 31, 2017Blog, Google, Javascript
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 //
  const values = SpreadsheetApp.getActiveSheet(); // This accesses all data in the active sheet 
  const row = values.getLastRow(); // This accesses only the last row with data in it 
  
  // EXTRACT VALUES //
  const question = values.getRange(row, 2).getValue(); //Note that column numbering start at 1 instead of at 0
  const email = values.getRange(row, 3).getValue();
  
  // PREPARE EMAIL //
  
  // Email recipient
  const emailRecipients = "info@allurewebsolutions.com";
  
  // Email subject
  const emailSubject = "[Allure Web Solutions] New Test Form Submission";
  
  // Email body
  const 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
  const submitterEmailSubject = "Thank you for submitting the Test Form";
  const 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");
  
  const ssResponses = SpreadsheetApp.getActive().getSheetByName("Form Responses 1");
  const testRange = ssResponses.getRange(2,1,1,ssResponses.getLastColumn());
  sendEmail(testRange);
}

Test Form

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

allure

Author allure

More posts by allure

Join the discussion 2 Comments

  • Karin Stiles says:

    This was really helpful. However, shouldn’t there be a var in front of submitterEmailSubject and submitterEmailBody? I did add those, but it still didn’t send an email on form submission. : ( Not sure why…

    • Mike Doubintchik says:

      You’re totally right about the vars. I’ve updated the script. Thank you.

      Regarding it not working…you can try using the debugger inside of Google Scripts.

Leave a Reply

Designed by

best down free | web phu nu so | toc dep 2017