Scoring a Secret Santa guessing game with R and Google Forms

This year’s Secret Santa had six people and a side game: before gifts were opened, everyone guessed who had drawn whom. I wanted the scoring to happen automatically, so I could put a leaderboard up on Christmas Eve instead of tallying slips of paper.

The form

The Google Form asked three kinds of questions: your name, who you got a gift for, and, for each player, “Guess who got name’s gift?”

Forms writes each submission as a row in a Google Sheet, and googlesheets4::read_sheet() reads that sheet into R as a tibble. Each row is one player’s answers. The “who did you get a gift for” column, read down the rows, is the answer key. I didn’t have to type in a separate key; the players provided it themselves.

Forms uses each question’s full text as the column header, so the guess columns start out as sentences. Two rename_with() calls fix that. The first uses a regex to strip the question wording, leaving just the person’s name. The second lowercases the names and replaces spaces with underscores. One player’s name ends in s, and I’d written their question with a bare apostrophe, so the regex matches both 's gift and ' gift.

Scoring with across() and cur_column()

This is the lightly cleaned core of the script:

library(tidyverse)
library(googlesheets4)
library(lubridate)

responses <- read_sheet(sheet_url)  # the form's response sheet

scored <- responses %>%
  # "Guess who got <name>'s gift?"  ->  "<name>"
  rename_with(~ str_remove_all(.x,
    "Guess who got |'s gift|' gift| did you get a gift for|\\?")) %>%
  rename_with(~ tolower(gsub(" ", "_", .x, fixed = TRUE))) %>%
  mutate_all(str_to_lower) %>%
  # one 0/1 column per guess column
  mutate(across(-c(1:3),
                list(q = ~ +(. == who[match(cur_column(), your_name)])))) %>%
  mutate(score = rowSums(select(., ends_with("_q"))))

After the first three columns (timestamp, name, recipient), across() visits every guess column. Inside the lambda, cur_column() returns the name of the column being processed. That name is a player, so match(cur_column(), your_name) finds that player’s row, and who[...] gives the person they drew. That value is the correct answer for the whole column. It gets compared with everyone’s guess (.), and the unary + turns TRUE/FALSE into 1/0. Naming the function q makes across() add a fox_q, owl_q, and so on beside the original columns, and rowSums() adds them up.

To show how one row gets scored, take a made-up four-player game where the draws are fox → owl, owl → yak, elk → fox, yak → elk. This is fox’s row:

Column	Fox guessed	Key: who[match(column, your_name)]	Point
fox	owl	owl	1
owl	elk	yak	0
elk	fox	fox	1
yak	owl	elk	0

Fox scores 2. Nothing in the code depends on how many players there are or what they’re called, so adding someone only takes a new question on the form.

The leaderboard

The rest was presentation: a horizontal bar chart of scores with a subtitle built from max(score), and a kableExtra table that shows everyone’s guesses next to the revealed pairings. I knit it to HTML on Christmas Eve. The two top players each had 2 correct guesses, and the other four had 1 each.

One question, two readings

“Guess who got X’s gift?” can be read two ways. I meant who received X’s gift, and that’s how the code scores it: column X is checked against X’s recipient. The responses show the players read it the other way. Under my reading, every player already knew one answer for sure, their own column, yet none of the six filled it in with their own recipient. Instead, all six wrote their own name in the column of the person they’d bought for. So they read the question as who bought X’s gift.

Scoring that reading only means reversing the lookup: your_name[match(cur_column(), who)] instead of who[match(cur_column(), your_name)]. Under that reading, each player gets one free point from the column where the answer is their own name. That point is the same for everyone, so I leave it out below. That leaves five real guesses per player under either reading.

Across all six players, that’s 8 correct guesses under my reading and 13 under theirs. Next year the question will say “Who do you think name bought a gift for?”, and I’ll look at a couple of test responses before anyone else fills in the form.