Send Google Forms Responses in an Email Automatically Using Google Apps Script

Intro

Let's create a Google Form for a survey, and collect the responses in google sheets. Aggregate the responses and send the latest result to participants after the form is submitted, all on autopilot with triggers.

Hi guys, this is Nibesh from Khadka Coding Lounge. I am today bringing you a new exciting blog on google apps script.

I'll be using the default Apps Script Editor but if you want to code locally you'll find this setup guide helpful.

Pre-Requisite

For this, you'll need a google account and a google drive which I believe everyone has. Second, you need knowledge of JavaScript. Everything else, I'll explain in brief.

Create Google Form

First, let's start with a google form for the survey. I'll create a few random tech-related questions that we usually see, which you can find here.

After you create the form and fill in the questions please add a destination google sheet file to collect responses. You can do that from the response tab.

Google Spreadsheet

Open the spreadsheet, and create two tabs. Let's name the one Original Responses and another one Processed Responses, we'll be using the first to collect all the responses and the second to refine the responses to only the ones we'll send to the survey participants.

Now from Extensions>App Scripts open the script editor. We'll create three files in total. I'll name them create_menu, create_send_content, and preprocessors.

Processed Response Sheet

Before we proceed, go to the processed response sheet and add six column names to the first row: Country, Gender, Job Role, IDE, Experience, and Programming Languages. We'll only analyze these columns.

First, let's create a function, it'll fetch data from the original responses and saves refined columns to the processed sheet.

// preprocessors.gs

/**
 * This app script fetches survey responses
 *Filters them and saves them to another file 
* On the second file, it aggregates the columns and returns a nice summary
*/

let fillSecondSheet = () => {
  // Get the spreadsheet 
  let ss = SpreadsheetApp.getActiveSpreadsheet();

// Get the original response sheet
  let surveyResponseSheet = ss.getSheetByName("Original Responses");

// Get process response sheet
  let processedResponseSheet = ss.getSheetByName("Processed Responses");

  // Get the Last row for indexing 
  let lastRow = surveyResponseSheet.getLastRow();


  let values = surveyResponseSheet.getRange(2, 4, lastRow - 1, 6).getValues();

  // console.log(values);
// Set values for response sheet
  processedResponseSheet.getRange(2, 1, values.length, 6).setValues(values);
};

Now, we'll create another function that analyzes the second sheet and then returns a total number of participants by country, programming languages, IDEs, and such.

// Function takes arrays counts values and returns as dictionaries

let countUnique = (arr) => {
  return arr.reduce((initObj, currVal) => {
    initObj[currVal] =
      initObj[currVal] === undefined ? 1 : (initObj[currVal] += 1);
    return initObj;
  }, {});
};

let analyzeSecondSheetData = () => {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let processedResponseSheet = ss.getSheetByName("Processed Responses");

  let lastRow = processedResponseSheet.getLastRow();

// Get the country column and use countUnique() function to get unique countries
  let countryCol = processedResponseSheet
    .getRange(2, 1, lastRow - 1, 1)
    .getValues()
    .flat();
  let uniqCountries = countUnique(countryCol);

  let genderCol = processedResponseSheet
    .getRange(2, 2, lastRow - 1, 1)
    .getValues()
    .flat();
  let genderCount = countUnique(genderCol);

  let jobCol = processedResponseSheet
    .getRange(2, 3, lastRow - 1, 1)
    .getValues()
    .flat();
  let jobCount = countUnique(jobCol);

  let ideCol = processedResponseSheet
    .getRange(2, 4, lastRow - 1, 1)
    .getValues()
    .flat();
  let ideCount = countUnique(ideCol);

  let experienceCol = processedResponseSheet
    .getRange(2, 5, lastRow - 1, 1)
    .getValues()
    .flat();
  let experienceCount = countUnique(experienceCol);

  // Need to do some coding to extract all the programming languages as unique
// Since the values are saved as a string separated with a comma: for instance, Python, Swift, Rust
// We want arrays 
  let programmingLangColInit = processedResponseSheet
    .getRange(2, 6, lastRow - 1, 1)
    .getValues()
    .flat()
    .map((item) => (item.indexOf(",") == -1 ? item : item.split(",")))
    .flat();

  // Formatting the string, trim extra space, uppercase first and lowercase the rest of the letters
  programmingLangCol = programmingLangColInit.map(
    (item) =>
      item.trim().charAt(0).toUpperCase() + item.trim().slice(1).toLowerCase()
  );

  let programmingLangCount = countUnique(programmingLangCol);
  //console.log(programmingLangCount)

  console.log([
   uniqCountries,
   genderCount,
   jobCount,
   ideCount,
   experienceCount,
    programmingLangCount,
  ]);
// Return summary as array
  return [
   uniqCountries,
   genderCount,
   jobCount,
   ideCount,
   experienceCount,
   programmingLangCount,
  ];
};

Create Menu On Spreadsheet

Before we jump on triggers and automation, let's create a menu on the spreadsheet for manual operations.

// create_menu.gs
/**
 *This file is for creating a Menu on the spreadsheet.
 *
 **/

let onOpen = (e) => {
  let ui = SpreadsheetApp.getUi();

  ui.createMenu("Helper Menu")
    .addItem("Fill Second Sheet", "fillSecondSheet")
    .addToUi();
};

Create Content To Be Sent

Now, let's create a template, that'll contain aggregates.

// create_send_content.gs

// Function that'll loop through dictionary
// return list items containing keys and values 

let dictTolistItems = (arr) => {
  let listStr = "";
  for (const [key, val] of Object.entries(arr)) {
    listStr += `<li> ${key}: ${val}</li>`;
  }

  return listStr;
};

// create content 
let createContent = () => {

  // De-structure the values 
  let [
    uniqCountries,
    genderCount,
    jobCount,
    ideCount,
    experienceCount,
    programmingLangCount,
  ] = analyzeSecondSheetData();

  let countries = dictTolistItems(uniqCountries);
  let gender = dictTolistItems(genderCount);
  let job = dictTolistItems(jobCount);
  let ide = dictTolistItems(ideCount);
  let experience = dictTolistItems(experienceCount);
  let programming = dictTolistItems(programmingLangCount);

  // const fileName = "Survey Report"
  let content = `
  <br>
  <strong>Participants Info: </strong><br>
<br>
  <p>
  <strong>Number of Participants By Countries </strong>: <ul> ${countries} </ul> 
  </p>
  <p>
  <strong>Gender Of Participants</strong>:  <ul> ${gender} </ul> 
  </p>
  <p>
  <strong>Job Roles Of Participants</strong>:  <ul> ${job} </ul> 
  </p>
  <p>
  <strong>Number of Preferred IDEs </strong>: <ul> ${ide} </ul> 
  </p>
  <p>
  <strong>Years of Experiences</strong>:  <ul> ${experience} </ul> 
  </p>
  <p>
  <strong>Programming Languages Used</strong>:  <ul> ${programming} </ul> 
  </p>
  
  `;

  return content;
};

Send Email To Survey Participant

Before we write the function to send emails to participants, let's create another column at the end of the "Original Response" tab named Replied At. It'll have a record of either date as value, of when a reply was sent, or empty if a reply has not been sent.

After that add sendEmail() function.

// create_send_content.gs

let sendEmail = () => {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let s1 = ss.getSheetByName("Original Responses");

  let lastRow = s1.getLastRow();
  let lastColumn = s1.getLastColumn();

  // Get data range from second to last row and second column to the last one
  let dataRange = s1.getRange(2, 2, lastRow - 1, lastColumn - 1).getValues();

  const subject = "Survey Stats";

// Loop over each row to check if the email is replied 
// if not send an email 
// then update replied column
  dataRange.forEach((data) => {
    let recipentName = data[1];
    let content = createContent();
    let email = data[0];
    let body = `Dear ${recipentName},
    <br><br>
    
    <p> 
    We would like to thank you for your participation in the survey.
  <br>
    We've sent you participation results up until now as follows:
  <br><br>
    ${content}

  <br><br>
     Sincerely, 
     <br>
     Code Eaters

     </p>
     
    `;
    if (data[data.length - 1] === "") {
      // If the email has not been sent
      MailApp.sendEmail({ to: email, subject: subject, htmlBody: body });

      // Create date values to fill in after the mail is replied in sheet
      let newDate = new Date();
      let datetime =
        newDate.getDate() +
        "-" +
        newDate.getMonth() +
        "-" +
        newDate.getFullYear() +
        "," +
        newDate.toTimeString().slice(0, 8);
      data[data.length - 1] = datetime;
    }
  });

  s1.getRange(2, 2, lastRow - 1, lastColumn - 1).setValues(dataRange);
};

Let's update the onOpen function, and add the sendEmail function to our menu on a spreadsheet.

// create_menu.gs
/**
 *This file is for creating a Menu on a spreadsheet.
 *
 **/

let onOpen = (e) => {
  let ui = SpreadsheetApp.getUi();

  ui.createMenu("External Helper Menu")
    .addItem("Fill Second Sheet", "fillSecondSheet")
    .addItem("Send Email", "sendEmail")// New line
    .addToUi();
};

Set Triggers

Let's write a function to run on auto triggers on form submission.

// create_send_content.gs
// Create a function to use as a trigger every time a form is submitted

let comboFunction = () => {
  // First Fill the second sheet
  fillSecondSheet();

  // Analyze the second sheet to send to the user
  analyzeSecondSheetData();

  // Then send the result of the analysis to the user
  sendEmail();
};

After this, we'll need to add this function to the trigger.

1. On the left panel select trigger(the one with the clock icon).
2. Click Add Trigger Button.
3. Choose comboFunction as the function to run.
4. Select From SpreadSheet as an event source.
5. Select On form submit as the event type.
6. Select failure notifications as you wish.

Then click save.

Summary

Let's remember the things that we did in this tutorial.

1. We create a google form
2. Wrote a code to refine the original responses.
3. Analyzes the responses.
4. Created an automated system that sends emails to the participants that have analyzed survey data.

Thank You

This is Nibesh Khadka from Khadka's Coding Lounge. Please do like and share if you like my work. Also, subscribe to get notified about the next upload.

I make WordPress Websites, Flutter Mobile Apps, Data Analysis with Python, Google Add-Ons, create content, and many more. If you need my services let me know.

Thank you for your time.

Also published here.