A reactive that gets colnames from an Excel sheet for a Shiny app to choose which column gets put into ggplot

Question

TL;DR, this is my first Shiny App ever, and I am stuck on this reactive problem.

I am writing a Shiny app that will take Excel survey data and put it into a ggplot function. The function should work, but survey questions vary from year to year. I want the app to do the following:

1. Take multiple Excel files and read them
2. Display three drop-down menus for organization name, number of volunteers/hours, and the year the survey was taken, and display three text-entry forms for X and Y labels and Title
3. Print a histogram that shows, for each organization, the number of volunteers with dodged bars for each year the organization appeared.

The problem is with the second task. I want the app to react when the files get uploaded by placing the whole list of colnames() into the drop-down menu for the user to choose which columns had the organization name. I have tried solutions from other questions on Stack Overflow, but they all ended up throwing errors.

Here is my UI and Server code:

---

library(rsconnect)
library(readxl)
library(shiny)
library(ggplot2)



dataset <- file

ui <- fluidPage(
  shinythemes::themeSelector(),
  titlePanel("Volunteer stats"),

  sidebarLayout(

    sidebarPanel(
      #I need: names of each of the files, 
      #   columns for org, num_vol, and survey year, 
      #   labels for x axis, y axis, and title, 
      #   name for the PDF to be created

    fileInput(inputId = "file", label = "Choose Excel file", multiple = TRUE),
    uiOutput("org_select"),
    uiOutput("num_select"),
    uiOutput("year_select"),
    textInput(inputId = "org_label", label = "X axis label"), 
    textInput(inputId = "vols_label", label = "Y axis label"), 
    textInput(inputId = "plot_title", label = "Chart title"),
    textInput(inputId = "pdf_title", label = "PDF title")), 

  mainPanel(
    plotOutput(
      outputId = "histogram"
  )
  )
))

server <- function(input, output) {

  output$org_select <- renderUI({
        selectInput("org_col", "Which column has the organization name?", choices = list(colnames(read_excel(input$file))), label = "Organization")
  })
  output$num_select <- renderUI({
    selectInput("num_vols", "Which column has the relevant metric?", choices = list(colnames(read_excel(input$file))), label = "Number of Volunteers")
  })
  output$year_select <- renderUI({
    selectInput("year", "Which column has the year?", choices = list(colnames(read_excel(input$file))), label = "Year")
  })

#assemble all the files into a list based on the input, to be passed to ggplot (not shown) 
  getData <- reactive({
    if (is.null(input$file)){
      return(NULL)
    }else{
      numfiles = nrow(input$file)
      files_list = list(
        for(i in 1:numfiles)
        {
          XL_file = read_excel(input$file[[i, 'datapath']], header = TRUE)
          lastrow = nrow(XL_file)
          shift = function(x, n){
            c(x[-(seq(n))], rep(NA, n)) 
          }
          XL_file$identity = shift(XL_file$identity, 1)
          files_list[[i]] = XL_file[-lastrow, ]
        }
      )
    }
  })
getData()
shinyApp(ui = ui, server = server)

I have not included my ggplot function for brevity. If I need help with that, I'll submit a separate question later.

Many thanks, Fearless

Answer 1

Just two minor fixes to make it work:

input$file is an object (or list if you want), but read_excel() expect a string containing the file path. Use input$file$datapath.

The choices argument expect either a named list of named or unnamed values. With list(colnames(...)) you are passing it a list with a single element containing the chioices:

list(colnames(read_excel('path/to/file')))
[[1]]
[1] "COL_1"      "COL_2"             "COL_3"           "COL_4"   

Just pass it the colnames().

server <- function(input, output) {

  output$org_select <- renderUI({
    selectInput("org_col", "Which column has the organization name?", choices = colnames(read_excel(input$file$datapath)), label = "Organization")
  })
  output$num_select <- renderUI({
    selectInput("num_vols", "Which column has the relevant metric?", choices = colnames(read_excel(input$file$datapath)), label = "Number of Volunteers")
  })
  output$year_select <- renderUI({
    selectInput("year", "Which column has the year?", choices = colnames(read_excel(input$file$datapath)), label = "Year")
  })
}