r handsontable: Can a custom renderer negate column formatting commands?

Question

This rendering of my rhandsontable in Shiny works just fine. Among other things, columns 'Pat. Owes' and 'Ins. Owes' show up formatted as US$ (see lines 4 and 5 from the bottom of the code section below):

renderRHandsontable({
      sessions_reactive$sessions[[patient_nr]], 
      row_highlight = row_highlight, col_highlight = col_highlight,
      width = 1000, height = 500) %>% 
      hot_rows(fixedRowsTop = 1) %>%
      hot_col("Pat. Owes", format = "$0,000.00", language = "en-US") %>%
      hot_col("Ins. Owes", format = "$0,000.00", language = "en-US") %>% 
      hot_table(highlightCol = TRUE, highlightRow = TRUE) %>% 
      hot_validate_numeric(cols = c(3, 5), min = 0, max = 500) %>% 
      hot_col(c(1, 3, 5, 6, 8), valign = 'htCenter')
})

The code below - but with those 2 US$-formatting lines commented out - also works fine. The renderer at the bottom does 2 things: makes the font bold and red in columns 5 and 8. And also, the background of the whole row becomes yellow if its last column contains a certain string.

    rhandsontable(
      sessions_reactive$sessions[[patient_nr]], 
      row_highlight = row_highlight, col_highlight = col_highlight,
      width = 1000, height = 500) %>% 
      hot_rows(fixedRowsTop = 1) %>%
    # hot_col("Pat. Owes", format = "$0,000.00", language = "en-US") %>%
    # hot_col("Ins. Owes", format = "$0,000.00", language = "en-US") %>% 
      hot_table(highlightCol = TRUE, highlightRow = TRUE) %>% 
      hot_validate_numeric(cols = c(3, 5), min = 0, max = 500) %>% 
      hot_col(c(1, 3, 5, 6, 8), valign = 'htCenter') %>%
      hot_cols(renderer = "
            function (instance, td, row, col, prop, value, cellProperties) {
                     Handsontable.renderers.TextRenderer.apply(this, arguments);

                     if (instance.params) {
                       hrows = instance.params.row_highlight
                       hrows = hrows instanceof Array ? hrows : [hrows]
                       hcols = instance.params.col_highlight
                       hcols = hcols instanceof Array ? hcols : [hcols]

                       if (hrows.includes(row)) {
                         td.style.background = 'yellow'
                       }

                       if (hcols.includes(col)) {
                         td.style.fontWeight = 'bold'
                         td.style.color = '#fc0f03'
                       }
                     }
            }")
  })

My issue: if I uncomment the 2 commented hot_col lines devoted to US$-formatting - they don't work in combination with my renderer at the bottom. I mean, there is no error, but those 2 columns just do not appear formatted as US$ - they just appear numeric. Clearly, my renderer at the bottom (although it doesn't refer to those columns specifically) somehow negates the US$ formatting. Moving those 2 lines to the bottom of rhandsontable definition doesn't help either. The render stuff is working, but the US$ formatting isn't.

Any advice? Thank you very much!

Answer 1

Would use NumericRenderer instead of TextRenderer. My understanding is that TextRenderer is the default with no validation. NumericRenderer can help with formatting of numbers.

Here's the previous example (with row and column formatting), with the addition of hot_col specifying US$ formatting:

library(shiny)
library(rhandsontable)

DF = data.frame(a=1:10, b=3:12, c=c("Dog", "Cat", "Mouse", 5:11), d=3:12, e=1:10, f=1:10, g=1:10, h=2:11, Comments = c("missed etc", rep("", 7), "missed", ""))

ui <- fluidPage(
  mainPanel(
    rHandsontableOutput('table')
  )
)

server = function(input, output, session) {

  output$table <- renderRHandsontable({
    row_highlight = which(grepl("missed", DF$Comments))-1
    col_highlight = c(5,8)-1
    rhandsontable(DF, row_highlight = row_highlight, col_highlight = col_highlight, width = 550, height = 300) %>%
      hot_rows(fixedRowsTop = 1) %>%
      hot_col(1, format = "$0,000.00", language = "en-US") %>%
      hot_col(2, format = "$0,000.00", language = "en-US") %>% 
      hot_table(highlightCol = TRUE, highlightRow = TRUE) %>% 
      hot_validate_numeric(cols = c(3, 5), min = 0, max = 500) %>% 
      hot_col(c(1, 3, 5, 6, 8), valign = 'htCenter') %>%
      hot_cols(renderer = "
            function (instance, td, row, col, prop, value, cellProperties) {
                     Handsontable.renderers.NumericRenderer.apply(this, arguments);

                     if (instance.params) {
                       hrows = instance.params.row_highlight
                       hrows = hrows instanceof Array ? hrows : [hrows]
                       hcols = instance.params.col_highlight
                       hcols = hcols instanceof Array ? hcols : [hcols]

                       if (hrows.includes(row)) {
                         td.style.background = 'yellow' 
                       }

                       if (hcols.includes(col)) {
                         td.style.fontWeight = 'bold'
                         td.style.color = '#fc0f03'
                       }
                     }
            }"
      ) 
  })
}

shinyApp(ui, server)