Localize your website using Google Sheets
1

For localization, to the producers or the clients, they probably prefer something like a full stacked CMS rather than a JSON/XML file as they are not keen with all those brackets and double quotes. To the front-end developers, on the other hand, they probably prefer the JSON/XML solution over the CMS solution as it is way easier to construct and to build with a JSON/XML localization system.

I had this issue before and at some point I learned some PHP to build a simple CMS just because of that. Until one of my ex-colleague Sylvain Tran introduce me of using Google Sheets + PHP to generate the localization files, everything has changed and I found it really handy for handling localization of my projects. Based on that to create my own set up with Node.js and I would like to share it here.

First thing first, here is the git repo and the demo.

The idea is pretty simple and here are the steps:

  1. Create a Google Sheets document and put the data in the key column and several locale columns
  2. Share your document to some selected accounts
  3. Use npm module google-spreadsheet to read the document
  4. Create a JSON/XML file as a template file and use template engine like lodash.template or handlebar to interpolate the data into the template file
  5. Exports into individual locale files

Actually there are some npm modules out there can do everything I mentioned above for you but I personally would recommend you to build you own one. Because it is very easy to build your own one and after that you can have better control over the data.

For example, if you want to add the language names of all available locales into the language bar, you can simply store those translations into a variable add serve it to the template through the lodash.template.

Step1

Create your Google Sheets document at https://docs.google.com/spreadsheets/

And then you can fill in the localized data like this:

In the example above, I created a category and a description columns which are some notes for the producers/clients and they won't be included in our locale files.

Structure-wise, you are free to do whatever you want except you cannot leave a complete empty row, otherwise the data below that empty row will be ignored. To check the range of the data, you can simply go to the A1 field and press Ctrl+A or Cmd+A to see if your data is covered or not.

Step2

Share your document to some selected accounts

In the Google Sheets, you can choose File > Share... to see the following pop up:

For private projects, you might want to disable the access to public and share the edit permission to your clients. Also, you will need an account to access this document in Node.js. Instead of using your own account, you might want to create a bot account which has the view access to the document.

For Node.js to read your document, you will need the mark down the document key:

Step3

Use npm module google-spreadsheet to read the document.

Say you have already installed google-spreadsheet, you can access the document with the following codes:

var GoogleSpreadsheet  = require('google-spreadsheet');
var sheet = new GoogleSpreadsheet('____YOUR_DOCUMENT_KEY____');
sheet.setAuth('___YOUR_USER_NAME___', '___YOUR_PASSWORD___', function(err){
    if(err) {
        console.log(err);
    } else {

        // read the first sheet in the document
        sheet.getRows( 1, function(err, rows){

            for(var i = 0, len = rows.length; i < len; i++) {
                row = rows[i];
                if(row['key']) {

                    // list out all of the English translation
                    console.log(row['key'] + ' is ' + row['en']);

                }
            }
        }
    }
});

At this point, you may want to create a settings.json file and put the data like your account info, the language white list and the file structure info in it:

{
    "user" : "__YOUR_USER_NAME__",
    "pass" : "__YOUR_PASSWORD__",
    "key" : "__DOCUMENT_KEY__",
    "refKey" : "key",
    "srcFile" : "./template.js",
    "dist" : "./locale/",
    "ext" : "js",
    "langList" : [
        "en",
        "fr",
        "es",
        "it"
    ]
}

Step4

Create a JSON/XML file as a template file and use template engine like lodash.template or handlebar to interpolate the data into the template file.

If you are using lodash.template like I did, your template can be something like this:

{
    "id" : "<%=LANG%>",
    "lang" : <%=JSON.stringify(LANG_LIST)%>,
    "meta" : {
        "title" : "<%=metaTitle%>"
    },
    "home" : {
        "title" : "<%=homeTitle%>"
    }
}

You can do whatever you want to leveage the use of lodash.template. You can pass some parsed objects to the template and you can even let the en locale file to access the data from fr. Super flexible!

Step5

Exports into individual locale files

var langList = settings.langList;
var src = fs.readFileSync(settings.srcFile);
var tmpl = _.template(src);
var data, lang;
for(var i = 0; i < langList.length; i++) {
    lang = langList[i];
    data = tmpl(langData[lang]);

    // inject some custom properties into the data
    data.LANG = lang;
    data.LANG_LIST = ['foo', 'bar'];

    fs.writeFileSync(settings.dist + lang + '.' + settings.ext, tmpl(data));
    console.log(lang + ' is generated');
}

Conclusion

meh.

Share:
Comments
Sean T.
2015-04-16 08:44:23
Reply to
I think a better way to localize a website would be to use a localization management platform like https://poeditor.com/ that offers support for JSON files plus other formats: .po, .xliff, .resx/resw or .xml. It comes also with some useful key features that are worth mentioning: REST API, translation memory, automatic translation, GitHub and Bitbucket integration. See if it helps, it improved my localization workflow a lot.
Cancel
What do you think?




All comments require my approval in order to be public. So... Spammers, please get the fuck out of my blog. Cunt!