How to change color of cells in a DT::dataTable in shiny, based on cells in another column?

Question

I am new to shiny/r and trying to change the background color of a cell (DT table) base on value of another cell. I tried using this example from https://rstudio.github.io/DT/010-style.html

datatable(df) %>% formatStyle(
  'V1', 'V6',
  backgroundColor = styleEqual(c(0, 1), c('gray', 'yellow'))
)

But somehow it doesn't seem to work for me

Here's my code :

dt_output = function(title, id) {
        fluidRow(column(
        12, h1(paste0(title)),
        hr(), DTOutput(id)
        ))
        }

 render_dt = function(data, editable = 'cell', server = TRUE, ...) {
    renderDT(data, selection = 'none', server = server, editable = editable, ...)
    }

ui = fluidPage(
downloadButton("mcp_csv", "Download in CSV", class="but"),
    
dt_output('Report', 'x9'),
)

server = function(input, output, session) {
d1 = readRDS("cmp.rds")
d9 = d1

output$x9 = render_dt(d9, 'cell', rownames = FALSE, extensions = 'Buttons', options = list(dom = 'Bfrtip', buttons = I('colvis')))
    
observeEvent(input$x9_cell_edit, {
d9 <<- editData(d9, input$x9_cell_edit, 'x9', rownames = FALSE)
saveRDS(d9, 'cmp.rds', version = 2)
})

datatable(d9) %>% formatStyle(
'R/Y/G', 'Y', #'R/Y/G' is the column cell I'm trying to change values for based on column 'Y'
backgroundColor = styleEqual(c(0, 1), c('red', 'yellow'))
)

I'm not sure what I'm doing wrong. Maybe it's in the wrong place, I don't know. Also if I needed to change color of column 'R/Y/G' based on three different columns (R, Y, G), based on dynamic input (not hardcoded like 0 and 1) =, how would I implement that? Thanks

P.S. If I add this code

dt_d9=datatable(d9) %>% formatStyle(
'R/Y/G', 'Y',
backgroundColor = styleEqual(c(0, 1), c('red', 'yellow'))
)

and replace

output$x9 = render_dt(d9, 'cell', rownames = FALSE, extensions = 'Buttons', options = list(dom = 'Bfrtip', buttons = I('colvis')))

with

output$x9 = render_dt(dt_d9, 'cell', rownames = FALSE, extensions = 'Buttons', options = list(dom = 'Bfrtip', buttons = I('colvis')))

I do get the colors on the R/Y/G column, but the edit cell function stops working. The edit cell function can be found here : https://yihui.shinyapps.io/DT-edit/

Answer 1

Your code gives the following error:

renderDataTable ignores ... arguments when expr yields a datatable object;

Since you now return a DT::datatable object to renderDT and not a dataframe. This is a bit similar to this Q/A. So now you have to move all arguments to the DT::datatable constructor:

render_dt = function(data) {
 renderDT(data)
}

and

  dt_d9 <- datatable(d9, editable = 'cell', rownames = FALSE, extensions = 'Buttons', options = list(dom = 'Bfrtip', buttons = I('colvis'))) %>% formatStyle(
'R/Y/G', 'Y', #'R/Y/G' is the column cell I'm trying to change values for based on column 'Y'
backgroundColor = styleEqual(c(0, 1), c('red', 'yellow'))
 )

 output$x9 = render_dt(dt_d9)