search

tidyverse / googlesheets4

Google Spreadsheets R API (reboot of the googlesheets package)
https://googlesheets4.tidyverse.org
Other
360 stars 53 forks source link

Keeping cells as 'NA' when writing them into google sheets instead of making them blank #243

Closed cindyyawencheng closed 3 years ago

cindyyawencheng commented 3 years ago

I've noticed that when writing sheets to google sheets using the write_sheets function, when a cell takes a value of NA, it appears as blank in google sheets. Would it be possible to have an option to keep these cells as taking the value of NA instead of making them blank?

To give an example of this issue in this data sample here, record_id's "R_24dtGnY9eVfJ7r4", "R_3PXDOkGJMDuCThv", "R_3kMohPgrIYHZQKg" and "R_qOfCTkUKxYDZ8VX" have NA values for the 'end' variable, but as you can see in the screenshot below, when I write this data into googlesheets, the cells are blank instead of 'NA'. 

structure(list(start = structure(c(18400, 18364, 18319, 18472, 
18356, 18478), class = "Date"), end = structure(c(NA, NA, NA, 
18472, NA, 18478), class = "Date"), record_id = c("R_24dtGnY9eVfJ7r4", 
"R_3PXDOkGJMDuCThv", "R_3kMohPgrIYHZQKg", "R_1dHcwDGdcEFeVad", 
"R_qOfCTkUKxYDZ8VX", "R_22DPJCyzwnFdIfO")), row.names = c(NA, 
-6L), class = c("tbl_df", "tbl", "data.frame"))

Screenshot 2021-10-08 at 12 38 58 PM

It is actually quite important for us to be able to keep the cells as reading 'NA' in google sheets because we are importing them into Tableau, which recognises/visualizes NAs differently than blank cells. Our visualisations don't work if the cells are empty; they need to take on the value of NA.

We have tried workarounds like:

i) exporting the 'end' variable as a character string, where we then write in the NA afterwards, but unfortunately this also doesn't work with Tableau because the format of the column needs to be in a 'Date' format in order to work. ii) simply uploading the data manually which does work. But we have 30 such sheets that we are planning to upload daily if possible and we need the write_sheets fn to do this automatically

It would be wonderful if we could get the option to write cells as NA instead of having a blank cell, thanks so much!

jennybc commented 3 years ago

I'm working on something else right now, but I think the only way I've been able to do this is by writing the formula =NA(). If my hunch is correct, then here's info on how to write formulas: https://googlesheets4.tidyverse.org/articles/articles/write-sheets.html#write-formulas

If by googling you discover some other way to write proper NAs with the Sheets API, please let me know!

cindyyawencheng commented 3 years ago

Hi Jenny, thanks for the fast reply! Haven't found a way to write proper NAs yet but we figured out how to make our hack of writing the data as a character string (with NAs as a character string) work! I think it'd still be a nice feature for googlesheets4 to have, but no worries, thanks again!