Secret Santa Mix-match with Google Sheets and Apps Script


December 21, 2018

Originally published on Hashnode, Read it here

1.png

Google Apps Script has always been a tool in the shadow. Many developers don't know about this tiny JavaScript-based powerful scripting language. In this tutorial, I will show you how to build a very simple Secret Santa mix-match using Google Sheets.

All reference links, code, random generators are listed at the bottom of the article.

The Problem

You have a huge list with people’s names/emails/whatever, and now you want to shuffle them and then assign their respective partners randomly.

This can be a really time-consuming task, but instead of doing this manually, I'll show you how to automate this process.

The Solution

Google Apps Script is this less-known JavaScript-based lightweight application programming language which allows you to automate the whole Google G-suite and build add-ons for it. In this tutorial, I will teach you how to deal with Google Sheets and automate a few things.

Create a new spreadsheet in sheets.google.com, let's call it MixMatch and fill the columns with users emails and their respective names.

1.png

Next, click on Script editor and a new tab will open with an editor.

2.png

3.png

Erase the content and let's start fresh.

Steps We Need to Complete

  1. Read the data of your current open spreadsheet.
  2. Arrange and shuffle the data.
  3. Save it in a new sheet
  4. If needed, send everyone their respective partner's details through email.

Step 1: Get the Data from Sheet

function readCurrentSheet() {
    var currentActiveSheet = SpreadsheetApp.getActiveSheet();
    Logger.log(currentActiveSheet.getDataRange().getValues());
    return currentActiveSheet.getDataRange().getValues();
}

SpreadsheetApp.getActiveSheet() is an API provided by Apps Script. You can read more about it here.

This returns a Range object, but what we need is the data from the sheet. For this, we can use getValues() method which returns a multi-dimensional array of cell values.

Logger.log() is like console.log() of Javascript. You can use that to log data and verify it. You can view the logs by clicking on view drop-down in the menu bar or use ctrl + enter.

When you type the above-mentioned function and hit save hit save ( ctrl+ s). You will be prompted for a project name, fill it and proceed.

Now, in the top menu bar select a function that you want to run. This will run that function and ask for your permission to access the sheet. Sign in to your account and you will see a security error. Don’t worry about that and proceed. It's you who is accessing your own sheets, so no worries there.

Run the readCurrentSheet() function and view logRun the readCurrentSheet() function and view log.

Step 2: Shuffle the Data

Now we have the data, all we have to do is mix it up.

  function shuffle(array) {
   var currentIndex = array.length, temp, randomIndex;
   while (0 !== currentIndex) {
     randomIndex = Math.floor(Math.random() * currentIndex);
     currentIndex -= 1;
     // Swap the elements
     temp = array[currentIndex];
     array[currentIndex] = array[randomIndex];
     array[randomIndex] = temp;
    }
    return array;
  }

We have an array consisting of rows of data from our sheet. Now we have to split it into two and save it in a new sheet. The catch here is that person A gifts B, now person C should gift A and not B. So for this, we will have to shuffle the second half and assign it back to the first list as givers.

Step 3 - Saving Data in a Final Sheet

Loop through and append the shuffled array into a new sheet.

// split it into two.
    var firstHalf = shuffledList.splice(0, shuffledList.length/2);
    var newShuffledGivers = [].concat(firstHalf);
    var newShuffledGivers = shuffle(newShuffledGivers);

    // Create a new sheet in the current Spreadsheet
    var newSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet('Final list'); 

    //Loop through array and append to sheet
    for(var i=0; i<firstHalf.length; i++){
        temp = [];
        temp = temp.concat(firstHalf[i] || ['','','']).concat(shuffledList[i]).concat(newShuffledGivers[i] || ['','','']);
        newSheet.appendRow(temp);
    }

You will now have a new sheet called "Final list" with shuffled data having each user with their respective partner's details. If you are running this script again, which means that you already have a script with name ‘Final list’, it will throw an error. So make sure to delete the sheet before you run it again or modify the script to update the script.

Step 4 - Emailing the Participants

Google Apps Script is so cool that you can even email from right inside this script ( from your email).

    MailApp.sendEmail(TO_EMAIL_ADDERSS, TITLE_HERE, MESSAGE_HERE);

A final function with mailing built-in.

    // Create a new sheet in the current Spreadsheet
    var newSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet('FinalList');

    // Loop through array and append to sheet
    for(var i=0; i < firstHalf.length; i++){
        temp = [];
        temp = temp.concat(firstHalf[i] || ['','','']).concat(shuffledList[i]).concat(newShuffledGivers[i] || ['','','']);

        MailApp.sendEmail(firstHalf[i][0], 'You secret santa partner', 'Hello there, your secret santa partner is ' + shuffledList[i][1]); // to 1st about 2nd
        
        MailApp.sendEmail(shuffledList[i][0], 'You secret santa partner', 'Hello there, your secret santa partner is ' + newShuffledGivers[i][1]); // to 2nd about 1st
        
        newSheet.appendRow(temp);
    }

Finally, you will have a list such as this one. Make sure to select function main() before you hit Run

Yellow send to green, Green send to RedYellow send to green, Green sent to Red

This script literally takes about 15 mins to write and does a whole lot of work. However, it's not perfect, we need to handle randomness and cover edge cases, but we weon't do that in this tutorial. Imagine using it for some bigger tasks, automate your business, maybe even build a tiny CRM for your company. Next time don't build a huge tool for your client, give Apps scripts a try.

I hope this will help you get started with the infamous Google Apps Script and help you understand how to use it. There are way more things that apps script can do, e.g. host a website from docs, sheets, interact with Firebase... Most of the add-ons for G-suite are built using apps scripts.

There is even a CLI & version control to manage your projects if it gets big. Check out intro of this article and main docs.

You can see the final script here, and get a glimpse of the whole setup. (Comment the mail part before you run this script since those are dummy emails)

Please let me know if you found this useful and feel free to share this tutorial with people you think might need this 😎✌

Links


Subscribe to my newsletter


powered by TinyLetter and illustration from Lukaszadam

Girish Patil

Hi, My name is Girish Patil
I am a full stack developer @hashnode