This content originally appeared on DEV Community and was authored by fredrikalindh
Have you heard about Google Apps Script? I hadn't, until my co-maker Mathis and I launched Papapal, learning languages with a pen pal. It wasn't much more than an idea and we didn't expect so much interest, but then we had 200+ people responding on our Google Form in just 72 hours. We realised that manual matching would be difficult and that's where Google Apps Script come in to the picture. With it you can write javascript code to automate tasks for most of the Google apps such as Drive, Sheets and Gmail.
That meant that on my form responses sheet I only need to navigate to Tools → Script Editor which opens a script editor connected to the sheet. Then you can access the sheet with SpreadsheetApp.getActiveSheet(); and the data with sheet.getDataRange().getValues();
I did a simple sorting function that groups by chosen language and level and then just matched adjacent users. Stored the information in a new sheet and that's it. All I need to do to create the matches is to click Run.
// Comparing two users by:
// 1. language (lexicographical)
// 2. Level (Beginner, Intermediate, Advanced)
function sort(a, b) {
// if different we sort by language
if (a[3] < b[3]) return -1;
if (a[3] > b[3]) return 1;
// if language was the same we sort by level
if (a[4] == b[4]) return 0; // same level
// 1: I + B & A + B & A + I
if (a[4] == 'Advanced' || (a[4] == 'Intermediate' && b[4] == 'Beginner')) return 1;
// -1: B + I & B + A & I + A
return -1; // a is beginner OR a is intermediate and b 'advanced'
}
function matchPals() {
const sheet = SpreadsheetApp.getActiveSheet();
const saveSheet = SpreadsheetApp.create('matchedPals')
saveSheet.appendRow(['round', 'name_1', 'email_1', 'name_2', 'email_2', 'language', 'level']);
const data = sheet.getDataRange().getValues();
data.sort(sort);
let prev = null;
// looping through all rows
for (let i = 1; i < data.length; i++) {
if (!prev) prev = data[i];
// if not same language or level as prev
else if (prev[3] != data[i][3] || prev[4] != data[i][4]) {
saveSheet.appendRow([1, prev[1], prev[2], null, null, prev[3], prev[4]]);
prev = data[i];
}
// save them as matched
else {
saveSheet.appendRow([1, prev[1], prev[2], data[i][1], data[i][2], prev[3], `${prev[4]}`]);
// data[i].app
prev = null;
}
}
}
You can even automate the mailing:
MailApp.sendEmail({
to: pal1.email,
cc: pal2.email,
subject: "Meet your Papapal ?",
htmlBody: `<h2>Hello there ${pal1.name} & ${pal2.name}!</h2>`
});
Last amazing feature is that you can create triggers. In our case when new users sign up, we can automatically check if there is someone on their Language+Level who doesn't have a pal yet and if so match them straight away.
Here is a guide Google created for getting started with Apps Script. Let me know what you think!
Also, if you think it sounds like a great idea to learn a language while getting to know a new person: Sign up here for Papapal, first round is about to start with people from 54 different countries, learning 18 different languages!
This content originally appeared on DEV Community and was authored by fredrikalindh
fredrikalindh | Sciencx (2021-05-04T16:54:31+00:00) How I used Google Apps Script and Sheets to build a matching service for Papapal. Retrieved from https://www.scien.cx/2021/05/04/how-i-used-google-apps-script-and-sheets-to-build-a-matching-service-for-papapal/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.