Every year I do some sort of digital Oscar Pool with friends. I used to rely on websites that featured a lot of advertising and spotty updates on the actual night of the Oscars, so I took it upon myself to build a little something last year using a Google form and my front-end skillz.
(New form for 2020 now available at "Copy this form" step and a new spreadsheet to keep track of the winners is noted in step 7).
I ended up spending more time than I wanted to this year trying to get everything working since Google seems to have greatly improved its forms since I first started this project awhile back. I'm using this gist to retrieve the data as json from the Google Doc. As one of the comments notes, the gist limits the results for some reason by cutting off the last two rows. To adjust, I entered data in the first cell of each of the two rows after my data. I tried to deploy my own version, but I was having issues.
After refactoring, I was able to make a few front end improvements
- Updated the Oscar count by each user name to be inside a tiny statue (super important).
- Changed the category order to match the broadcast order. [edit: apparently I still didn't get the order correct]
- Froze the left column.
- Updated the hover states on each prediction to include the category and winner.
- Made it responsive-ish-er.
- Upgraded to the newest version of Google form so I can add trailers. Wish I hadn't done this, but better off for the future.
I almost hooked the "winners" entry that marks your predictions as correct or incorrect into a universal spreadsheet controlled by me, but I don't want to be responsible for ruining your leader board if I accidentally fall asleep during the broadcast.
You TOO can have an Oscar pool with your friends in 8 easy steps
- Fork the pen: 2018 & 2019 | 2020 [updates in progress so check back for improvements]
- Copy this form: 2018 | 2019 | 2020 and send the link to your friends.
- Send the form responses to a spreadsheet (you can do this before people are done responding). You can also turn off responses on this screen of the form before the broadcast begins
- Set the spreadsheet to public (click the "share" button in the upper right corner then click "done")
- Update the URL of the spreadsheet in the body tag of your forked pen.
- Name the first sheet of the workbook "pool"
- Take a look at my data workbook 2018 | 2019 | 2020. The first sheet was generated by the form with the exception of the last three rows. Add a second sheet/tab to your Google Doc and copy into it the data from my second spreadsheet.
UPDATE: I'm realizing only the owner of the spreadsheet can see the data validation piece that was the whole reason for this multi-step bullet. Sorry. Here's how to set the sheet up to work for you once you copy the second sheet over: Click on the cell in the winner row under the first column of nominees and go to Data > Data validation
Click on Criteria > Select data range
Highlight the range of nominees and click "ok" and "done"
There will now be a drop down with the list to choose from during the broadcast. Repeat this step for each category.
Next, copy the last three rows from your second sheet over to the end of your first sheet. Now you will be able to update the winner in each category as it is announced.
- Share the full page view of the pen with your friends.
Extra fun
Invite the participants to a chat (Google hangout, slack, what have you) when you close down submissions (you'll have their email addresses). I don't like to reward people who don't participate, so I wouldn't send out the chat invite to the same list you invited to the form... only the addresses who submitted predictions. If we can't make fun of your misguided picks, you can't make fun of ours.
I'm planning on updating the form every year, so bookmark this page and bug me for the new form link in the comments if I forget to post it. I might also turn it into a little app if I can. Anyone want to help do that for next year?