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!