One such ?simple, 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 GoT cast members whose characters are still alive as of Season 7, episode 3.
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, later:
Then, we will use the Sheets query function to fill in the Birthday Calendar.
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 gig at comedy night.
The way to do it is to type January into C2:
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:
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 populate the calendar with 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 query. 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 (column E) 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 within 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.
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 birthday, say, into 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 birthday 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 birthday, 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. Likewise, it would be neat to be able to automate the E=x part of the select according to the month you want to fill. I may find a way soon after I hit the Publish button.
Being a feature of a spreadsheet app, query-select is, as you might expect, most useful in a business setting.
For instance, a wholesaler records, as they come in, data on sales into a Google Sheet. The data includes, at a minimum, the date of sale, customer, product, quantity, price, and total amount due (which may ? in fact, should ? be computed).
The same wholesaler would have suppliers, and purchases might be recorded, as they come in, into the same Google Sheet. The data includes at a minimum the date of purchase, supplier, product, quantity, price, total amount due.
The data entry may even include validation to accept only recognized customers’ and suppliers’ transactions with the wholesaler.
A second sheet will summarize, at the end of the month ? or, maybe even at any time &mash; the total of the month-to-date purchases of each customer, and the total of the month-to-date due to each supplier.
Want to play with the Sheet? Make a copy and have fun with it.