--- title: "Using DataEditR in Shiny apps" output: rmarkdown::html_vignette: self_contained: false vignette: > %\VignetteIndexEntry{Using DataEditR in Shiny apps} %\VignetteEngine{knitr::rmarkdown} %\VignetteEncoding{UTF-8} --- ```{r setup, include = FALSE} knitr::opts_chunk$set( collapse = TRUE, comment = "#>", eval = FALSE ) screenshot <- function(file) { path <- file.path("images", file) if (!file.exists(path)) { path <- paste0( "https://raw.githubusercontent.com/DillonHammill/DataEditR/master/", "vignettes/images/", file ) } knitr::include_graphics(path) } ``` `data_edit()` is a shiny gadget built from a set of shiny modules. The same modules can be used to add an editable table to your own shiny applications. This article shows how to use the `dataEdit` module and how it fits together with the other modules that ship with **DataEditR**. ## 1. The dataEdit module Every shiny module has two parts: a user interface function to place in the UI of your app, and a server function to call in your server. For the data editor these are `dataEditUI()` and `dataEditServer()`. Give both the same `id`: ```{r} library(shiny) library(DataEditR) ui <- fluidPage( titlePanel("Car data"), dataEditUI("cars") ) server <- function(input, output, session) { cars <- dataEditServer("cars", data = reactive(mtcars)) } shinyApp(ui, server) ``` | Function | Use it to | |---------------------|-------------------------------------------------------------------| | `dataEditUI()` | place the editor (grid, toolbar, review panel, status bar) in the UI | | `dataEditServer()` | load data into the editor and get the edited data back as a reactive | | `dataEditChanges()` | count the changes that have not been saved yet | | `dataEditCommit()` | save the changes (write them to a database, or start afresh from the current data) | `dataEditUI()` adds the grid together with the toolbar (undo/redo, columns, filter, find, review changes, code and keyboard shortcuts), the review panel and the status bar. The grid supports everything described in `vignette("DataEditR")`: editing, range selection, copy and paste, fill down, adding and removing rows and columns, header filters and the Filters panel, change tracking and the review panel. The editor uses Bootstrap 5, so use a Bootstrap 5 page such as `bslib::page_fluid()` (or `fluidPage(theme = bslib::bs_theme(version = 5))`) for the menus and dialogs to look their best. ### 1.1 Layout options * `height` sets the height of the grid (`"65vh"` by default); * `toolbar = FALSE` shows only the grid, without the toolbar, review panel and status bar; * `toolbar_left` and `toolbar_right` add your own controls at the start or end of the toolbar. `data_edit()` uses these slots for its `Open` and `Export` menus. ```{r} ui <- bslib::page_fluid( dataEditUI( "cars", height = "500px", toolbar_right = downloadButton("save", "Save", class = "btn-sm") ) ) ``` ### 1.2 Server options `dataEditServer()` accepts the same editing options as `data_edit()`, including `col_bind`, `col_edit`, `col_options`, `col_stretch`, `col_names`, `col_readonly`, `col_hide`, `col_factor`, `row_bind`, `row_edit`, `read_fun`, `read_args`, `quiet` and `track`. The `data` argument can be a reactive expression or a fixed value, and can contain: * a data.frame, tibble, data.table or matrix; * the dimensions (e.g. `c(10, 5)`) or column names of an empty template; * the name of a file to read with `read_fun`; * a `dbplyr` lazy table or `db_table()` (see `vignette("databases")`). Whenever `data` changes the editor loads the new data and its history is cleared. `code_name` sets the name of the data in the code shown in the `Code` tab. This is also how the editor is updated from the server, for example to go back to an empty template after each record has been saved: ```{r} server <- function(input, output, session) { template <- data.frame(name = NA_character_, age = NA_integer_) reset <- reactiveVal(0) record <- dataEditServer("record", data = reactive({ reset() template }), col_options = list(age = "integer")) observeEvent(input$add, { DBI::dbAppendTable(con, "people", record()) # load the template again reset(reset() + 1) }) } ``` The settings such as `col_options`, `col_readonly`, `col_hide` and `col_widths` stay the same for new data; columns they name that are not in the new data are ignored. ### 1.3 The returned data `dataEditServer()` returns a reactive expression containing the edited data, which updates after every change. Use it like any other reactive: ```{r} server <- function(input, output, session) { cars <- dataEditServer("cars", data = reactive(mtcars), track = TRUE) output$save <- downloadHandler( filename = "cars.csv", content = function(file) write.csv(cars(), file) ) } ``` The reactive also carries a `"view"` attribute: `attr(cars, "view")` is a reactive returning only the rows and columns on screen (after header filters, hidden columns and the `rows`/`columns` arguments below). ### 1.4 Showing part of the data The `rows` and `columns` arguments restrict what the grid shows without removing anything from the data. They accept fixed values or reactive expressions returning the row indices and column names to display (`NULL` shows everything). Edits made to the visible part are applied directly to the complete data, which is what the returned reactive contains. ```{r} ui <- bslib::page_fluid( checkboxGroupInput("cyl", "Cylinders", choices = c(4, 6, 8), selected = c(4, 6, 8), inline = TRUE), dataEditUI("cars") ) server <- function(input, output, session) { cars <- dataEditServer( "cars", data = reactive(mtcars), rows = reactive(which(mtcars$cyl %in% input$cyl)), columns = c("mpg", "cyl", "hp", "wt") ) } ``` ### 1.5 Saving changes Pass the reactive returned by `dataEditServer()` to `dataEditCommit()` to save the changes, typically when the user presses a *Save* button, and to `dataEditChanges()` for a summary of the changes that have not been saved yet: * for **database tables** the changes are written to the database in a single transaction, exactly as `data_edit()` does when you press *Done*. The rows are then read back from the database, so new rows show the keys the database generated and the editor starts afresh; * for **data in your R session** nothing is written, but the current data becomes the new starting point: the review panel, change highlights and the code in the `Code` tab start again from the saved data. Write the data wherever it needs to go in the same observer. `dataEditChanges()` returns the number of rows `updated`, `inserted` and `deleted`, the number of `columns` added, removed or renamed, the number of edited `cells` and the `total` number of changes. Like any reactive it updates as the data is edited. Supply `rules` to `dataEditServer()` (formulas named with their messages, see `vignette("DataEditR")`) to check the data as it is entered; `dataEditProblems()` returns the rows that break them, one per rule and row, so you can keep the *Save* button disabled until they are fixed: ```{r} observe({ ch <- dataEditChanges(cars) problems <- dataEditProblems(cars) shinyjs::toggleState("save", !is.null(ch) && ch$total > 0 && nrow(problems) == 0) }) ``` Column actions (formulas, find and replace, recoding, type changes and sorting) are offered in the column menu; formulas can only use column names and common functions, so users cannot run other R code on your server. Set `col_actions = FALSE` to remove them. Column summaries are worked out on the server from all rows, only for the columns that changed and not while they are hidden; set `col_summary = FALSE` to leave them out. `dataEditCommit()` returns the same counts and the `log` of the changes it saved: one row per changed cell (or added, removed or renamed row and column) with the `time`, the `user`, `how` the change was made, the `row`, `column`, `old` and `new` values and the `note` the user added in the `History` tab. Write it next to the data to keep a record of who changed what. `dataEditLog()` returns the log of the whole session. The user is taken from `session$user` when the app runs on a server that signs users in (such as Posit Connect), and can be set with the `user` argument of `dataEditServer()`. The log has a `session` column that names the editing session. To list earlier sessions in the `History` tab, pass the log you kept to the `history` argument of `dataEditServer()` (a data frame or a reactive returning one); entries of the current session are left out: ```{r, eval = FALSE} history <- reactiveVal(DBI::dbReadTable(con, "cars_log")) cars <- dataEditServer("cars", data = tbl(con, "cars"), history = history) observeEvent(input$save, { res <- dataEditCommit(cars) DBI::dbWriteTable(con, "cars_log", res$log, append = TRUE) history(DBI::dbReadTable(con, "cars_log")) }) ``` Comments that users leave on cells (right-click a cell, or `Shift+F2`) are returned with the data as `attr(x, "comments")`: a data frame with the `row`, `column`, `comment`, `user` and `time`. Pass data with that attribute to show its comments. ```{r} library(shiny) library(DataEditR) con <- DBI::dbConnect(RSQLite::SQLite(), "cars.sqlite") ui <- bslib::page_fluid( shinyjs::useShinyjs(), dataEditUI( "cars", toolbar_right = actionButton("save", "Save", class = "btn-primary") ) ) server <- function(input, output, session) { cars <- dataEditServer("cars", data = dplyr::tbl(con, "cars")) # only enable the button when there is something to save observe({ ch <- dataEditChanges(cars) shinyjs::toggleState("save", !is.null(ch) && ch$total > 0) }) # ask before writing to the database observeEvent(input$save, { ch <- dataEditChanges(cars) showModal(modalDialog( title = "Save changes?", sprintf("%d row(s) updated, %d added and %d removed.", ch$updated, ch$inserted, ch$deleted), footer = tagList(modalButton("Keep editing"), actionButton("confirm", "Save", class = "btn-primary")) )) }) observeEvent(input$confirm, { removeModal() res <- tryCatch(dataEditCommit(cars), editr_conflict = function(e) e) if (inherits(res, "editr_conflict")) { showNotification(conditionMessage(res), type = "error", duration = NULL) } else { # keep a record of who changed what next to the data if (nrow(res$log) > 0) { DBI::dbWriteTable(con, "cars_log", res$log, append = TRUE) } showNotification("Changes saved") } }) } shinyApp(ui, server) ``` The *Save* button sits at the end of the editor's toolbar and is only enabled when there are unsaved changes. Pressing it shows a summary of the changes from `dataEditChanges()`: ```{r, echo = FALSE, fig.align = "center", out.width = "100%", eval = TRUE} screenshot("shiny-save-confirm.png") ``` After the changes are written, the rows are read back from the database: the removed row is gone, the edited values are saved and the new row has the `id` the database generated. The button is disabled again until the next edit. ```{r, echo = FALSE, fig.align = "center", out.width = "100%", eval = TRUE} screenshot("shiny-save-done.png") ``` If a row was changed or removed by someone else since it was loaded, `dataEditCommit()` rolls back the whole transaction, writes nothing and raises an error of class `"editr_conflict"`. The changes stay in the editor, so the user can review them and try again. Other errors (e.g. a lost connection) are raised as usual; wrap the call in `tryCatch()` to show them to the user. For data frames, save the edited data in the same observer: ```{r} observeEvent(input$save, { write.csv(cars(), "cars.csv", row.names = FALSE) dataEditCommit(cars) }) ``` ## 2. The other modules Earlier versions of **DataEditR** combined several modules inside `data_edit()`. These are still exported and can be combined with `dataEdit` in your own apps. They use `shinyjs`, which their UI functions now load themselves, so `hide = TRUE` works without adding `shinyjs::useShinyjs()` to your UI. | Module | What it does | |--------------|-------------------------------------------------------------------------------| | `dataInput` | text box to load an R object or template by name, and a file upload | | `dataOutput` | download button that writes the data to a file with any writing function | | `dataFilter` | dialog to build row filters (equal, greater than, between, contains, ...) | | `dataSelect` | dialog to choose columns | | `dataSync` | merges an edited subset back into the complete data | `dataFilterServer()` returns a list with the filtered `data` and the matching `rows`, and `dataSelectServer()` a list with the selected `data`, column indices (`columns`) and column `names`. These can be passed straight to the `rows` and `columns` arguments of `dataEditServer()`, so there is no need for `dataSync`: ```{r} library(shiny) library(DataEditR) ui <- bslib::page_fluid( dataInputUI("input"), dataFilterUI("filter"), dataSelectUI("select"), dataOutputUI("output"), dataEditUI("edit") ) server <- function(input, output, session) { data_input <- dataInputServer("input", data = "mtcars") data_filter <- dataFilterServer("filter", data = data_input) data_select <- dataSelectServer("select", data = data_input) data_edit <- dataEditServer( "edit", data = data_input, rows = data_filter$rows, columns = data_select$names ) dataOutputServer("output", data = data_edit) } shinyApp(ui, server) ``` By default `dataFilterServer()` and `dataSelectServer()` reset whenever their `data` changes. Supply a different trigger to `reset` (e.g. the data input rather than the edited data) to keep filters active while the data is being edited. `dataSyncServer()` is still available for apps that edit a copy of a subset: give it the complete `data`, the edited `data_subset` and the `rows` and `columns` of the subset, and set `auto = TRUE` to merge changes as they happen.