Secret Santa Mix-match with Google Sheets and Apps Script
Originally published on Hashnode, Read it here
All reference links, code, random generators are listed at the bottom of the article.
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.
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
- Read the data of your current open spreadsheet.
- Arrange and shuffle the data.
- Save it in a new sheet
- If needed, send everyone their respective partner's details through email.
Step 1: Get the Data from Sheet
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
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.
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.
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).
A final function with mailing built-in.
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 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.
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 😎✌