When I was an active member of the community of the game Flight Rising, a browser game focused on an economy surrounding dragons, I frequently pitched in to community event planning, managing, and content creating. I learned many valuable tricks on Google Sheets to accomplish streamlined, complex spreadsheets that would be intuitive and easy for community members to use. The visual identity/brand of the particular subcommunity using these Sheets was also a large factor when decorating the functional Sheet before launching it.
Two of my biggest projects (pictured above) were for a fire-themed art sale and creative raffle. These sheets were used by dozens of managers for the event and served as a reference and payment tracker for hundreds of participants. I'll focus on the Fire Flight Creative Raffle sheet because it served the largest event our artists had pulled off to date.
Using complicated but convenient Google Sheets pages is common practice for the community I was working with. Most users have some kind of Google account, so access control is simple and secure. Setup doesn't take too long, and reuse of material for future Sheets is easy. Because the community includes thousands of active players and each event involves hundreds of transactions, some kind of tracking system is a necessity. Typically, each event involves users paying with virtual goods for tickets or other virtual goods from a specific person, and all revenue needs to be sent to a central pot which is managed by community leaders. As Sheets and processes have become more streamlined, the community puts increasing pressure on event planners to continue to have streamlined, speedy, and error-free processes. Any inconvenience could lead participants to use competing services instead.
My Sheets needed to benefit two separate groups: the event coordinators and the event participants (i.e., customers). For the large Creative Raffle, this meant a lot of overhead for tracking two sides of the equation. Coordinators would want to know how many donations of what type each acceptor had in stock and be able to quickly input new donations, and participants would want to know how many raffle tickets they had for each category and how many tickets any kind of donation would net them.
In the image above, several of the relevant sheets are shown. The image-heavy top screenshot shows the table that potential donors could use to quickly identify how many tickets any given item was worth. Underneath it, the top of a lengthy inventory list for each member accepting the donations shows one of the views used internally for event coordinators. This itemized list is accumulated in another sheet for coordinators.
The Creative Raffle sheet was set up to be as simple as possible for all its users. For any of the volunteers accepting donations on behalf of the raffle, they only needed to put down the donation amount and type in one sheet for each donation. They could receive two types of virtual currency, virtual items, or virtual dragons, and each of these had unique payout rates. For currency, equations handled the conversion to tickets, and for the latter two, the equation requested the acceptor to override it with a value based on the table in another sheet. In another sheet, the inventory possessed by each acceptor was automatically accumulated, and acceptors could indicate when they sent bundles of items or currency to the community pool. This sheet would quickly show managers who still had community inventory on their private accounts.
Participants never had to input any information into the sheet. However, the main page prominently showed them how many raffle tickets they'd accumulated for each category which was automatically updated as soon as acceptors listed a donation. They also had the large reference sheet for how many tickets each type of donation earned, including almost any item in the game (some falling in broad categories like "meat food") and dragons of varying levels. When winners were drawn and announced, these results were also saved on the raffle tickets page.
With each new Sheets project, I learn more interesting Sheets formula tricks. For the Creative Raffle, I finally broke into territory where QUERY() formulas were needed to handle the complexity. I had two Sheets projects from myself and one other to base the simpler pages around, but for the accumulations of inventory, I needed to query the itemized list several times. This was primarily to make the acceptors' job as easy as possible. One sheet was the list of every donation ever received, no matter what type of donation it was, but each of the four types of donations needed to be handled uniquely. Money could be summed, but items had to be handled as a group and dragons might wind up equalling money or being kept permanently.
As I implemented the sheet, I showed it to the event coordinators frequently for feedback and data validation. I wanted to be sure I was capturing all the information the event coordinators would need to manage the dozens of acceptors and hundreds of donations we received. For accuracy, redunancies existed for the raffle ticket counts to be sure each user's tickets were being accumulated correctly for each category. Wherever possible, drop-down lists were used instead of open-ended input both to speed up the input process and insure the data was uniform enough to be queried in other places.
For the Creative Raffle, I also coded a quick raffle drawing program which can be seen [on Github].
As the event took place, I kept the sheet in working order and made sure everyone knew how to use it. Several improvements were made while the event was live, including a quick calculator for food point to ticket conversions (which wound up being a more popular donation than anticipated) and an additional equation for currency conversions in the donations list. We ended up using a new sheet for each separate week of donations, so minor adjustments were made to other sheets as the new week's sheet came online. Other minor bugs were fixed here and there.
In the beginning, there was also an issue where some users had two separate entries for their username. I discovered this was due to some names being inserted with a space after the username and others not, so I manually handled the issue any time it came up. Luckily, Sheets' feature of autosuggesting input helped keep the acceptors from making a lot of typos when entering repeat donators.