CODEWITHSUNDEEP
google-apps-script
architect_mosby017
architect_mosby017

Reputation: 41

How to extract the pubhtml ID in google sheet using Apps Script?

I'm trying to get the webpage link of my google sheet using Apps Script. When you go to the File>Publish to the web>publish, you will see a weblink there. I want to get that link via apps script. Please help.

Upvotes: 2

Views: 2784

Answers (1)

Tanaike
Tanaike

Reputation: 201388

  • You want to retrieve the URL like https://docs.google.com/spreadsheets/d/e/2PACX-###/pubhtml using Google Apps Script.

If my understanding is correct, how about this answer?

Issue and workaround:

File>Publish to the web>publish can be run for the Google Docs. This URL could been retrieved with publishedLink of Drive API v2 before. But in the current stage, unfortunately, this cannot be used for both Drive API v2 and v3. So as a workaround, I would like to propose to use file ID.

By the way, 2PACX-### of https://docs.google.com/spreadsheets/d/e/2PACX-###/pubhtml is not the file ID. Also please be careful this.

Sample script:

In this case, as a test case, the URL of published Spreadsheet is retrieved.

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var url = ss.getUrl().replace("edit", "pubhtml"); // or replace("edit", "pub");
  Logger.log(url)
}

Note:

  • At above script, when the Spreadsheet is not published to web, when you access to the URL, the Spreadsheet is not shown. Please be careful this.

  • For example, if you want to retrieve the URL of Google Document, you can use the following script. Unfortunately, at Google Document, getUrl() returns https://docs.google.com/open?id=###. So above script cannot be used.

    function myFunction() {
  var doc = DocumentApp.getActiveDocument();
  var url = "https://docs.google.com/document/d/" + doc.getId() + "/pub";
  Logger.log(url)
}

References:

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

Added:

If you can enable Drive API at Advanced Google Services, you can also use the following script.

Pattern 1:

When you use the container-bound script of Google Docs, you can use this.

var url = Drive.Files.get((DocumentApp.getActiveDocument() || SpreadsheetApp.getActiveSpreadsheet() || SlidesApp.getActivePresentation()).getId()).alternateLink.replace(/\/edit.+/, "/pub");
Logger.log(url);

Pattern 2:

When you have the file ID of Google Docs, you can use this.

var url = Drive.Files.get(fileId).alternateLink.replace(/\/edit.+/, "/pub");
Logger.log(url);

Upvotes: 4

Related Questions