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 and another one , 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. Original Responses Processed Responses Now from open the script editor. We'll create three files in total. I'll name them , and . Extensions>App Scripts create_menu create_send_content, preprocessors Reminder: Don't provide extensions to the files like . The editor will take care of that. fileName.gs Processed Response Sheet Before we proceed, go to the processed response sheet and add six column names to the first row: . We'll only analyze these columns. Country, Gender, Job Role, IDE, Experience, and Programming Languages 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();
}; The is not an arbitrary name. It’s a reserved keyword for the function to create a Menu. onOpen 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 = `
 
 Participants Info: 
 
 
 Number of Participants By Countries : <ul> ${countries} </ul> 
 
 
 Gender Of Participants: <ul> ${gender} </ul> 
 
 
 Job Roles Of Participants: <ul> ${job} </ul> 
 
 
 Number of Preferred IDEs : <ul> ${ide} </ul> 
 
 
 Years of Experiences: <ul> ${experience} </ul> 
 
 
 Programming Languages Used: <ul> ${programming} </ul> 
 
 
 `;

 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 . 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. Replied At After that add function. sendEmail() // 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},
 
 
 
 We would like to thank you for your participation in the survey.
 
 We've sent you participation results up until now as follows:
 
 ${content}

 
 Sincerely, 
 
 Code Eaters

 
 
 `;
 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 function, and add the function to our menu on a spreadsheet. onOpen sendEmail // 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. On the left panel select trigger(the one with the clock icon). Click . Add Trigger Button Choose as the function to run. comboFunction Select as an event source. From SpreadSheet Select as the event type. On form submit Select failure notifications as you wish. Then click save. Summary Let's remember the things that we did in this tutorial. We create a google form Wrote a code to refine the original responses. Analyzes the responses. 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

Fetch

Google

2022 - Tech Tutorial Teacher of the Year

Subscribe To Our News Letter For Free Contents

Hire Me

Newsletter

Buy Me a Coffee

Nominated for 2022 - Tech Tutorial Teacher of the Year

Developer 

Too Long; Didn't Read

In your car, at home, or at work — Bosch technology shapes many areas of life.

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

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

Too Long; Didn't Read

Companies Mentioned

Khadka's Coding Lounge.

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

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

Too Long; Didn't Read

Companies Mentioned

Khadka's Coding Lounge.

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

RELATED STORIES