Tag: Google Docs

Google Docs recently got a pretty exciting feature – integration with Google Translate.  But as exciting as it is, if you combine the new functionality with some bits of the previously available functionality, you can get truly mind-blowing results.

Consider an example.  You have a feedback form on your web site.  You fanatically collect responses and study them to make your web site better.  The problem however is that some of the questions that you ask in your feedback form are open-ended.  Meaning that people can write whatever they want in there.  And more often than you would like to,  people fill those fields in their native language.  Which might be very different from anything that you can understand.  This forces you to guess which language was used for each response, and then translate them one by one.  Needless to say, that takes a lot of time and effort.

One of the solutions to this problem can be achieved with Google Docs.  For some time now, Google Docs had Form functionality, where you could built pretty much any form you needed, and then easily integrate it with your web site.

If you don’t know how, go to Google Docs and select Form from the Create New menu.  Using a very user-friendly wizard build the form.  When you are done, open Form’s More Actions menu and select Embed.  This will give you a pop-up window with a little HTML code snippet.  Copy this code and paste it into your web site.

Whenever someone submits your new feedback form, the results will automatically go into a special spreadsheet in your Google Docs account.  You can see this spreadsheet by navigating to See responses menu in your Form editing screen and selecting Spreadsheet.

All you need to do now is add two columns for each form field that you want to translate in this spreadsheet (I tried a single-column solution, but for some reason it didn’t work for me).  One will keep the auto-detected language of the form field submission, and another will keep the translation of the submitted field to the language that you understand.  Here is how you do it.

First, fill out and submit the feedback form yourself.  By doing so, you’ll make sure that the form is correct, all fields make sense, the HTML code is right, and that you are able to see the responses.  You’ll also have some sample data in your spreadsheet which will make your life easier.

Secondly, next to the column with the field value in the foreign language write a formula to guess the language.  If your field value is in cell B2, add =DetectLanguage(B2) to cell C2 and =GoogleTranslate(B2, C2, “en”) to cell D2.  Now, if you get some Russian text in B2, cell C2 wil lshow “ru” and cell D2 will show the English translation of the Russian text.

The only minor issue with resulting spreadsheet is that when  you get more submissions of your feedback form, language detection and translations are not done automatically.  But since we used the formulas in the cells, all we need to do to get those new submissions translated is drag and drop the formulas down to the new table rows.

While this is not exactly perfect, it is still a substantial improvement to the manual process used earlier.

P.S.: And so just that you know, it is entirely possible and in fact very easy to publish the spreadsheet back at your web site (for example, in password-protected area for your site administrators to see).  Every time the spreadsheet will be updated, the changes will be automatically reflected on your site as well.