Get filename from url google sheet

问题:

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

enter image description here

回答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);
}

回答2:

You can create a custom function in spreadsheets like this.

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