How to Select a Cell Range in rhandsontable Using Text Input in a Shiny App?

Question

I am currently working on a Shiny app in R that incorporates an rhandsontable element. I would like to add a feature where a user can specify a cell range (e.g., "A1:B2") in a text input field, and the rhandsontable would then select that range on the table.

Here's a simplified version of my current code:

library(shiny)
library(rhandsontable)

ui <- fluidPage(
    textInput("cell_range", "Enter cell range:"),
    rHandsontableOutput("table")
)

server <- function(input, output) {
    output$table <- renderRHandsontable({
        df <- data.frame(A = 1:5, B = 6:10, C = 11:15)
        rhandsontable(df)
    })
}

shinyApp(ui = ui, server = server)

This is the expected outcome:

I'm not sure how to proceed with the cell range selection part. I've looked through the rhandsontable documentation but couldn't find a straightforward way to do this.

Answer 1

Here is a way but it does not allow to enter an Excel cell range. Instead, you have to enter the cell range as rowStart:columnStart:rowEnd:columnEnd.

library(shiny)
library(rhandsontable)
library(htmlwidgets)

js <- c(
  "function(el, x) {",
  "  let hot = this.hot;",
  "  $('#cell_range').on('keyup', function(e) {",
  "    if(e.keyCode == 13) {",
  "      let cellRange = $('#cell_range').val();",
  "      let x = cellRange.split(':');",
  "      let rowStart    = parseInt(x[0]) - 1;",
  "      let columnStart = parseInt(x[1]) - 1;",
  "      let rowEnd      = parseInt(x[2]) - 1;",
  "      let columnEnd   = parseInt(x[3]) - 1;",
  "      hot.selectCells([[rowStart, columnStart, rowEnd, columnEnd]]);",
  "    }",
  "  });",
  "}"
)

ui <- fluidPage(
  textInput(
    "cell_range", 
    "Enter cell range (rowStart:columnStart:rowEnd:columnEnd) and press Enter:"
  ),
  rHandsontableOutput("table")
)

server <- function(input, output) {
  output$table <- renderRHandsontable({
    df <- data.frame(A = 1:5, B = 6:10, C = 11:15)
    rhandsontable(df) %>% onRender(js)
  })
}

shinyApp(ui = ui, server = server)

---

If you want to trigger the range selection with a button rather than a press on the Enter key, you can do:

js <- c(
  "function(el, x) {",
  "  let hot = this.hot;",
  "  $('#submit').on('click', function(e) {",
  "      let cellRange = $('#cell_range').val();",
  "      let x = cellRange.split(':');",
  "      let rowStart    = parseInt(x[0]) - 1;",
  "      let columnStart = parseInt(x[1]) - 1;",
  "      let rowEnd      = parseInt(x[2]) - 1;",
  "      let columnEnd   = parseInt(x[3]) - 1;",
  "      hot.selectCells([[rowStart, columnStart, rowEnd, columnEnd]]);",
  "  });",
  "}"
)

ui <- fluidPage(
  textInput(
    "cell_range", 
    "Enter cell range (rowStart:columnStart:rowEnd:columnEnd) and press the button:"
  ),
  actionButton("submit", "Submit"),
  rHandsontableOutput("table")
)