Update existing key file to version 2 with additional information and new features

Early work with the SOM data indicated that additional details about the data sets are required. To accomodate more detail, new additions to the key file are needed. The key_update_v2 function addresses desired changes to the key files. It is critical that information already entered into key files was not lost, so the new key file features had to be added to existing key file without information loss.

key_update_v2(
  sheetName,
  keyFileDownloadPath = "/home/shares/lter-som/key_file_download/",
  keyFileArchivePath = "/home/shares/lter-som/key_file_archive/",
  keyFileUploadPath = "/home/shares/lter-som/key_file_upload/",
  keyFileUpdateLogPath = "/home/shares/lter-som/key_file_update_log.csv"
)

Arguments

sheetName

name of or https path to key file in Google Drive to update with version 2 changes
keyFileDownloadPath

a intermediary directory to where the key file from Google Drive may be downloaded for importing into the R environment
keyFileArchivePath

directory to where csv versions of the location and profile tabs of the sheet to be updated will be archived
keyFileUploadPath

a intermediary directory to where the new key workbook from the R environment can be saved for eventual upload to Google Drive
keyFileUpdateLogPath

log file that catalogs the occurence of a succesful update of a key file. This file must exist in advance of running the update utility

Value

key_update_v2 actions and output include: (1) csv versions of the source key file location and profie tabs are written to a specified directory; (2) a new key file is created with desired version 2 modifications; if successful, the key file update action is written to an identified key file update log.

Note

key_update_v2 workflow:

  1. download project key file with googledrive

  2. load downloaded (now xlsx) into R with openxlsx::loadWorkbook (see note)

  3. additions to the workbook object as needed

  4. prescribe validations with openxlsx::dataValidation

  5. fix styling as needed with openxlsx::createSytle/addStyle

  6. write workbook back to file with openxlsx::write.xlsx (or saveWorkbook)

  7. upload workbook back to project directory to be followed by a re-homog

The workflow was getting hung up at step 2 in the workflow. The solution was to use the development branch of the openxlsx package (see https://github.com/awalker89/openxlsx/issues/386) devtools::install_github("awalker89/openxlsx") Rcpp package is a dependency

Key file version 2 new features include:

  1. several new metadata fields in the location tab ('time_series', 'gradient', 'experiments', 'control_id', 'number_treatments', 'merge_align', 'key_version').

  2. A new 'logical' column in the Units tab to facilitate a YES, NO drop-down option for several of the new metadata fields added to the location tab.

  3. Revised options in the Units tab for the list of drop-down options in the treatment rows of the Profile tab.

  4. Add pull-down menu for units field of lit_lig in location tab.

  5. Clarify meanings (Var_long field) of profle tab c_tot and soc (bulk, not fraction).

  6. Duplicate var values in the Location and Profile tabs are renamed with unique names.

Though specific to version 2 features, this workflow could be modified to implement new features for future versions.

This workflow defaults to being run on Aurora with default file paths set to that environment. These paths can be altered for the function to work outside of the Aurora environemnt, but the lter-som group should not do this as we need to document all changes, and a log file must exist.

To establish or reset a keyfile update log:

  create template for logging details of key file upversions (caution: this code
  will overwrite an existing log)

  keyFileUpdateLogPath <- '/home/shares/lter-som/key_file_update_log.csv'

  tibble(
    keyFileName = as.character(NA),
    keyFileDirectory = as.character(NA),
    timestamp = as.POSIXct(NA)
  ) %>%
  write_csv(path = keyFileUpdateLogPath, append = FALSE)

Examples