Shiny and rhandsontable - Conditional cell/column formatting based on column sum

Question

I have an rhandsontable inside a shiny app. My goal is to color all cells in a column based on the sum of the column. Ex : if the sum of the values in the column is 1, then, all cells in this column are colored in green.

The expected outcome displayed to the user is like this:

It seems possible to do so with a JS formatting like this :

rhandsontable(myrhandsontable) %>% 
        hot_cols(renderer ="some JS script")

I have never done any JS before and I struggle to get the sum of the column inside the "some JS script" part.

Here is a minimal reproductible exemple to work with:

library(shiny)
library(rhandsontable)
library(tidyverse)

# basic user interface (not important here)
ui <- fluidPage(
    rHandsontableOutput(outputId = "ex")
)

# server side calculations
server <- function(input, output) {
    # create a dummy dataset
    ex_data = data.frame(id = letters[1:3],
                         attr1 = c(0.5, 0.4, 0.3),
                         attr2 = c(0.6, 0.3, 0.1))

    # create the rhandsontable object and define conditional formatting
    output$ex = renderRHandsontable({
        rhandsontable(ex_data) # %>% renderer ="JS script for conditional formatting")
    })

I unsuccessfully tried to use these posts and tutorials:

Any idea is welcome :)

A. Suliman · Accepted Answer · 2019-11-07T11:41:10.100

We can see this solution here rhandsontable - Custom Renderer, however, this solution has an issue when we implement in shiny, luckily, the issue has been resolved here

library(shiny)
library(tidyverse)
library(rhandsontable)

# basic user interface (not important here)
ui <- fluidPage(
  rHandsontableOutput(outputId = "ex")
)

# server side calculations
server <- function(input, output, session) {
  # create the rhandsontable object and define conditional formatting
  output$ex = renderRHandsontable({
    # create a dummy dataset
    ex_data = data.frame(id = letters[1:3],
                         attr1 = c(0.5, 0.4, 0.3),
                         attr2 = c(0.6, 0.3, 0.1))
    #create index with columns sum is equal to 1
    col_highlight <- unname(which(colSums(ex_data[c(2,3)])==1))

    rhandsontable(ex_data, col_highlight = col_highlight,
                  width = 550, height = 300) %>%
      hot_cols(renderer = "
    function(instance, td, row, col, prop, value, cellProperties) {
      Handsontable.renderers.NumericRenderer.apply(this, arguments);

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

      if (instance.params && hcols.includes(col)) {
        td.style.background = 'lightgreen';
      }
  }")
  })

}
shinyApp(ui,server)

score 0 · Answer 2 · answered May 16 '23 at 14:29

Just to add more to the previous answer and enhance the functionality.

if(interactive()) {
  library(shiny)
  library(tidyverse)
  library(rhandsontable)
  
  # basic user interface (not important here)
  ui <- fluidPage(rHandsontableOutput(outputId = "ex"))
  
  # server side calculations
  server <- function(input, output, session) {
    # create the rhandsontable object and define conditional formatting
    output$ex = renderRHandsontable({
      # create a dummy dataset
      ex_data = data.frame(
        id = letters[1:3],
        attr1 = c(0.5, 0.4, 0.3),
        attr2 = c(0.6, 0.3, 0.1),
        attr3 = c(0.1, 0.2, 0.3)
      )
      #create index with columns sum is equal to 1
      col_highlight_1 <-
        unname(which(colSums(ex_data[c(2, 3, 4)]) == 1))
      #create index with columns sum is greater than 1
      col_highlight_gt_1 <-
        unname(which(colSums(ex_data[c(2, 3, 4)]) > 1))
      #create index with columns sum is less than 1
      col_highlight_lt_1 <-
        unname(which(colSums(ex_data[c(2, 3, 4)]) < 1))
      
      rhandsontable(
        ex_data,
        col_highlight_1 = col_highlight_1,
        col_highlight_gt_1 = col_highlight_gt_1,
        col_highlight_lt_1 = col_highlight_lt_1,
        width = 550,
        height = 300
      ) %>%
        hot_cols(
          renderer = "
    function(instance, td, row, col, prop, value, cellProperties) {
      Handsontable.renderers.NumericRenderer.apply(this, arguments);

      console.log(instance.params)

      if (instance.params) {
            hcols_1 = instance.params.col_highlight_1;
            hcols_1 = hcols_1 instanceof Array ? hcols_1 : [hcols_1];

            hcols_gt_1 = instance.params.col_highlight_gt_1;
            hcols_gt_1 = hcols_gt_1 instanceof Array ? hcols_gt_1 : [hcols_gt_1];

            hcols_lt_1 = instance.params.col_highlight_lt_1;
            hcols_lt_1 = hcols_lt_1 instanceof Array ? hcols_lt_1 : [hcols_lt_1];
          }

      if (instance.params && hcols_1.includes(col)) {
        td.style.background = 'lightgreen';
      } else
      if (instance.params && hcols_gt_1.includes(col)) {
        td.style.background = 'orange';
      } else
      if (instance.params && hcols_lt_1.includes(col)) {
        td.style.background = 'lightblue';
      }
      return td;
  }"
        )
    })
    
  }
  shinyApp(ui, server)
}

Keep Coding!

Shiny and rhandsontable - Conditional cell/column formatting based on column sum

2 Answers2

Linked