2

I have a script that takes rows from a spreadsheet, and creates individual spreadsheet files for each row. Then the script publishes them to the web.

What I'm not able to get is the id that will allow me to get an url for a web version of it.

A regular spreadsheet id looks like this:

1N8h00iN2L7aaUbI9TYRWioaZHjHNSc_vNucCbVADj1o

And a published to the web id looks like this:

2PACX-1vTPSkv4Rz7CqwabTTfxa3xcVF-Gzxu7IboLyLkfoShP2d3qdX8o0qdvk5d_nWZD2rq43E2LXmVAm5HI

(longer, and it starts with 2PACX-)

Anybody know how to?

Thanks in advance.

Lau
  • 25
  • 1
  • 3

1 Answers1

4

You want to retrieve URL from the web published spreadsheet using file ID. If my understanding is correct, how about this answer? Unfortunately, "publishedLink" cannot be retrieved by Drive API v3. "publishedLink" got to not be able to be used from Drive API v3. Furthermore now, Drive API v2 also cannot be used anymore. So it is required to create the link manually.

When the spreadsheet is published to Web, you retrieve URL like as follows.

https://docs.google.com/spreadsheets/d/e/2PACX-1vTPSkv4Rz7CqwabTTfxa3xcVF-Gzxu7IboLyLkfoShP2d3qdX8o0qdvk5d_nWZD2rq43E2LXmVAm5HI/pubhtml

When you want to use the URL using file ID, please use the following URL. You can use both URLs.

Pattern 1:

https://docs.google.com/spreadsheet/pub?key=### fileId ###

Pattern 2:

https://docs.google.com/spreadsheets/d/### fileId ###/pubhtml

If I misunderstand your question, I'm sorry.

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • Thank you! That’s correct, I want to get the url. My question now is, where can I get the key?, if possible – Lau Jun 23 '18 at 09:00
  • @Lau I'm sorry. I cannot understand about the key. Is the key you think the file ID? In your case, ``1N8h00iN2L7aaUbI9TYRWioaZHjHNSc_vNucCbVADj1o`` is the file ID. If you want to retrieve this from URL of spreadsheet, you can see it at [here](https://developers.google.com/sheets/api/guides/concepts#spreadsheet_id). Is my understanding for your comment correct? – Tanaike Jun 23 '18 at 12:01
  • Ah ok, now I understand. key = fileID. Thank you very much!, this was it! – Lau Jun 23 '18 at 17:49
  • 1
    @Tanaike and Lau, I still dont see how you got this key 1N8h00iN2L7aaUbI9TYRWioaZHjHNSc_vNucCbVADj1o for the published google spreadsheet on the link https://docs.google.com/spreadsheets/d/e/2PACX-1vTPSkv4Rz7CqwabTTfxa3xcVF-Gzxu7IboLyLkfoShP2d3qdX8o0qdvk5d_nWZD2rq43E2LXmVAm5HI/pubhtml. Could you please explain this part? – MARIO Nov 08 '19 at 09:59
  • Is [this information](https://developers.google.com/sheets/api/guides/concepts#spreadsheet_id) useful? – Tanaike Nov 08 '19 at 11:43
  • The answer is not clear. Its a simple question. Is it possible to get the published URL from the googlesheet (website)? – Lazarus Thurston May 02 '21 at 05:42
  • @Lazarus Thurston Thank you for your comment. I apologize for the inconvenience and I deeply apologize for my poor English skill. When the Google Spreadsheet is published to Web, the URL of `https://docs.google.com/spreadsheets/d/e/2PACX-###/pubhtml` is shown. Drive API v2 had been able to retrieve this URL before. But in the current stage, Drive API v2 and v3 cannot retrieve this URL. So, in the current stage, the answer for your question of `Is it possible to get the published URL from the googlesheet (website)?` is as follows. – Tanaike May 02 '21 at 07:30
  • @Lazarus Thurston `2PACX-###` of `https://docs.google.com/spreadsheets/d/e/2PACX-###/pubhtml` is not the Spreadsheet ID. By this, in order to directly retrieve this URL, it is required to retrieve the URL on Google Spreadsheet using the browser. If your question of `Is it possible to get the published URL from the googlesheet (website)?` includes the method for manually retrieving the URL, the answer is YES. If you want to retrieve the URL of `2PACX-###` of `https://docs.google.com/spreadsheets/d/e/2PACX-###/pubhtml` using a script, it's NO. At that time, please use the following workaround. – Tanaike May 02 '21 at 07:30
  • @Lazarus Thurston As the current workaround, `https://docs.google.com/spreadsheet/pub?key=### fileId ###` and `https://docs.google.com/spreadsheets/d/### fileId ###/pubhtml` can be used instead of `https://docs.google.com/spreadsheets/d/e/2PACX-###/pubhtml`. In this case, these URLs can be created using the Spreadsheet ID. So I proposed this. This is due to my poor skill. I deeply apologize for my this proposal. – Tanaike May 02 '21 at 07:30
  • @Lazarus Thurston If my these reply comments were not what you want, I apologize again. – Tanaike May 02 '21 at 07:30
  • Thank you for the detailed explanations @Tanaike. I will soon try them and let you know. – Lazarus Thurston May 02 '21 at 11:21
  • @Lazarus Thurston Thank you for replying. If my explanation was not what you want, I apologize again. – Tanaike May 02 '21 at 11:55