Using Google Sheets Python API to Manage Scriptable Data in Unity

In my free time, I've been developing a digital trading-card game in Unity with my twin brother. We had accumulated a large amount of card data in a Google Sheet, and when it came time to start porting them into Scriptable objects in our Unity project, I decided I would rather spend some time trying to automate this process.

The goal of this was simply to allow us to use the Google Sheet as our card database, and have the Unity project automatically populate its internal Scriptables folder with the appropriate card Scriptables based on the data present in the Sheet.

The first step I took was determining how to get Google Sheets and Unity to talk to each other. After some initial research, I determined that there were two possible courses of action - I could utilize Google's C# API to access Google Sheets directly with Unity, or I could write a Python script using Google Sheet's API that acted as a sort of middle-man between the two services.

There were several factors I ended up weighing when it came to this decision. Firstly, as far as I could tell, there wasn't a direct way to access Google Sheets specifically through C#; rather, Google provided a general API for all of its application suite, and within this API, Sheets data could be potentially accessed and manipulated. On the other hand, a Google Sheets Python module was readily available and relatively easy to set-up. You already know the result of this decision based on the title of this blog post, but I'll explain why I went with Python.

• Usability & Accessibility - Setting up the system with Python would take a couple of lines of code at most and would be extremely easy to read, understand, and build off of if future features became necessary. This is mostly due to the existence of the gsheets library, a wrapper around the Google Sheets API.
• Lack of need for speed - This operation would not be one of constant importance. We'd only need to access the Google Sheets data when something was updated, changed, or added, and once the operation was finished, we wouldn't have to use it again until the next time. We didn't expect to have to update the data more than once or twice a week.
• I will say this; during this stage of development, we were trying to create a prototype of the game that would help us build out the features and design an enjoyable card game, with the hopes of one day having the resources to create a game of the scope and size of Hearthstone. If we ever reach that point, speed will become more important as we'd need to be updating the game's server with new card releases and changes. In that situation, the C# API, or another system entirely, would almost certainly be the right way to go.

Once I decided to go with Python, I knew eventually I'd have to get the data into Unity, so I decided to try and construct the following data pipeline.

Google Sheets -> Python -> .csv file(s) -> C# -> Scriptable Objects

It was time to write the Python script. This proved to be straightforward - gsheets has a lot of extremely helpful documentation on how to quickly utilize the API through Python. You can find it here. The first step was creating and setting up the the necessary .json files, which is a step covered in the linked documentation. Once those were set up, we were free to start accessing the Sheet in question, using said .json files.

Thanks to the ease of gsheets, it was trivial to produce CSV files. For context on the following code snippet, our card-game features two card types at the moment, 'Creature' and 'Auxiliary' cards. There were two subsheets containing cards of these two types in the Google Sheet, so I split them up into separate CSVs here in order to simplify the Scriptable creation process downstream.

At this point, the Python step of the pipeline was done. Now, we needed a way to translate the CSV data to Unity Scriptables. First, I developed an editor tool that directly ran the Python script with the press of a button to provide for easy access. The tool was going to be quite simple: provide some fields for paths, and a button which would run the Python script. Here is the function that runs the Python script (NOTE: the variables authJSONFilesPath and pythonPath are class variables initialized in the GUI start-up function not displayed here):

I won't go into the details of how to create a Unity editor tool here as it's not super relevant for the topic of this blog post, but here's a great place to get started: click me. The resulting GUI looked like this:

Clicking "Get Latest CSV Files" calls the function GetCSV().

Finally, we needed to translate that CSV data to Scriptables. This part was fairly trivial compared to the rest of the project. The CSV data could easily be interpreted as a list of strings and then processed to produce each Scriptable with the proper values. 

Thus, my project was complete and had achieved its goal. Without spending a minute of time manually producing Scriptables, I had developed a pipeline that automatically created them from our Google Sheet in mere seconds through the click of a button. Though it may not be optimal for the large-scale game we hope to turn this prototype into one day, and there certainly may be better ways to accomplish each step of the pipeline I described here, it still got the job done and is useful for the current stage of development, so I'm happy with it! I wanted to share how I developed my solution in hopes that it may help or inspire someone else to make something cool.

Thanks for reading!