Godot 3.0: Exporting game data from Google Sheets into a staticData .gd file using node.js

If you’re making a Godot game with a bunch of “static data” such as items with stats and effects, quests, characters with stats, enemies, loot tables, spawn tables, etc. – then you might be wondering where to put your game’s data (and how to format it) in a Godot project.

When I first set out to do this I couldn’t find a great guide so I decided to figure it out and make one. My way isn’t perfect, but it gets the job done. 

My goals (and what I ultimately built):

• Organize my game’s data into separate tabs in a Google Sheets spreadsheet.  Advantages: I would get to use Sheets’s validation features, I can access/edit my data from any computer anywhere, working in Sheets is easy and familiar to me and anyone who has used Excel.
• Export from Sheets into something I can process into Godot objects. I went with JSON. (More on this later)
• Turn the exported JSON into a game data file, which would be “globally” available for my game’s code to use
• Easily access items, mobs, etc. by ID from within the game’s code. I want to do something like this: var sword = allItems[“Rusty Sword”].duplicate()

Setting up the game data in Google Sheets

If you’re doing something similar feel free to build your data however you like. I liked rows, with the thing’s id in the first column. 

This first example is the items sheet. These are all the swords, robes, armor pieces, crafting items, quest items, etc. in the game. Every item has a name, a “slot” it goes into, class restrictions, and stat bonuses. Typical RPG stuff. This sheet is very large, but it’s easy to search and I color coded by slot to help make it easier to find things. 

The next example is my game’s enemy (mob) data. Enemies (mobs) in my game just have a few stats, but you can see how the data could be built out to support much more complex monsters.

You might have noticed that the mobs have loot tables. Loot tables are yet another tab in the data sheet.

Google Sheets lets you set up “validation” so that some cells can only contain data from another sheet. (You can type the loot table name or pick it out of the dropdown that Google Sheets generates when you use validation.)

Adding a simple validation here helps cut down on typos and errors that result from things getting renamed. 

There are more data sheets for my game, but this should be enough to give you an idea of how to put your game data in a spreadsheet. Next up, exporting.

Exporting the game data as .json

I picked .json for a few simple reasons:

• It’s easy to find Google Sheets to JSON exporters
• I’m already familiar with JSON and it’s fairly human-readable
• Godot has some built-in capabilities for parsing JSON

For the export itself, I used the free “Export Sheet Data” Google Sheets add-on (link). Just install it and find it in the Add-ons dropdown.

There are a bunch of settings in here to play with depending how you want your data formatted.

I wanted mine to be an array of objects, so the settings I checked are:

The exported data looks like this. (This is a super truncated version of what the items tab gets exported as.)

[
  {
    "name": "Novice's Blade",
    "icon": "sword1.png",
    "bodySprite": "sword1.png",
    "itemType": "sword",
    "consumable": false,
    "prestige": 0,
    "slot": "mainHand",
    "rarity": "common",
    "classRestriction1": "warrior",
    "classRestriction2": "",
    "classRestriction3": "",
    "classRestriction4": "",
    "classRestriction5": "",
    "hpRaw": 0,
    "manaRaw": 0,
    "dps": 2,
    "armor": 0,
    "strength": 0,
    "defense": 0,
    "intelligence": 0,
    "noDrop": false
  },
  {
    "name": "Rusty Mace",
    "icon": "mace1.png",
    "bodySprite": "mace1.png",
    "itemType": "mace",
    "consumable": false,
    "prestige": 0,
    "slot": "mainHand",
    "rarity": "common",
    "classRestriction1": "cleric",
    "classRestriction2": "paladin",
    "classRestriction3": "",
    "classRestriction4": "",
    "classRestriction5": "",
    "hpRaw": 0,
    "manaRaw": 0,
    "dps": 2,
    "armor": 0,
    "strength": 0,
    "defense": 0,
    "intelligence": 0,
    "noDrop": false
  }
]

Turning the exported .json into a .gd file for Godot 

Here’s what we need to do:

• Open the .json file(s) (remember there’s one .json for every tab in the spreadsheet)
• Parse it line by line and make any formatting changes necessary
• Write the parsed and modified data to a .gd file that contains all of the game’s “static” data
• Access the static data in the .gd file from the game’s code 

There are practically infinite number of ways to accomplish these steps (it’s just some file i/o and text parsing), but I went with what I already knew – Javascript. My quick-n-dirty parser (named “Parsely”) is far from beautiful code but it gets the job done.

You can see it in its entirety here: Parsely.js gist.

Parsely’s code is written specifically for my project, but one thing I want to point out is the way it transforms my objects. 

An object comes out of the Google Sheets exporter like this simplified example:

[
  {
    "name": "Novice's Blade",
    "icon": "sword1.png"
  },
  {
    "name": "Awesome Weapon",
    "icon": "sword2.png"
  }
]

But what I really need is this:

{
  "Novice's Blade": {
    "name": "Novice's Blade",
    "icon": "sword1.png"
  },
  "Awesome Weapon": {
    "name": "Awesome Weapon",
    "icon": "sword2.png"
  },
}

Notice the array has become one giant object, and each object is a property within that object. I want to grab my item by its name, ie: “Novice’s Blade”, and get the associated object data. I also want all this stuff to be in one giant object so I can grab objects directly without having to iterate through it (as I would have to if it were an array). 

So that’s what Parsely is doing when it does this:

for (var value of fromJSON) {
  var key = "";
  if (file == "items.json") {
    key = value["name"];
    formatted[key] = value;
  } ...
... 

It’s getting the name and making that the key, and it’s getting the whole object and making that the value. Now I have a giant object full of smaller objects, where each smaller object is a unique item in the game (like the second JSON example above).

Also, sometimes I wanted to add more parameters to an item at runtime, such as a unique ID or a boolean representing whether it was an enhanced item or not. Those things don’t exist in my spreadsheet, they’re just slapped on when this script is run. 

Here’s an example from parsely.js of three new params (“itemID”, “improved”, and “improvement”) being added to each item as it is processed.

For the sake of keeping everything related to my project in one place, I placed parsely.js and the .json files it processes in my Godot project folder like so:

When I export from Google Sheets, I download the .json file it generates and place the .json file in the appropriate folder (names, staticData, or timedNodeData). Parsely handles files from different source folders slightly differently, ie: files from names get built into arrays, files from timedNodeData get some “inProgress”, “readyToCollect” booleans attached, etc.

Finally, we can run it! I open a Terminal window and navigate to the Parsely folder. To run, I type:

node parsely.js

It then grabs those json files, processes them, and places them in res://gameData (which is where I want them to go).

Inside each of these .gd files is an object or an array formatted as one long line.

This is staticData.gd:

Using the data in my Godot project

Finally, make the staticData.gd file global by adding it in Project Settings > Auto Load. Now it’s accessible everywhere in the project. 

Now, throughout the project items, mobs, loot tables, etc. are all accessed from staticData like in these examples:

var newItem = staticData.items[itemNameStr].duplicate()

var loadout = staticData.loadouts[loadoutIDStr]

And that’s it! Now my game has all of its items, spawn tables, loot tables, quests, crafting recipes, and more pre-loaded as data objects and universally available anywhere in the game’s code.