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 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.
- On the left panel select trigger(the one with the clock icon).
- Click Add Trigger Button.
- Choose comboFunction as the function to run.
- Select From SpreadSheet as an event source.
- Select On form submit as the event type.
- 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. Find my blogs here. 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.
Top comments (0)