How To Send Google Forms Responses In Email Using 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.

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.

Before 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.

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…