Get filename from url google sheet

Question

I have a problem. How to get the filename from the url?

enter image description here

Answer 1

I wouldn't normally do this since you haven't shown us what you've tried, but I'm feeling generous.

This function should work for you. (Note that you'll need to grant permissions for it to run.)

function getFileNames() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("Get_File_Name");
  var links = sheet.getRange("A2:A").getValues();
  var filenames = []; 
  for (var i = 0; i < links.length; i++) {
    var url = links[i][0];
    if (url != "") {
      var filename = SpreadsheetApp.openByUrl(links[i][0]).getName();
      filenames.push([filename]);
    }
  }
  var startRow = 2; // print in row 2 since row 1 is the header row
  var fileNameColumn = 2; // Column B = column 2
  var destination = sheet.getRange(startRow, fileNameColumn, filenames.length, filenames[0].length);
  destination.setValues(filenames);
}

Answer 2

You can create a custom function in spreadsheets like this.

function getSSName(name) {
  var ss = SpreadsheetApp.openByUrl(url);
  return ss.getName();
}