Load Google Sheet into field_labels

# packages
source(here::here("../apps/libs/db.R")) # defines variables: con, dir_gdrive
librarian::shelf(
  googlesheets4, knitr, purrr, readr)

# google sheet
gsheet <- "https://docs.google.com/spreadsheets/d/1ghM30pIdcsun7XWzLRKh4EilUplN60YdHayfiilPFwE/edit"

# no need to authenticate since shared for anyone to view
gs4_deauth() 

# read data
d <- read_sheet(gsheet)

write_csv(d, here("data/field_labels.csv"))

# check that variables are globally unique
stopifnot(d$table_field %>% duplicated() %>% sum() == 0)

# check that table.variable exists in the database
d <- d %>% 
  # filter(active) %>% 
  mutate(
    tbl_exists = map_lgl(
      table_field, function(tbl_fld){
        # tbl_fld = "ctdcast_bottle.t_deg_c"
        x <- strsplit(tbl_fld, ".", fixed=T)[[1]]
        tbl <- x[1]
        fld <- x[2]
        tbl %in% dbListTables(con, tbl) }),
    fld_exists = map_lgl(
      table_field, function(tbl_fld){
        # tbl_fld = "ctdcast_bottle.t_deg_c"
        x <- strsplit(tbl_fld, ".", fixed=T)[[1]]
        tbl <- x[1]
        fld <- x[2]
        if (!tbl %in% dbListTables(con, tbl))
          return(FALSE)
        fld %in% dbListFields(con, tbl) }))
d %>% 
  filter(active) %>% 
  pull(fld_exists) %>% 
  stopifnot(all(.))

# write to database
d %>% 
  select(-tbl_exists, -fld_exists) %>% 
  dbWriteTable(con, "field_labels", ., overwrite=T)

# show table
kable(d)
active category table_field plot_title plot_label plot_color color_palette tbl_exists fld_exists
TRUE Oceanographic ctd_bottles.t_degc Temperature Temperature (C) red Reds TRUE TRUE
TRUE Oceanographic ctd_bottles.salinity Salinity Salinity (practical salinity scale) purple Purples TRUE TRUE
FALSE Oceanographic ctd_bottles_dic.bottle_o2_ml_l Oxygen Content Oxygen (mL/L) blue NA FALSE FALSE
FALSE Oceanographic ctd_bottles_dic.bottle_o2_mmol_kg Oxygen Content Oxygen (µmol/kg) blue NA FALSE FALSE
FALSE Oceanographic ctd_bottles_dic.dic1 Dissolved Inorganic Carbon DIC (µmol/Kg) brown YlOrBr FALSE FALSE
FALSE Oceanographic ctd_bottles_dic.dic2 Dissolved Inorganic Carbon DIC (µmol/Kg) on a replicate sample brown YlOrBr FALSE FALSE
TRUE Oceanographic ctd_bottles.o2sat Oxygen Saturation Oxygen percent saturation blue Blues TRUE TRUE
FALSE Oceanographic larvae_counts.count Larvae # Larvae / Liter green Greens TRUE TRUE

Time for the chunk field_labels to run: 13.64 seconds