workbook_update.R

cat("Checking dependencies...\n")
required_packages <- list(
  openxlsx2 = "1.21", dplyr = "1.1.4", readr = "2.1.5", optparse = "1.7.5"
)

# Loop through the required packages
for (pkg_name in names(required_packages)) {
  # Check if the package is installed
  if (!requireNamespace(pkg_name, quietly = TRUE)) {
    cat(paste0("Installing ", pkg_name, "...", "\n"))
    install.packages(pkg_name)
  } else {
    # Check if the installed version meets the minimum requirement
    if (packageVersion(pkg_name) < required_packages[[pkg_name]]) {
      cat(paste0("Upgrading ", pkg_name, "...", "\n"))
      install.packages(pkg_name)
    } else {
      cat(paste0(pkg_name, " is already installed", "\n"))
    }
  }
}

cat("\n")

suppressMessages(library(openxlsx2))
suppressMessages(library(dplyr))
suppressMessages(library(readr))
suppressMessages(library(optparse))

# clear existing data in the environment if running this interactively
rm(list = ls())

# Parse command line options ####
parser <- OptionParser()
parser <- add_option(parser, c("-i", "--input"), default = "input",
                     help = paste("Relative path to the folder that contains",
                                  "the input files [default: %default]"))
parser <- add_option(parser, c("-o", "--output"), default = "output",
                     help = paste("Relative path to the folder that will",
                                  "contain the output files [default: %default]"))
parser <- add_option(parser, c("-r", "--ref"), default = "full_data",
                     help = paste("Relative path to the folder that contains",
                                  "the reference financial spreadsheets",
                                  "[default: %default]"))
args <- parse_args(parser)

# Read in full data ####
# these are the full data files downloaded from DataInsights
# make sure PROJECT is character to preserve leading zeros
# make sure account is numeric to avoid issues with workbook formulae

# check for files
# identify files with regex (might have "(14)" etc. in the filename because of
# multiple downloads)
bud_files <- list.files(args$ref, "BUD.*\\.csv", full.names = TRUE)
bud_files <- bud_files[order(file.info(bud_files)$mtime, decreasing = TRUE)]
com_files <- list.files(args$ref, "COM.*\\.csv", full.names = TRUE)
com_files <- com_files[order(file.info(com_files)$mtime, decreasing = TRUE)]
exp_files <- list.files(args$ref, "EXP.*\\.csv", full.names = TRUE)
exp_files <- exp_files[order(file.info(exp_files)$mtime, decreasing = TRUE)]
if (length(bud_files) == 0) {
  stop("No BUD.csv file found in ", args$ref)
} else if (length(bud_files) > 1) {
  warning("Multiple BUD.csv files found in ", args$ref,
          "; using the most recent one: ", bud_files[1])
}
if (length(com_files) == 0) {
  stop("No COM.csv file found in ", args$ref)
} else if (length(com_files) > 1) {
  warning("Multiple COM.csv files found in ", args$ref,
          "; using the most recent one: ", com_files[1])
}
if (length(exp_files) == 0) {
  stop("No EXP.csv file found in ", args$ref)
} else if (length(exp_files) > 1) {
  warning("Multiple EXP.csv files found in ", args$ref,
          "; using the most recent one: ", exp_files[1])
}

options(warn = -1)  # suppress parsing warnings
bud_enc <- guess_encoding(bud_files[1])$encoding[1]
bud_df <- suppressMessages(
  read_delim(bud_files[1],
             col_types = cols(
               PROJECT = col_character(),
               ACCOUNT = col_integer()
             ), locale = locale(encoding = bud_enc)))
com_enc <- guess_encoding(com_files[1])$encoding[1]
com_df <- suppressMessages(
  read_delim(com_files[1],
             col_types = cols(
               PROJECT = col_character(),
               ACCOUNT = col_integer()
             ), locale = locale(encoding = com_enc)))
exp_enc <- guess_encoding(exp_files[1])$encoding[1]
exp_df <- suppressMessages(
  read_delim(exp_files[1],
             col_types = cols(
               PROJECT = col_character(),
               ACCOUNT = col_integer()
             ), locale = locale(encoding = exp_enc)))
options(warn = 0)  # re-enable warnings

# Process Workbooks ####
if (!dir.exists(args$input)) {
  stop("The input folder '", args$input, "' does not exist")
}
# loop over all workbooks in a directory
to_process <- list.files(args$input, pattern = "\\.xls[x|m]$")
if (length(to_process) == 0) {
  stop("No .xlsx files found in ", args$input)
}

# create output directory if it doesn't exist
if (!dir.exists(args$output)) {
  dir.create(args$output, recursive = TRUE)
}

for (wb_file in to_process) {
  cat(paste("Processing workbook:", wb_file))

  ## Load workbook ####
  grant_wb <- wb_load(file.path(args$input, wb_file))
  # handle xlsm files without macros (https://github.com/JanMarvin/openxlsx2/issues/1452)
  # if extension is xlsm
  if (grepl("\\.xlsm$", wb_file, ignore.case = TRUE)) {
    grant_wb$Content_Types[grepl('<Override PartName="/xl/workbook.xml" ', grant_wb$Content_Types)] <-
      '<Override PartName="/xl/workbook.xml" ContentType="application/vnd.ms-excel.sheet.macroEnabled.main+xml"/>'
  }
  cat(".")

  # get the project ids from the summary sheet
  award_info <- wb_to_df(grant_wb, sheet = "Award Info", start_row = 2,
                         skip_empty_rows = TRUE, skip_hidden_cols = TRUE,
                         check_names = TRUE)
  project_ids <- award_info$Project.ID

  ## Subset financial data ####
  # need to make sure the number of rows matches the existing workbook
  n_bud <- wb_to_df(grant_wb, sheet = "Budget Data", skip_empty_rows = FALSE) %>%
    nrow()
  bud_sub <- bud_df %>%
    filter(PROJECT %in% project_ids) %>%
    add_row(PROJECT = rep(NA, n_bud - nrow(.)))

  n_com <- wb_to_df(grant_wb, sheet = "Commitments Data", skip_empty_rows = FALSE) %>%
    nrow()
  com_sub <- com_df %>%
    filter(PROJECT %in% project_ids) %>%
    add_row(PROJECT = rep(NA, n_com - nrow(.)))

  n_exp <- wb_to_df(grant_wb, sheet = "Expense Data", skip_empty_rows = FALSE) %>%
    nrow()
  exp_sub <- exp_df %>%
    filter(PROJECT %in% project_ids) %>%
    add_row(PROJECT = rep(NA, n_exp - nrow(.)))
  cat(".")

  ## Update data ####
  # write to the Budget Data sheet in the workbook
  grant_wb$add_data(sheet = "Budget Data", x = bud_sub, start_row = 2,
                    col_names = FALSE, na.strings = "")

  # write to the Commitments Data sheet in the workbook
  grant_wb$add_data(sheet = "Commitments Data", x = com_sub, start_row = 2,
                    col_names = FALSE, na.strings = "")

  # write to the Expense Data sheet in the workbook
  grant_wb$add_data(sheet = "Expense Data", x = exp_sub, start_row = 2,
                    col_names = FALSE, na.strings = "")
  cat(".")

  ## Write workbook ####
  wb_file_write <- gsub("\\d{1,2}\\.\\d{1,2}\\.\\d{2,4}",
                        format(Sys.Date(), "%m.%d.%y"),
                        wb_file)
  grant_wb$save(file.path(args$output, wb_file_write),
                overwrite = TRUE)
  cat("Done!\n")
}
cat("All done!\n")