Optionally import empty cells

[nacnudus]

googlesheets4/R/sheets_cells.R

Lines 107 to 110 in 5b97156

	## cells can be present, just because they bear a format (much like Excel)
	## as in readxl, we only load cells with content
	cell_is_empty <- map_lgl(out$cell, ~ is.null(pluck(.x, "effectiveValue")))
	out[!cell_is_empty, ]

It's exciting to see this take shape!

Empty, formatted cells have a lot of utility when unpivoting pivot tables or decoding formatting-as-data. For example, legends often have the formatting and the code in separate cells, so the formatted cell is empty.

Are there good reasons not to import them at all? (I accept that importing them by default could be confusing)

[jennybc]

I will push a bunch of work soon. sheets_cells() is meant to be a low-level-but-user-facing function, for people like you, who might want to build things on top. So I definitely want to get sheets_cells() right. Once I push, let's discuss further about what is missing and how to provide access. Right now it's very much shaped by what I need, probably too much.

[jennybc]

I'm ready to talk about this now. When you're ready, would you create a Sheet with features that would allow us to talk concretely about the benefits of returning empty cells?

[nacnudus]

The response to this appeal wasn't overwhelming. Build it and they will come? 😬

But anyway, here's a googlesheet to demonstrate use cases.

The "legend" tab uses fill colour to encode the species. The legend unhelpfully doesn't combine text and fill colour in the same cell. Unless the empty, coloured cells are imported, this information is lost.

The "anchor" tab uses a border to mark the bottom of a table, before a footnote. The border is applied to the top of the empty cell below the table, rather than to the bottom of the data cell at the bottom of the table.

The "comment" tab has a comment on an empty cell.

I can't get reprex() to work because of problems authenticating its session, but this code should show that the border marking the bottom of the table in sheet anchor is applied to top of the empty cell A5, rather than to the bottom of the data cell A4, and also that the empty cells, colours, borders and comments can be imported by tidyxl after exporting to xlsx.

library(googledrive)
library(googlesheets4)
library(tidyxl)
library(here)

# Download the spreadsheets natively and in xlsx format
gs_legend <- sheets_cells("1UbdlyITXLvsxQt6kpszu5gfiDmF5Q-wOrNC7l4E9jOg", sheet = "legend")
gs_anchor <- sheets_cells("1UbdlyITXLvsxQt6kpszu5gfiDmF5Q-wOrNC7l4E9jOg", sheet = "anchor")
gs_comment <- sheets_cells("1UbdlyITXLvsxQt6kpszu5gfiDmF5Q-wOrNC7l4E9jOg", sheet = "comment")
drive_download(file = "empty-formatted-cells", overwrite = TRUE)

# The empty cells of the legend are omitted
gs_legend

# The empty cell with the top border is ommitted
gs_anchor

# The empty cell with the comment is omitted
gs_comment

xl_cells <- xlsx_cells(here("empty-formatted-cells.xlsx"))
xl_formats <- xlsx_formats(here("empty-formatted-cells.xlsx"))

bottom_border <- xl_formats$local$border$bottom$style
top_border <- xl_formats$local$border$top$style

# No cell has a bottom border
dplyr::filter(xl_cells, sheet == "anchor", !is.na(bottom_border[local_format_id]))

# The cell with a top border is empty
dplyr::filter(xl_cells, sheet == "anchor", !is.na(top_border[local_format_id]))

# The empty cell with a comment is retrieved, and so is the comment
dplyr::filter(xl_cells, sheet == "comment")[, c("address", "comment")]

[nacnudus]

This came up again with an annual leave planning sheet (anonymised)

https://docs.google.com/spreadsheets/d/1Zm3QzsRbrGZQCrmchHL47YO92yrNYHtfqLXyy2swN54/edit?usp=sharing

[jennybc]

So this is going to require changing which fields I retrieve, because right now, I truly have no data for those emtpy-but-formatted cells. It has to happen around here:

googlesheets4/R/get_cells.R

Line 59 in 51b0e5a

fields <- fields %||% "spreadsheetId,properties,sheets.data(startRow,startColumn),sheets.data.rowData.values(formattedValue,userEnteredValue,effectiveValue,effectiveFormat.numberFormat)"

Am currently mulling over whether to expose fields in its full glory in sheets_cells() or if some higher-level argument should appear that says "give me format info" and googlesheets4 decides how to implement that via fields.

[jennybc]

Because I don't know where else to file this knowledge, I'll put it here.

There are 2 ways to annotate a cell:

• Note via Insert > Note
• Comment via Insert > Comment

I can see notes via the API but not comments. A note is part of the CellData schema. So it is now accessible via sheets_cells().

I can't see the comment @nacnudus put in his demo sheet. Neither in the browser, nor via the API. I think you can access the comments via the Drive API.

Conclusion: if you want an annotation to be available in the Sheets-world, use a note.