0

Using the Google Sheets script editor, I want to get the number starting 2PACX that is in the code generated from File -> Publish to the web for a Google Doc.

Note this is different to fileId(), or the number shown in the normal URL for the doc that starts with 1. Is there another name for it? I've spent ages googling and found nothing.

This question is basically the same, but the answer accepted isn't one that actually answers the original question (i.e. getting the 2PACX number programatically). [Already explained the duplicate in this paragraph but editing as it looks like I have to to address the comment below]

Greg Colbourn
  • 1
  • 2
  • What I want to do is automatically generate google docs from a template (done), share them with people (done), and then generate code for embedding them in a website (this step, generating the code, i.e. getting the 2PACX.. numbers, is where I'm stuck). – Greg Colbourn Oct 05 '19 at 17:03
  • Possible duplicate of [How to get the id for a published to the web spreadsheet in google apps scripts](https://stackoverflow.com/questions/50997579/how-to-get-the-id-for-a-published-to-the-web-spreadsheet-in-google-apps-scripts) – ZektorH Oct 07 '19 at 09:04
  • As I said in the OP, that question isn't answered and looked like it was closed by having an answer accepted that doesn't actually answer the question! – Greg Colbourn Oct 07 '19 at 13:35

1 Answers1

0

I checked in the Drive API in v2 or V3 and the url is not available when you get file details : https://developers.google.com/drive/api/v2/reference/files/get

There is an alternative url which the embedLink but this one is only available with the v2 API.

To get the url you can use this code :

function urlPublishToTheWeb(){  
  var id = 'YOUR_SHEET_ID';
  var url = 'https://www.googleapis.com/drive/v2/files/' + id  ;

  var options = {
    method      : "get",
    headers     : {"Authorization": "Bearer " + ScriptApp.getOAuthToken()},
    muteHttpExceptions:false
  }

  var html = UrlFetchApp.fetch(url,options).getContentText();
  var json = JSON.parse(html)

  Logger.log(json.embedLink)
}

Then go to log (CTRL + Enter) to view the link. If you check your link and this link it looks like pretty similar, just the tabs are at bottom and not top.

Stéphane

St3ph
  • 2,232
  • 17
  • 17
  • Thanks, but that gives: "...1wsZ-qwYS_IlNA7oK2ri4xf3C3176hlDyii27yyh3IGE/preview?ouid=104406273905203370907" when what I want is "...2PACX-1vRIxx25NDOREZl3DFlGXFlSeVSidziWNElqLfjdDy8Z8PwnN-m9gXomnbKfHZwL-z3zhgpKismqXA_r/pub" – Greg Colbourn Oct 05 '19 at 16:56
  • Yes I told you this url is not available in the API so impossible to get it. An alternative is the embedlink url which provide almost the same view. If you want absolutely the url there is no solution with drive API for that,v will remain a manual action. – St3ph Oct 07 '19 at 05:08
  • Ok, thanks, trying that embed link gives a blank grey box when embedding though. Do I need to publish to the web in the script rather than manually as well for it to work? Trying the answer here - https://stackoverflow.com/questions/40476324/how-to-publish-to-the-web-a-spreadsheet-using-drive-api-and-gas - results in it failing at the line "var revisions = Drive.Revisions.list(fileId);" – Greg Colbourn Oct 07 '19 at 08:16
  • Ok, changing the flags a bit and I can get it to load as an embedded document, but it massively slows down the web page to the point of being unusable unfortunately (and it still has a grey box around it that I don't want). – Greg Colbourn Oct 07 '19 at 08:25
  • In that case you will have to get url manually in the app, no solution with API... – St3ph Oct 07 '19 at 14:44