Automated Facebook reporting with R and Google Spreadsheets

Imagine you want to do an automated reporting of the usage of a Facebook page (or multiple pages) and want the results to be displayed in a Google Spreadsheet. You can use two wonderful APIs in R to reach your goal easily with just a few lines of code and automate the whole process.


First of all let us get some data from a public Facebook page with the help of the awesome Rfacebook package. This package provides a series of functions that allow R users to access Facebook’s API to get information about users and posts, and collect public status updates that mention specific keywords. Before requesting data you have to go to the Facebook developer website, register as a developer and create a new app (which will then give you an ID and secret to use the API). See the reference manual of the package for detailed information about the authentication process.

# get packages

# set parameters
my_id <- "myAppID"
my_secret <- "myAppSecret"

# create fb dev account and do auth
my_oauth <- fbOAuth(app_id=my_id,app_secret=my_secret)

# get data from the facebook page with the ID 111492028881193
getpagedata <- getPage(111492028881193, token = my_oauth, n = 10) 

The getPage function will request information from a public Facebook page. In our case we are requesting the last ten posts of a page with the ID 111492028881193. The request will also include information on the date the post were created, the content of the post and metrics like likes_count and shares_count. To find the ID of a Facebook page you can use this helpful website. See the reference manual of the package to find a lot more functions to get data via the API.

Now having this data in a neat little data frame in R we want to write it automatically to a Google Spreadsheet. Here we can use the googlesheets package, which allows you to access and manage your Google spreadsheets directly from R. In our example we just going to create a new spreadsheet named “facebook_test” and load up our data from the Facebook API with just one line of code. Now you have an automated reporting from Facebook to Google spreadsheets with a little help of R. Make sure you also have a look at the reference manual of the googlesheets package, as it provides a lot of more possibilities to automate your reporting. The cool thing is that it is designed for the use with the %>% pipe operator and, to a lesser extent, the data-wrangling mentality of dplyr.

# get package

# create a spreadsheet and fill in the data
facebook_test <- gs_new("facebook_test", ws_title = "Data From Facebook API", input = getpagedata, trim = TRUE)

Go to my Github to see the code along with some other projects.


Author: inside data blog

data analysis & visualization blog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s