CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-drive-api
Neural_oD
Neural_oD

Reputation: 47

Get File Name from list of URL's - Google Drive

So I'm needing to get the list of file names from a range of Google Drive URLs in a spreadsheet. Browsing around the net, I came across the code below. It works but only for the old style urls, which I heard Google changed in September 2021. Note that links are not fully functional, please replace with real links to check!

The old style is: https://drive.google.com/file/d/1GMUwYxZxsNpLiaYOiVMBwl41LpreQ-fc/view?usp=sharing This works correctly from the code below.

What I'd like though is two things.

It should handle a range of a couple of columns, currently reading AE2:AE, and printing out on AM2:AM. What I'd like is to go through the range: AE2:AL and print out: AM2:AT
Secondly it should also handle the newer form urls: https://drive.google.com/file/d/0B9EZQqsLDEqDUGlsdy1oVEtETGs/view?usp=sharing&resourcekey=0-h7HOcxayPaHJ5r6dAAslVQ


Current Code: 
    function getNames() {
  var activeRange = SpreadsheetApp.getActiveSheet().getDataRange();
  var height = activeRange.getHeight();
  var links = SpreadsheetApp.getActiveSheet()
    .getRange("AE2:AE" + height)
    .getValues();

  var nameValues = [];

  links.forEach((row) => {
    try {
      var link = row[0];
      var fileID = getIdFromLink(link);
      var name = DriveApp.getFileById(fileID).getName();
      nameValues.push([name]);
    } catch (e) {
      nameValues.push(["NO NAME FOUND"]);
    }
  });

  var nameRange = SpreadsheetApp.getActiveSheet().getRange("AM2:AM" + height);
  nameRange.setValues(nameValues);
}

function getIdFromLink(link) {
  var regex = new RegExp(
    /(?<=https:\/\/drive\.google\.com\/file\/d\/)(.+)(?=\/)/
    );
  return regex.exec(link)[0];
}

How should the code above be modified to enable what I'm wanting. Sorry, I tried a couple of if/else statements, but my Javascript knowledge is severely limited. Any help would be greatly appreciated.

Current "screenshot" showing:
(1) - Old style url - correctly picking up file name (2) (3) - New style url - not picking up file name (4)

enter image description here

Upvotes: 1

Views: 3251

Answers (2)

AnthonyNjina
AnthonyNjina

Reputation: 11

You do not need any script... just copy the columns with the links to a separate column. Highlight all the links in the the copied column, right click, scroll down to "smart chips" and select "convert to file chips"

Upvotes: 1

doubleunary
doubleunary

Reputation: 19220

Your getIdFromLink() function should work just fine as long as the files have not been shared in such a way that they require a resource key as well.

To work with resource keys, use DriveApp.getFileByIdAndResourceKey(), like this:

function getFileNamesByLink() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const sourceRange = sheet.getRange('AE2:AL');
  const targetRange = sheet.getRange('AM2');
  const fileNames = sourceRange.getValues()
    .map(row => row.map(link => getFileNameFromLink_(link)));
  targetRange
    .offset(0, 0, fileNames.length, fileNames[0].length)
    .setValues(fileNames);
}


function getFileNameFromLink_(link) {
  if (!link) {
    return null;
  }
  const fileId = getIdFromLink_(link);
  if (!fileId) {
    return NaN;
  }
  let file;
  try {
    file = DriveApp.getFileById(fileId);
  } catch (error) {
    try {
      file = DriveApp.getFileByIdAndResourceKey(fileId, getResourceKeyFromLink_(link));
    } catch (error) {
      return NaN;
    }
  }
  return file.getName();
}


function getIdFromLink_(link) {
  const match = String(link).match(/file\/d\/([-\w]+)/i);
  return match ? match[1] : null;
}


function getResourceKeyFromLink_(link) {
  const match = String(link).match(/resourcekey=([-\w]+)/i);
  return match ? match[1] : null;
}

Note that the script may time out if you have thousands of links. If that happens, process the links in a piecemeal fashion, or see if the Advanced Drive Service works for you.

Upvotes: 2

Related Questions