Tech Stories: From Google Sheets to JSON files

Good RPG games have a deep configuration and variants in order to enrich the gaming experience and the replayability for the different battles, weapons, powers, characters, and more.

This is very challenging when we need to translate all these configurations to the game source code, and even more challenging when all these configs and their formulas and test cases are already available in a Google Sheet.

Starting point

Creating our own JSON files and reading them from the source code was not a problem, there are many available solutions to achieve that. We’ve started with some mock data, everything was working as expected, and we were able to move forward with the game logic implementation and test the full flow to confirm the correct functionality.

Then, we had to go beyond that and update all these JSON files with the real values, and be flexible enough to update them later easily if our awesome designer decides to tweak these values again for a better experience.

Implementing the solution

The idea was clear: our input data is a Google sheet, and the output are JSON files. After looking at some existing tools, there’s no magical solution available, specially when we don’t want to convert the entire sheet and using exactly the same column names and values, we were looking for a ‘map reduce’ solution.

Google provides App Scripts, a cloud-based JavaScript platform that lets you integrate with and automate tasks across Google products, https://developers.google.com/apps-script

After looking for a basic sheet-to-json example, I’ve been working on my custom changes to create multiple menu options that we can execute and run individually. At the end, I was able to apply a kind-of map-reducer solution to convert the output into a custom JSON string.

The time spent on these scripts was not so much, and the result is great.