Shiny DT unable to add buttons to download as csv/excel

Question

Having trouble adding buttons for "download as csv/excel" to my Shiny app DataTable section. It seems that as my "Data" is queried from redshift, it doesn't recognize it as a data frame.

Error thrown: 'data' must be 2-dimensional (e.g. data frame or matrix)

From this document it seems to return it as a data frame which would contradict the error message. Any idea how I could fix this and get the buttons to work?

server <- function(input, output) {
  
  observeEvent(input$executeSQL, {
    if (input$selection == "CL7D") {
      output$mytable = DT::renderDataTable({ 
        dbGetQuery(conn, "select * from dummy")
        extensions = "Buttons" 
        options = list(paging = TRUE,
                       scrollX=TRUE, 
                       searching = TRUE,
                       ordering = TRUE,
                       dom = 'Bfrtip',
                       buttons = c('copy', 'csv', 'excel', 'pdf'),
                       pageLength=5, 
                       lengthMenu=c(3,5,10) )
      })
    }
    
  })
}

Answer 1

Your first argument to renderDataTable is indeed an expression, as it should be, but ... your expression is not doing much.

1. First, it retrieves some data from dbGetQuery, but since it isn't stored anywhere, it is immediately lost.
2. Then it creates a local variable named extensions and assigned the value "Buttons".
3. Last, it creates a local variable named options that is list(..).

Since the default behavior in R is for an expression with { ... } is for it to "return" the last expression within it, that means that it doesn't return the value from dbGetQuery, it doesn't return extensions, it instead returns the value of options, which is not 2-dimensional.

Perhaps you meant:

  observeEvent(input$executeSQL, {
    if (input$selection == "CL7D") {
      output$mytable = DT::renderDataTable(        # remove the '{'
        dbGetQuery(conn, "select * from dummy"),   # add a ','
        extensions = "Buttons",                   # add a ','
        options = list(paging = TRUE,
                       scrollX=TRUE, 
                       searching = TRUE,
                       ordering = TRUE,
                       dom = 'Bfrtip',
                       buttons = c('copy', 'csv', 'excel', 'pdf'),
                       pageLength=5, 
                       lengthMenu=c(3,5,10) )
      )                                            # remove the '}'
    }
    
  })

Side notes:

1. I tend to prefer to keep data-creating/querying actions as simple reactive components that then may be used elsewhere. For example, let's say you wanted a banner at top of your shiny app to display the number of rows returned. With your current code, you would need to run dbGetQuery twice.

   Instead, make it reactive and then use it later; that way, if you need to use the data for any other component in your shiny app, you'll have it available separately.

     mydata <- eventReactive(input$executeSQL, {
       dbGetQuery(conn, "select * from dummy")
     })
     observeEvent(mydata(), {
       if (input$selection == "CL7D") {
         output$mytable = DT::renderDataTable(
           mydata(),
           extensions = "Buttons",
           options = list(paging = TRUE,
                          scrollX=TRUE, 
                          searching = TRUE,
                          ordering = TRUE,
                          dom = 'Bfrtip',
                          buttons = c('copy', 'csv', 'excel', 'pdf'),
                          pageLength=5, 
                          lengthMenu=c(3,5,10) )
         )
       }
   
     })
   

2. I've yet to see one working example of shiny where having a reactive component nested within another made sense. I have not tested your code here (lacking data and ui and such), but I suspect that your code would operate better, more-cleanly as

     mydata <- eventReactive(input$executeSQL, {
       dbGetQuery(conn, "select * from dummy")
     })
     output$mytable <- DT::renderDataTable(
       if (input$selection == "CL7D") mydata(),
       extensions = "Buttons",
       options = list(paging = TRUE,
                          scrollX=TRUE, 
                          searching = TRUE,
                          ordering = TRUE,
                          dom = 'Bfrtip',
                          buttons = c('copy', 'csv', 'excel', 'pdf'),
                          pageLength=5, 
                          lengthMenu=c(3,5,10) )
         )
   

   If that doesn't work, move the if conditional out,

     mydata <- eventReactive(input$executeSQL, {
       dbGetQuery(conn, "select * from dummy")
     })
     data_CL7D <- reactive({
       if (input$selection == "CL7D") mydata()
     })
     output$mytable <- DT::renderDataTable(
       data_CL7D(),
       extensions = "Buttons",
       options = list(paging = TRUE,
                          scrollX=TRUE, 
                          searching = TRUE,
                          ordering = TRUE,
                          dom = 'Bfrtip',
                          buttons = c('copy', 'csv', 'excel', 'pdf'),
                          pageLength=5, 
                          lengthMenu=c(3,5,10) )
         )