3

I'm trying to create a shiny app that uses information that's available externally in a google spreadsheet, which I'm downloading to a temporary .csv file. The app works correctly on my local machine, but when I try to push it to shinyapps.io the app fails. I've attempted to recreate the error below using a simply example. The shiny app can be found here: https://n8sty.shinyapps.io/test/. I've copied the server.R and ui.R files that work locally below.

I believe the issue is that the server doesn't handle downloading the external google sheet file in the same manner as my local machine. Does anyone know how to use google docs in a shiny app?

ui.r

require(shiny)

shinyUI(fluidPage(

  mainPanel(
    tableOutput("table")
    )

  ))

server.r

require(shiny)

td <- tempdir()
tf <- tempfile(pattern = 'test', tmpdir = td, fileext = '.csv')
url <- 'https://docs.google.com/spreadsheets/d/1c1ZI0P0ydunm-wc1eb3-jYbWfwMZQc4eAyWlHJut2wY/export?usp=sharing&format=csv'
download.file(url, tf, method = 'auto', quiet = TRUE)
df <- read.csv(tf)

shinyServer(function(input, output) {

  output$table <- renderTable({
    df
    })

})

Thanks

n8sty
  • 1,418
  • 1
  • 14
  • 26
  • 1
    This seems relevant: http://stackoverflow.com/questions/25685712/connection-to-google-spreadsheets-not-opening-in-shiny-server – MrFlick Sep 12 '14 at 21:35
  • @MrFlick seems very relevant. Thanks for the pointer. If someone wants to beat me to the answer feel free. If not, I'll post it when I have a chance to go through it tomorrow. – n8sty Sep 12 '14 at 22:29

1 Answers1

1

After much searching around through SO and the rest of the wide world of the web here's what I came up with that works for now. A quick caveat, it looks like that as packages in development change and as google changes the format of its docs, code that worked a few months ago to read google sheets no longer works.

I defined the following function with inspiration from the fetchGoogle(...) function from the developmental version of the mosaic package.

loadGoogSheet <- function(URL, key = NULL,
                          stringsAsFactors = default.stringsAsFactors(), na.strings = "NA",
                          colClasses = NA, blank.lines.skip = TRUE
                          ) 
{

  #.try_require("RCurl")
  if (missing(URL) & !is.null(key)) 
    URL = paste("https://docs.google.com/spreadsheet/pub?key=", 
                key, "&single=TRUE&gid=0", "&output=csv", sep = "")
  s = RCurl::getURLContent(URL)
  foo = textConnection(s)
  b = read.csv(foo,
               stringsAsFactors = stringsAsFactors,
               na.strings = na.strings,
               colClasses = colClasses,
               blank.lines.skip = blank.lines.skip
  )
  close(foo)
  return(b)

}

Depending on how your machine is handling certs you might need to include this piece of code BEFORE the above: 

require(RCurl)
options(RCurlOptions = list(cainfo = system.file("CurlSSL", "cacert.pem", package = "RCurl")))
n8sty
  • 1,418
  • 1
  • 14
  • 26