Can't register Sheet that is not published to the web

[ahmedmoustafa]

While trying to read a Google spreadsheet via its shared URL, gs_url throws the following error message:

Error in stop_for_content_type(req, expected = "application/atom+xml; charset=UTF-8") : 
  Expected content-type:
application/atom+xml; charset=UTF-8
Actual content-type:
text/html; charset=UTF-8

Here is the URL of the Google spreadsheet: Irf6

[jennybc]

Is this Sheet "published on the web"? This is different from "public on the web".

File > Publish to the web ...

[ahmedmoustafa]

It was not published on the web. After publishing, it gave another error msg:

Sheet-identifying info appears to be a browser URL.
googlesheets will attempt to extract sheet key from the URL.
Putative key: e
Worksheets feed constructed with public visibility
Error in function_list[[k]](value) : Bad Request (HTTP 400).

This is the published URL

[jennybc]

In the above hyperlink, this is what you provide this as published URL:

https://docs.google.com/spreadsheets/d/e/2PACX-1vTcf3zaAr78AFWt2P97AfMt55mif44cAVhPuApCCrcblkcT4kbabV2i60dfoqkJoMUSvHm63Gh_I9V7/pub?gid=0&single=true&output=tsv

But that is, rather, a URL which downloads the file as a tsv.

[jennybc]

Now that the Sheet is published, I can access it like so:

> gs_url("https://docs.google.com/spreadsheets/d/1Cbpedw-zK5Xy0oLdddJ52pnrq1yg61V2SSjL41r8Lgo/edit?usp=sharing", lookup = FALSE)
Sheet-identifying info appears to be a browser URL.
googlesheets will attempt to extract sheet key from the URL.
Putative key: 1Cbpedw-zK5Xy0oLdddJ52pnrq1yg61V2SSjL41r8Lgo
Worksheets feed constructed with public visibility
                  Spreadsheet title: Irf6
                 Spreadsheet author: amoustafa
  Date of googlesheets registration: 2018-03-10 00:36:45 GMT
    Date of last spreadsheet update: 2018-03-09 22:51:21 GMT
                         visibility: public
                        permissions: rw
                            version: new

Contains 1 worksheets:
(Title): (Nominal worksheet extent as rows x columns)
Irf6: 45102 x 26

Key: 1Cbpedw-zK5Xy0oLdddJ52pnrq1yg61V2SSjL41r8Lgo
Browser URL: https://docs.google.com/spreadsheets/d/1Cbpedw-zK5Xy0oLdddJ52pnrq1yg61V2SSjL41r8Lgo/

If this Sheet shows up in your Google Drive, then you can probably drop the lookup = FALSE from the above command. That is an ugliness that will go away in the googlesheets4 reboot (underway).

And you probably would prefer to access via key or name. gs_url() is a convenience to quickly check something out from URL, but I wouldn't write it into a script.

[PrasanthV454]

`myUrl<-gs_url("https://docs.google.com/spreadsheets/d/e/2PACX-1vQifqzHDZlCuxNwNydrKx-hhq8g3sGG6SIu8mdONQFNcu3RXVwgusxclnxnPb5CmLBFitsMoEiwN-of/pubhtml") Sheet-identifying info appears to be a browser URL. googlesheets will attempt to extract sheet key from the URL. Putative key: e Worksheets feed constructed with public visibility Error in function_list[k] : Bad Request (HTTP 400).

myUrl<-gs_url("https://docs.google.com/spreadsheets/d/e/2PACX-1vQifqzHDZlCuxNwNydrKx-hhq8g3sGG6SIu8mdONQFNcu3RXVwgusxclnxnPb5CmLBFitsMoEiwN-of/pubhtml", lookup = FALSE) Sheet-identifying info appears to be a browser URL. googlesheets will attempt to extract sheet key from the URL. Putative key: e Worksheets feed constructed with public visibility Error in function_list[k] : Bad Request (HTTP 400)`