The Spreadsheet

Now that's an interesting bonus content, isn't it? What kind of memoir provides a bonus content on how to build a spreadsheet?

But I love it, so I will impose this on everyone

The problem it would solve

We didn't have a contract that booked ice for a season, partly because I didn't expect us to keep going the way we did.

Therefore it was important for us to keep track of the cost each week, and the number of attendees

Then divide the cost amongst everyone

We were also booking two hours instead of just one, because some of us don't skate hard enough

Also: I'm lazy and timid

Without anything to keep track, I would assume everyone had paid me and not have any idea whether I made my money back.

So, with a spreadsheet, and in combination of BenchApp, I can keep track of who is attending, how much it costs, and how much I'm up/down by

Using The Spreadsheet

Step 1: Getting attendance

I acquired this skill from my highschool days when I thought I was doing something good for the world

and clicked ads on the charity websites to generate income for them... until one day, they explicitly asked us to step by adding such text in the HTML

It's good old HTML manipulation, I made script that would look at everyone who's attending, and copied it into an easy to paste list

and then paste it onto the spreadsheet

Step 2: Click on a button that updates the current week's attendance

With everyone on the spreadsheet, I made a button that looks at how much the ice rental for each hour is, and what everyone's status is

It would calculate how much they would need to pay, with some special logic that, making sure the goalies are recognized so I don't have to update their column (Yes, I'm that lazy)

Step 3: Enter how much everyone had paid

Since people don't always pay the exact amount, instead of a simple checkmark. I had to enter the actual amount they paid.

Step 4: When I'm ready, click another menu item to generate a summary

It would how how much everyone owed, and how much they had paid. It would also highlight anyone that still owes money, making it easy

to track down the deliquints.

Google Apps Script

Setting up the Spreadsheet setup

• Create an Apps Script
• Paste the code into app script
• Create a sheet named "Summary", this is a special sheet that will contain the summary
• The other sheets can be named anything, but I prefer to use the dates of the event to keep it organized
• On each sheet, enter Total cost on E2 and E3, cost per person on H2 and H3
• Paste the attendance list anywhere on column A (hour 1) and column B (for hour 2)
• To create the table, select everyone in column A, while holding ctrl, select everyone in column B
• Click on Update Table
• Adjust price as required
• That's it, and use Update Summary to generate the summary on the Summary sheet
• To make this work for one hour, no script change is required, simply keep column A empty, select it. Then select everyone in column B as usual

Hope that was helpful, here's a video to demonstrate

Final thoughts

We have some pretty cool (but not 100% accurate) stat on people's attendance, someone should volunteer their kids to make a poster or something

that we can share amongst our group. Come on, I'm not asking for a full 3D rendered shinny reenactment here.

If you are interested, please feel free to hit me up using the Contact Us link