How to Translate Survey Responses using Google Sheets

By 26th Apr 2019Blog, Google Sheets, Surveys
How to Translate Survey Responses using Google Sheets

In this blog post I’ll show you how to translate survey responses using Google Sheets!

Let’s assume you have a survey running in which the responses may be in multiple languages. What’s the easiest way to quickly and simply translate them to a language you’re more comfortable with?

In this post I’ll show you how you can use some Google Sheets formulas to translate survey responses.

Below is our survey, in this example a Google Form. Please fill in your own answer! You’ll see there are no other questions, just the option to submit a sentence.

And below are all the responses displayed in Google Data studio. You’ll notice that the language of the response and the translated text are also displayed.

I’ll go through the steps of setting this up.

How to Translate Survey Responses using Google Sheets

The first step is to create a Google Form. Then you need to create a spreadsheet to collect responses.

We have some responses to our Google Form, but they are in several languages. To translate them we must open up Google Sheets.

Google form responses in different languages.

In our sheet we have two columns generated by Forms; a timestamp showing when the user submitted their answer and the answer itself.

How to Translate Survey Responses using Google Sheets

To translate the submitted sentence, we will use two formulas.

The first is =DETECTLANGUAGE(B2)

Google Detect language formulas.

This identifies what language the sentence is written in. It outputs a two letter Language code.

Google Detect language formulas.

The next step is to translate our sentences into a language we are comfortable with. In this case it’s English.

To do that we use the formula =GOOGLETRANSLATE(B2,C2,”en”)

Google Translate formulas.

Adding this formula translates the responses to English.

Translating survey responses using Google Sheets

One drawback is that we need to manually drag the formula down the column to translate new responses.

How to Translate Survey Responses using Google Sheets
How to Translate Survey Responses using Google Sheets

I hope this post was useful for you, if you need a way to translate survey responses between languages.

If you liked this article you might like my other blog posts.

Or you can follow me on Twitter!

Michael

Author Michael

More posts by Michael

Leave a Reply