Create your own personalised translator in Google sheets

Have you ever thought about how you could combine a language lesson with mathematics and spreadsheets? The magic of Google Sheets allows us to be able to use certain functions to create our own language translating spreadsheet using Google translate. Follow these three steps to create your own personalised translator for you and your students.

Create your own personalised translator in #GoogleSheets, by following these three steps #usetechbetter Click To Tweet    

The other week I was running a workshop and demonstrating how to use google sheets and was demonstrating how to see what functions are available inside sheets. It was then that I saw there was a function called Google. There are a number of commands available within a spreadsheet that utilise some of the cool features of Google and one of them is Google Translate.

By combining the detectlanguage and googletranslate function you can build a spreadsheet that automatically identifies the language that you are writing in and converts it to another language. The detectlanguage function looks at what has been entered into a cell and identifies the language used. The googletranslate function will translate the text in a cell to a language you specify. This function works when you tell it what language the original text was written in. By combining the two functions, you can detect the original language and convert it to your desired language all in one step.

Whenever I combine two or more functions I tend to make sure they both live independently and then combine them. By doing this I can build up quite complex combined formulas ensuring that every single step works before writing one big long formula and having to find syntax errors. The next steps take you through how each function works before we combine them.

Step 1

In your spreadsheet enter a phrase from another language (in this example I am using French in row 2). Then in the next column use the detectlanguage function to find out what language it is written in. The syntax is =detectlanguage(A2). The result will be a two character code that Google uses to identify the language.

Step 2

Use this code and the googletranslate function to convert your phrase into another language, in this example, I am converting it to English. The syntax is =GOOGLETRANSLATE(A2, “fr”,”en”). Enter the cell that has the text in it, the language you have just detected and the language you want it converted to.


Step 3

Now that we know the formulas work, we can delete the working columns’, combine the functions by removing the “fr” in the googletranslate function and replace it with the detectlanguage function. It should look like this =googletranslate(A2, DETECTLANGUAGE(A2), “en”). This will allow you to enter text in any language in the first column and have it converted to English.

If you want to add another layer of coolness to the sheet add a vlookup function to pick what language you want the phrase converted to. This combines a vlookup table, a named range and data validation. Have a look and give it a crack.

Grab your copy of the sheet here to see how all the magic works.

Check this blog to see how to combine several functions in #googlesheets to translate phrases from any language into the language of your choice. Click To Tweet    

This is a great way of combining a couple of subjects that we don’t think go together by using the Google Translate function within Google Sheets. Check out this blog post on data validation and conditional formatting to help you master sheets and to learn more tips and tricks join our online professional learning community.

Letter merge email

How to mail merge letters in Google Docs

In a recent post, we shared the top five add-ons you can use to perform a mail merge in Google Drive. In this post, we’ll show you how to use one of those tools, autoCrat, to perform a mail merge in Google Docs that creates and emails personalised letters. This same technique can also be used to personalise any other type of document that is based in Google Docs.

VIEW POST

How to help junior students login to their computers

If you’ve ever used Chrome books with students under the age of 7 then you’ll instantly know the pain and stress that I’m about to describe. I was working with a classroom of 25 Year 1 (6 year olds) students who had never logged in with their accounts before, let alone be able to find the @ key on the keyboard.

VIEW POST

Top Tip for Google Classroom with Lara

I’m going to show you a Google classroom tip. In this media management classroom that I in, this is actually a student view so this is Clumsy Smurf here and this is an assignment that Clumsy has been allocated by his teacher.

VIEW POST

Here’s What
Our Clients Say

PR Industrya

Client

In 2018, Using Technology Better designed and delivered a two phase post-migration training program for this New Zealand based graphic design firm. The initial goal of the training program was to reduce frustration with G Suite, with the long term aim of facilitating a change in culture and collaboration that can lead to transformative practices

Download your skills checklist to see how you score...

 

Sign Up Now
To Get The Latest Updates

 

Holiday Rescue Maker Camp

Student to agree to and tick:
Guardian to agree to and tick: