highlight the row if a cell is edited in Rhandsontable and keep track of all changes

Question

I'm developing a shiny app which is reading data from SQL database and present it in a rhandsontable and allows user to edit the data. I would like to add some functionality before I submit the changes back to the database :

1. How could I highlight a row in rhandsontable if a cell in that row was edited ?
2. Is there a way to collect all the changes / edits on the table, since the input$myTable$changes$changes just shows the last changes !

here is my minimal sample code:

df <- structure(list(NumberOfWEC = c(3, 4, 54, 19, 10, 6, 8, 11, 140, 
                               11, 34), Source = c("SRP", "SRP", "SRP", "SRP", "SRP", "RBP", 
                                                   "SRP", "SRP", "SRP", "SRP", "SRP"), Status = structure(c(3L, 
                                                                                                            3L, 2L, 3L, 2L, 1L, 2L, 1L, 2L, 2L, 2L), .Label = c("CANDIDATE", 
                                                                                                                                                                "APPROVED", "EXISTING"), class = c("ordered", "factor"))), row.names = c(NA, 
                                                                                                                                                                                                                                         11L), class = "data.frame")



ui <- fluidPage(
  rHandsontableOutput("myTable")
)

server <- function(input, output, session) {
  
  ColNames <- (colnames(df))
  ColNames <- ColNames[!ColNames %in% "Status"]
  
  
  output$myTable <- renderRHandsontable({rhandsontable(df) %>% hot_col(ColNames, readOnly = TRUE)})
  
  observeEvent(input$myTable$changes$changes, {

    changedRow = 1 + as.numeric(input$myTable$changes$changes[[1]][[1]])
    changedColl = 1 + as.numeric(input$myTable$changes$changes[[1]][[2]])
    OldValue = input$myTable$changes$changes[[1]][[3]]
    NewValue = input$myTable$changes$changes[[1]][[4]]
    print(paste0("changedRow = ",changedRow, " changedColl = ", changedColl, " OldValue = ", OldValue, " NewValue = ", NewValue, " by = ",session$user))
    
  })
  
}

shinyApp(ui, server)

Answer 1

I came across this problem by myself and found some time ago the solution here Change cell background of rHandsontable with afterChange event on client side So the provided code is not generated by myself:

The main changes to your code:

1. add the change_hook function at the top.
2. this code in the server part:

  output$myTable <- renderRHandsontable({rht = rhandsontable(df) %>% hot_col(ColNames, readOnly = TRUE)
  htmlwidgets::onRender(rht,change_hook)
  })

library(shiny)
library(rhandsontable)

change_hook <- "function(el,x) {
var hot = this.hot;  
var cellChanges = [];

var changefn = function(changes,source) { 
if (source === 'edit' || source === 'undo' || source === 'autofill' || source === 'paste') {
row = changes[0][0];
col = changes[0][1];
oldval = changes[0][2];
newval = changes[0][3];

if (oldval !== newval) {
  var cell = hot.getCell(row, col);
  cell.style.background = 'pink';
  cellChanges.push({'rowid':row, 'colid':col});
}
}
}

var renderfn = function(isForced) {

for(i = 0; i < cellChanges.length; i++)
{

var rowIndex = cellChanges[i]['rowid'];
var columnIndex = cellChanges[i]['colid'];

var cell = hot.getCell(rowIndex, columnIndex);
cell.style.background = 'yellow';

}


}

var loadfn = function(initialLoad) {

for(i = 0; i < cellChanges.length; i++)
    {
      var rowIndex = cellChanges[i]['rowid'];
      var columnIndex = cellChanges[i]['colid'];

      var cell = hot.getCell(rowIndex, columnIndex);

      cell.style.background = 'white';

    }
cellChanges = []

}


hot.addHook('afterChange', changefn);
hot.addHook('afterRender', renderfn);
hot.addHook('afterLoadData', loadfn);


}"

df <- structure(list(NumberOfWEC = c(3, 4, 54, 19, 10, 6, 8, 11, 140, 
11, 34), Source = c("SRP", "SRP", "SRP", "SRP", "SRP", "RBP", 
"SRP", "SRP", "SRP", "SRP", "SRP"), Status = structure(c(3L, 
3L, 2L, 3L, 2L, 1L, 2L, 1L, 2L, 2L, 2L), .Label = c("CANDIDATE", 
"APPROVED", "EXISTING"), class = c("ordered", "factor"))), row.names = c(NA, 
11L), class = "data.frame")



ui <- fluidPage(
  rHandsontableOutput("myTable")
)

server <- function(input, output, session) {
  
  ColNames <- (colnames(df))
  ColNames <- ColNames[!ColNames %in% "Status"]
  
  
  output$myTable <- renderRHandsontable({rht = rhandsontable(df) %>% hot_col(ColNames, readOnly = TRUE)
  htmlwidgets::onRender(rht,change_hook)
  })
  
  observeEvent(input$myTable$changes$changes, {
    
    changedRow = 1 + as.numeric(input$myTable$changes$changes[[1]][[1]])
    changedColl = 1 + as.numeric(input$myTable$changes$changes[[1]][[2]])
    OldValue = input$myTable$changes$changes[[1]][[3]]
    NewValue = input$myTable$changes$changes[[1]][[4]]
    print(paste0("changedRow = ",changedRow, " changedColl = ", changedColl, " OldValue = ", OldValue, " NewValue = ", NewValue, " by = ",session$user))
  })
  
}

shinyApp(ui, server)