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.
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.
In our sheet we have two columns generated by Forms; a timestamp showing when the user submitted their answer and the answer itself.
To translate the submitted sentence, we will use two formulas.
The first is =DETECTLANGUAGE(B2)
This identifies what language the sentence is written in. It outputs a two letter Language code.
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”)
Adding this formula translates the responses to English.
One drawback is that we need to manually drag the formula down the column to translate new responses.
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!