Unity Google Sheets Localization

readme.md

A little snippet of how to serve localization from google sheets using a Google Apps Script.

• Endpoint.gs is the code for the web endpoint you build in Google Apps Scripts. It takes a locale= parameter in the GET query and serves a combined json blob of k:v pairs from all sheets within the spreadsheet.
• AppScript.json gives this the appropriate permissions
• LocalizationBuilder.cs adds a unity menu item that parallel executes requests for each locale, saving <locale>.json for each.

The assumed format for the localization sheets is:

key	en_US	fr_FR	...	es_ES	translator notes or some other column
button.select	Select	Sélectionner	...	Seleccione	It's a button label

etc

Note AppScript.json is the low-security route (just a secret url) to avoid oauth stuff when pulling. There are other run-as and access restrictions you can set.

you can then deserialize the json using JsonUtility.FromJson<SerializedLocalization>(filename) and build the entries into a real data structure like a dict.

it'd be real easy to change this to make it serve csv also if that's your thing.

AppScript.json

{
  "webapp": {
    "access": "ANYONE_ANONYMOUS",
    "executeAs": "USER_DEPLOYING"
  },
  "exceptionLogging": "STACKDRIVER",
  "oauthScopes": ["https://www.googleapis.com/auth/spreadsheets"],
  "runtimeVersion": "V8"
}

Endpoint.gs

/**
 * Serves json blob of combined localization for a requested locale
**/

spreadsheetID = "ID_OF_SPREADSHEET_FROM_URL";

function doGet(e) {
  var locale = e != null && "locale" in e.parameter ? e.parameter.locale : "en_US";
  var spreadsheet = SpreadsheetApp.openById(spreadsheetID);

  var sheets = spreadsheet.getSheets();
  var allTranslations = [];
  
  var doSheet = function(sheet)
  {
    var keys = sheet.getRange(2, 1, sheet.getLastRow()+1, 1).getValues();
    var header = sheet.getRange(1, 1, 1, 20).getValues(); // 20 is abritarily "big" to cover all language columns
    var localeColumn = -1;
    for(i=0;i<20;i++)
    {
      if(header[0][i] == locale)
      {
        localeColumn = i+1;
        break;
      }
    }
    
    if(localeColumn < 0)
    {
      return ContentService.createTextOutput("").setMimeType(ContentService.MimeType.JSON);
    }
    
    var locColumn = sheet.getRange(2, localeColumn, sheet.getLastRow()+1, 1).getValues();
    
    for(i=0;i<locColumn.length;i++)
    {
      var key = keys[i][0].toString();
      var value = locColumn[i][0].toString();
      if(key != "" && value != "")
      {
        allTranslations.push({"k":key, "v":value})
      }
    }
  }
  
  sheets.forEach(doSheet)

  // This is silly but it's necessary for using unity's JsonUtility. It only understands 
  // object-like json blobs so we wrap the list in an object
  var json = JSON.stringify({"list":allTranslations});
  
  // return JSON text with the appropriate Media Type
  return ContentService.createTextOutput(json).setMimeType(ContentService.MimeType.JSON);
}

LocalizationBuilder.cs

using System;
using System.Collections.Generic;
using System.Threading;
using Assets.Code.Utilities;
using UnityEditor;
using UnityEngine.Networking;

namespace Code.Editor.Automation
{
#pragma warning disable 0649
  // These are dumb but Unity can only deserialize json to object, and of course the usual problem with generics
  [Serializable]
  private struct SerializedLocPair
  {
    public string k;
    public string v;
  }

  // run JsonUtility.FromJson<SerializedLocalization>(filename), then you can iterate the list and shove into a real dictionary
  [Serializable]
  private class SerializedLocalization
  {
    public List<SerializedLocPair> list;
  }
  // End dumb stuff
#pragma warning restore 0649

  public static class LocalizationBuilder
  {
    private static readonly string[] AllLocales = {"en_US", "fr_FR", "it_IT", "de_DE", "es_ES"};
    private const string GoogleAppsEndpoint = "https://script.google.com/ENDPOINT_GOOGLE_GIVES_YOU_WHEN_YOU_PUBLISH";

    [MenuItem( "Tools/Rebuild Localization", false, 2000 )]
    public static void BuildLocalizationFiles()
    {
      var asyncOps = new List<UnityWebRequestAsyncOperation>();
      foreach( var locale in AllLocales )
      {
        var request = new UnityWebRequest( $"{GoogleAppsEndpoint}?locale={locale}", "GET", 
          new DownloadHandlerFile( $"Assets/Resources/Localization/{locale}.json", false ), 
          null  );
        var asyncOp = request.SendWebRequest();
        asyncOps.Add( asyncOp );
      }
      
      
      while( asyncOps.FindIndex( _ => !_.isDone ) >= 0 )
      {
        var progress = 0f;
        foreach( var op in asyncOps )
        {
          progress += op.progress;
        }
        progress /= asyncOps.Count;
        
        if( EditorUtility.DisplayCancelableProgressBar( $"Fetching Localization", "", progress ) )
        {
          foreach( var op in asyncOps )
          {
            if( !op.isDone )
            {
              op.webRequest.Abort();
            }
          }
          break;
        }
        Thread.Sleep( TimeSpan.FromSeconds( 0.25 ) );
      }
      
      EditorUtility.ClearProgressBar();
      AssetDatabase.Refresh();
    }
  }
}