publish a Google Spreadsheet through Google Apps Scripts

Solution 1:

  • 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:

  • Advanced Google services
  • Revisions: update

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.