get Drive File ID from a Spreadsheets's published URL in Google Apps Script

Question

I have a very long list of the published URLs for a bunch of Google Spreadsheets. The URLs look like https://docs.google.com/a/company.com/spreadsheets/d/e/2PACX-[some very long string]/pubhtml?gid=[sheet ID]&single=true&widget=true&headers=false.

All of the source Spreadsheets are in a folder on a Shared Drive that I have access to.

I need to get the normal Drive File ID for each of the spreadsheets.

I tried to use UrlFetchApp to open fetch the published URL file to see if it had any information I could use to extract the Drive File ID but I get a 401 error.

Is there anyway to get the internal Drive File ID from the published URL?

Answer 1

• You know the URL of published Spreadsheet. The URL is like below.
  • https://docs.google.com/spreadsheets/d/e/2PACX-###/pubhtml?gid=0&single=true&widget=true&headers=false
• The published Spreadsheet is put in the shared folder with you.
• You know the folder ID of the shared folder. The URL is like below.
  • https://drive.google.com/drive/folders/###?usp=sharing
  • ### is the folder ID.
• You can access to the shared folder.

If my understanding for your situation is correct, how about this workaround?

Issue:

Unfortunately, the file ID cannot be directly retrieved from the published URL like https://docs.google.com/spreadsheets/d/e/2PACX-###/pubhtml?gid=0&single=true&widget=true&headers=false. I think that this might be the specification.

Workaround:

So from your situation, I would like to propose a workaround. The flow of this workaround is as follows. Please think of this as just one of several answers.

Flow:

1. Retrieve HTML data from the URL of https://docs.google.com/spreadsheets/d/e/2PACX-###/pubhtml?gid=0&single=true&widget=true&headers=false.
2. Retrieve the filename from the retrieved HTML data.
   • In this case, ### of <title>###</title> is the filename of Spreadsheet.
3. Retrieve files from the shared folder.
4. Retrieve the file ID from the filename.

When above flow is reflected to the script, it becomes as follows.

Sample script:

When you use this script, please set the URL of the published Spreadsheet and folder ID of the shared folder.

function myFunction() {
  var url = "https://docs.google.com/spreadsheets/d/e/2PACX-###/pubhtml?gid=0&single=true&widget=true&headers=false"; // URL of the published Spreadsheet.
  var folderId = "###"; // FolderId of shared folder including the published Spreadsheet.

  var res = UrlFetchApp.fetch(url);
  var filename = res.getContentText().match(/<title>(.+)<\/title>/)[1];
  var files = DriveApp.getFolderById(folderId).getFiles();
  while (files.hasNext()) {
    var file = files.next();
    if (file.getMimeType() == MimeType.GOOGLE_SHEETS && file.getName() == filename) {
      Logger.log("filename: %s, fileId: %s", filename, file.getId())
    }
  }
}

Note:

• In this workaround, the file ID is retrieved from the filename. So when there are several files with the same filename in the shared folder, several file IDs are retrieved. At that time, please retrieve the file ID you want by comparing the data of each Spreadsheet.

References:

• Class UrlFetchApp
• match()
• Class DriveApp

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