Tools

Rapid Bulk Translation in Excel with the Google Translate API

by on 5th February 2019

I often come across the need to create a large data set from websites that don’t use English language content.

Most often, the translate-at-some-kind-of-scale-cheaply challenge occurs when I’m building product feeds, adding new products to one of my retail sites or rewriting product copy at some sort of scale (genuinely, a favourite task of mine).

Translation is hard. That’s because most often you need something translating in a subject that you understand very well, but a translation agency does not. Translation can be expensive, slow and relationships need vetting and cultivating to become effective.

Time and time again, I’ve found it’s more efficient to just do the work yourself. Today I want to demonstrate how easy it is to:

  1. Programmatically translate content, and
  2. Rewrite the (bad) English that comes out

Some of this work is time intensive, but I think up to a medium scale (in the thousands of items) one person can do this work over the period of an intense few days.

My demonstration uses MS Excel and the SEO Tools for Excel Google Translate Connector. You could have a developer write a script to execute a translation to CSV, but I suspect in the end Excel is where you’ll inevitably end if you’re rewriting product data as I do in this demo.

Get a Google API Key

You’ll need a Google API key. Sign in with your Google Account, create a project, enable the Translate API and then copy your new API key ready to add to SEO Tools for Excel.

To get started, head to the Google API console and create a project:

After the project has saved (this takes 30 seconds or so) you can select your new project.

After you’ve selected your new project, enable the translate API by finding it in the API Library.

You’ll get a select credentials dialogue. Ignore all of this and just click the “API Key” link.

Your API key is now generated. Copy it and get ready to move to SEO Tools for Excel.

Add your Google API Key to SEO Tools for Excel

The Google Translate feature in SEO Tools for Excel can be found here: Others > Language > Google Translate.

Just click Translate:

Now insert your API key using the settings link in the left hand dialogue and you’re ready to go:

Execute a translation

Before you go ahead and start using the translator, I think it’s good to adjust your http settings to avoid hitting any request limits on large datasets. I’ve accidentally DDOS’d sites with scrapers in SEO Tools because by default, it can send a lot of requests at the same time!

Play nice by adding a random ms delay between requests via Settings > Global HTTP Settings.

Now you’re ready to go. Add a column next to your target text for translation, and paste in this formula:

=Connector("GoogleTranslate.Translate",AZ3,"en",,"Result,SourceLanguage",TRUE)

The translation will now run. Here’s one I ran earlier. It’s surprisingly fast and, as I keep telling people, SEO Tools for Excel is so well made these days that you can go to another tab and keep working while this is running:

For more on the syntax of the query, read the official support from SEO Tools for Excel here.

And finally, the rewrite

Obviously the translated output isn’t beautiful, but it takes almost no time to rewrite the outcome nicely:

This output:

“Allows the driver complete freedom of movement of the head for an unrestricted side view. Retrofit of any FHR system possible. The length 18 ” is installed by default on all Medium and Large models. For helmets shorter straps can be used because the clips are located farther back of the helmet. Kit for upgrading a FHR system to the moveable tether system includes mounting brackets, washers with washers, tether, and complete instructions.”

I can immediately edit to make more readable, user friendly and turn it into better product copy more easily than (in my opinion) a translator who might be unfamiliar with my business could.

My argument is obviously in favour of machine translation with human editing. While it wouldn’t be an appropriate solution to say, translating legal text or literature, machine translation definitely has it’s uses.

In any case, this method is probably a time saver for a lot of our readers. You might not agree, of course so your thoughts are extremely welcome!

Responses

  1. Richard, you represent a marketing company who understands how things work on the internet. Your articles contain excellent IT tips. So, you have a bias, like we all do. To you, digital manipulation is easy.

    In this article, you ‘save’ time by replacing a Language Service Provider relationship with an API process which, in most companies, only geeks would be able to do. You downplay translation expertise whilst offering technology expertise in its place that an average Jane would not know how to implement.

    You live on the internet and from it. To you, what’s available there is fair game to be crawled, scraped, downloaded, indexed and tracked. Your proposed translation process boils down to using a public MT engine owned by a data giant who is aiming at world dominion. Professional translators work with MT pretty much the same way you have done here, except with a bit more data protection. Thus, it’s the process of post-editing MT output and the qualifications of post-editors that must be the focus of your proposal: why use translators for it if a subject-expert in the content owner company can do the job.

    The language industry has already acknowledged that there are uses for MT that bypass professional linguists completely. There are cases where LSPs and language technology companies provide only the technology and leave the client to deal with the output.

    But like you said, the uses of that are limited. You can only trust the output if you are able to understand the source language to some extent. The risks of mistranslation – both in the engine quality and in the post-editor’s skills – are multiplied when English is not either the source or the target language.

    “Translation is slow and expensive”. I’m sure that for some laymen so is SEO consultancy and digital marketing. How do you measure the value of any professional service, how do you compare its value to something that is ‘good enough’ if I do it myself? If you and I worked together on a multilingual digital marketing campaign where you designed it brilliantly and our team localised it well into five languages, which portion of the campaign’s success could you claim, and which would belong to me?

Comments are closed.

We're hiring – check out our careers page Careers

Get insights straight to your inbox

Stay one step ahead of the competition with our monthly Inner Circle email full of resources, industry developments and opinions from around the web.