in shiny

shiny provides the most interesting use of handsontable.

normal stuff

  • config (handsontableOutput() + renderHandsontable())
  • observers + built-in hooks
  • set data for specific cells

fun stuff

you can do a lot of interesting stuff when you throw a little js into the mix.

any application that can be written in javascript, will eventually be written in javascript

— atwood’s law

click btn, set col values

sometimes you need to set all values in a column at once. common use case: “approve all”/“reject all” buttons for approval workflows.

the approach:

  • add buttons above/beside the table
  • use javascript to find the target column and update all rows

here’s an example with approval workflow:

app.R
library(shiny)
library(bslib)
library(handsontable)

addResourcePath(prefix = "static", directoryPath = "public")

# sample data
set.seed(123)
sample_data <- data.frame(
  id = 1:10,
  name = paste("Item", 1:10),
  category = sample(c("A", "B", "C"), 10, replace = TRUE),
  value = round(runif(10, 10, 100), 2),
  approval = sample(c("pending", "approved", "denied"), 10, replace = TRUE)
)

ui <- page(
  title = "bulk actions example",
  theme = bs_theme(version = 5L),
  lang = "en",

  tags$h3("bulk actions example"),

  tags$div(
    # we'll use the ".approvals" class to get the table
    class = "approvals mb-3",
    tags$button(
      class = "btn btn-success me-2 approve-all",
      type = "button",
      "Approve All"
    ),
    tags$button(
      class = "btn btn-danger reject-all",
      type = "button",
      "Reject All"
    ),
    handsontableOutput("table")
  ),

  tags$script(src = "/static/bulk-actions.js")
)

server <- function(input, output, session) {
  output$table <- renderHandsontable({
    handsontable(
      data = sample_data,
      adaptiveHeight = TRUE,
      rowHeaders = TRUE
    ) |>
      hot_col(
        col = "approval",
        type = "dropdown",
        source = c("pending", "approved", "denied")
      )
  })

  # detect changes in the table
  observeEvent(input$table, {
    updated_data <- hot_to_r(data = input$table)
    cat("Updated data:\n")
    print(updated_data)
  })
}

shinyApp(ui, server)

create ./public/bulk-actions.js:

./public/bulk-actions.js
/**
 * Bulk Actions for Handsontable
 */

/**
 * Main function to handle approve/reject all button clicks
 * @param {string} action - 'approve' or 'reject'
 */
function handleBulkAction(action) {
  const hot = findHandsontableInstance();
  if (!hot) return;

  const approvalColIndex = findApprovalColumnIndex(hot);
  if (approvalColIndex === -1) {
    console.warn("Approval column not found in handsontable");
    return;
  }

  const targetValue = action === "approve" ? "approved" : "denied";
  updateAllRows(hot, approvalColIndex, targetValue);
}

/**
 * Find handsontable instance in ".approvals" div
 * @returns {Object|null} - Handsontable instance or null
 */
function findHandsontableInstance() {
  const $container = $(".approvals").find(".handsontable");

  if (!$container) {
    console.warn("Handsontable instance not found");
    return null;
  }

  const $hot = $container[0].hot;
  if (!$hot) {
    console.warn("Cannot convert to hot");
    return null;
  }

  return $hot;
}

/**
 * Find the column index for the approval column
 * @param {Object} hot - Handsontable instance
 * @returns {number} - Column index or -1 if not found
 */
function findApprovalColumnIndex(hot) {
  const colHeaders = hot.getSettings().colHeaders;

  // Check column headers first
  if (colHeaders) {
    for (let i = 0; i < colHeaders.length; i++) {
      if (colHeaders[i]?.toLowerCase().includes("approval")) {
        return i;
      }
    }
  }

  // Fallback: check first row data for approval values
  const data = hot.getData();
  if (data.length > 0) {
    const approvalValues = ["pending", "approved", "denied"];
    for (let i = 0; i < data[0].length; i++) {
      const cellValue = hot.getDataAtCell(0, i);
      if (typeof cellValue === "string" && approvalValues.includes(cellValue)) {
        return i;
      }
    }
  }

  return -1;
}

/**
 * Update all rows to target status
 * @param {Object} hot - Handsontable instance
 * @param {number} colIndex - Column index for approval column
 * @param {string} targetValue - Target value ('approved' or 'denied')
 */
function updateAllRows(hot, colIndex, targetValue) {
  const rowCount = hot.countRows();
  const changes = [];

  for (let row = 0; row < rowCount; row++) {
    const currentValue = hot.getDataAtCell(row, colIndex);
    if (currentValue !== targetValue) {
      changes.push([row, colIndex, targetValue]);
    }
  }

  if (changes.length > 0) {
    hot.setDataAtCell(changes);
  }
}

/**
 * Initialize the bulk actions functionality
 */
$(document).ready(function () {
  $(document).on("click", ".approve-all", function (e) {
    e.preventDefault();
    e.stopPropagation();
    handleBulkAction("approve");
  });

  $(document).on("click", ".reject-all", function (e) {
    e.preventDefault();
    e.stopPropagation();
    handleBulkAction("reject");
  });
});

key points:

  • find handsontable instance in “.approvals” div
  • use hot.setDataAtCell(changes) with an array of changes for efficiency
  • find column index dynamically by header name or data values

undo/redo with visual feedback

  • cell highlighting during undo/redo: briefly flash to indicate changes.

conditional cell formatting

  • heat maps based on cell values

add margin totals

  • row/col totals