Use values from edited table for calculations in Shiny

Question

What I am attempting to do, is to allow the user to pass in a configuration/lookup excel table into shiny, display this table in shiny, allow the user to make cells edits in shiny, and use the values that were edited from the editable table for calculations. My problem arises for the last step "use the values that were edited from the editable table for calculations".

The excel file consists of 2 tabs with data of the following content:

Tab1 Name: "parameters" data.frame(Name = c("a", "b", "c"), Value = c(1:3))

Tab2 Name: "parameters2" data.frame(Name = c("a", "b", "c"), Value = c(4:6))

The ideal shiny app would do the following:

1) At upload, perform a calculation adding the unchanged first values of Tab 1 and Tab 2. This would be 1 + 4 = 5.

2) If user edits Tab 1's value of 1 to 8, then the calculation would result in 8 + 4 = 12.

Effectively, I want to use the edited tables values to update all my calculations if the user makes any edits to it. I know this can be done by simply uploading a new file in shiny, but I would rather allow them to do this in shiny as opposed to uploading a new file.

Here is my shiny app. Appreciate any help/guidance!

    library(shiny)
library(DT)
shinyApp(
  ui <- fluidPage(
    fileInput(inputId = "config", label = "Upload Configuration File", 
              multiple = F, accept = c(".xlsx", ".xls")), 
    verbatimTextOutput("txt"), 
    tagList(tags$head(tags$style(type = 'text/css','.navbar-brand{display:none;}')),
            navbarPage(title = "",
                       tabPanel(title = "Parameters",
                                dataTableOutput(outputId = "edit.param", width = 2)), 
                       tabPanel(title = "Parameters2",
                                dataTableOutput(outputId = "edit.param2", width = 2))
            )
    )
  ),
  server = function(input, output, session) {

    config.path = reactive({

      inFile = input$config

      if(is.null(inFile)) {
        return(NULL)
      } else {
        return(inFile$datapath)
      }

    })

    df.param = reactive({
      read_excel(path = config.path(), sheet = "parameters")
    })

    df.param2 = reactive({
      read_excel(path = config.path(), sheet = "parameters2")
    })

    output$edit.param = renderDT(df.param(), selection = "none", server = F, editable = "cell")
    output$edit.param2 = renderDT(df.param2(), selection = "none", server = F, editable = "cell")

    observeEvent(input$edit.param_cell_edit, {
      df.param()[input$edit.param_cell_edit$row, input$edit.param_cell_edit$col] <<- input$edit.param_cell_edit$value
    })

    observeEvent(input$edit.param2_cell_edit, {
      df.param()[input$edit.param2_cell_edit$row, input$edit.param2_cell_edit$col] <<- input$edit.param2_cell_edit$value
    })

    output$txt = reactive({

      df.param()$value[1] + df.param2()$value[1]

    })

  }

)

I also tried this for the server section and had no luck either:

    output$edit.param = renderDT(df.param(), selection = "none", server = F, editable = "cell")
output$edit.param2 = renderDT(df.param2(), selection = "none", server = F, editable = "cell")

observe(input$edit.param_cell_edit)
observe(input$edit.param2_cell_edit)

Answer 1

Could you try this? (I have not tried).

library(shiny)
library(DT)

shinyApp(
  ui <- fluidPage(
    fileInput(inputId = "config", label = "Upload Configuration File", 
              multiple = F, accept = c(".xlsx", ".xls")), 
    verbatimTextOutput("txt"), 
    tagList(tags$head(tags$style(type = 'text/css','.navbar-brand{display:none;}')),
            navbarPage(title = "",
                       tabPanel(title = "Parameters",
                                dataTableOutput(outputId = "edit_param", width = 2)), 
                       tabPanel(title = "Parameters2",
                                dataTableOutput(outputId = "edit_param2", width = 2))
            )
    )
  ),
  server = function(input, output, session) {

    config.path = reactive({

      inFile = input$config

      if(is.null(inFile)) {
        return(NULL)
      } else {
        return(inFile$datapath)
      }

    })

    df_param <- reactiveVal()
    observe({
      req(config.path())
      df_param(read_excel(path = config.path(), sheet = "parameters"))
    })

    df_param2 <- reactiveVal()
    observe({
      req(config.path())
      df_param2(read_excel(path = config.path(), sheet = "parameters2"))
    })

    output$edit_param = renderDT({
      req(df_param())
      datatable(isolate(df_param()), selection = "none", editable = "cell")
    })
    output$edit_param2 = renderDT({
      req(df_param2())
      datatable(isolate(df_param2()), selection = "none", editable = "cell")
    })

    proxy <- dataTableProxy("edit_param")
    proxy2 <- dataTableProxy("edit_param2")

    observeEvent(input$edit_param_cell_edit, {
      info <- input$edit_param_cell_edit
      df_param(editData(df_param(), info, proxy, resetPaging = FALSE))
    })

    observeEvent(input$edit_param2_cell_edit, {
      info <- input$edit_param2_cell_edit
      df_param2(editData(df_param2(), info, proxy2, resetPaging = FALSE))
    })

    output$txt = renderPrint({
      df_param()$value[1] + df_param2()$value[1]
    })

  }

)