1

I am trying to add pairs of values (date, value) to Google Sheets via gs_add_row from googlesheets. This succeeds, except that both values show up as dates on Google Sheets.

I checked the "value" beforehand to make sure it is as expected (eg. 50), but Sheets displays a date (1970-02-20).

I even tried setting the cell formatting in Sheets to "Numbers" instead of "Automatic" but it made no difference.

Is there a way to specify variable types?

EDIT Example code:

library(lubridate)
library(googlesheets)
library(dplyr)

#URL <- "[some 'published to the web' Google Sheet URL]"
dt <- ymd(today())
val <- 50

URL %>% 
    gs_url() %>% 
    gs_add_row(ws=3, input = c(dt, val))
adatum
  • 655
  • 9
  • 23
  • 1
    You really need to show a snippet of the data before it got exported to google sheets (`dput(head(yourdf,20))`). Check the structure of the data by using `str` to make sure that your column is actually is not `Date`. – Abdou Jul 05 '16 at 21:37
  • @Abdou It's not even a data frame; just a vector of a date and value pair. I checked that the values are correct in the code right before `gs_add_row` tries to add them to Google Sheets. – adatum Jul 05 '16 at 22:42
  • 1
    when you use `c`, the output type is determined from the highest type of the components in the hierarchy `NULL < raw < logical < integer < double < complex < character < list < expression`. Therefore, the output of the concatenation (using `c`) is of `Date` class. If you had done `c(val, dt)`, the output type would be `numeric`. Your best bet is to do: `gs_add_row(ws=3, input = c(as.character(dt),val)))`. Try it and let me know. – Abdou Jul 05 '16 at 23:04
  • @Abdou `as.character(dt)` got the same result as before. And for fun I also tried `input = c(val, dt)` and both are still dates again! – adatum Jul 05 '16 at 23:15
  • 1
    Are you sure? Cause I just tried it and it worked for me. Again, the code is: ```URL %>% gs_url() %>% gs_add_row(ws=3, input = c(as.character(dt), val))``` – Abdou Jul 05 '16 at 23:25
  • I got `2016-07-05 50` – Abdou Jul 05 '16 at 23:25
  • Make sure to use the `as.character` transformation within your `gs_add_row` call. – Abdou Jul 05 '16 at 23:29
  • @Abdou I now tried it on a new Sheet and it works as you said: `2016-07-05 50`. I tried again on the old Sheet and it continued to fail! I copied the data, deleted the old Sheet, and made a new Sheet with the same data, and now adding works. Thank you!! – adatum Jul 05 '16 at 23:37
  • Oh great! Glad it finally worked. I will add the comments as the answer for you to accept. Thanks! – Abdou Jul 05 '16 at 23:40

1 Answers1

1

The issue arises from the usage of c to concatenate dt and val into a vector. when you use c, the output type is determined from the highest type of the components in the hierarchy NULL < raw < logical < integer < double < complex < character < list < expression. Therefore, the output of the concatenation (using c) is of Date class in this case. If you had done c(val, dt), the output type would be numeric.

Your best bet is to do:

URL %>%
    gs_url() %>% 
    gs_add_row(ws=3, input = c(as.character(dt), val))

If this does not work the first time around, you may have to delete the old Sheet, make a new Sheet with the same data, and run the code on the new sheet.

Thanks!

Abdou
  • 12,931
  • 4
  • 39
  • 42