How I used Google Apps Script and Sheets to build a matching service for Papapal

fredrikalindh — Tue, 04 May 2021 16:54:31 +0000

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!

DEV Community: fredrikalindh

How I used Google Apps Script and Sheets to build a matching service for Papapal