Prevent character cells containing number from being written with an ` appended

[py9mrg]

Let's say I have to, through no fault of my own (!), write a very non-tidy tibble to a google sheet. One that contains character columns with both actual strings in some cells and only numbers in others.

So, something like:

library(tidyverse)
library(googlesheets4)
df <- tibble(column = c("A", "B", "2", "3.1"))
write_sheet(df)

Obviously, strictly speaking, these are all characters, but pretend only the A and B "cells" are, and bear with me.

Currently, unless there's an option I've missed, the cells containing numbers will be written with an ` appended in front of the number (e.g. `3.1), while those with strings won't (e.g. just B). It would be nice if the numbers could be written without the ` too - to save me having to manually go into the sheet afterwards and convert all the number cells to number format.

Edit:

Just for anyone who might encounter a similar issue, assuming there isn't a simple solution to this, I have just thought up a couple of hacky solutions.

1. If the row position of the character and numeric values are constant, then you can use slice from dplyr to separate out your tibble into blocks of all character or all numeric sections, do a type_convert() from readr on each section, and then use range_write to write to the appropriate section of the google sheet.
2. If the row position of the character and numeric values varies, so you cant set up constant sections with slice, you can do the same as (1) but slice the tibble row by row, then type_convert() and then write using sheet_append.

Obviously, these solutions, particularly the second, are a little slow.

[jennybc]

The most correct and general way to handle this is to form a list-column on the R side. That allows you to hold "cells" of different type in one data frame column. Here's one way to use readr's type-guessing to form that list column based on your exact input. There are other ways, depending on how df comes into existence in the first place.

library(tidyverse)
library(googlesheets4)

df <- tibble(column = c("A", "B", "2", "3.1"))
ss <- write_sheet(df)
#> Creating new Sheet: "endowed-bear"
ss
#>   Spreadsheet name: endowed-bear
#>                 ID: 1DnwsztT4sm5lbCILZgy_YP3HSE2VxoFmvwEC_VpbEJw
#>             Locale: en_US
#>          Time zone: Etc/GMT
#>        # of sheets: 1
#> 
#> (Sheet name): (Nominal extent in rows x columns)
#>           df: 5 x 1

df2 <- tibble(
  character = df$column,
  list = lapply(df$column, readr::parse_guess)
)
df2
#> # A tibble: 4 x 2
#>   character list     
#>   <chr>     <list>   
#> 1 A         <chr [1]>
#> 2 B         <chr [1]>
#> 3 2         <dbl [1]>
#> 4 3.1       <dbl [1]>

write_sheet(df2, ss, sheet = "use-a-list-column")
#> Writing to "endowed-bear"
#> Writing to sheet "use-a-list-column"
gs4_browse(ss)

^{Created on 2020-06-11 by the reprex package (v0.3.0.9001)}

I know there is an endpoint I could use for writing where it's possible to say "process this data as if a human typed it into a cell", which would "just work" with your original character column. But so far I have not exposed it, because sending properly typed data is a much higher priority.

[py9mrg]

Ooooooh, ok. Thanks.