A Google Sheets Birthday Calendar for the Game of Thrones Cast

I'm taking a short (?) break from my neo4j posts to bring you this cartoon, courtesy of Neal Ford:

One such repetitive task is filling a birthday calendar based on a sheet consisting of at least the persons' names and birthdays (of course!, d'uh). The idea is to generate a Birthday Calendar for those persons, something like this:

This calendar shows the birthdays of 38 surviving GoT cast members. On that, a couple of clarifications:

These are characters that are still alive as of Season 7, episode 3. OK,

<SPOILER>
both Tyene Sand and Lady Olenna Tyrell were poisoned toward the end of episode 3. However, they're still alive when the credits roll, so they're counted. Also, Tyene's poison was supposed to be slow, in contrast to Lady Olenna's which would probably have killed her by the time the credits stopped rolling.
</SPOILER>

As to Daario Naharis, while he hasn't (yet?) made an appearance in Season 7, his status is listed as “alive”.

So, how do we keep our computers from having a laugh-fest at our expense when the lights go out?

We start with the list of the surviving cast of Game of Thrones, together with their birthdays and a couple of other columns whose purpose may not be clear at the moment and will therefore require explanation:

Then, we will use the Sheets query function to fill in the Birthday Calendar.

Let's get started.

Creating the Calendar

Seems simple right? Just type in the months, and arrange them into quarters — or, in this case, trimesters.

You've just given your computer material for her comedy night.

The way to do it is to start with January:

See that dot at the lower right corner of the cell, and the cross-shaped cursor near it?

Drag that dot to the right up to column N, and...

violà!, Google Sheets detects that you want to fill cells C2:N2 with the months of the year. Just be careful not to include row 3 in the drag, otherwise you'll get two rows of the names of the months. No major hassle though, simply delete the extra row if you do manage to create one accidentally.

As an aside, I started in row 2 to leave space for a heading, maybe something like “Birthdays of GoT cast members whose characters still live”.

I also left the first two columns blank, the first as a spacer, and the second for the day of the month.

Next, cut-and-paste the months — or drag-and-drop — and arrange them into trimesters:

Or, I could've dragged from C2 to F2, typed in “May” in C12 and dragged to F12, and finally typed in “September” in C24 and dragged to F24.

I've left space for 10 birthdays per month, which is a lot considering the number of surviving characters. You may adjust as necessary for the number of people in your application.

Next, put blank columns between the months:

Filling in the birthdays

So, how would you enter the birthdays?

If you think you're clever by sorting the 'Cast and Roles' sheet on the birthdays, then manually typing in the days of birth and corresponding names in the square for the month — look deep into your computer, and you'll see a snicker forming.

And if you thought the auto-fill of the months was magical, just wait.

Google Sheets has this query function which is something like an SQL (or Cypher ) query. I won't go into the details, instead I'll give the function that'll generate the entries for February (there being no January-born cast members): =query('Cast and Roles'!$D3:$F, "select day(D), F where E = 2 ORDER BY day(D)").

In case you're interested, the function that creates entries for January is =query('Cast and Roles'!$D3:$F, "select day(D), F where E = 1 ORDER BY day(D)").

This is about the time to explain the last two columns of the 'Cast and Roles' sheet.
The first is the numerical value of the months of the actors' birthdays:

See that =month(D3) in the input area of Sheets? That tells Sheets to extract the month of the entry in D3, which is the full birthday of the cast member. That formula is copied down to the last row.

Column F, on the other hand, is the concatenation of the actor's first and last names, accomplished with =concatenate(B3, " ", A3) and copied down to the last row.

I had to do that because Sheets wouldn't let me =query('Cast and Roles'!$D3:$F, "select day(D), concatenate(B3, " ", A3) ...) so I had to do the concatenation in the 'Cast and Roles' sheet.

So, I enter the formula in E3, and
The Sheets query function has two parameters. The first is the name of the sheet ('Cast and Roles') followed by an exclamation point, then the range of the sheet to query, in this case, from row 3, column D, up to the last row of column F. This tells =query where to operate.

The second parameter is the select statement itself, which in this case takes the day of D (the birthdate of the actor), and F, which is the concatenated first and last name of the actor. Sheets knows to extract D and F from the 'Cast and Roles' sheet, and not from the Birthday Calendar sheet, by the 'Cast and Roles'! specifier.

The select statement further tells Sheets to limit the selection to cells in the range where E=2 — i.e., the month of the birthday is 2, for “February”. Lastly, the ORDER BY day(D) tells Sheets to sort the resulting data set according to the day of birthdate.

Next, paste the formula into H3, and change E=2 to E=3, 3 for “March”. Rinse and repeat for the other months.

Cleaning up

The calendar is far from pretty — for one, those day() entries. In hindsight, I could've created another column in the 'Cast and Roles' Sheet extracting the day of the birthdate, e.g., column G. The select would then be select G, F where E=x ORDER BY G. This might've made the query faster as well.

Conversely, I could've done away with the 'Cast and Roles'!E column and instead queried for month(D)=x. However, no getting away from the F column, the concatenation of the first and last names.

I took the easy way out after I realized that — I simply hid rows 4, 13, and 25.

There's also the matter of the cell widths, with the columns for the days of the birthdate being too wide, and those for the names being too narrow. Easy enough to fix: first right-click on the columns for the days of the birthdate, Resize column, Fit to data.

You may also want to narrow the spacing between the months. You may want to set the months to a different font, size, or background color.

Then, in regard to the "scratch" columns in 'Cast and Roles' — the birthmonth and the concatenation of the first and last names. They are an eyesore. Easy enough, just hide them.

In any event, these are cosmetic issues.

In closing, there may be ways to assign the computer, through a Google Sheets script or similar mechanism, some of the tasks above. E.g., creating the calendar — could I have told Sheets to break the months of the year into trimesters instead of a cut-and-paste of the cells? I haven't found ways to streamline the process — yet. I may do so soon after I hit the Publish button.

Resources:
Want to play with the Sheet? Make a copy of it and have fun with it.