Fetch Google Drive File path in Google Sheet

Question

Want to fetch Google Drive File path, by exact File Name search with Extension and update in other column "Drive File Path"

function onOpen() {
  let menu = SpreadsheetApp.getUi().createMenu("Copy Drive Path");
  menu.addItem ("CopyDPath","copypath");
  menu.addToUi();
}
//Copy Drive Path
function copypath() {
  // put your code here, want to search Drive path by searching through EXACT file name with Extension
}

Get Link option looks like this

Answer 1

Try

function copypath() {
  const sh = SpreadsheetApp.getActiveSheet()
  var data = sh.getDataRange().getValues()
  data.forEach((d, i) => { try { if (i > 0) d[2] = getPath(d[1]) } catch (e) { } })
  sh.getDataRange().setValues(data)
}
function getPath(fileNameWithExtension) {
  parents = DriveApp.getFileById(getFileId(fileNameWithExtension)).getParents()
  var path
  while (parents.hasNext()) {
    folder = parents.next();
    folderName = folder.getName();
    if (path == null) path = folderName;
    else path = folderName + '/' + path;
    parents = folder.getParents();
  }
  return path
}
function getFileId(name) {
  var files = DriveApp.getFilesByName(name);
  while (files.hasNext()) {
    var file = files.next();
    return (file.getId())
  }
}

references

searchFiles()

getParents()

hasNext()

edit

to get a clickable link, try

function copypath() {
  const sh = SpreadsheetApp.getActiveSheet()
  var data = sh.getDataRange().getValues()
  data.forEach((d, i) => {
    try {
      if (i > 0) {
        var parents = DriveApp.getFileById(getFileId(d[1])).getParents()
        while (parents.hasNext()) {
          folder = parents.next();
          d[2] = folder.getUrl();
        }
      }
    } catch (e) { }
  })
  sh.getDataRange().setValues(data)
}
function getFileId(name) {
  var files = DriveApp.getFilesByName(name);
  while (files.hasNext()) {
    var file = files.next();
    return (file.getId())
  }
}

edit2

to retrieve the link for each file

function clickableLink() {
  const sh = SpreadsheetApp.getActiveSheet()
  var data = sh.getDataRange().getValues()
  data.forEach((d, i) => {
    try {
      if (i > 0) {
          d[2] = getFileUrl(d[1]);
      }
    } catch (e) { }
  })
  sh.getDataRange().setValues(data)
}
function getFileUrl(name) {
  var files = DriveApp.getFilesByName(name);
  while (files.hasNext()) {
    var file = files.next();
    return (file.getUrl())
  }
}

Answer 2

I believe your goal is as follows.

• You want to search the files using an extension like .pdf.
• You want to retrieve the filenames and the file path on Google Drive.
• You want to put them in the columns "B" and "C".
• Your files are existing in your Google Drive.
• You want to achieve this using Google Apps Script.

In this case, how about the following sample script?

Usage:

In this sample script, a Google Apps Script library is used.

1. Install Google Apps Script library.

Please install FilesApp Google Apps Script library. You can see the method for installing it at here.

2. Enable Drive API.

Please enable Drive API at Advanced Google services.

2. Sample script:

Please copy and paste the following script to the script editor of Spreadsheet and set your sheet name and save the script.

function copypath() {
  const sheetName = "Sheet1"; // Please set the sheet name.
  const extension = ".pdf"; // Please set the extension you expect.

  // 1. Retrieve folder structure from Google Drive.
  const res = FilesApp.getAllFoldersInFolder("root");
  const obj = res.name.reduce((o, e) => (o[e[e.length - 1]] = e.join("/"), o), {});

  // 2. Retrieve files by an extension and create an array for putting to Spreadsheet.
  const files = DriveApp.searchFiles(`title contains '${extension}' and trashed=false`); // or const files = DriveApp.searchFiles(`mimeType='application/pdf' and trashed=false`);
  const values = [];
  while (files.hasNext()) {
    const file = files.next();
    const filename = file.getName();
    if (filename.slice(-4).toLowerCase() == extension) {
      const parent = file.getParents();
      values.push([filename, obj[parent.hasNext() ? parent.next().getName() : ""] || "No parent"]);
    }
  }

  // 3. Put the array to Spreadsheet.
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  sheet.getRange(2, 2, values.length, 2).setValues(values);
}

• When this script is run, the following flow is run.

  1. Retrieve folder structure from Google Drive.
     • Here, FilesApp of Google Apps Script library is used.
  2. Retrieve files by an extension and create an array for putting into Spreadsheet.
  3. Put the array on Spreadsheet.
     • The retrieved values are put to the columns "B" and "C".

• If you want to retrieve the PDF file, you can also use const files = DriveApp.searchFiles(`mimeType='application/pdf' and trashed=false`); instead of const files = DriveApp.searchFiles(`title contains '${extension}' and trashed=false`);.

Note:

• This is a simple sample script. So, please modify this for your actual situation.

References:

• FilesApp of Google Apps Script library
• reduce()

Added:

From your reply,

No, this is not what I want. What I want is extract file path through File Name Search

If you want to retrieve the filenames from the column "B" of Spreadsheet and retrieve the file path, and want to put the file path to the column "C" of the Spreadsheet, how about the following sample script?

Sample script:

function sample2() {
  const sheetName = "Sheet1"; // Please set the sheet name.

  // 1. Retrieve folder structure from Google Drive.
  const res = FilesApp.getAllFoldersInFolder("root");
  const obj = res.name.reduce((o, e) => (o[e[e.length - 1]] = e.join("/"), o), {});

  // 2. Retrieve filenames from Spreasheet.
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const range = sheet.getRange("B2:B" + sheet.getLastRow());

  // 3. Retrieve the file path and create an array for putting to Spreadsheet.
  const values = range.getValues().map(filename => {
    const file = DriveApp.getFilesByName(filename);
    if (file.hasNext()) {
      const parent = file.next().getParents();
      return [obj[parent.hasNext() ? parent.next().getName() : ""] || "No parent"];
    }
    return ["File was not found."];
  });

  // 4. Put the array to Spreadsheet.
  range.offset(0, 1).setValues(values);
}