R/sheet_copy.R

In googlesheets4: Access Google Sheets using the Sheets API V4

#' Copy a (work)sheet
#'
#' Copies a (work)sheet, within its current (spread)Sheet or to another Sheet.
#'
#' @eval param_ss(pname = "from_ss")
#' @eval param_sheet(
#'   pname = "from_sheet",
#'   action = "copy",
#'   "Defaults to the first visible sheet."
#' )
#' @param to_ss The Sheet to copy *to*. Accepts all the same types of input as
#'   `from_ss`, which is also what this defaults to, if unspecified.
#' @param to_sheet Optional. Name of the new sheet, as a string. If you don't
#'   specify this, Google generates a name, along the lines of "Copy of blah".
#'   Note that sheet names must be unique within a Sheet, so if the automatic
#'   name would violate this, Google also de-duplicates it for you, meaning you
#'   could conceivably end up with "Copy of blah 2". If you have better ideas
#'   about sheet names, specify `to_sheet`.
#' @eval param_before_after("sheet")
#'
#' @return The receiving Sheet, `to_ ss`, as an instance of [`sheets_id`].
#' @export
#' @family worksheet functions
#' @seealso
#' If the copy happens within one Sheet, makes a `DuplicateSheetRequest`:
#'   * <https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#duplicatesheetrequest>
#'
#' If the copy is from one Sheet to another, wraps the
#' `spreadsheets.sheets/copyTo` endpoint:
#' * <https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.sheets/copyTo>
#'
#' and possibly makes a subsequent `UpdateSheetPropertiesRequest`:
#'   * <https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#UpdateSheetPropertiesRequest>
#'
#' @examplesIf gs4_has_token()
#' ss_aaa <- gs4_create(
#'   "sheet-copy-demo-aaa",
#'   sheets = list(mtcars = head(mtcars), chickwts = head(chickwts))
#' )
#'
#' # copy 'mtcars' sheet within existing Sheet, accept autogenerated name
#' ss_aaa %>%
#'   sheet_copy()
#'
#' # copy 'mtcars' sheet within existing Sheet
#' # specify new sheet's name and location
#' ss_aaa %>%
#'   sheet_copy(to_sheet = "mtcars-the-sequel", .after = 1)
#'
#' # make a second Sheet
#' ss_bbb <- gs4_create("sheet-copy-demo-bbb")
#'
#' # copy 'chickwts' sheet from first Sheet to second
#' # accept auto-generated name and default location
#' ss_aaa %>%
#'   sheet_copy("chickwts", to_ss = ss_bbb)
#'
#' # copy 'chickwts' sheet from first Sheet to second,
#' # WITH a specific name and into a specific location
#' ss_aaa %>%
#'   sheet_copy(
#'     "chickwts",
#'     to_ss = ss_bbb, to_sheet = "chicks-two", .before = 1
#'   )
#'
#' # clean up
#' gs4_find("sheet-copy-demo") %>%
#'   googledrive::drive_trash()
sheet_copy <- function(from_ss,
                       from_sheet = NULL,
                       to_ss = from_ss,
                       to_sheet = NULL,
                       .before = NULL,
                       .after = NULL) {
  from_ssid <- as_sheets_id(from_ss)
  to_ssid <- as_sheets_id(to_ss)
  maybe_sheet(from_sheet)

  if (identical(from_ssid, to_ssid)) {
    sheet_copy_internal(
      ssid = from_ssid,
      from_sheet = from_sheet,
      to_sheet = to_sheet,
      .before = .before,
      .after = .after
    )
  } else {
    sheet_copy_external(
      from_ssid = from_ssid,
      from_sheet = from_sheet,
      to_ssid = to_ssid,
      to_sheet = to_sheet,
      .before = .before,
      .after = .after
    )
  }
}

sheet_copy_internal <- function(ssid,
                                from_sheet = NULL,
                                to_sheet = NULL,
                                .before = NULL,
                                .after = NULL,
                                call = caller_env()) {
  maybe_string(to_sheet, call = call)
  x <- gs4_get(ssid)
  s <- lookup_sheet(from_sheet, sheets_df = x$sheets, call = call)
  gs4_bullets(c(v = "Duplicating sheet {.w_sheet {s$name}} in {.s_sheet {x$name}}."))

  index <- resolve_index(x$sheets, .before, .after, call = call)
  dup_request <- new(
    "DuplicateSheetRequest",
    sourceSheetId = s$id,
    insertSheetIndex = index,
    newSheetName = to_sheet
  )

  req <- request_generate(
    "sheets.spreadsheets.batchUpdate",
    params = list(
      spreadsheetId = ssid,
      requests = list(duplicateSheet = dup_request)
    )
  )
  resp_raw <- request_make(req)
  resp <- gargle::response_process(resp_raw)
  to_name <- pluck(resp, "replies", 1, "duplicateSheet", "properties", "title")
  gs4_bullets(c(v = "Copied as {.w_sheet {to_name}}."))

  invisible(ssid)
}

sheet_copy_external <- function(from_ssid,
                                from_sheet = NULL,
                                to_ssid,
                                to_sheet = NULL,
                                .before = NULL,
                                .after = NULL,
                                call = caller_env()) {
  from_x <- gs4_get(from_ssid)
  to_x <- gs4_get(to_ssid)
  maybe_string(to_sheet, "sheet_copy", call = call)

  from_s <- lookup_sheet(from_sheet, sheets_df = from_x$sheets, call = call)
  gs4_bullets(c(
    v = "Copying sheet {.w_sheet {from_s$name}} from \\
         {.s_sheet {from_x$name}} to {.s_sheet {to_x$name}}."
  ))

  req <- request_generate(
    "sheets.spreadsheets.sheets.copyTo",
    params = list(
      spreadsheetId = from_ssid,
      sheetId = from_s$id,
      destinationSpreadsheetId = as.character(to_ssid)
    )
  )
  resp_raw <- request_make(req)
  to_s <- gargle::response_process(resp_raw)

  # early exit if no need to relocate and/or rename copied sheet
  index <- resolve_index(to_x$sheets, .before, .after, call = call)
  if (is.null(index) && is.null(to_sheet)) {
    gs4_bullets(c(v = "Copied as {.w_sheet {to_s$title}}."))
    return(invisible(to_ssid))
  }

  sp <- new(
    "SheetProperties",
    sheetId = to_s$sheetId,
    title = to_sheet,
    index = index
  )

  update_req <- new(
    "UpdateSheetPropertiesRequest",
    properties = sp,
    fields = gargle::field_mask(sp)
  )

  req <- request_generate(
    "sheets.spreadsheets.batchUpdate",
    params = list(
      spreadsheetId = to_ssid,
      requests = list(updateSheetProperties = update_req)
    )
  )
  resp_raw <- request_make(req)
  gargle::response_process(resp_raw)
  gs4_bullets(c(v = "Copied as {.w_sheet {to_sheet}}."))

  invisible(to_ssid)
}