Getting Google Docs data into R

This was a tortuous path to success … seems like Google has changed the way GDocs are accessed several times over the past couple of years, including https by default which meant that normal read.csv(url) functions didn’t work.

To get the mobile internet spreadsheet I wrote up for TZCRC:

    • Opened the spreadsheet in Google Docs, then selected File|Publish to web and selected CSV to get the link (below).
    • Used the link with RCurl and read.csv in the code below:
require(RCurl)
u <- "https://docs.google.com/spreadsheet/pub?hl=en_GB&hl=en_GB&key=0AmFzIcfgCzGFdHQ0eEU0MWZWV200RjgtTXVMY1NoQVE&single=true&gid=4&output=csv"
tc <- getURL(u, ssl.verifypeer=FALSE)
net <- read.csv(textConnection(tc))

Giving the data frame:

Provider Bundle MB TSH TSH/MB Validity
Airtel Daily Bundle 20 500 25.0 1
Airtel Handset Browsing Bundle 400 2,500 6.3 30
Airtel 1 Day bundle 300 3,000 10.0 1
Airtel Weekly bundle 3,072 15,000 4.9 7
Airtel Monthly bundle 8,000 70,000 8.8 30
Airtel Quarterly Bundle 24,000 200,000 8.3 90
Airtel Yearly Bundle 96,000 750,000 7.8 365
Tigo Light 200 3,000 15.0 1
Tigo Light 500 7,500 15.0 7

… etc.