2

Is it possible to publish a Google Spreadsheet to the web using Google Apps Scripts? Right now I have to do it manually using File > Publish to the web....

I checked all of the Google Apps Scripts references and guides but don't see anything about publishing a script through GAS automation.

Tanaike
  • 181,128
  • 11
  • 97
  • 165
IMTheNachoMan
  • 5,343
  • 5
  • 40
  • 89

1 Answers1

7
  • You want to achieve File > Publish to the web... using Google Apps Script.

If my understanding is correct, how about this sample script?

When you use this script, please enable Drive API at Advanced Google Services.

Sample script:

var spreadsheetId = "###"; // Please set this.
Drive.Revisions.update({published: true, publishedOutsideDomain: true, publishAuto: true}, spreadsheetId, 1);

Official document:

  • publishAuto: Whether subsequent revisions will be automatically republished. This is only populated and can only be modified for Google Docs.
  • published: Whether this revision is published. This is only populated and can only be modified for Google Docs.
  • publishedOutsideDomain: Whether this revision is published outside the domain. This is only populated and can only be modified for Google Docs.

Note:

  • Spreadsheet has the revision ID of 1 as the default, when new Spreadsheet is created. And by publishAuto, when the Spreadsheet is updated, the updated Spreadsheet is automatically reflected to the published Spreadsheet.
    • I used them to this sample script.
    • If you want to modify this settings, please modify the script.

References:

If I misunderstood your question and this was not the result you want, I apologize.

Edit:

About the URL of published Spreadsheet, when the Spreadsheet is published manually, the URL can be retrieved like https://docs.google.com/spreadsheets/d/e/2PACX-###/pubhtml. In this case, 2PACX-### is not the Spreadsheet ID. Unfortunately, in the current stage, this URL cannot be retrieved by API. Drive API v2 had retrieved it with publishedLink before. But now, it cannot be retrieved. When Drive API is updated from v2 to v3, publishedLink was removed. This is the current situation.

But as a workaround, you can create the URL of published Spreadsheet using Spreadsheet ID. Please check the following URL.

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

You can access the published Spreadsheet using above URL.

Community
  • 1
  • 1
Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • I see. I need to figure out how to use this code in a Google Apps Script. Let me play with it and see if I can get it to work. Thanks! – IMTheNachoMan Jun 06 '19 at 01:12
  • @IMTheNachoMan Thank you for replying. This is Google Apps Script. You can use this script on the script editor after Drive API is enabled at Advanced Google Services in the script editor. If this was not the direction you want, I apologize. – Tanaike Jun 06 '19 at 01:52
  • @IMTheNachoMan Is there anything that I can do for your question? If my answer was not useful for your situation. I have to apologize and modify it. If you can cooperate to resolve your issue, I'm glad. I would like to think of about the solution. – Tanaike Jun 08 '19 at 02:07
  • 1
    I think this will work but I have not tested it yet. I am working on it right now and hope to get to this code today. I will update shortly. Thank you! – IMTheNachoMan Jun 08 '19 at 02:13
  • @IMTheNachoMan Thank you for replying. If you have any questions, feel free to tell me them. I would like to think of the solutions. Thank you, too. – Tanaike Jun 08 '19 at 02:14
  • So the above code works but I am not able to figure out how to get the published URL or ID. It's not in the [revisions resource](https://developers.google.com/drive/api/v2/reference/revisions#resource) response nor is it `selfLink`, `publishedLink`, `downlloadUrl`. Any idea? – IMTheNachoMan Jun 14 '19 at 03:18
  • @IMTheNachoMan Thank you for replying. I'm glad the script worked for you. But I deeply apologize that from your question I couldn't notice that you want to retrieve the URL of published Google Docs. This is due to my poor English skill. I updated my answer. Could you please confirm it? If that was not the direction you want, I apologize. – Tanaike Jun 14 '19 at 04:24
  • The URL you added cannot be found. Where can I find the `key=###`? And thank you so much for this. You're very very helpful and I appreciate it. – IMTheNachoMan Jun 14 '19 at 14:12
  • @IMTheNachoMan Thank you for replying. I apologize for the inconvenience. ``### spreadsheetId ###`` of ``https://docs.google.com/spreadsheet/pub?key=### spreadsheetId ###`` is the Spreadsheet ID. In the script in my answer, you can see as ``###`` of ``var spreadsheetId = "###";``. Also you can see about the Spreadsheet ID at [the official document](https://developers.google.com/sheets/api/guides/concepts#spreadsheet_id). I deeply apologize for my poor English skill. – Tanaike Jun 14 '19 at 21:55