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.

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

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.

preprocessors.gs

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

/**
 * 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 the 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 a Menu On the 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 it's a reserved keyword for the function to create a 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 an 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 namedReplied 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 the 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 the 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.

Hire Me

This is Nibesh Khadka from Khadka's Coding Lounge. Please do like and share. If you need my services let me know from here.

Thank you for your time.