Is there a way to load several excel files from a dropbox folder into an R-shiny app?

Question

I am still relatively new at working in R shiny and I am trying to load several excel files into an R-shiny app. Part of the problem is that I need to be able to pull several files from a dropbox folder without specifying what the data file is called. So I need to be able to tell R to read in all the files from a dropbox folder. Also the files I am working with are in .xlsx format and I will need to read them into R as such.

I tried to do this first by using a folder on my computer desktop. I managed to get it to work using my local directory with the code below:

library(readxl)
library(tidyverse)

files <- list.files(path = "~/Desktop/data", pattern = "*.xlsx", full.names = TRUE) #read files from folder on desktop
df <- sapply(files, read_excel, simplify = FALSE) %>% #read files from the path, and bind them together
  bind_rows() 

I tried to adjust the code above to work with the drop_dir function in rdrop2. The code I tried is below:

library(rdrop2)
library(tidyverse)
library(readxl)

token <- drop_auth()
files <- drop_dir("!dropbox_folder", dtoken = token) #List all files in Dropbox folder MPD_03_Test
f <- files$path_display #list directory to dropbox
df <- sapply(f, read_excel, simplify = FALSE) %>%  #runs the read function for all the files that are pulled
  bind_rows() # .id="id creates a unique ID for each row and then binds them all together based on the ID.

When I run it the code is not loading the data files from the dropbox into R. When I run the dropbox code it just creates an empty object. Any help on where to go to figure this out will be greatly appreciated! Also I intend to use this as how I read data into and R-shiny app if that helps frame any suggestions you may have about how to approach my problem.

Thank You!

Answer 1

@MrGumble is correct in his comments. The files need to downloaded before being read. The drop_dir() function lists file paths on dropbox server and we can only read in data saved locally to our machine. If you have .csv files then this can be down in 1 step with the drop_read_csv() function. But since you have excel files these need to first to be downloaded explicitly with drop_download() and then read in with read_excel().

library(rdrop2)
library(tidyverse)
library(readxl)
#install.packages("xlsx")
library(xlsx)

token <- drop_auth()

#make a few excel file with iris dataset, save locally, and upload to dropbox root
iris_filenames <- paste0("iris", 1:3, ".xlsx")
walk(iris_filenames, ~write.xlsx(iris, file = .x, row.names = FALSE))
walk(iris_filenames, drop_upload)

#list all files on dropbox root and filter for only iris ones
iris_files_on_dropbox <- drop_dir(dtoken = token) %>% 
  filter(str_detect(name, 'iris'))
#make new filenames so we can see that the download worked correctly
#you could do overwrite = TRUE and not pass through new filenames
#see ?drop_download for all options
new_iris_filenames <- paste0("iris", 1:3, "-from-dropbox.xlsx")

#download the files first
walk2(iris_files_on_dropbox$name, new_iris_filenames, ~drop_download(path = .x, local_path = .y))

#then read them all in
df <- bind_rows(map(new_iris_filenames, read_xlsx))

Additionally, we can create our own custom function to do the download and reading in 1 step just as drop_read_csv() does by altering the source code for this function. All we need to do is change the read...() function from read.csv to read_excel and the reference to the dtoken default get_drop_token() to rdrop2:::get_drop_token() which is an un-exported function from the rdrop2 package so we need the three ':::'.

#source for drop_read_csv we can rewrite for excel files
# drop_read_csv <- function(file, dest = tempdir(), dtoken = get_dropbox_token(), ...) {
#   localfile = paste0(dest, "/", basename(file))
#   drop_download(file, localfile, overwrite = TRUE, dtoken = dtoken)
#   utils::read.csv(localfile, ...)
# }


drop_read_excel <- function(file, dest = tempdir(), dtoken = rdrop2:::get_dropbox_token(), ...) {
  localfile = paste0(dest, "/", basename(file))
  drop_download(file, localfile, overwrite = TRUE, dtoken = dtoken)
  readxl::read_excel(localfile, ...)
}

df2 <- bind_rows(map(iris_files_on_dropbox$name, drop_read_excel))

To work in a shiny app we first need to save the drop_auth token so we can authenticate while using the shiny app. Save this into your shiny app directory.

saveRDS(token, file = "token.rds")

Now here is a shiny app. When the 'go' button is clicked the iris excel files are downloaded and shown in the UI. We need to call drop_auth() in the global environment or global.R along with the custom drop_read_excel() function to use it.

library(shiny)
library(rdrop2)
library(tidyverse)


#saveRDS(token, file = "token.rds") into shiny app directory
#authenticate in global.R or outside of ui/server 
drop_auth(rdstoken = "token.rds")
drop_read_excel <- function(file, dest = tempdir(), dtoken = rdrop2:::get_dropbox_token(), ...) {
  localfile = paste0(dest, "/", basename(file))
  drop_download(file, localfile, overwrite = TRUE, dtoken = dtoken)
  readxl::read_excel(localfile, ...)
}


ui <- fluidPage(
  actionButton("go", "go"),
  tableOutput("table")

)

server <- function(input, output, session) {
 df <-  eventReactive(input$go, {
   withProgress(message = 'Downloading from dropbox',
                detail = 'This may take a while...', value = 0.5, {
    iris_files_on_dropbox <- drop_dir() %>% 
      filter(str_detect(name, 'iris'))
    setProgress(value = 0.75)
    df <- bind_rows(map(iris_files_on_dropbox$name, drop_read_excel))
    setProgress(value = 1)
                })
   return(df)
  })

 output$table <- renderTable({
   df()
 })

}

shinyApp(ui, server)