How to Send Google Forms Responses in an Email Automatically?
Tags: javascript, appscript, googlesheets, googleappsscript
Published: 2022-08-14 06:05:00

## 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](https://kcl.hashnode.dev/how-to-write-google-apps-script-code-locally) 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](https://github.com/nibukdk/AppsScriptProjects/blob/main/Google%20Files/Developer%20Survey%20-%20Google%20Forms.pdf). 
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. 

![G From To Sheet](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lvvdk18pcsqaq7azinjy.png)

## Google Spreadsheet

Open the spreadsheet, and create two tabs. Let's name one __Original Responses__ and another one __Processed Responses__, we'll be using the first one 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__. 

*Reminder: Don't provide extensions to the files like __fileName.gs__. The editor will take care of that.*

### Processed Response Sheet

Before we proceed, go to the __Processed Response__ tab and add six column names to the first row: __Country__,	__Gender__,	__Job Role__, __IDE__, __Experience__, and	__Programming Languages__.	We'll only analyze these columns. 																			

__preprocessors.gs__

First, let's create a function, it'll fetch data from the original responses and saves refined columns to the processed sheet. 
```
/**
 * 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 processed response sheet(tab) 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();
};

``` 
BTW __onOpen__ is not an arbitrary name its a reserved keyword for the function to create Menu.

### 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, add the __sendEmail__ function to our menu on 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.  


![Set Triggers](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dk8ulgekypadk2g6dji0.png)




![Khadka's Coding Lounge](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ibm1yyp9ao02mcfzqya7.png)

## 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. Find my blogs [here](https://kcl.hashnode.dev/).  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. 



![Like, Share, and Subscribe](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fgrbkw7llaxe97cotzog.png)