Excel workbooks stored to Google Drive do not Support Google Sheets API

[ralmond]

An Excel workbook stored on a google drive looks and feels like a google sheet, but it does not support the API. The problem is that when trying to connect to the document, the error message is very unhelpful.

gs4_deauth()
read_sheet("1qsPaQqiMKLuU3Ksr9NmkKQ8rR_Nmc6jb")

This yields the following error:

Error in `gargle_abort_request_failed()`:
! Client error: (400) FAILED_PRECONDITION
• Request can not be executed in the current system state, such as deleting a
  non-empty directory.
• This operation is not supported for this document
Run `rlang::last_error()` to see where the error occurred.

It looks like an authentication problem, but it is fact a problem that the Google Sheets API does not support converted Excel Sheets. The work around is to convert the Excel sheet to a Google Sheet using the "Save as Google Sheet" menu item in the web interface.

Here is the same sheet after saving.

gs4_deauth()
read_sheet("1-M_4DQSMTd_xAi8sPcocMVAikUzh7fvi3MDQDCqu70E")

This works as expected.

I'm not sure that this is a bug, as there is probably nothing that can be done in the R code that can fix it. It might be possible to get a better error message though.

On the other hand, posting this here might be of help to somebody having the same problem.

[jennybc]

Thanks for reporting, because yes maybe it will help someone else searching for this error message.

But, also yes, I don't see an obviously good thing I can do in googlesheets4. I try to be very disciplined about exposing the API's error and not adding my own hunches about what's wrong or how to fix. It's a bummer the API doesn't return a better error here, but it's out of my hands.