Automate publishing to Google Slides with R

Written by Hairizuan Noorazman, Data Engineer at Sparkline.

Every month, analysts at Sparkline create monthly reports for various clients across different industries. It involves manipulating a Google Slides template and updating details, data, and charts obtained via R and Google Analytics for the current reporting period. It may not be a problem if you only have 2 slides, but imagine if you have a huge deck which presents monthly reports on multiple metrics & goals. It ends up becoming a time-consuming task to update all that information on every slide every single time.

image17
Example of a slide with a month & year in the heading.

 

image14
Example of a slide with a month & year in the heading and a list in the content area

image06

Last year Google announced the release of the Google Slides API. This was one of the most awaited (and most probably the most requested) API release. It was exciting news for us, one that was long awaited and one which would allow us to automate our work. This would allow Sparkline’s and our clients’ analysts to focus on actioning insights rather than spending time on data extraction and presentation.

Publish to Google Slides from an R script

image03

With the new Google Slides API anyone who wants to automate the process of making presentation slides can now create them using R scripts. This removes the last barrier when it comes to presenting data. If you have existing R scripts, you can add a few more lines of code to it and publish those results straight to your Google Slides presentation.

Unfortunately,  the official Google API SDK do not support R a popular scripting language used by professionals in the big data industry.

So during the year end holidays, I had some free time and put together a small R package that integrates with Google Slides API. We made it available as an open source project at Github.

image15

https://github.com/sparklineanalytics/rgoogleslides

Quick Example: Replacing text in slides

Let’s quickly go through a simple example.

We will automate the update for the monthly report slides, with the month and year with the current date.

First, let’s alter the slides and make it into a template. Alter the June 2016 to {month-year} on each slide.

image18

 

image13

We can now use the Google Slides R package to update the slides.

Get the Slide ID from the template’s url:

The bolded portion is where the Slide ID is.

https://docs.google.com/presentation/d/1EtDqjWDXXXXXBYVdAJo/edit#slide=id.g1af69dd764_0_63

 

image21

Next, download and install the Googleslides R package by following these instructions:

install.packages("devtools")
library(devtools)
devtools::install_github("sparklineanalytics/googleslides", build_vignettes = TRUE)

Then, run the following in RStudio or your R IDE of choice:

# Get the current month and date
library(lubridate)
Month <- as.character(month(today(), label=TRUE))
Year <- as.character(year(today())
authorize()
replace_all_text("1EtDqjWDXXXXXBYVdAJo", paste(Month, Year), "{month-year}")

Congratulations! You have done up the initial R script on manipulating Google Slides using R.

The following method of doing things is inspired from the following tutorial in the Google Slides API documentation: https://developers.google.com/slides/how-tos/merge#example

But rather than stopping here, let’s go one step further, is it possible to send a dataframe (ie R’s representation of a data table) into the Google Slides presentation?

Let’s begin with an empty slide with a replaceable title.

image19

Let’s have the slides present a Channel Analysis. The data for such a slide can be obtained from Google Analytics via the RGA package. By doing some quick data manipulation such as sorting and getting the top 10 pages by sessions, we can then push and present the data into the slides.

One thing to note, as the Googleslides package is quite heavily influenced by the RGA package, when attempting to use authorize, you need to ensure that you are calling the right authorize methods. See the commented section of the code on this below.

library(googleslides)
library(RGA)
library(dplyr)

# Both RGA and googleslides have the authorize function
# In order to differentiate the functions of both packages
RGA::authorize()
googleslides::authorize()

# Getting data from Google Analytics
# Get top 10 records for the source medium records
profile_id <- "8XXXXXXX6"
data <- get_ga(profile_id, start.date = "2016-12-01", end.date = "2016-12-31",
 metrics = c("ga:sessions", "ga:bounceRate"), dimensions = c("ga:source", "ga:medium"))
data <- arrange(data, desc(sessions))
data <- data[1:10, ]

On each page of each slide, the ID of that page is appended at the end of the URL. After the slide=id section of the URL, that would the Slide Page ID.

image16

# Editing the slides
slide_id <- "1aXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX1-0"
slide_page_id <- "g1XXXXXXXXXXXXXXXX66"

# Replace the title
replace_all_text(slide_id, "Channel Analysis", "{analysis title}", TRUE)
slide_page <- page_element_property(slide_page_id)
create_data_table(id = slide_id, pageElementProperty = slide_page, 
 data = data, headers = TRUE)

After running the above R code, we get this:

image08

The package is still under heavy development and more features are being added to make it more impactful. So, if you find any bugs or if you have feature requests, create an issue ticket at the Github repository. If you have any more questions, get in touch with us. We would love to hear your feedback to this new R package.

 

Did you like it?

Let us know so we can improve

+6 Vote DownVote Up