read and write text color and background color

[jakejh]

Hello, I love this package. To extend its capabilities, I've written a couple rough functions to get and set the background color. Would there be any interest in a pull request including functions something like range_write_color() and range_read_color()? Thanks.

[jennybc]

I have done this myself, anecdotally, but have never put it in the package, because I haven't sorted out what the interface should be.

So, while I'm happy to get a PR to see how you decided to approach this, I can't really predict how likely it is that I would merge it, given how up in the air the interface is.

That being said, it's always helpful to see a working implementation and to see how someone else thinks it should work, so if making the PR on those terms feels OK, then please go for it!

[jakejh]

Ok, then what about something like this?

range_read_cells_background <- function(
    ss, sheet = NULL, range = NULL, skip = 0, n_max = Inf, discard_empty = TRUE) {

  oot <- range_read_cells(ss, sheet, range, skip, n_max, 'full', discard_empty)

  out <- list()
  for (p in c('red', 'green', 'blue')) {
    y <- map(oot$cell, ~ .x$effectiveFormat$backgroundColorStyle$rgbColor[[p]])
    out[[p]] <- map_dbl(y, ~ if (is.null(.x)) 0 else .x)
  }

  out <- oot %>%
    dplyr::select(!cell) %>%
    cbind(tibble::as_tibble(out))
  out
}

Sorry if it's not the best tidyverse code, I typically use data.table.

The function just adds a few columns based on cell. The output of this function could serve as input to a function to write background color.

To get text formatting you could imagine a separate but similar function, or renaming this function and adding an argument like property that could take values "background" or "text".

[jennybc]

Thanks for sharing.

To get text formatting you could imagine a separate but similar function ...

Yeah, so this is exactly the interface question. There are various styling/formatting matters you might want to surface. I don't think one function per property makes sense, so then which alternative does? The design of this interface is really the heart of the matter.

[jakejh]

Right, so here's one option, which returns only particular components. Another would be to just return everything and do some minor renaming, or to allow group to have more than one element. Another option would be to incorporate this functionality into range_read_cells() using an argument called something like unnest.

range_read_cells_format <- function(
    ss, sheet = NULL, range = NULL, skip = 0, n_max = Inf,
    discard_empty = TRUE, group = c('background', 'text', 'other')) {

  group = match.arg(group)
  oot <- range_read_cells(ss, sheet, range, skip, n_max, 'full', discard_empty)

  if (group == 'background') {
    # for some colors, one or more components may be missing from the list
    out <- list()
    for (p in c('red', 'green', 'blue')) {
      y <- map(
        oot$cell, ~ .x$effectiveFormat$backgroundColorStyle$rgbColor[[p]])
      out[[p]] <- map_dbl(y, ~ if (is.null(.x)) 0 else .x) # RStudio no like
    }
    out <- tibble::as_tibble(out)

  } else if (group == 'text') {
    cols <- c(
      'fontFamily', 'fontSize', 'bold', 'italic', 'strikethrough', 'underline')
    out1 <- map_dfr(oot$cell, ~ .x$effectiveFormat$textFormat[cols])

    out2 <- list()
    for (p in c('red', 'green', 'blue')) {
      y <- map(
        oot$cell,
        ~ .x$effectiveFormat$textFormat$foregroundColorStyle$rgbColor[[p]])
      out2[[p]] <- map_dbl(y, ~ if (is.null(.x)) 0 else .x) # RStudio no like
    }
    out2 <- tibble::as_tibble(out2)
    out <- cbind(out1, out2)

  } else {
    out1 <- map_dfr(oot$cell, ~ .x$effectiveFormat$padding)
    # colnames(out1) = paste0('padding_', colnames(out1))

    cols = c('horizontalAlignment', 'verticalAlignment',
             'wrapStrategy', 'hyperlinkDisplayType')
    out2 <- map_dfr(oot$cell, ~ .x$effectiveFormat[cols])
    out <- cbind(out1, out2)

  }

  out <- oot %>%
    dplyr::select(!cell) %>%
    cbind(out)
  out
}

d1 <- range_read_cells_format(ss, sheet, range, group = 'background')
d2 <- range_read_cells_format(ss, sheet, range, group = 'text')
d3 <- range_read_cells_format(ss, sheet, range, group = 'other')

[Pancreas-Pratik]

@jakejh Thank you for sharing this. This is helpful for making a way to streamline biological image analysis and visualization for our lab (Rosenberg-Nakanishi Lab) in UConn Health. I am using Google Sheets to make a way for lab members to specify which markers (and what colors they would like to see the "multi-fluorescent-type" images in). Basically, I am downloading the sheet I automatically upload after processing and use the coloring "red, green, blue" specified in the Google Sheet to assign colors to different biological markers.

Using this now, would you happen to have already made one to write formatting, by any chance range_write_cells_format()? If not, honestly, I will probably just write something just to write background color and foreground text color.

By the way, I read through your lab page @jakejh, very interesting research!! I/we have lots of similar interests in the Center for Molecular Oncology in UConn Health (diet-nutrition, circadian rhythm, electronic health records, etc...)... myself, I love the gene expression/mechanism stuff, obviously (since I am doing data analysis in bioinformatics in the year 2023 lol)... really cool!! 👍

[jakejh]

Here's something for you to adapt.

#' Set background colors in columns of a Google Sheet
#'
#' This function constructs a JSON string, then makes and sends a Google Sheets
#' API request.
#'
#' @param file_id A `drive_id` corresponding to a Drive file.
#' @param background A `data.table` having columns `start_col`, `red`, `green`,
#'   and `blue`.
#'
#' @return The result of [googlesheets4::request_make()], invisibly.
drive_set_background = function(file_id, background) {
  assert_class(file_id, 'drive_id')
  assert_data_table(background)

  # only for setting one color per entire column
  gfile = drive_get(file_id)
  cli_alert_success('Setting background colors for "{gfile$name}".')

  bod_base = '{
  "repeatCell": {
    "range": {
      "startColumnIndex": (start_col),
      "endColumnIndex": (start_col + 1)
    },
    "cell": {
      "userEnteredFormat": {
        "backgroundColor": {
          "red": (red),
          "green": (green),
          "blue": (blue)
        }
      }
    },
    "fields": "userEnteredFormat.backgroundColor"
    }
  }'

  background = copy(background)
  background[, bod := glue(bod_base, .envir = .SD, .open = '(', .close = ')')]
  bod = sprintf('{"requests": [%s]}', paste(background$bod, collapse = ',\n'))

  request = googlesheets4::request_generate(
    'sheets.spreadsheets.batchUpdate', list(spreadsheetId = file_id))
  request$body = bod
  result = googlesheets4::request_make(request)
  invisible(result)
}