Bug: Use of Null values does not accurately "skip" cells within range_write()

[JerePlum99]

I believe that the results of #203 may have removed the intended functionality from the Googlesheets API - as it now seems there is no way to skip columns or cells when writing data with functions such as range_write().

My understanding of the Sheets API and specific functions comes from the following pages:

• Method: spreadsheets.values.batchUpdate - used in range_write()
• ValueRange - underlying component that alters data - I reference this section, as it notes that "For input, supported value types are: bool, string, and double. Null values will be skipped. To set a cell to an empty value, set the string value to an empty string."

With this, I expect that when using range_write() I would be able to use NA to specifically write empty values, while using NULL to specifically skip editing values.

I provided the below reprex, in which I create a tibble & sheet with a formula column in between, and attempt to edit them, with the goal of essentially "skipping" an edit to the "formula" column, ideally with the use of NULL values.

Let me know if you have any questions or if I'm thinking about this wrong - appreciate all the help!

library(googlesheets4)
library(googledrive)

# Create a general example of a spreadsheet with 3 columns of data, one that is a formula
tbl <- tibble::tibble(
  numeric = 1:100, 
  formula = googlesheets4::gs4_formula("=A2*2"),
  string = c(rep("example 1", 100))
)

# Create the example sheet
ss <- gs4_create(sheets = tbl)

# View the example sheet
gs4_browse(ss)

# The following examples depict attempts to try and edit a sheet while skipping a column with formulas (arrayformulas or formulas)

# ATTEMPT 1: Using NA - this understandably results in the formula column being overwritten with NA values

tbl_2 <- tibble::tibble(
  numeric = 1:100, 
  formula = NA,
  string = c(rep("example 2", 100))
)

edit_sheet_na <- googlesheets4::range_write(ss, data = tbl_2)

# Restore the original data
googlesheets4::range_write(ss, data = tbl)

# ATTEMPT 2: Using Null - this still results in the formula column being overwritten with NA values
# However, I would not expect this behavior based on the Google Documentation referenced in the issue
tbl_3 <- tibble::tibble(
  numeric = 1:100, 
  formula = list(rep(NULL, 100)),
  string = c(rep("example 3", 100))
)

edit_sheet_null <- googlesheets4::range_write(ss, data = tbl_2)

# Restore the original data
googlesheets4::range_write(ss, data = tbl)

googledrive::drive_rm(ss)

[JerePlum99]

@jennybc Hi Jenny, I know there's a backlog here but wanted to see if I can help in any way, as I've been unable to figure out a workaround without maintain a fully separate package. I think the question probably comes down to Google's intent for passing in NULL values, and whether they should write NA or skip entirely.

My understanding is that they should skip entirely (so you wouldn't overwrite data/formula in a range), and that the fix would basically be reverting this commit. Let me know if there's anything else I can do!