Difficulty editing sheet that has a "table" (in the formal Sheets sense)

[bshor]

I'm using the latest version of googlesheets4 (1.1.2), running on R 4.5.1 and the latest Rstudio on Linux (Fedora 42), with all of my packages fully updated. It has always run great for me, and I can still read sheets without a problem. But today for the first time I got a strange error when I'm writing a sheet.

students %>%
+       sheet_write(gsheet.main, sheet = "Roster")

This is a simple tibble with the following columns.

gsheet.main is the unique ID of the google sheet, which already exists.

This leads to the following error:

Error in map():
ℹ In index: 1.
Caused by error in check_against_schema():
! Properties not recognized for the Sheet schema:
✖ tables
Run rlang::last_trace() to see where the error occurred.

The trace reveals:

Backtrace:
▆

1. ├─students %>% sheet_write(gsheet.main, sheet = "Roster")
2. └─googlesheets4::sheet_write(., gsheet.main, sheet = "Roster")
3. └─googlesheets4:::sheet_add_impl_(ssid, sheet_name = sheet)

4. └─googlesheets4:::new_googlesheets4_spreadsheet(resp$updatedSpreadsheet)
   

5.   └─purrr::map(x$sheets, ~new("Sheet", !!!.x))
   

6.     └─purrr:::map_("list", .x, .f, ..., .progress = .progress)
   

7.       ├─purrr:::with_indexed_errors(...)
   

8.       │ └─base::withCallingHandlers(...)
   

9.       ├─purrr:::call_with_cleanup(...)
   

10.       └─googlesheets4 (local) .f(.x[[i]], ...)
    

11.         └─googlesheets4:::new("Sheet", !!!.x)
    

12.           └─googlesheets4:::check_against_schema(dots, schema = schema)
    

I'm happy to give you any other info you might need.

[jennybc]

I've just done a quick check that I can write a Sheet as a normal user and that works. So there must be some of the specifics of the situation that matter. Does the existing Sheet (e.g. in a worksheet other than "Roster") have some interesting feature? Like a table in it?

[jennybc]

OK I've been able to replicate if I try to write to worksheet A in a Sheet where another worksheet, say worksheet B, has a "table" in the formal Sheets sense. So their schema has changed, which means that googlesheets4's internal data object about the API surface needs to be updated.

[jennybc]

Two observations:

• Yes, locally, I've updated googlesheets4's internal data on the API, which fixes this problem.
• Even with the current version (v1.1.2), when you see the error about "! Properties not recognized for the Sheet schema: ✖ tables", just try again. The second request seems to work. Obviously that's not a good long-term solution, but gets you unstuck right now.

[bshor]

Sorry I wasn't able to answer you earlier. Yes; the other worksheet in my Google Sheet has a table (I think Google Sheet asked me if I wanted to convert the data into a table and I just experimented with saying yes ... maybe a mistake but it's probably something other users are going to get into!) It looks prettier but leads to this problem.

Well I think your fix worked since I don't see this error anymore! Thank you so much @jennybc for creating this awesome package and providing this swift help! I'm a big evangelist for this package to all my students.

[jennybc]

These tables have been around for a while, so I think there just aren't many folks editing Sheets programmatically that also contains one or more tables. But it's a good nudge to update googlesheets4's internal data object. I'm going to record some notes to myself here.

The error is coming from this line:

googlesheets4/R/sheet_write.R

Line 128 in 8aaba21

x <- sheet_add_impl_(ssid, sheet_name = sheet)

Specifically when we try to instantiate a new spreadsheet from the response received after adding a new (work)sheet:

googlesheets4/R/sheet_add.R

Line 89 in 8aaba21

new_googlesheets4_spreadsheet(resp$updatedSpreadsheet)

(That also explains why a second attempt to write works, because we don't hit this code.)

That response now contains info on the tables property, which we don't recognize. The solution is, I think, just to update the API info, which is how I'm closing this. Elsewhere, e.g. in gs4_get(), we ask for specific fields to be returned, which is why we don't see the problem there. But I don't think I can control the fields in the response when adding a (work)sheet, nor do I really want to do this all over the place.

Here's the new tables property in the Sheet schema:

https://github.com/tidyverse/googlesheets4/pull/323/files#r2331252395

[bshor]

I'm having a different version of this problem. I'm trying to write another Google Sheet and am getting the same error. I am running the latest version of R (4.5.1) and googlesheets4 (1.1.2) (though this time idiosyncratically on a Mac).

The error is:

Error in map():
ℹ In index: 1.
Caused by error in check_against_schema():
! Properties not recognized for the Sheet schema:
✖ tables
Run rlang::last_trace() to see where the error occurred.

The code I'm writting is very simply:

sheet_write(gsheet.main, sheet = i)

It is true one of the sheets in gsheet.main has a table. But somehow I thought you fixed this earlier.

The backtrace is:

Backtrace:
     ▆
  1. ├─global process_exams(gsheet = T)
  2. │ └─... %>% sheet_write(gsheet.main, sheet = i) at Canvas/canvas teaching funcs.R:481:7
  3. └─googlesheets4::sheet_write(., gsheet.main, sheet = i)
  4.   └─googlesheets4:::sheet_add_impl_(ssid, sheet_name = sheet)
  5.     └─googlesheets4:::new_googlesheets4_spreadsheet(resp$updatedSpreadsheet)
  6.       └─purrr::map(x$sheets, ~new("Sheet", !!!.x))
  7.         └─purrr:::map_("list", .x, .f, ..., .progress = .progress)
  8.           ├─purrr:::with_indexed_errors(...)
  9.           │ └─base::withCallingHandlers(...)
 10.           ├─purrr:::call_with_cleanup(...)
 11.           └─googlesheets4 (local) .f(.x[[i]], ...)
 12.             └─googlesheets4:::new("Sheet", !!!.x)
 13.               └─googlesheets4:::check_against_schema(dots, schema = schema)

The problem goes away when I switched the table to unformatted data. I don't need tables really in my Google Sheets, so this may not be that important to fix, but I wanted to let you know.

[jennybc]

@bshor I haven't done a googlesheets4 release since I fixed this. We discovered it shortly after a release and it was too soon. But I could do one now, so this is a good reminder to move this up my todo list.

In the meantime, you could use the dev version: pak::pak("tidyverse/googlesheets4").

[bshor]

Ahh, understood. No, no need to move this up the priority list as the fix (to not use tables) is an easy one to take. I'm not sure why but I thought your fix was server-side (also possibly I installed the dev version in Linux and didn't do so in Mac). This is not a burning issue at all.