Function to automatise data extraction for RedCap and KoboToolbox (API)

[loremerdrignac]

On a weekly basis, data extraction from RedCp and KoboToolBox is done manually whereas an API exists and could be called from R. Need to use .Renviron to store RedCap token (See Jenny for more tech information available on RedCap and KoboToolBox website)

[loremerdrignac]

Useful website: https://blog.asitavsen.com/post/how-to-download-kobotoolbox-data-in-r/ https://campus.datacamp.com/courses/tabular-data-at-car/accessing-redcap-from-r?ex=5 Instructions for Renvironment.zip

[epi-jhd]

James Humphreys has set up the process to do this from RedCap in the WHO/EURO project. tokens currently stored on his computer. Next challenge is to devise secure way to store and share tokens.

[EPI-JHS]

Apologies I hadn't seen this issue previously. I have a (relatively) simple function for automated queries to RedCAP. It's currently set to my requirements, though I can generalise this script for use in other projects/Epiuf package:

Token for RedCAP is passed in call to function (see below for Token assignment):

runApi <- function(token) {
  url <- "CENSORED REDCAP API URL"
  ## Parameters for the request - here you can refine which records you're interested in
  formData <- list("token"=token,
                   content='record',
                   action='export',
                   format='csv',
                   type='flat',
                   csvDelimiter='',
                   rawOrLabel='raw',
                   rawOrLabelHeaders='raw',
                   exportCheckboxLabel='true',
                   exportSurveyFields='true',
                   exportDataAccessGroups='true',
                   returnFormat='csv'
  )
  ## make the request using httr and POST method
  response <- httr::POST(url, body = formData, encode = "form")
  ## convert the httr response content and assign it to the name 'df'
  REDCapExtract <- httr::content(response)
  dataLocation <- pathToFile("SOURCES", paste0(countryEq,"/", yearDate ," ", month_number))
  fileName <- paste0(dataLocation,"CENSORED PROJECT FILE PATH",Sys.Date(),"_",strftime(Sys.time(), "%H%M"),".csv")
  ## download the r dataframe to CSV
  write.csv(REDCapExtract, fileName, row.names=FALSE)
}

Token set in a function below. It searches the project folders for an existing RedCAP extract, then looks for a local config I have called 'QueryRCAP_GENERALISED.R'. That script loops through the projects country names. Depending on the one set in a previous script, it will assign the correct API Key as Token. If no local config exists, it prompts the user for an API key:

API_request <- function(updateDate, country_code) {

  updateYearMonth <- strsplit(updateDate, "-")[[1]][1:2]
  updateYearMonth <- gsub("_", "", updateYearMonth)
  formattedUpdateDate <- paste(updateYearMonth, collapse = " ")

  source_path <- pathToFile("SOURCES", paste0(country_code,"/",formattedUpdateDate))
  filenames <- list.files(path = source_path)

  # Check if any of the filenames contain the updateDate
  file_exists <- any(grepl(updateDate, filenames))

  if (file_exists) {
    print("File exists. No API call was made.")
  } else if (file.exists("C:/Users/james/Documents/Dev/R/GIT-R/local_configs/EuroConfigs/QueryRCAP_GENERALISED.R")) {

    source("C:/Users/james/Documents/Dev/R/GIT-R/local_configs/EuroConfigs/QueryRCAP_GENERALISED.R")
  } else {
    tokenValue <- readline(prompt = "Please enter your API token for the survey of interest: ")
    responseDataframe <- runApi(tokenValue)
  }
}

Note on automated extraction: this can be achieved quite easily by adapting the below to set Country/Project, Schedule (weekday, regularity of extraction etc), and file path for API keys. Please let me know and I can adapt this (or otherwise await the August meeting)

[loremerdrignac]

2023-09-14 Meeting with JHS, JHD, GDS, LMC

You will find below a quick summary of our conclusion:

• Short term: We identified a quick and easy option that would provide a minimal security to the current way of storing and using the API credentials. The idea is to use the 'keyring' R package which allow to store an encrypted version of the token before it uses it in the REDCap API.
  • The token could be initially stored in an .Renviron file (locally) but then an encrypted version is stored by the 'keyring' R package on the user's machine. It is only this encrypted local version of the token that is then used in the scripts. There is no trace of the token "in clear"/not encrypted neither in the scripts, nor GitHub.
  • An example on how the 'keyring' package was used in ArboRisk project is attached and can be adapted to the REDCap data extraction. Please James, do not hesitate to come back to me for any kind of clarification in the script!!
• Long term: We agreed that the best option for the team to investigate is the use of GitHub secrets to store the token securely, and then GitHub actions to automatically download the data on a weekly basis and store it on Epiconcept side. This GitHub action could also be triggered directly from R whenever we need to update the REDCap data extraction.

keyring_example.zip

[loremerdrignac]

Note: Once the agreed solution will be integrated in James' script above, we will include it in the epiuf package to share with the other projects using REDCap.

[EPI-JHS]

Update 12/10/23:

• Created a generalised 'AccessREDCap' function for creating, storing, and retrieving REDCap secrets before making API calls and storing the data frame response