Importing Spreadsheet data from Google Docs Sheets to Unity via Google Sheets for Unity Lite and JsonDotNet

No pretty pictures to accompany this one - just a workflow process that is useful, but a little bit painful to get set up the first time.

For several games that I've worked on, it's handy to use a spreadsheet to manage data, and then import that data into the game. Conceivably, you might do that import at runtime, but I get a little twitchy about games that require an internet connection without a compelling gameplay benefit.

So, this process starts with data in a Google Docs Spreadsheet (officially called "Google Sheets", because "Google Docs" is apparently just the word processing component of the Google office suite, which is nuts to me - a word processing document is a document, and a spreadsheet is a document, and a slide deck is a document). I'll be using Google Sheets For Unity Lite, which installs a web service script to serve up your data (password protected), which you pull down into the Unity editor as JSON data. Then, we'll use JsonDotNet to turn that data into C# instances of serializable classes, which you can use immediately. I'll also store a copy of the data in the resources directory, which is where the game will get the data at runtime.

Links

Google Sheets For Unity Lite - there are a few versions of Google Sheets For Unity, but for my purposes, the lite version on the Unity Asset Store suffices. It's a paid product, currently $19.99, for which you get code and good documentation. 

JsonDotNet - a free asset on the Unity Asset Store, I don't have a lot to say about it.

Workflow Video - I got a lot of this knowledge from this video by Sloan Kelly. In the video, he walks you through the basic process I detail here. It's from an earlier version of the GSFU tool, but the overall process is the same.

If all this documentation is so good, why the blog post? Maybe there's not a lot of value to be added, but I find that I can use information better sometimes when there's a two page checklist of a process, as compared to a 45 minute tutorial video. And my process isn't exactly the same as the GSFU documentation. Also, the GSFU documentation is in a PDF, which is fine, but is sometimes not as easy to work with. So, this is intended to supplement stuff that was already useful to me.

Process

1. [Optional] Watch the video linked above. I'm sure there will be details that are covered there that I'll miss
2. [Recommended] Read the GSFU doc. 
3. Go to the Unity Asset Store from within the Unity Editor to purchase and install both GSFUlite and JsonDotNet.
4. Drag the "Drive Connection" prefab from the GSFU/Utils folder into your scene. Verify that the Drive Connection prefab has a linked ConnectionData object, we'll be using it later.
5. Find your spreadsheet ID, which is the alphanumeric string between "/d/" and "/edit" when you look at the URL for your spreadsheet in Google Docs. e.g. if your URL is https://docs.google.com/spreadsheets/d/abc123456/​edit#gid=671966417, your spreadsheet ID is abc123456. It'll likely be a lot longer than that, like maybe a 40 character string. Security!
6. Fill in the spreadsheet ID into the ConnectionData object, above.
7. Create a file in Assets/Scripts/Editor that defines a class that derives off Editor. This class will define functions that are called by the Unity Editor UI. You'll want to have one or more functions annotated with a string like "[MenuItem ("MicroTwenty/Import Monsters from Google Sheets")]" which tells the Unity Editor to create a MicroTwenty menu option in the main menu, one item of which is to Import Monsters. You can add more levels of submenus by adding more slashes to your item name. The function can be named anything you like, but it should be a static void function.
   
8. In your new static void function, register a callback like so: "GoogleSheetsForUnity.Drive.responseCallback += HandleDriveResponse;" and then ask GSFU to request your sheet data like so: "GoogleSheetsForUnity.Drive.GetTable (_monsterTableName, false);". The HandleDriveResponse callback will be a function you write (soon). The _monsterTableName is the name of the specific sheet within your spreadsheet. This might not be case sensitive. 
9. Your HandleDriveReponse function (if you haven't already auto-generated it) will be a static void function that takes a Drive.DataContainer. First, you want to deregister for the callback (seems like a lot of work for each menu action, but that's Unity. "GoogleSheetsForUnity.Drive.responseCallback -= HandleDriveResponse;". Then test that you're getting the expected data back: " if (dataContainer.QueryType == Drive.QueryType.getTable) {
       string rawJSon = dataContainer.payload;
     if (string.Compare (dataContainer.objType, _monsterTableName) == 0) {"
10. At this point, you've got an array of JSON objects, but what you might like is a JSON object that contains an array of JSON objects. So, we wrap the JSON by wrapping the json with "{\"monsters\": " and "}", which gives us JSON that we can deserialize into a C# object.
11. [Optional] We use JsonUtility to deserialize the JSON: "var monsterSheet = JsonUtility.FromJson<MonsterSheet> (wrappedJson);" where MonsterSheet is a C# class we'll write shortly. Perhaps Debug.Log the first monster from the monsterSheet to make sure that things have downloaded and parsed as expected: "Debug.Log ("monster 0 " + monsterSheet.monsters[0].Name);"
12. Save the JSON into the game's resources by making a path: " var path = System.IO.Path.Combine (UnityEngine.Application.dataPath, "Resources/JSON/weapons.json");  " and then writing the wrapped JSON out to that location " System.IO.File.WriteAllText (path, wrappedJSON);  ".
13. Now we need to make the MonsterSheet class, which is a C# object that is a container for monster data. This class should be defined in the main assembly (i.e. not the Editor assembly, where your fetcher script is). It should define a Serializable class MonsterSheet with an array of MonsterRows. We'll call this array "monsters", to match the name we added when wrapping our JSON, above.
14. Now we need to make the MonsterRow class, which is a C# object that is a container for data about a single monster, a single row from our spreadsheet. This should have a public variable for each column in the table. You can use strings, ints, floats, and probably other data fields, too. Make sure this class is Serializable.
15. Now we need to set up the Google Cloud deployment. For this, find the "Deployment/Server Side" section of the GSFU doc. There's a link there to a Google Cloud script. Make a copy of that script (my copy of the docs says that clicking on the link makes a copy, but I don't think that it does). Rename your copy something useful that has no spaces. I don't know why that's an issue, but it apparently is.
16. Go to File / Project Properties to open a properties dialog, and from that dialog, go to the Script Properties tab. You'll get a UI where you can add properties. Add a row with a key "PASSWORD" (no quotes) and a value of a password that you like. I won't look. Keep a copy of the password in a safe place.
17. Go to File / Manage Versions, and "Save New Version".
18. Go to Publish / Deploy as Web App. There will be several steps making sure you want to allow this script to access your data. This is perhaps the scariest part of the whole process, but the script is there for you to look at, you can convince yourself it's not downloading malware, or uploading your personal information. Copy the script URL to a safe place.
19. Back in Unity, in the ConnectionData object, paste in the web service app URL and the password.
20. Create a "Resources/JSON" folder to receive your downloaded JSON objects.
21. At runtime, load the data like this: "             var monsterJSON = Resources.Load<TextAsset> ("JSON/monsters");
                var monsterTable = JsonUtility.FromJson<MonsterSheet> (monsterJSON.text);
    ". Note that Unity will add on the .json extension for you. (Magic!)

Phew. As simple as that. Hopefully, that will supplement the other documentation out there for this process. Enjoy!

MicroTwenty: Using Hex Grid math to select correct orientation of arrow sprites

Left of the white circled unit are 4 pixels that constitute an arrow. That's what we're talking about today.

Longtime readers of this blog may know that I've got a long-term project of making a computer role playing game (CRPG) in my spare time. I've made many many versions of this game already, in various forms, which I liken, in a way, to painters doing "studies" of a work before doing the full project. Call them tech demos, or vertical implementations, or just small versions.

One might hope to put those projects together into one big game, but a) that's not how my projects work b) they're all written in different languages.

My current take on this project is what I'm calling "MicroTwenty" - a small amount of content, but as feature-complete as I can make it. Content always runs away from me, and I love adding in new maps, new monsters, new weapons. So, we'll see how "micro" it is when I walk away from it.

The particular (ha - there's a related block of code called ParticleOrder, but that's not what I'm talking about here) bit of code that I wanted to touch on today was using hex grid "cubical" coordinates to figure out which of six arrow sprites (or three, if you're being lazy and using back-to-front symmetrical arrows) to use when one unit is shooting at another unit.

In a higher-res (read: not retro pixel) graphic style, you could just find the vector from the attacker to the target, and get a quaternion or using arctan to get an angle. And I could do something like that here - I know the target location (in tile space) and I know the shooter's location (also in tile space) - I could totally convert those locations into screen space, find a screen space vector, and do some trig.

There's an easier way, though. If you're familiar with Red Blob Games' Hexagon Grids page, you'll already be comfortable using an integer triple to represent tile coordinates. By subtracting the target coordinates from the shooter's coordinates, you get a vector, again in integer triple space.

I wanted a function that took in a HexCoord (that is, an integer triple), and returned a "Hextor" index; an integer value in the range zero to five indicating the "facing" that the vector was pointing in (starting at East = 0, Northeast = 1, and proceeding counterclockwise, as you'd expect).

My first implementation is this:

        private int CalcHextorForVectorSlow (HexCoord vec)
        {
            List<HexCoord> bases = new List<HexCoord> {
                new HexCoord(1, -1, 0), // East
                new HexCoord(1, 0, -1), // NE
                new HexCoord(0, 1, -1), // NW
                new HexCoord(-1, 1, 0), // West
                new HexCoord(-1, 0, 1), // SW
                new HexCoord(0, -1, 1)  // SE
            };

            int maxDot = -1;
            int bestHextor = -1;
            for (int i = 0; i < 6; ++i) {
                var b = bases [i];
                int dot = vec.x * b.x + vec.y * b.y + vec.z * b.z;

                if ((bestHextor == -1) ||
                    (dot > maxDot)) {
                    maxDot = dot;
                    bestHextor = i;
                }
            }
            return bestHextor;
        }


Which is pretty easy to read - it just does a dot product against each of the six "basis" vectors. If you wanted to change things around to have the facings oriented slightly differently, you could rewrite the bases list, and the logic would remain the same.

I reached out to Amit Patel (the man behind the Red Blob Games site), and asked if there was an easier way. He pointed me to this "directions" page which uses simpler math, though trades heavily on the characteristics of cube coordinates.

My implementation of that looks like this:

        private int CalcHextorForVector (HexCoord vec)
        {
            // from https://www.redblobgames.com/grids/hexagons/directions.html
            // Thanks, Amit!

            var xmy = vec.x - vec.y;
            var ymz = vec.y - vec.z;
            var zmx = vec.z - vec.x;

            var axmy = Math.Abs (xmy);
            var aymz = Math.Abs (ymz);
            var azmx = Math.Abs (zmx);

            if ((axmy > aymz) && (axmy > azmx)) {
                // E or W
                return (xmy > 0) ? 0 : 3;
            } else if (azmx > aymz) {
                // SW or NE
                return (zmx > 0) ? 4 : 1;
            } else {
                // NW or SE
                return (ymz > 0) ? 2 : 5;
            }
        }

It's fewer calculations, but maybe a little harder to understand what's going on when you look at it. I mean, documentation is good, and that's part of why I'm writing this blog post.

So, now I can choose the correct 4-pixel sprite of an arrow in flight to get within plus or minus 30 degrees of the arrow's trajectory. Seems good enough for this game.