Poorman’s automated translation with R and Google Sheets using {googlesheets4}

A little trick I thought about this week; using Google Sheets, which includes a “googletranslate()” function to translate a survey that we’re preparing at work, from French to English, and using R of course. You’ll need a Google account for this. Also, keep in mind that you’ll be sending the text you want to translate to Google, so don’t go sending out anything sensitive.

First, let’s load the needed packages:

library(googlesheets4)
library(dplyr)
library(tibble)

As an example, I’ll be defining a tibble with one column, and two rows. Each cell contains a sentence in French from the best show in the entire French speaking world, Kaamelott:

my_french_tibble <- tribble(~french,
                  "J'apprécie les fruits au sirop",
                  "C'est pas faux")

To this tibble, I’m now adding two more columns, that contain the following string: “=googletranslate(A:A,”fr“,”en“)”. This is exactly what you would write in the formula bar in Sheets. Then, we need to convert that to an actual Google Sheets formula using gs4_formula():

(
my_french_tibble <- my_french_tibble %>%
  mutate(english = '=googletranslate(A:A, "fr", "en")') %>%
  mutate(portuguese = '=googletranslate(A:A, "fr", "pt")') %>%
  mutate(english = gs4_formula(english),
         portuguese = gs4_formula(portuguese))
)
## Warning: `...` is not empty.
## 
## We detected these problematic arguments:
## * `needs_dots`
## 
## These dots only exist to allow future extensions and should be empty.
## Did you misspecify an argument?
## # A tibble: 2 x 3
##   french     english                           portuguese                       
##   <chr>      <fmla>                            <fmla>                           
## 1 J'appréci… =googletranslate(A:A, "fr", "en") =googletranslate(A:A, "fr", "pt")
## 2 C'est pas… =googletranslate(A:A, "fr", "en") =googletranslate(A:A, "fr", "pt")

We’re ready to send this to Google Sheets. As soon as the sheet gets uploaded, the formulas will be evaluated, yielding translations in both English and Portuguese.

To upload the tibble to sheets, run the following:

french_sheet <- gs4_create("repliques_kaamelott",
                           sheets = list(perceval = my_french_tibble))

You’ll be asked if you want to cache your credentials so that you don’t need to re-authenticate between R sessions:

Your browser will the open a tab asking you to login to Google:

At this point, you might get a notification on your phone, alerting you that there was a login to your account:

If you go on your Google Sheets account, this is what you’ll see:

And if you open the sheet:

Pretty nice, no? You can of course download the workbook, or better yet, never leave your R session at all and simply get back the workbook using either the {googledrive} package, which simply needs the name of the workbook ({googledrive} also needs authentication):

(
translations <- googledrive::drive_get("repliques_kaamelott") %>%
  read_sheet
)

You’ll get a new data frame with the translation:

Reading from "repliques_kaamelott"
Range "perceval"
# A tibble: 2 x 3
  french                    english                     portuguese              
  <chr>                     <chr>                       <chr>                   
1 J'apprécie les fruits au… I appreciate the fruits in… I apreciar os frutos em…
2 C'est pas faux            It is not false             Não é falsa             

Or you can use the link to the sheet (which does not require to re-authenticate at this point):

translations <- read_sheet("the_link_goes_here", "perceval")

You could of course encapsulate all these steps into a function and have any text translated very easily! Just be careful not to send out any confidential information out…

Hope you enjoyed! If you found this blog post useful, you might want to follow me on twitter for blog post updates and buy me an espresso or paypal.me, or buy my ebook on Leanpub. You can also watch my videos on youtube. So much content for you to consoom!