Color a whole row in rhandsontable based on a string value in one column

Question

I have an rhandsontable and I want the WHOLE ROW to be yellow if the cell in the text in the last column ("Comments") includes string "missed".

The code below highlights any cell that has a value 'missed', but not the whole row. Besides, I'd like the row to turn yellow when a cell in the last column CONTAINS "missed" - even inside a longer string, and not just when it contains only "missed" - as it is written now - I just don't know how to match strings in JavaScript.

DF = data.frame(a = 1:2, b = 3:4, Comments = c("missed etc", "missed"))
rhandsontable(DF, width = 550, height = 300) %>%
  hot_cols(renderer = " function (instance, td, row, col, prop, value, cellProperties) { 
                     Handsontable.renderers.TextRenderer.apply(this, arguments); 
                     if(value == 'missed') { 
                     td.style.background = 'yellow' } 
                     }")

Thank you very much!

To clarify, in reality, I am dealing with a larger table that I am rendering in rShiny. So, ideally the solution that works for the little data frame above should also work here. Currently it is not (nothing shows up at all):

    output$session_table <- renderRHandsontable({
        req(input$select_a_patient)
        patient_nr <- which(patient_names_reactive$names %in% input$select_a_patient)

        row_highlight = which(grepl("missed", 
sessions_reactive$sessions[[patient_nr]]$Comments))-1

        rhandsontable(sessions_reactive$sessions[[patient_nr]],
                      row_highlight = row_highlight,
                      width = 1000, height = 500) %>% 
            hot_rows(fixedRowsTop = 1) %>%
            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); 

                     tbl = this.HTMLWidgets.widgets[0]
                     hrows = tbl.params.row_highlight
                     hrows = hrows instanceof Array ? hrows : [hrows]

                     if (hrows.includes(row)) { 
                       td.style.background = 'yellow' } 
                     }") %>% hot_col(c(5, 8), renderer = " 
                        function (instance, td, row, col, prop, value, cellProperties) {
                           Handsontable.renderers.TextRenderer.apply(this, arguments);
                           td.style.fontWeight = 'bold';
                           td.style.color = '#fc0f03';}"
            )

Answer 1

Based on the tutorial using a custom renderer:

https://jrowen.github.io/rhandsontable/

you could do the following (doing the string match outside of the js).

DF = data.frame(a = 1:4, b = 3:6, Comments = c("missed etc", "", "missed", ""))

rhandsontable(DF, row_highlight = which(grepl("missed", DF$Comments))-1, width = 550, height = 300) %>%
  hot_cols(renderer = "function (instance, td, row, col, prop, value, cellProperties) { 
                     Handsontable.renderers.TextRenderer.apply(this, arguments); 

                     tbl = this.HTMLWidgets.widgets[0]

                     hrows = tbl.params.row_highlight
                     hrows = hrows instanceof Array ? hrows : [hrows]

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

Edit: This apparently requires additional modification if used as a shiny app. As noted in documentation:

When using this approach in a shiny app or in a document with more than one widget, the widget search logic will need to be more robust.

If instance.params is available, we can try the following:

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_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'
                       }
                     }
            }"
      ) 
  })
}

shinyApp(ui, server)

Edit1: Integrated second renderer with the first one, so that column formatting doesn't overwrite row background color.

Edit2: Adding explanation regarding col_highlight added.

Starting at the beginning we have col_highlight = c(5,8)-1 which creates a vector storing the columns we wish to have different formatting (bold, red font). We subtract one since arrays in javascript are zero-based.

The following line for rhandsontable allows us to pass in col_highlight = col_highlight so that we can later access these selected columns through instance.params.col_highlight in the renderer function later on. Once we access them and assign them to hcols, we make sure it is an array if it wasn't already.

The statement if (hcols.includes(col)) checks to see if the hcols array contains the column (col) being rendered. If the column rendered is 5, this is contained in the vector (5, 8), and the td.style would be set to bold and red.

Note that since hrows would only change background color, and hcols would only change font bold and color, one does not overwrite the other, and can be used together.