Solve the Secret Santa Mix-Match with Google Sheets and Apps Script

Originally published on Hashnode : https://hashnode.com/post/secret-santa-mix-match-with-google-sheets-and-apps-script-cjptefe1d00rikas2z9joelqa

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.

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

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 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; 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.

var firstHalf = shuffledList.splice(0, shuffledList.length/2); var newShuffledGivers = [].concat(firstHalf); var newShuffledGivers = shuffle(newShuffledGivers); var newSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet('Final list'); 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.

var newSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet('FinalList'); 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]); MailApp.sendEmail(shuffledList[i][0], 'You secret santa partner', 'Hello there, your secret santa partner is ' + newShuffledGivers[i][1]); newSheet.appendRow(temp); }

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

Yellow to green, Green 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 😎✌

Originally published at hashnode.com.