Using Google Translate in Your Google Spreadsheets

translation

Google Translate works very well together with Spreadsheets to turn whatever language you don’t read into your own (or English of course). Once you’re acquainted with the functions used, you’ll quickly be able to modify your original text into whichever language you require. With a bit of clever work, you could automate processes, by connecting your sheet with If This Then That (IFTTT). Below I’m providing two examples of applications I’ve found useful.

But first, we’ll review the formulas.

The formulas

Google spreadsheets has two formulas to help you both translate and identify the language of text within a column.

=GOOGLETRANSLATE(text, [source_language],[target_language])

You can also set the [source_language] to auto-detect by using “auto” instead of a source language code, like this:

=GOOGLETRANSLATE(text, “auto”,”en”) to translate these anchor texts into English.

You don’t need to set the target language, as it will default to the language used in the spreadsheet.

The second formula can help you filter by language:

=DETECTLANGUAGE(text_or_range)

Full list of 2 letter ISO language codes on Wikipedia.

Translating backlink anchor text

In my consulting work, my team and I often come across backlinks in a range of languages and alphabets. Of course, this makes it difficult to evaluate backlink profiles: Is that anchor text a Brand, Compound, Money or Other term in our classification? Rather than just shrug our shoulders and chuck all of these incomprehensible text snippets in either Money or Other, I decided that using Google Spreadsheets to translate the lot would be more helpful.

To ensure that I got a broad selection of non-English anchor texts, I pulled the backlinks for Aliexpress.com. As they’re in mostly Chinese, it came in handy for the example. Removing the unnecessary columns, we are left with this:

Aliexpress backlinks
Backlink examples for Aliexpress.com

By using the formula =GOOGLETRANSLATE(D8, “auto”, “en”) in the appropriate columns, we’ll end up with a translated text.

GoogleTranslate formula Aliexpress
Translating with auto-detect to English

Copying the formula down the sheet, and waiting a few moments, we end up with results. I also translated the link source page titles to further illustrate how useful these functions are:

Translated Aliexpress results
The anchor texts and page titles translated

In our work, we would now be easily able to classify the anchor texts in the right groupings.

Auto-translating Google Reader replacement

While Google Reader is no more, Spreadsheets can use the ImportFeed formula to import RSS or Atom feeds.

=ImportFeed(URL, [feedQuery | itemQuery], [headers], [numItems]). Formula arguments are the following:

URL is the url of the RSS or ATOM feed.

feedQuery/itemQuery is one of the following query strings: “feed”, “feed title”, “feed author”, “feed description”, “feed url”, “items”, “items author”, “items title”, “items summary”, “items url”, or “items created”. The feed queries return feed properties; the feed’s title, the feed’s author, etc. If you want the feed data, do an “items” request.

  1. the “feed” query returns a single row with all of the feed information
  2. the “feed ” query returns a single cell with the requested feed information
  3. the “items” query returns a full table, with all of the item information about each item in the feed
  4. the “items ” query returns a single column with the requested information about each item
  5. using a “feed” query, the numItems parameter isn’t necessary and is replaced by the option headers param
  6. with an “items” query, the numItems parameter is expected as the third parameter, and headers as the fourth
  7. headers – “true” if column headers is desired. This will add an extra row to the top of the output labeling each column of the output

Building the spreadsheet

I decided to grab content from Spin Sucks for this example:

ImportFeed formula example
Pulling in the Spin Sucks RSS feed
ImportFeed Results
The ImportFeed formula output

Now for translating the contents of the feed. I picked Swedish (my birth language) by using the formula =GOOGLETRANSLATE(E4, “auto”, “sv”)

GoogleTranslate to Swedish
Formula to translate source into Swedish

And the results are predictably poor but understandable Swedish:

GoogleTranslate Swedish Results
Translating feed text into Swedish

The above is of course a very basic implementation of the formulas, but gives you a starting point to develop from.

Other useful import queries

IMPORTXML: Imports data from any of various structured data types including XML, HTML, CSV, TSV, and RSS and ATOM XML feeds.

IMPORTRANGE: Imports a range of cells from a specified spreadsheet.

IMPORTHTML: Imports data from a table or list within an HTML page.

IMPORTDATA: Imports data at a given url in .csv (comma-separated value) or .tsv (tab-separated value) format.

BrightonSEO September 2013 Presentation Slides

I hope everyone enjoyed BrightonSEO yesterday. Tracking down the slides and videos from conference presentations is always a time-consuming challenge, so I’ve done the hard work for you.

Brighton SEO Logo

If you know of any slides or Twitter handles I’m missing to date, please get in touch on @JackNorell or via email at hello@jacknorell.com.

If you’re the presenter of any of the below, and you’d like to add a short summary of your presentation, add a link to your blog post about it or to your website, please get in touch as above.

Session 1

Social track

Real-Time Marketing for any Brand

Oliver Snoddy Slides @olisnoddy Website

Google+ for Brands

Adriano Accardo Slides @AccardoAdriano Website

The Ins & Outs of Testing Social

Covers many tools and ways to test your social media efforts.

Jennifer Sable Lopez Slides @Jennita jensablelopez.com

International track

International Link Curation

What are the key things in creating links internationally?

Kevin Gibbons Slides @kevgibbo BlueGlass UK

International Social & Link Building

Alessandro’ presentation on “International and Social Link Building” focussed on the untapped potential of international social media markets for UK companies. It also featured Batman, Robin and Bane – what more could you want from a Brighton SEO presentation?

Alessandro Brunelli Slides @Ale_Brunelli Caliberi.com

The Other Search Engines

Since Google has the biggest market share in Europe, we tend to only watch their developments. Don’t forget we have other search engines too: how are Blekko, Yandex, Baidu, Sogou and other “foreign” engines developing?

Jan-Willem Bobbink Slides @jbobbink notprovided.eu

Networks track

How Do You Trust People and Pages You’ve Never Seen Before

Dixon Jones Slides @Dixon_Jones DixonJones.com

Making Sense of Lots of Data

Peter A. Passaro Slides @NousExMachina Website

Social Signal Processing. An Introduction.

Introduction to Social Signal Processing, the computing domain aimed at modelling analysis and synthesis of nonverbal communication in human-human and human-machine interactions.

Alessandro Vinciarelli Slides / Keynotes @alevincia SSPNet.eu

Session 2

Creative track

Video Hacks

Phil takes us through tips and tricks on making video work at it’s best for SEO.

Phil Nottingham Slides @PhilNottingham Distilled.net

The Rules of ‘The Game’: 6 Tips for Successful Outreach

Danny Ashton Slides @DannyAshton NeoMam.com

Actionable Content Marketing and Strategy

Tony Samios took to the main stage with his presentation on “Actionable Content Marketing and Strategy”, which mixed in-depth, actionable advice with the odd X-Factor audition video. “Ant and Deaf” – quality!

Tony Samios Slides Twitter Caliberi.com

Linkbuilding That "Seemed Like a Good Idea at the Time"

Paul Madden Slides @PaulDavidMadden PaulMadden.co.uk

LoMo track

Mobile Strategy for Small Businesses

Bridget Randolph Slides @BridgetRandolph BridgetRandolph.com

Design for Mobile… Responsive or Adaptive

Justin Taylor Slides @JustinGraphitas Graphitas.co.uk

Managing Local Listings

Local Search requires consistent branch level information listed in multiple local listing sites to work effectively. David Whatley from MiShop.local discusses how brands can use their network to significantly extend their local search footprint. He also talks about the “NAP Anchor” – where consistent Name Address and Phone Number together provide the unique identifier for local search.

David Whatley Slides @MIShopLocal MiShopLocal.co.uk

Effective Adwords Tactics for Local Businesses

AdWords have come under fire for becoming too expensive for small and local businesses. These advanced PPC tactics help you become competitive again.

Tara West Slides @Koozai_Tara Koozai.com

Technical track

Crawling the Internet for Fun and Profit

Dom Hodgson Slides @Thehodge thehodge.co.uk

How to Crush the Competition by Watching the SERPs

Rob Bucci Slides @STATrob Getstat.com

In-Core Mining of Large Networks

Sebastiano Vigna Slides Twitter Website

A Crash Course in Natural Language Processing

Oliver Mason Slides @ojmason Phrasys.net

Session 3

Onsite track

The Keyword is Dead: Long Live the Keyword

Stefan Hull Slides Twitter propellernet.co.uk

Next Gen Measurement in Google Analytics

Dara’s presentation provides a look into the new ‘Universal Analytics’ and other new features of Google Analytics, with some ideas on how to make the most of what they have on offer.

Dara Fitzgerald Slides @darafitzgerald Fresh Egg

On Page Content Marketing

Lisa Myers Slides @LisaDMyers vervesearch.com

Ecommerce SEO – Selling Does Not Make You Link Worthy

Tim Grice Slides @Tim_Grice SEOWizz.net

Links track

Make Your PR Idea a National SEO Success

Keith White Slides @Keith_Marketing TheEventaGroup.co.uk

Data and Content Production

Alan Cairns Slides @lancairns jellyfish.co.uk

Low Cost Link Building with Juicy, Juicy Data

Stacey Cavanagh Summary & slides @staceycav blogsession.co.uk

The Inhouse and Agency SEO Should be Friends

If you have a client with an experienced Search Manager in place you should be thanking your lucky stars. The inhouse SEO can be your best asset, so don’t make them your worst enemy. PLUS, the hidden dangers of having a client with no in-house expertise.

Max Brockbank Slides @maxormark maxbrockbank.com

Hacks track

Increasing Prices without Losing Sales

Acquiring customers is increasingly difficult. 98% of site visitors won’t buy and our marketing is incredibly ineffective. This has been bugging marketers for decades. Justin Deaville shows how the lessons that direct mail marketers learnt over decades can rapidly boost your conversion rates through testing.

Justin Deaville Slides @justindeaville Receptional.com

More Offline Leads from Online Traffic

We all spend vast sums of money on digital marketing activities that drive traffic to our websites. What can we do to get the right type of traffic, and increase our conversion rate, leads and sales?

Ali White Slides @AlistairWhite Calltracks.com

The Magic of APIs

Matt Beswick Writeup & slides @mattbeswick Mattbeswick.co.uk

Facebook Ad Optimisation

Stephen Croome Slides Twitter Firstconversion.com

Session 4 – Lightning Talks

How We Plan Editorial at BBC Sport

Paul Plunkett Slides @paulplunkett66 BBC.co.uk/sport

Tech Holes in Your Reporting

Katrina Gallagher Slides @Digitangle Digitangle.co.uk

Schema & You: Making Microdata Sexy

Sam Harries Slides @PurposeGaming SiteVisibility.co.uk

How Five Ski Companies Built Links with Great Content Marketing

Iain Martin Slides @skipedia Skipedia.co.uk

7 Minute SEO

James Ray Slides Twitter Forward3D.com

Multi-channel Technical SEO

Director of Strategy Mike Briggs was on hand to present a seven-minute “lightning” on “Multichannel Technical SEO”, which he described as “Website Structure for Humans, Robots & Profit”.

Michael Briggs Slides @michaelkebriggs mercurythread.co.uk

Breaking Down the Silos

Matt Roberts Slides @linkdex_matt Linkdex.com

Still hungry for more BrightonSEO content? Tony Dimmock put up a round up with links to all the presentations and writeups.