How I crowd-translated my product tour with Google sheets as a custom collaboration tool

This post is about my experience using Google sheets as a custom collaboration tool to crowd-translate the tour in my Plus for Trello Chrome extension. The extension adds many features to trello.com (used by over 10 million users). The extension has over 40,000 users and growing quickly.

Just after one hour of publishing this tool I got volunteers signed-up for five different languages (now gone up to 14).

Deploy your own working copy in under 1-minute to better follow this post. Just copy this template from a desktop browser, then use its "Plus" sheet menu that automatically appears in your sheet copy. Custom Google sheet menus or sidebars are not yet supported in the mobile sheets app.

You need to be familiar with JavaScript to play with Google Apps Script. To experiment with this sample you should also know the typical front-end HTML+JS+CSS and the concept of "client-side" (.js) and "server-side" (.gs) apps script code.

I picked Google sheets to crowd-translate the extension because it has excellent collaboration features with support for multiple editors at the same time, comment approvals, suggestions and so forth.

The steps:

• Make a master spreadsheet. Later make one copy per language.
• Export the English tours (one per tab) to the master spreadsheet.
• Create a custom sidebar in the master using client and server-side JavaScript.
• Handle all oauth permissions with no code.
• Deploy the tool to volunteers using a Google form for sign-up.
• Update all code and fixed content in the copies from a single place using libraries, templates and importRange.
• Monitoring progress real-time by consolidating status into a "database" for me and all users to see. Potential volunteers easily see which areas still need work.

Make a template "master spreadsheet"
I use one sheet per language, shared to one or many volunteer translators. I first make this template with all the rules, protections, custom menu and sidebar code and then made a copy for each language and share with editors. The embedded apps-script code in the template also gets copied without additional setup needed. It is now a trivial single-step to add new languages or editors to the crowd. I started with translating the extension “tour”. It has over 50 bubbles that move around the different Trello and Plus features inside trello.com pages:

The orange bubbles contain the HTML text we want to translate. It is tricky because the format needs to also fit well within the bubble (in three supported bubble sizes). I wanted the translator to try their texts in different bubble sizes and easily try different layouts.

Turns out this was easy to do in Sheets. I built a custom Google Sheets sidebar using JavaScript, the same language used by the extension. I already had all that bubble code and tour data in the extension so it was mostly a matter of copy-pasting the code and libraries into the apps script editor. Displaying the bubble in the sidebar was easy and took a few minutes to get working.

To get the initial English tour into the spreadsheet I first added code in the extension to write the tours to the Chrome console as tab separated values (TSV) which I can copy and paste to the master spreadsheet in three separate tabs (one for each tour). Google sheets recognizes the format and places each item into the right row and column.

//tour: array of steps.
//step: object like {text:"step html", size: "150", id: "3"}
//name: tour name
//special string \r\n is means line-break. \t means tab

var log = "";
tour.forEach(function (step) {
  var text = step.text;
  if (text.indexOf('"') >= 0)
    text = text.replace(/"/g, '""'); //escape any existing double quotes.
  log = log + (log?"\r\n":"") + '"tour"\t"' + name + '"\t"' +step.size+'"\t"'+ step.id + '"\t"' + text + '"';
});
console.log(log);

Client-side HTML+JS+CSS in a Sheets sidebar

I followed the htmlService guide and its best practices which explain how to separate the code into familiar HTML + JavaScript + CSS plus a bonus  google.script.run function that lets you easily call into your server-side (.gs) apps script.

function showPlusSidebar() {
var html=HtmlService.createTemplateFromFile('preview')
.evaluate()
.setSandboxMode(HtmlService.SandboxMode.IFRAME)
.setTitle("Plus tour preview");
SpreadsheetApp.getUi().showSidebar(html);
}

and the HTML:

<!DOCTYPE html>
<html>
<head>
<base target="_top">
<?!= include('Stylesheet'); ?>
</head>
<body>
<div style='text-align:center;'>
<div style='margin-bottom:5px;'><button id='refresh'>Refresh</button></div>
<button style='margin-right:5px;' id='prev'>Prev</button><span id='point'></span><button id='next'>Next</button>
<div style='height:300px;'></div>
<button id='help'>Help</button>
<p id='helpBottom' style='display:none;'>
To jump to another row or language:<br>
Select a sheet and cell in a language column.<br>
Change bubble size in column D (optional).<br>
'Refresh' to update bubble with new text.<br>
<a href='http://www.simplehtmlguide.com/cheatsheet.php' target='_blank'>HTML basics</a>. </p>
<div id='htmlTextUsed' style='display:none;'>preview</div>
<div id='autotranslated' style='display:none;margin:5px;'>Auto-translated</div>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
<?!= include('Grumble'); ?>
<?!= include('Javascript'); ?>
</body>
</html>

The HTML template includes three files: "Stylesheet", "Grumble" (the jquery-grumble bubble library) and "Javascript" with the client-side code.

Standard libraries like jquery can be imported normally from their regular CDN, while other ones like Grumble is included in the apps script because I customized the official one to add navigation arrows and such. Its also a small library that is better to inline in the HTML.

Note: "include" is a small function defined in the library.

So you have the choice to include with <script> or to inline it by using <?!= include('code'); ?>. The latter is received client-side on the first page load so it could load faster in the client. Its faster than having to sequentially load it client-side like I did here with the jquery library. I chose to load jquery from the CDN because 1) the library is large and I don't want to stress apps script serving large html files. 2) the apps script code editor plays dead for a minute if you paste the minified library into the project (thou fine later if you dont view it again) and 3) I tried both, timed them and didn't find a noticeable difference.

I chose to use templates to better organize my code, but note that if you want the best performance (for publishing as an add-on for example) before publish you should minimize templating and manually inline everything in single .html and .gs files, with large client-side libraries pulled from a CDN (like its done here with jquery) and also inlining all apps script libraries used.

The only modification I had to do was in the bubble library. The CSS referenced an image but Apps Script does not provide a place to store image resources. There are a few options to do so in Google: You could put the image in a Google sites file cabinet which I did initially. However it made the code a little slower because Sites do not have image caching and the apps script was re-downloading the image for each bubble step and the image itself was served a little slow. Google Drive also has "Drive hosting" but that feature is deprecated and not super-fast.

Instead, I moved the image to a Google Cloud Platform storage bucket (no code needed, just upload from the web console but it does need billing activated and a few cents monthly). That reduced each bubble display time by about 1/2 second The image is referenced in the "Grumble" file. Its a tile with many bubbles of different size and colors.

The server-side

To make the client-side code interact with the sheet, it needs to call server-side javaScript functions (in .gs files) for things like changing cell selection or getting cell text (reading the spreadsheet).

The sidebar displays the selected cell in the bubble according to the language in each column where you put the selection, and the arrows (and “Prev” and “Next” buttons) move the Sheet selection up or down, then display the text from the selected Sheet cell inside the bubble. Each user has their own sidebar and selection, thus they can collaborate efficiently.

google.script.run.withSuccessHandler(onGetRow).withFailureHandler(onFailure).getRow(delta)

This says to call the server-side function “getRow” with parameter “delta”. In this case delta can be -1, 0 or 1 for the “prev”, “refresh” and “next” buttons). onGetRow and onFailure are client-side functions in the sidebar to handle success and failure of the server call.

If the cell in the translated column is empty, it displays an automatic translation of the English column to the given language so it can be used as a starting point or to get ideas. Translating was easy using built-in Apps Script functionality with a single function call:

var sheetConfig = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Instructions");
var lang = sheetConfig.getRange("F1").getValue();
var valEnglish = sheetData.getRange(row, 3).getValue();
var val = LanguageApp.translate(valEnglish, "en", lang);

Handling permissions to run the script

Getting a webapp to handle authentication and permission (with oauth2) is sometimes tedious and tricky. However, Google Apps Script makes it very easy and automates all the work for you. It scans your code and automatically displays approval dialogs for any new "scopes" your app is using.

The first time the volunteer clicks the menu item, a Google permissions dialog shows:

and asks for permission to view and manage spreadsheets "that this application has been installed in". That means just that spreadsheet and nothing else in your Google Drive.

The key to get that permission dialog to restrict the permission just to the single spreadsheet is by using a special Apps Script notation:

/**
* @OnlyCurrentDoc
*/

At the .gs file level, this special comment tells Apps Script that I want all permissions to apply only to the current spreadsheet. I use this at the top of my "DataAccess.gs" file. 

That's actually ALL the authentication code in this sample, a simple code comment annotation and nothing else. Oauth flow and saved tokens are all taken care for you. More details here.

Deploying the tool to volunteers

To find volunteer translators I posted a Google form to the extension's Google+ page. Within hours I had volunteers for five languages. It was very easy to deploy all languages: I had all the code in a master spreadsheet and just needed to make a copy for each language.

Before making the copies I moved as much of the code as possible into an apps script library. This allows for a single place for the code and simplifies making code changes later. All spreadsheet copies will use my latest code as I keep improving the sidebar webapp by just editing the single library.

I protected a few ranges in my master spreadsheet so only I can modify them, like the instructions sheet and English columns.

I also moved the "Instructions" page text into a separate "help template spreadsheet", and used the Google Spreadsheet function "ImportRange" to show that help text on my master template. The master's "Instructions" sheet contains this in cell A1:

=IMPORTRANGE("1z5afM6Wn-8RKsdUpTPicU0d81YCWX4bqzgKIna1uep0","Instructions!A1:E")

Try pasting this on any google sheet of yours. It copies the entire A:E column contents from the "help template" with that spreadsheet key starting with "1z5a" to my master spreadsheet (where the formula is). 
Note that Im using an "open range" that does not specify the last row. This tells sheets to copy until the last row in the source sheet. If I later add more rows, it will add them to all sheets that have the formula.

When I later make copies of my master that formula will remain, thus the copies will also pull their help text from my "help template spreadsheet". Goggle takes a few seconds to show the updated text as I modify cells in my "help template". Finally, I made one copy for each language, and on each copy I configured a single parameter in each sheet: the language code to be translated, kept in cell Instructions!F1.

How does the user finally get to see this custom sidebar? All they need is to click on the “Plus” menu that gets automatically added when they open the sheet:

Showing this custom menu is easy in apps script:

function onOpen(e) {
  SpreadsheetApp.getUi()
  .createMenu('Plus')
  .addItem('Tour preview', 'libraryplusfortrellotranslate.previewRow')
  .addToUi();
}

The special “onOpen” function adds menus to the sheet and specify the function to call for each item. In this case it runs “'libraryplusfortrellotranslate.previewRow”, a function  inside the library.

Monitoring progress real-time

So now I need to monitor progress. With fourteen languages, I don't want to open fourteen spreadsheets and manually check them all the time. I also want users to know the translation status without me doing much work.

This is easy with Google sheets. The master has a "counts" sheet that summarizes its progress by counting how many rows are translated and marked done per tour. I only need to consolidate all those "counts" sheets from all the language spreadsheets. This is done by using again the ImportRange function to pull the data for each spreadsheet.

But, how to concatenate all those fourteen importRanges into a single sheet?

Just putting the importRange formula right after each part wouldnt work because each part number of rows could change. Fortunately Google sheets have a way to concatenate results. A sheet cell formula in the form:

={IMPORTRANGE(...) ; IMPORTRANGE(...) ; IMPORTRANGE(...)}

will concatenate results downwards (or use "," instead of ";" to concatenate horizontally)

It builds my "database" in this sheet (make a copy to play, the cell formula is in base!A1).

I then "query" this database from another sheet tab so I can group it the way I want to see it:

=QUERY(base!A2:E, "select C, sum(D), sum(B), sum(E), sum(B)/sum(D), sum(E)/sum(D) where C<>'' group by C order by sum(B)/sum(D) desc label sum(B) 'Done', sum(D) 'Total', C 'Lang', sum(B)/sum(D) '% done',sum(E)/sum(D) '% filled',sum(E) 'filled'")

The query formula is a little long because I labeled many columns so it looks pretty on the resulting table. Users and myself can see progress and where is help needed. Cells are automatically calculated real-time by Google sheets and are colored with conditional-formatting which also happens real-time.

These are my real-time stats. It auto-updates as editors work on the many language spreadsheets. The query function is here in the black "Lang" cell which builds the table automatically:

This sheet updates right away when opened the normal way in Google Drive (as in clicking this link to the sheet). When embedded in a web page (like in this blog post) it uses a sheets "publishing" feature that takes a few minutes to update and needs refreshing the page to see changes. That does not happen when opening the sheet directly as editors do.

Sometimes (rarely) the spreadsheet shows #VALUE when changes are made to the source spreadsheets. This gets corrected once any viewer opens the stats spreadsheet directly (not as an IFRAME like in this blog post). However, it seems that issue has gone away after I changed the spreadsheet settings (in the File menu) so Recalculation is done "On change and every minute".


Next steps

Once all translations are done I will add more code to export the translated tour as a JSON file and incorporate the new translated tours into the next version. I don't need to write that code yet because I can add it later to the library while the editors work on the translations.

I improved the sidebar by also showing the HTML below the bubble. This helps in the auto-translate case so editors can copy+paste it as a starting point. With a little more time it can use a HTML editor library right inside the sidebar. I haven't gone that far because its not really needed for this project, the tour bubbles use very simple HTML like <b> or <br> and a few anchors.

Update: I've added extra functionality to let me review changes and export the tours to JavaScript format. Those changes are not in the public template but I can add them if requested.

To try this tool

1. Open an make a copy of this sheets template (from desktop)
2. In Instructions!F1 change the language code if you wish (defaults to Spanish).
3. Read that "Instructions" sheet tab. Basically is just clicking on the sheet "Plus" menu.

To play with the code open it from "Tools : Script editor”. There is little code there as most is in the included library. To use your own modified library:

1. Make a copy of the library.
2. From the script editor, open "Resources : Libraries". Remove the one there, and add yours. In short, you need to find your new library's "Project key" (in its File Properties) and add the library, using the name "libraryplusfortrellotranslate".

Using apps script for this project was an excellent experience. Its a great tool for quick integrations. You can even publish it as an Add-On on the marketplace so any Google user can also find and use it.

Enjoy!