Skip to contents

dataEdit renders an editable, spreadsheet-like grid built on the MIT-licensed 'Tabulator' JavaScript library. Edits are sent to R as cell-level changes rather than whole tables, which keeps large datasets responsive and makes undo/redo, change tracking and database commits possible.

Usage

dataEditUI(
  id,
  height = "65vh",
  toolbar = TRUE,
  toolbar_left = NULL,
  toolbar_right = NULL
)

dataEditServer(
  id,
  data = reactive(NULL),
  col_bind = NULL,
  col_edit = TRUE,
  col_options = NULL,
  col_stretch = FALSE,
  col_names = TRUE,
  col_readonly = NULL,
  col_hide = NULL,
  col_factor = FALSE,
  row_bind = NULL,
  row_edit = TRUE,
  row_index = reactive(NULL),
  rows = NULL,
  columns = NULL,
  quiet = FALSE,
  read_fun = "read.csv",
  read_args = NULL,
  track = TRUE,
  code_name = "data",
  autosave = FALSE,
  user = NULL,
  col_actions = TRUE,
  rules = NULL,
  formats = NULL,
  col_summary = TRUE,
  history = NULL,
  row_names = TRUE,
  col_widths = NULL,
  page_rows = NULL,
  page_cols = NULL,
  ...
)

Arguments

id

unique identifier for the module to prevent namespace clashes when making multiple calls to this shiny module.

height

height of the grid as a valid CSS unit, set to "65vh" by default.

toolbar

logical indicating whether the toolbar (undo/redo, columns, find, review changes, code), review panel and status bar should be shown around the grid, set to TRUE by default.

toolbar_left, toolbar_right

optional UI elements to add at the start or end of the toolbar (data_edit() adds its Open and Export menus here).

data

a reactive expression containing an array (e.g. data.frame, matrix or data.table), a database table (see db_table) or a vector indicating the dimensions of the array (e.g. c(10,10)) or column names to construct a new template for editing. If no data is supplied a template with 10 rows and columns will be generated for editing.

col_bind

additional columns to add to the data prior to loading into editor, can be either an array containing the new data, a vector containing the new column names for empty columns or a named list containing a vector for each new column.

col_edit

logical indicating whether columns can be added or removed, set to TRUE by default.

col_options

a list named by column with the input to use for each column:

  • c(TRUE, FALSE) or "checkbox": checkboxes;

  • a vector of values, e.g. c("A", "B", "C"): a dropdown menu. Give the values names to show the names instead, as for shiny::selectInput(): list("New South Wales" = "NSW", "Victoria" = "VIC");

  • "dropdown": a dropdown menu of the values in the column (or the levels of a factor);

  • "date" or "datetime": a date or date-time picker (the column is returned as Date or POSIXct);

  • "time": a time picker ("HH:MM" text);

  • "textarea": text over several lines;

  • "password": hidden text;

  • "url": web addresses with a button to open them;

  • "html": cells shown as HTML (links, line breaks, bold, images, ...) without scripts or styles; double-click to edit the HTML;

  • "colour" (or "color"): colours (R colour names such as "grey50" or hex codes such as "#1D4ED8") shown with a swatch and edited with a colour picker. Set the colours the picker offers with list(type = "colour", palette = c("red", "#1D4ED8")); any colour can still be typed or picked;

  • c(min = 0, max = 10, step = 1) (any of the three): numbers within limits, in steps of step counted from min;

  • "integer": whole numbers, returned as integers (with limits: list(type = "integer", min = 0, max = 10));

  • "slider": a slider and bar for numbers from 0 to 100;

  • "rating": a rating from 0 to 5 stars.

Set the limits of a slider with c(type = "slider", min = 0, max = 1, step = 0.05) and the number of stars of a rating with c(type = "rating", stars = 10) (a list() works too). Values outside the limits or off the steps are marked in the editor and cannot be entered. Columns that are not in the data are ignored (with a message), here and in col_readonly, col_hide and col_widths, so the same settings can be used for different data. Unlike earlier versions, using col_options no longer disables adding or removing columns.

col_stretch

logical indicating whether columns should be stretched to fill the full width of the display, set to FALSE by default.

col_names

logical indicating whether column names can be edited or a vector of column names that cannot be edited, set to TRUE by default to allow editing of column names.

col_readonly

names of columns that cannot be edited. Values and names of these columns are locked in the grid.

col_hide

names of columns to hide from the editor. Hidden columns will not be visible or editable but will be retained in the data returned by the module.

col_factor

logical indicating whether character columns should be converted to factors prior to returning the edited data, set to FALSE by default. Factor columns in the data are always returned as factors with their levels; new values become new levels after the existing ones.

row_bind

additional rows to add to the data prior to loading into editor, can be either an array containing the new data, a vector containing the new row names for empty rows or a named list containing a vector for each new column.

row_edit

logical indicating whether rows can be added or removed, set to TRUE by default.

row_index

retained for backwards compatibility and no longer required. Rows now carry stable identifiers, so new rows are numbered automatically.

rows

a reactive expression returning the row indices (positions in the data returned by this module) to display, or NULL to display all rows. Edits made to the displayed subset are applied directly to the full data.

columns

a reactive expression returning the names of the columns to display, or NULL to display all columns.

quiet

logical to suppress messages and notifications.

read_fun

name of the function to use to read in the data when a file is selected, set to read.csv by default.

read_args

a named list of additional arguments to pass to read_fun when reading in files.

track

TRUE (the default) highlights cells that have been edited or added with a blue tint and border. Give a CSS colour (e.g. "#DC2626" or "red") to use another colour, or FALSE to turn the highlighting off.

code_name

name of the data used in the code shown in the review panel, set to "data" by default. Can also be a reactive expression returning the name. Two names, e.g. c("mtcars", "cars"), give the data the code starts from and the name the code assigns the result to.

autosave

logical indicating whether changes should be saved as a draft while editing, so that they can be restored if the app is closed before they are saved. When the same data is opened again by the same user, they are asked whether to restore the changes. Set to FALSE by default. Drafts are kept in tools::R_user_dir("DataEditR", "data") (or the folder set with options(DataEditR.drafts = )) and are removed when the changes are committed or after 30 days.

user

name of the person editing, recorded in the change log (see dataEditLog()). By default the user signed in to the app (session$user), the DataEditR.user option or the system user name.

col_actions

logical indicating whether the column menu offers actions that change a whole column: formulas, find and replace, recoding values, changing the type and sorting the rows. They are shown in the generated code as one dplyr verb each. Set to TRUE by default.

rules

a list of rules the data should follow, each a one-sided formula that is TRUE for valid rows, named with the message to show, e.g. list("End must be after the start" = ~ end >= start). Cells that break a rule are marked and listed in the Problems tab (see dataEditProblems()). Users can add rules to a column from its menu (Rules...).

formats

conditional formats: how to colour cells, without changing the data. A list of formats, each a one-sided formula or a list:

  • list(~ mpg > 25, fill = "#DCFCE7", color = "#166534", bold = TRUE): cells where the formula is TRUE get the fill, text colour and/or bold text (a formula alone gets a yellow fill). The cells of the columns the formula uses are coloured; give columns to colour other columns, or columns = "all" for whole rows;

  • list(scale = c("white", "#EF4444"), columns = "hp"): a colour scale shading numbers from the lowest to the highest value (two colours, or three for low, middle and high). limits = c(0, 300) fixes the values of the end colours and mid the middle one.

Names of the list are labels. When several formats colour a cell, the later one wins. Users can add formats to a column from its menu (Conditional formatting...).

col_summary

logical indicating whether a summary of each column (a histogram or the most common values, the share of missing values and the number of distinct values) is shown under its header, set to TRUE by default. Clicking a bar shows only those rows; the summaries can be hidden with the toolbar button.

history

the change log of earlier editing sessions to list in the History tab under the changes of this session: a data frame with the columns of dataEditLog() (at least step and action), or a reactive expression returning one. For example the entries returned by dataEditCommit() and written to a database table, read back with DBI::dbReadTable(). The session column tells the sessions apart (entries of the current session are left out). data_edit(log = "file.csv") reads the earlier sessions of the data from its file.

row_names

logical indicating whether the row names of the data are shown in the editor, set to TRUE by default. When FALSE the row names are kept out of sight but still returned; rows added in the editor are named by their row number.

col_widths

widths of the columns in pixels: one number for every column, or numbers named by column, e.g. c(notes = 400, id = 60). Other columns are as wide as their values, up to 320 pixels; drag the edge of a column header to resize it.

page_rows, page_cols

number of rows and columns to show at a time, or NULL (the default) to show them all. The status bar has buttons to move between the pages, and Pages in the status bar changes the page sizes while editing. Pages only change what is on screen: filters, find, Done and exports cover every page.

...

not in use.

Value

reactive expression containing the edited data. Pass it to dataEditCommit() to save the changes (e.g. write them to a database) and to dataEditChanges() for a summary of the changes that have not been saved yet. The reactive also carries the attribute "view", a reactive returning only the rows and columns on display.

Author

Dillon Hammill, dillon.hammill21@gmail.com

Examples

if (interactive()) {
  library(shiny)

  ui <- fluidPage(
    dataInputUI("input-1"),
    dataOutputUI("output-1"),
    dataEditUI("edit-1")
  )

  server <- function(input, output, session) {
    data_to_edit <- dataInputServer("input-1")
    data_edit <- dataEditServer("edit-1",
      data = data_to_edit
    )
    dataOutputServer("output-1",
      data = data_edit
    )
  }

  shinyApp(ui, server)
}