Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
ralmond opened this issue Dec 1, 2022 · 1 comment
Closed

Comments

@ralmond
Copy link

ralmond commented Dec 1, 2022

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
Copy link
Member

jennybc commented Mar 23, 2023

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.

@jennybc jennybc closed this as completed Jun 2, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants