Sunday, December 1, 2013

Reading public Google Drive spreadsheets in Unity, without authentication

I'm working on a project with a collaborator who doesn't use Unity and doesn't really have an interest in game development (gasp) but it is still important that she can add/edit item data for the game. From a practical workflow perspective, I probably would've kept the item data separate from the game code anyway, to make it easier to balance and tweak stuff. This is usually the stage at which you'd make your own level editor or game database editor or something, but maybe there's a better way -- we can just tell Unity to read from a public Google Docs spreadsheet and parse the data. That way, anyone can edit the game levels or localization strings or whatever from anywhere in the world, and the game client will update data seamlessly.

A lot of this post comes from Clark Kromenaker's great post on accessing Google Docs services with C#, and a lot of my setup process is the same as his.

However, my particular project didn't need any data kept private, the game itself didn't need write access to the documents, and authentication looked like a pain (e.g. using OAuth 2.0 requires you to open a browser window so the user can okay the permissions? Yeah, no thanks) so I worked out how to access read-only publicly published Google Drive spreadsheets without any logins or anything.

First, start with a lot of steps from Kromenaker's post:

1) Download and install the Google Data API for .NET...

2) Grab the following DLLs from the package and put them in your project folder, maybe under /Assets/Plugins/ (as far as I know, this should work fine in the free version of Unity since these are pure .NET assemblies? I think?)
  • Google.GData.AccessControl
  • Google.GData.Client
  • Google.GData.Extensions
  • Google.GData.Spreadsheets
  • Newtonsoft.Json
From there, I diverge from the original tutorial:

3) Open up your spreadsheet in Google Docs and go to File >> Publish to the Web, and publish it to the web. To make it publicly accessible to the API, you MUST publish it publicly like this! You CANNOT just click the "Share" button and change access rights to share with anyone, that's something different according to the API. Yes, it's confusing.

4) Now before we go back to Unity, we need some way to tell Unity which spreadsheet document to grab. To do that you should copy the document key identifier, that long string of letters and numbers after "key=" in the URL to access your spreadsheet, and save that somewhere for later. It should look something like "1Ak-N8rbAmu7WdGRFdllybTBIaU1Ic0FxYklIbk1vYlE"... and stop at the first ampersand ("&") in the URL, you don't want any ampersands here.

5) Copy and paste the script below into a file like Assets/Scripts/GDocService.cs or something, somewhere in your assets folder. It doesn't matter where they go because the classes have public static functions and thus any script in the project can use them.

The "InsecureSecurityPolicy" stuff is to trick Mono into making HTTPS calls. More details on that are in Kromenaker's post.

6) Make a new separate script and make sure you have "using Google.GData.Client;" and "using Google.GData.Spreadsheets;" at the top.

7) To grab the data from that script, you'll need to use that document key you copied from step 3 to pass into my GDocService.GetSpreadsheet( ); function. From there, use the list-based spreadsheet feed API to grab data from your spreadsheet, or look at the debug stuff in the GetSpreadsheet( ); function to get started with that.

Keep in mind that all the data you grab will be of type "string" -- to get it usable as numbers, you will have to use int.Parse( ); or float.Parse( ); to turn the string into a number. Or let's say you have a dollar sign ($) at the beginning of each column value in the spreadsheet; you'll have to use string.Substring( ); or string.TrimStart( ); to take that dollar sign off.

  • Ideally, you will cache the spreadsheet data when your game starts up, and work with that data.
  • This is read-only access, and public access. You are telling Unity to read from a public spreadsheet feed that anyone can grab from their browser too.
  • This does not work in the Unity web player's security sandbox. The security sandbox allows you to grab files only on the same site as where the web player is hosted. What you'd do in the web player instead, maybe, is use Application.ExternalCall to call some Javascript on the web page, have that Javascript grab the data, and then pass that data back into Unity. For more information on Unity browser communication, see the manual