NA values in a non-editable date column in a datatable in a shiny app change to "Invalid Date" when clicked on

Question

I'm trying to make a shiny app with a table, including some editable cells and some fixed cells. Some columns are dates, with some values missing.

When I run the app and double click on an empty cell in a non-editable date column, it does not let me edit it (as expected). However, after clicking out of the cell, it displays "Invalid Date". (See row 2 of column 1 in the example below).

Here's a simplified version of my code:

library(shiny)
library(lubridate)
library(DT)

ui <- 
  fluidPage(
    fluidRow(
      column(
        width = 12,
        dataTableOutput("date_df")
        
      )
    )
  )

server <- 
  function(input, output){
    output$date_df <- 
      renderDataTable(
        datatable(
          data.frame(
            fixed_date = 
              c(
                ymd("20240101"),
                NA
              ),
            editable_date = 
              c(
                ymd("20240101"),
                NA
              ),
            fixed_text = 
              c(
                "Day 1",
                "Day 2"
              )
          ),
          editable = 
            list(
              target = "cell",
              disable = list(columns= c(1, 3)) 
            )
        ) %>% 
          formatDate(
            columns = 
              c(
                "fixed_date",
                "editable_date"
              ),
            method = "toLocaleDateString",
            params = 
              list(
                'en-US'
              )
          )
      )
  }

shinyApp(
  ui = ui,
  server = server
)

Is there a way to prevent this while keeping the columns as dates? I know I could store them as strings instead, but they're used as dates in some computations. Alternatively, I think I could create a display column with the dates converted to strings and hide the column where they're stored as dates, but I'd prefer a simpler solution if one exists.

Answer 1

You can use a callback JS function that only sets values if the correct column is edited. Also in this first column (zero-indexed) if the entered value is null (when nothing was entered), we can paste an empty date (''), so that invalid date is prevented. In this solution you have to adjust the date columns according to your needs

Zero-indexing

fixed_date	editable_date	fixed text
0	1	2

1. updatedCell.indexes.column === 1 - your zero-indexed editable date column
2. targets = c(0, 1) - All zero-indexed Date columns

Result

Code

library(shiny)
library(lubridate)
library(DT)

ui <- 
  fluidPage(
    fluidRow(
      column(
        width = 12,
        dataTableOutput("date_df")
      )
    )
  )

server <- 
  function(input, output){
    # Create the data frame
    df <- data.frame(
      fixed_date = 
        c(
          ymd("20240101"),
          NA
        ),
      editable_date = 
        c(
          ymd("20240101"),
          NA
        ),
      fixed_text = 
        c(
          "Day 1",
          "Day 2"
        ),
      stringsAsFactors = FALSE
    )
    
    output$date_df <- 
      renderDataTable({
        datatable(
          df,
          editable = list(
            target = "cell",
            disable = list(columns = c(1, 3)),
            callback = JS("function(updatedCell, updatedRow, oldValue) {
              if(updatedCell.indexes.column === 1)  // editable_date column (0-indexed)
                var value = updatedCell.data;
              }")
          ),
          options = list(
            columnDefs = list(
              list(
                targets = c(0, 1),  # Date columns (0-indexed)
                render = JS("function(data, type, row, meta) {
                  if(data === null) return '';
                  return type === 'display' && data !== null && data !== '' ? 
                    new Date(data.replace(/-/g, '\\/')).toLocaleDateString('en-US') : data;
                }")
              )
            )
          )
        )
      })
  }

shinyApp(
  ui = ui,
  server = server
)