Developing Exchange Gift Generator with Forms/Spreadsheets and Apps Script
Celine’s family has a Christmas tradition of exchanging gifts on Christmas Eve (Secret Santa). Normally, we do the draw when we’re all together. This year, however, some people are outside of the country and having the draw upon their return would be a little late for us to buy the gifts and/or wait for the delivery. The plan would be to do the draw online.
As I don’t want to use or download yet another app and give data to others, I thought of making an app instead. It would be as simple as a Google Form for everyone to fill up with a script to do the draw and email the results. I have experience using Apps Script a few years ago so I volunteered to Celine to give me some time to work on it.
I spent a Sunday morning working on the project. First, I created a Google Form with name, email address (to send the results of the draw), and wish list. I changed the name field to a dropdown instead of a text so I can easily code the conditions later. Then, I started working on the script itself.
The first thing I did was add an “Exchange Gift” menu and a “Start Draw” menu item for the script. It was one of the things I learned before and I like to add this on my App Script projects.
I added a column in the spreadsheet for the Status so that I can know if ever there would be an error. In the code, I made a list of objects per person (with row id, name, email address, and wish list) based on the inputs provided.
To determine the person that one will be giving a gift to, what I did was to shuffle the list and then make the next person in the list be the recipient (with the last giving a gift to the first in the list). This would ensure that no person would be sending a gift to himself/herself and that there will be no two persons who will just give gifts to each other. However, I still have to check that someone in a couple shouldn’t send a gift to his/her partner. That’s the reason I used a predefined list for the names in the form. I added extra handling to check and restart the draw.
After the checking the list generated, the script will then send an email:
I added the noReply:true so that the email will come from noreply@tigcal.com instead of my email address. The script will then update the Status column with an “OK” value and once everything has been done, it will display an alert message:
I was also done with my script and did a little testing. Celine redesigned the form and updated some names with close and open parenthesis. I updated the couple checking in my code.
The day of the draw happened and it was successful. Unfortunately, Celine got me. It shouldn’t have happened because of the couple checking. For some reason, that part broke due to the open/close parenthesis. (Oh, Javascript!). It was a good thing it only happened to us. Now, she has to buy me a gift without me knowing to not ruin the surprise. I updated the code so that it should work in the future.
