Cheat Sheets
Why spreadsheet plug-ins should be every designer’s secret weapon
Hi there!
This is my first MBG Medium article and I’m very excited. It’s only appropriate that for this occasion I start with something about which I am really passionate and have thought about… almost too much. Some have called it the greatest, most important kind of software there is. That’s right — spreadsheets!
We’ve already seen spreadsheets mentioned as one of the main tools in the game designer’s arsenal, but I’m going to take things up a notch and tell you how you can supercharge your spreadsheets by using custom extensions.
Why custom extensions?
Let’s say you’re building a game with an extensive player progression system. It has loads of levels, multiplayer matches, different currencies, upgrades, XP system, loot boxes — the whole shebang. Naturally, you have all this data somewhere in a spreadsheet and can run some calculations with it — averages, sums, whatever derived numbers you want to have a look at.
These might not answer all the questions you have, however. As a designer, you need to know what an average player state is going to look like down the line — after their third session, for example. Maybe it’s important that they’re able to unlock something good after their 10th match? It could be important to know how long it takes a player to max out something; or perhaps you want to avoid gaps in the action, where the player doesn’t feel like they’re building up to anything. You might get some answers by just playing the game over and over, asking your colleagues to do the same or simply hoping your own analytics eventually provide some insights. But this is a slow process: to observe what impact a change has had on player experience might take days or weeks, and even then you might learn you were not headed in the right direction.
Or, you might see results with clever data structuring, obscure spreadsheet functions, and lookup tables, depending on how complex your system and how in-depth your questions are. Still, this strands us in the territory of multi-line formulas, strange quirks, and almost always an old-fashioned headache whenever you need to change something. Believe me, I’ve tried!
Given the time-wasting pitfalls of most standard approaches, custom extensions could be the solution you need.
What are they?
Custom extensions, scripts, “macros” — most spreadsheet software has some means of writing custom code to accomplish tasks where vanilla spreadsheet formulas don’t cut it anymore. The one I’m personally using and can recommend to you? Google Sheets and their Google Apps Script. The online documentation is excellent and there are some great guides for getting started.
So, to get a better glimpse at my game’s player-progression journey, I would want my extension to factor in all the game balance variables it needs from the spreadsheet, do some loops (play a level -> win/lose -> get reward -> repeat), and output the player state back to the spreadsheet. A simple version of this can be written in 50 lines of JavaScript. Of course, much more awaits us further down the rabbit hole.
Here’s a small example to get your juices flowing. You will need to make a copy of this spreadsheet and give the script permission for it to function. If you are at all suspicious, you can inspect the code by clicking Tools -> Script Editor.
To summarise, custom code such as this will allow us to use cycles in our player-progress calculations, manage randomness more intelligently (e.g. make outcomes dependent on other variables), naively recreate basic player decision making (play a level, open a loot box or buy an upgrade), and track any parameter we might be interested in throughout this simulated player journey. This is going to make your iteration on complex, multi-faceted systems like player progression much quicker.
This seems hard!
Has the mention of writing some code has left you unconvinced of the method’s overall efficiency? Consider a few more advantages to using custom extensions:
- Your data might already be in the spreadsheets. You would be using the same tool you’ve come to rely on, just with a few added bells and whistles. If you have been diligent about keeping data in the spreadsheets, you might not need to do any extra data-entry either.
- You can mix and match. Your spreadsheet functions are still there even if some values in the spreadsheet are calculated by a script. Do the complicated part in code and then tailor it for your purposes alongside any preferred old-school formulas.
- Spreadsheets can serve as a pretty UI for your tool. Once you have calculated something with your shiny new extension, the results can be made to look readable and user-friendly using only the standard spreadsheet software features. Some thicker borders around the important things, some custom formatting to make the numbers readable at a glance, a graph here and there and you quickly have something that is going to be pleasant to use — compare that to building a custom tool from scratch.
- It’s just JavaScript. (JavaScript is what Google Apps Script is written in, so your mileage may vary if you’re using VBA in Excel or something else.) It might get a bad rap, but I’ve found it’s not at all hard to pick up, even for those more allergic to code. The web is awash with free resources and the kinds of debug questions you might have when starting out.
What else can I do?
So far I’ve only described one scenario in which you might find the extra flexibility of code in your spreadsheets useful. Let’s look at a few more:
- Manage your data. Game engines don’t speak spreadsheet, so if you have data in spreadsheets, you might need an easy way to get it in or out and keep it all up to date. Scripts can manage this transition by parsing and exporting text files as needed.
- Test your matchmaking, ranking, or leaderboard systems. These are features that can be hard to evaluate without a player base, not to mention time-consuming to iterate on. I’ve had success writing simplified versions of systems like these and testing what the the experience might be like with 5, 50 or 500 average concurrent players, for example.
- Generate new data. Perhaps you have a quest system in your game, in which some of the quests need to be generated. This might be a relatively resource-draining task, especially if you want to keep iterating on it. In some cases, generating some static data with a script might work well as an initial draft which you can then re-implement in-game once it’s tested out.
Any potential hurdles?
Let’s say I’ve won you over and you are now a full-time Google Apps Script apostle. What stumbling blocks will you encounter and how can you avoid them?
- In some of the use cases I’ve described, game experience is being simulated in the code to some degree. You cannot (or rather should not) re-implement ALL of your game’s systems to do the same. Think carefully about which insights you need most and pick the parts that are crucial for this — everything else should be left abstracted. Once you’re done, test that your results correlate in some way to the systems you’ve left out.
- You will read and write data from the spreadsheet A LOT, so getting some short helper libraries written for this might be a good second step once you’ve gotten your feet wet. For example, finding a label in the spreadsheet and organising all values underneath it into an array might be something you want to have a utility function for.
- If you’ve chosen to go with Google Apps Script, you should know that making calls to the Spreadsheet API is expensive, time-wise. Ideally, you want to read all of the data in one go, make all of your calculations, and then output it in full.
- Once your toe skin is all wrinkly (because, y’know, your feet are so wet), consider using clasp and TypeScript. It’s a bit more complicated to set up, but can result in cleaner and more reusable code.
Too good to be true?
Despite my enthusiastic tone, there are some caveats to keep in mind. First, you will still need to play your game, test it and analyse real player behaviour. This is not a replacement for that. If there are any discrepancies you have between what your script outputs and what actually happens in the game, you might need to continue to update your model in order to keep using it.
Secondly, this method will mean building your tools on top of a proprietary product. Especially with something web-based like Google Sheets, you only have Google’s word that something you put together today will keep working in a year’s time.
That’s it for today! If you have found this article useful or have found some great uses for spreadsheet scripts in your game projects, let me know in the comments!
