tidyverse/googlesheets4

`sheet_append` should allow adding additional columns

Closed this issue · 3 comments

deschen1 commented

sheet_append currently is overly strict when appending new rows to an existing sheet, i.e. the appended data must not have more column than the existing sheet. Which makes the whole adding of new rows cumbersome, because what someone currently needs to do is:

  • create a subset of the new columns,
  • upload this subset with range_write,
  • append the new row.

The solution (if supported by the Google API) would be maybe an optional parameter strict = TRUE/FALSE, indicating what should happen when the new row has more columns than the existing sheet (E.g. error as of now or add the column).

If adding additional columns is not supported by the Google API, then it would be preferable if sheet_append could do the above mentioned workaround under the hood.

Note: it is not an option in this use case to simply re-write/overwrite the entire sheet, because it might contain content online that is not part of the sheet that is upload (e.g. a user might have added a comment or applied some formats).

library(googlesheets4)
my_data <- data.frame(x = 1:3, y = letters[1:3])

sheet_info <- gs4_create("sheets-create-demo",
                         sheets = my_data)

# Works
sheet_append(ss    = sheet_info,
             data  = data.frame(x = 4, y = "d"),
             sheet = "my_data")

# Doesn't work
sheet_append(ss    = sheet_info,
             data  = data.frame(x = 4, y = "d", z = TRUE),
             sheet = "my_data")

✔ Writing to sheets-create-demo.
✔ Appending 1 row to my_data.
Error in `gargle::response_process()`:
! Client error: (400) INVALID_ARGUMENT
• Client specified an invalid argument. Check error message and error details for more information.
• Invalid requests[0].appendCells: Attempting to write column: 2, beyond the last requested column of: 1
Run `rlang::last_error()` to see where the error occurred.

rlang::last_trace()
<error/gargle_error_request_failed>
Error in `gargle::response_process()`:
! Client error: (400) INVALID_ARGUMENT
• Client specified an invalid argument. Check error message and error details for more information.
• Invalid requests[0].appendCells: Attempting to write column: 2, beyond the last requested column of: 1
---
Backtrace:
    ▆
 1. └─googlesheets4::sheet_append(...)
 2.   └─gargle::response_process(resp_raw)
 3.     └─gargle:::gargle_abort_request_failed(error_message(resp), resp)
 4.       └─gargle:::gargle_abort(...)
 5.         └─cli::cli_abort(...)
 6.           └─rlang::abort(...)
jennybc commented

sheet_append() is absolutely designed for the narrow case of adding rows to an existing table, i.e. without making any change to the "schema". So adding columns definitely falls outside the scope of sheet_append().

deschen1 commented

Understood. Any chance for creating an additional function then for the (probably very frequent) use case of adding rows/columns at the same time?

jennybc commented

No near term plans. I do keenly feel that there's a whole missing toolkit for various types of modification of a data frame, including the one you're talking about, but it's also a big project. There's a lot to think about in terms of how to wire this up into the Sheets API and also how it should "feel" in R. For the moment, googlesheets4 is definitely focused on the "write an entire data rectangle at once" use case, with a bit of support for adjacent moves, like appending a row.