Download a Google Sheet via regular web-browser in R

download_gsheet.R

#' Download a Google Sheet via regular web-browser
#'
#' Opens the sheet's CSV export URL in the default browser, waits for the CSV
#' file to appear in the downloads directory, and loads/returns the data.
#'
#' This function is a very hacky ducktape and binder-twine solution for Google
#' Sheets that require browser-based authentication.
#' **NOTE:** For public sheets, just load the export URL directly:
#' `readr::read_csv(
#'   "https://docs.google.com/spreadsheets/d/{id}/export?format=csv&gid={gid}"
#' )`
#'
#' @details
#' The browser should be configured to download files automatically without
#' prompting for a save location.
#'
#' A browser tab is opened for each download. When downloading multiple sheets,
#' open a dedicated browser window first.
#'
#' Don't download other CSV files to `downloads_dir` while this function is
#' running.
#'
#' @param sheet_id Character scalar. Google Sheets document ID.
#' @param gid Numeric or character sheet identifier. Defaults to `0`, which is
#'   typically the first worksheet.
#' @param downloads_dir Directory to watch for the downloaded CSV file.
#'   Defaults to the user's Downloads folder.
#' @param timeout_sec Number of seconds to wait for the download to complete.
#'
#' @return A tibble containing the contents of the downloaded worksheet.
#'         This includes 4 columns at the end with sheet metadata.
#'
#' @export
#'
#' @examples
#' # Download the data from the google sheet at:
#' # https://docs.google.com/spreadsheets/d/1qefm0mlQdqTlu5GPeT52ONlh4MQzoaoZRbxPckGf-XY/edit?gid=0#gid=0
#' sheet <- download_google_sheet_csv(
#'   sheet_id = "1qefm0mlQdqTlu5GPeT52ONlh4MQzoaoZRbxPckGf-XY"
#' )
#'
#' sheets <- c(
#'   "1qefm0mlQdqTlu5GPeT52ONlh4MQzoaoZRbxPckGf-XY",
#'   "1YHNxjWjCJYw8NWMwHHY_nlKJyzu85G_ZMVmd2Y8BPlk",
#'   "1MFa0ZbstQV9z2nAvGwrGnWuB2npPV3BFbdh_Ql39iX0"
#' )
#'
#' data <- purrr::map(
#'   sheets,
#'   download_google_sheet_csv
#' ) |>
#'   dplyr::bind_rows()
download_google_sheet_csv <- function(
  sheet_id, # Google sheets id from url
  gid = 0, # Usually 0, check the url for gid=num
  downloads_dir = file.path(Sys.getenv("USERPROFILE"), "Downloads"),
  timeout_sec = 60
) {
  csv_url <- sprintf(
    "https://docs.google.com/spreadsheets/d/%s/export?format=csv&gid=%s",
    sheet_id,
    gid
  )

  before <- list.files(
    downloads_dir,
    pattern = "\\.csv$",
    full.names = TRUE
  )

  message("Downloading ", csv_url)
  browseURL(csv_url)
  deadline <- Sys.time() + timeout_sec

  repeat {
    Sys.sleep(0.25)

    after <- list.files(
      downloads_dir,
      pattern = "\\.csv$",
      full.names = TRUE
    )

    new_files <- setdiff(after, before)

    if (length(new_files) > 0) {
      file_info <- file.info(new_files)

      newest_file <- rownames(file_info)[which.max(file_info$mtime)]

      size1 <- file.info(newest_file)$size
      Sys.sleep(3)
      size2 <- file.info(newest_file)$size

      if (size1 == size2) {
        data <- readr::read_csv(
          newest_file,
          show_col_types = FALSE
        )

        parts <- strsplit(
          tools::file_path_sans_ext(basename(newest_file)),
          " - ",
          fixed = TRUE
        )[[1]]

        data <- data |>
          dplyr::mutate(
            # prepend period so they get added to the end
            .sheet_id = sheet_id,
            .gid = gid,
            .sheet_name = if (length(parts) >= 2) parts[1] else NA_character_,
            .worksheet_name = if (length(parts) >= 2)
              sub("\\s*\\(.*$", "", parts[2]) else NA_character_
          )
        message("Done.")
        return(data)
      }
    }

    if (Sys.time() > deadline) {
      stop("Timed out waiting for CSV download.")
    }
  }
}