Allow sheet_append to match existing sheet format (or add append option to range_write)

[py9mrg]

When I use sheet_append to add rows to an existing sheet, the new rows are all format free - even if the table in the existing sheet contains formatting. It would be nice to have a reformat = FALSE option for sheet_append similar to range_write.

I think the lack of this option is by design - I assume sheet_append is literally adding new rows to the sheet as opposed to writing over existing rows - hence they contain no formatting. But, often I am writing to an existing file with formatting and it would be nice if I could use sheet_append without having the blank formatting (or add an append option to range_write, if that's easier). Currently I just use range_write with the reformat = FALSE option but then I need to do a bit of extra faff to get the starting row from the existing file by reading it in and then using nrow - but the file is often very big and takes ages to read in to do that. If it's not too much hassle to fudge a background solution to one of those functions, it would be great.

[alexbhatt]

I would like to boost this. If you are attempting to following along the Rstudio persistent data blogpost and using sheet_append your data never loads in, since it is formatted differently.
You need to manually reformat the syle of data each time. As I have noticed in the gsheet it added a ' before the value eg. '2021-03-02 23:39:48 was inserted for a datetime event pasted by Sys.time()

[jennybc]

I feel like these two comments are sort of conflating 2 things.

The original issue is about ... inheriting style from the row above. Given the API feature I am using (AppendCellsRequest), it is conceivable I can set the FieldMask in a way that provides better behaviour. I would very much like to use a built-in gesture to "inherit style from those other cells", as opposed to writing style transfer logic myself.

The second comment (re: the persistent data blogpost) sounds like there are some issues around datetimes. It should probably be its own issue. The blog post is also rather short of details.

In both cases, though, I would be able to explore and maybe improve things much faster with a proper reprex:

https://googlesheets4.tidyverse.org/articles/articles/googlesheets4-reprex.html

Without this, it's rather daunting to dig into this, as I need to invent a Sheet that has the properties I think you're talking about (what sort of formats do they have in mind?), initialize the way I think you are doing, and send new data the way I think you're doing. It's easy for us to not be thinking about the same things.

[py9mrg]

Hello @jennybc

I can't comment on @alexbhatt 's point as I don't fully understand it!

I did think about a reprex in my original comment but I am struggling to work out how to do it as it needs an existing googlesheet file, with a table that contains some formatting. My only Google account is a work G Suite account and they're a bit funny about sharing files externally - anyone with the link is not a sharing option and drive_share won't allow me to make it open to anyone, so I don't think this (or any of the reprex options) will work without adding you explicitly via your email (and I completely understand you don't want to put that here!). But here goes...

The sheet link explicitly just in case you need to use this instead: https://docs.google.com/spreadsheets/d/1m-g8U2YkG4QGrUNXKk9eKoBnsWgZ_tdJFjQhwDC_Y88/edit?usp=sharing

library(googlesheets4)

to_append <- tibble::tibble(Sample = "F", Variable1 = 1, Variable2 = 2, Variable3 = 3, Variable4 = 4, Variable5 = 5)

sheet_append("1m-g8U2YkG4QGrUNXKk9eKoBnsWgZ_tdJFjQhwDC_Y88", data = to_append, sheet = "only_table_formatted")

sheet_append("1m-g8U2YkG4QGrUNXKk9eKoBnsWgZ_tdJFjQhwDC_Y88", data = to_append, sheet = "everything_formatted")

In case this doesn't work, what I have is a file with two sheets. The first has a table where only the table itself is formatted, the other sheet formats the entire columns as per the following two images:

When I use sheet_append as above, it clears the formatting so I get one of the following:

But what I would like is either of:

I don't actually mind whether it picks up the formatting from the row above (so the only_table_formatted method would work) or whether it just doesn't write over the existing formatting - as then I'd just make the whole sheet formatted like the other method.

Hope that's clear(er)?

[jennybc]

Yes thanks that helps!

[alexbhatt]

@jennybc, while creating the reprex, I happened to solve the problem, possibly with a workaround?
But in summary, after submitting a date as text, if the sheet is appended as text, instead of a datetime the issue can be averted however, if the gsheet column is formatted as datetime, you cannot push the data and read it back in correctly. Fixed by reading and writing the data as character text and formatting as datetime after import.

Link to the reprex github: https://github.com/alexbhatt/ibd_tracker/tree/reprex

[jennybc]

I haven't delved into the date time issue, but will have a look.

But now we no longer clobber, e.g. pre-existing gridlines, when appending. I achieved this by simply sending a more precise description of the format information that we potentially send, i.e. by not asserting that we were sending the entire cell format. I'm still sending the numberFormat, because we actively use this with Date and POSIXct (date time) cells. I still have to see if I can figure out the point above re: date times.

[jennybc]

I can't see the problem when appending in the presence of dates or date times. I suspect it's a usage problem, with unnecessary trips through a character representation.

TL;DR If you have a Date or date time POSIXct, leave it that way. Don't coerce it to a string, thereby creating the need for someone (R or googledrive or Sheets) to re-parse it back into a date or date time.

library(googlesheets4)
library(googledrive)

# hidden auth chunk here

ss <- gs4_create("sheet_append-fun-with-dates-and-datetimes")
#> ✓ Creating new Sheet: "sheet_append-fun-with-dates-and-datetimes".

to_start <- tibble::tibble(
  foo = LETTERS[1:2],
  datetimes = Sys.time() - (0:1) * 3600,
  dates = Sys.Date() - (0:1)
)
to_start
#> # A tibble: 2 x 3
#>   foo   datetimes           dates     
#>   <chr> <dttm>              <date>    
#> 1 A     2021-07-15 18:43:43 2021-07-15
#> 2 B     2021-07-15 17:43:43 2021-07-14

to_start %>% 
  write_sheet(ss, sheet = 1)
#> ✓ Writing to "sheet_append-fun-with-dates-and-datetimes".
#> ✓ Writing to sheet 'Sheet1'.
dat <- read_sheet(ss, col_types = "cTD")
#> ✓ Reading from "sheet_append-fun-with-dates-and-datetimes".
#> ✓ Range 'Sheet1'.
dat
#> # A tibble: 2 x 3
#>   foo   datetimes           dates     
#>   <chr> <dttm>              <date>    
#> 1 A     2021-07-16 01:43:43 2021-07-15
#> 2 B     2021-07-16 00:43:43 2021-07-14

to_append <- tibble::tibble(
  foo = LETTERS[3:4],
  datetimes = Sys.time() - (2:3) * 3600,
  dates = Sys.Date() - (2:3)
)
to_append
#> # A tibble: 2 x 3
#>   foo   datetimes           dates     
#>   <chr> <dttm>              <date>    
#> 1 C     2021-07-15 16:43:45 2021-07-13
#> 2 D     2021-07-15 15:43:45 2021-07-12

ss %>% 
  sheet_append(data = to_append)
#> ✓ Writing to "sheet_append-fun-with-dates-and-datetimes".
#> ✓ Appending 2 rows to 'Sheet1'.

dat <- read_sheet(ss, col_types = "cTD")
#> ✓ Reading from "sheet_append-fun-with-dates-and-datetimes".
#> ✓ Range 'Sheet1'.
dat
#> # A tibble: 4 x 3
#>   foo   datetimes           dates     
#>   <chr> <dttm>              <date>    
#> 1 A     2021-07-16 01:43:43 2021-07-15
#> 2 B     2021-07-16 00:43:43 2021-07-14
#> 3 C     2021-07-15 23:43:45 2021-07-13
#> 4 D     2021-07-15 22:43:45 2021-07-12

googledrive::drive_rm(ss)
#> File deleted:
#> • 'sheet_append-fun-with-dates-and-datetimes'
#>   <id: 1e_J4-arZCifqLPzpsPESWEGphk9WUprXmR6SvcPBFIY>

^{Created on 2021-07-15 by the reprex package (v2.0.0.9000)}