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)