Editable DT with AUTOFILL is not working in Shiny

[lsusatyo-gcmlp]

The current release version of DT as well as the developer version currently doesn't support AutoFill capability when used inside Shiny. See the code below:

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
    })
  }
)

---

By filing an issue to this repo, I promise that

• [x] I have fully read the issue guide at https://yihui.name/issue/.
• [x] I have provided the necessary information about my issue.
  • If I'm asking a question, I have already asked it on Stack Overflow or RStudio Community, waited for at least 24 hours, and included a link to my question there.
  • If I'm filing a bug report, I have included a minimal, self-contained, and reproducible example, and have also included xfun::session_info('DT'). I have upgraded all my packages to their latest versions (e.g., R, RStudio, and R packages), and also tried the development version: remotes::install_github('rstudio/DT').
  • If I have posted the same issue elsewhere, I have also mentioned it in this issue.
• [x] I have learned the Github Markdown syntax, and formatted my issue correctly.

I understand that my issue may be closed if I don't fulfill my promises.

[fjshi]

I have the exact same issue and would love to see this is addressed.

[stla]

This works with the option server = FALSE: output$x1 = renderDT({datatable(x$df, selection = 'none', editable = TRUE, extensions = 'AutoFill', options = list(autoFill = TRUE))}, server=FALSE) But the cells edited by autofill are not captured in x1_cell_edit. I know how to capture them but I have not the time to do it right now.

[stla]

Here is the code which captures the cells edited by autofilling. But currently it works only with the option horizontal = FALSE. And that will not work currently because of a bug in the current version of AutoFill in DT.

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 = Array(cells.length);",
  "  for(var i=0; i<cells.length; ++i){",
  "    var c = cells[i][0];",
  "    var value = c.set === null ? '' : c.set;", # null causes problem in R
  "    out[i] = {row: c.index.row+1, col: c.index.column, value: value};",
  # if you want a color for the autofilled cells:
  "    $(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(
  DTOutput("dt"),
  br(),
  verbatimTextOutput("table")
)

server <- function(input, output){
  
  dat <- iris[1:5,]
  
  output[["dt"]] <- renderDT({
    dtable <- datatable(dat, 
              editable = list(target = "cell"),
              selection = "none",
              extensions = "AutoFill",
              callback = JS(callback), 
              options = list(
                autoFill = list(horizontal = FALSE)
              )
    )
  }, 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)

[stla]

See my answer on stackoverflow. I downgraded DT to 0.8 because of the above mentionned bug in 0.9.

[stla]

On my blog now.

[dkibalnikov]

There is conflict between dt_cell_edit and dt_cells_filled once you made changes using EDIT in cell then you are not able to set new value with FILL. This is caused by code: info <- rbind(input[["dt_cells_filled"]], input[["dt_cell_edit"]]) which means you add both type of changes simultaneously. Even more new version of DT throws something like this if you try to set changes with FILL after EDIT:

Error in : Row index 3 is used more than once for assignment datatables shiny

I spent some time to figure out how to make things more appropriate, came to:

info <- rbind(input[["dt_cell_edit"]], input[["dt_cells_filled"]])
    if(!is.null(info)){
      info <- info  %>%  
        distinct(row, col, .keep_all = TRUE)
      info$value[info$value==""] <- NA
      dat <<- editData(dat, info)

This code solves problem with error but it isn't able to solve strange behaviour FILL after EDIT.

[AhmedKhaled945]

Here is the code which captures the cells edited by autofilling. But currently it works only with the option horizontal = FALSE. And that will not work currently because of a bug in the current version of AutoFill in DT.

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 = Array(cells.length);",
  "  for(var i=0; i<cells.length; ++i){",
  "    var c = cells[i][0];",
  "    var value = c.set === null ? '' : c.set;", # null causes problem in R
  "    out[i] = {row: c.index.row+1, col: c.index.column, value: value};",
  # if you want a color for the autofilled cells:
  "    $(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(
  DTOutput("dt"),
  br(),
  verbatimTextOutput("table")
)

server <- function(input, output){
  
  dat <- iris[1:5,]
  
  output[["dt"]] <- renderDT({
    dtable <- datatable(dat, 
              editable = list(target = "cell"),
              selection = "none",
              extensions = "AutoFill",
              callback = JS(callback), 
              options = list(
                autoFill = list(horizontal = FALSE)
              )
    )
  }, 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)

@stla Hello, for this solution, it works just fine, but once i go from page 1 to let's say page 2, if I dragged a value in second row to 5th, it will show row indices as if it is page 1 (2,3,4,5,6), instead of (12,13,14,15,16),

is there a quick way to solve this?

[stla]

@AhmedKhaled945 I don't remember sorry. Did you check the version given on my blog?

[JasperSch]

@AhmedKhaled945

You can use input$dt_rows_current[input$dt_rows_filled] instead of input$dt_rows_filled.