Edit datatable using autofill extension?

Question

How do I use Autofill extension on editable tables? In the example below, modified from this previous question , the autofill action (filling in table using the blue square) isn't being captured.

Thanks

Iain

library(shiny)
library(DT)
shinyApp(
ui = fluidPage(
DTOutput('x1'),
verbatimTextOutput("print")
),
server = function(input, output, session) {
x = reactiveValues(df = NULL)

observe({
  df <- iris
  df$Date = Sys.time() + seq_len(nrow(df))
  x$df <- df
})

output$x1 = renderDT(x$df, selection = 'none', editable = TRUE, extensions = 'AutoFill', options = list(autoFill = TRUE))

proxy = dataTableProxy('x1')

observeEvent(input$x1_cell_edit, {
  info = input$x1_cell_edit
  str(info)
  i = info$row
  j = info$col
  v = info$value

  x$df[i, j] <- isolate(DT::coerceValue(v, x$df[i, j]))
})

output$print <- renderPrint({
  x$df
})
}
)

There seem to be some issues with the datatable extensions in shiny, see here: https://rstudio.github.io/DT/extensions.html. Outside shiny your example works well. Workaround idea could be to capture the state of the app right after the change (since it does change indeed but very quickly reverts),...but capturing the changes is not possible to my knowledge,... — Tonio Liebrand, Jan 18 '19 at 21:48

Stéphane Laurent · Answer 1 · 2019-09-19T23:34:08.017

Here is a way. It requires server = FALSE.

library(shiny)
library(DT)

callback <- c(
  "var tbl = $(table.table().node());",
  "var id = tbl.closest('.datatables').attr('id');",
  "table.on('autoFill', function(e, datatable, cells){",
  "  var out = [];",
  "  for(var i=0; i<cells.length; ++i){",
  "    var cells_i = cells[i];",
  "    for(var j=0; j < cells_i.length; ++j){",
  "      var c = cells_i[j];",
  "      var value = c.set === null ? '' : c.set;", # null causes problem in R
  "      out.push({row: c.index.row+1, col: c.index.column, value: value});",
# if you want to color the autofilled cells, uncomment the the two lines below  
#  "      $(table.cell(c.index.row, c.index.column).node())",
#  "        .css('background-color', 'yellow');",
  "    }",
  "  }",
  "  Shiny.setInputValue(id + '_cells_filled:DT.cellInfo', out);",
  "  table.rows().invalidate();", # this updates the column type
  "});"
)

ui <- fluidPage(
  br(),
  DTOutput("dt"),
  br(),
  verbatimTextOutput("table")
)

server <- function(input, output){

  dat <- iris[1:5,]

  output[["dt"]] <- renderDT({
    datatable(dat, 
              editable = list(target = "cell"),
              selection = "none",
              extensions = "AutoFill",
              callback = JS(callback), 
              options = list(
                autoFill = TRUE
              )
    )
  }, server = FALSE)

  Data <- reactive({
    info <- rbind(input[["dt_cells_filled"]], input[["dt_cell_edit"]])
    if(!is.null(info)){
      info <- unique(info)
      info$value[info$value==""] <- NA
      dat <<- editData(dat, info)
    }
    dat
  })

  output[["table"]] <- renderPrint({Data()})  
}

shinyApp(ui, server)

EDIT

With server = TRUE it suffices to replace

dat <<- editData(dat, info)

with

dat <<- editData(dat, info, proxy = "dt")

+1 this is great -- one problem is if you try to fill a column that has been cell edited first. For example, if you change the value of `[1, "Sepal.Width"]` and then autofill the value from `[1, "Sepal.Length"]` across the row the underlying value of `[1, "Sepal.Width"]` does not change. When changing `server = TRUE` it does not change in the displayed table or underlying table. This is due to the order of the `rbind` statement. Is there a way to adjust this so the most recent fill/edit is on the bottom (or top) of `info` so it is easily subset when `row` and `col` are duplicated? — LMc, May 18 '23 at 15:27
A way to fix this issue is to have two different `observeEvents`: one for triggered by `input[["dt_cells_filled"]]` and another by `input[["dt_cell_edit"]]` — LMc, May 19 '23 at 21:32

Edit datatable using autofill extension?

1 Answers1

EDIT