Connecting Unity Editor to Google Sheets

If you worked in Unity for a while, you’ve probably seen some plugins, like the I2 Localization, that can connect and sync with Google Sheets. It seemed like magic to me, so I decided to take a look at how it’s done and it turns out to be super simple.

Let’s start by creating a new google spread sheet. Selecting “Script Editor” from the top menu, will open a new tab with a sort of IDE for writing g-drive scripts.

The Javascript API allows you to add menu items for custom functions and lots of other things. What we’ll use is that if you declare a function called doGet() you can listen for web requests.

Here is a script that takes the first 8 values in the spreadsheet and returns them in a JSON format.

function doGet(e)
{
  var result;
  
  var sheets = SpreadsheetApp.getActiveSheet();
  var values = [];
  for( var i = 0; i < 8; i++ )
  {
    values.push( sheets.getRange(1+i, 1).getValue() );
  }
  
  var result = {result:values};
  
  
  var JsonValue = JSON.stringify(result);
  
  return ContentService.createTextOutput(JsonValue.toString()).setMimeType(ContentService.MimeType.JSON);
}

 

Now click Publish/Deploy as web app and choose Who has access to this app – Anyone, even anonymous. Now you should get an URL that can be used to access this script from the outside. (You might also get a warning about using 3rd party apps).

Now let’s go to Unity and create a scriptable object to hold our values.

[CreateAssetMenu(fileName="MySO",menuName = "MyScriptableObject")]
public class MyScriptableObjectScript : ScriptableObject
{
    public int[] Value;
}

 

Now with a custom inspector we can actually write the web request, using UnityWebRequest. The only problem is we cannot use a Coroutine to wait for the result, so instead we need to check the state of the request on EditorApplication.update.

[CustomEditor(typeof(MyScriptableObjectScript))]
public class MySOInspector : Editor
{
    private UnityWebRequest _webRequest;

    public override void OnInspectorGUI()
    {
        DrawDefaultInspector();
        
        if (GUILayout.Button("Update from GDrive"))
        {
            _webRequest = UnityWebRequest.Get(YOUR_URL_HERE);
            _webRequest.SendWebRequest();

            EditorApplication.update += CheckForImportRequestEnd; // the only way to wait for a process to finish is with this
        }
    }

    private class GDocResponse // this class is used to parse the JSON
    {
        public int[] result;
    }
    
    private void CheckForImportRequestEnd()
    {
        if (_webRequest != null && _webRequest.isDone)
        {
            var result = JsonUtility.FromJson<GDocResponse>(_webRequest.downloadHandler.text);
            MyScriptableObjectScript myTarget = (MyScriptableObjectScript)target;
            myTarget.Value = result.result;
            EditorApplication.update -= CheckForImportRequestEnd;
            Repaint();
        }
    }
}

 

And that’s it. After pressing the button in the inspector, you should see the first 8 values from your google spreadsheet.

If you edit the spreadsheet script, make sure to publish it with a higher version number. Otherwise the changes won’t show when you call the URL.

The reverse – sending data from Unity to the spreadsheet – can be done using the doPost method with one argument and reading the incoming data from it. On Unity side, use the UnityWebRequest.Post method.

All source code can be found here: github.com/randalfien/unity-editor-scripting-tips

This is a second blog post in a series on editor scripting, check out the first part here.