Get filename from url google sheet

Question

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

Welcome to Stack Overflow! Please add images rather as images than as links. This time I've done this for you - please see [the editing help](https://stackoverflow.com/editing-help) for more information on formatting. You probably have to add more details to your question, read [how to ask](https://stackoverflow.com/help/how-to-ask). — jay.sf, Jul 18 '18 at 07:09
Tried anything? Done any research *at all*? Why is this tagged both Excel and Google spreadsheet? Are your links in Excel, and you're trying to get the name of files on Google Drive? This is why providing specific details in your question helps everyone - your question is well received, and people trying to answer have no ambiguities to resolve (or ignore). — tehhowch, Jul 18 '18 at 10:50

score 1 · Accepted Answer · answered Jul 18 '18 at 09:28

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

answered Jul 18 '18 at 09:28

Diego

9,261
2
19
33

2

This assumes that all links will be spreadsheet documents btw. Given that the name is wanted, I would use `DriveApp` instead for maximum compatibility. – tehhowch Jul 18 '18 at 10:20
Thanks, that's a very fair point @tehhowch. Given the image, it looks like they're all spreadsheets, but I'll leave it to the poster to modify the code if needed. – Diego Jul 18 '18 at 10:29
Your function name is generic :) One would certainly hope anyone who copies code to achieve a purpose actually reads and comprehends said code, but there are plenty who ask questions about "Service Unavailable: Docs / Sheets" errors when their own code blindly opens a file without checking for the appropriate mimetype. – tehhowch Jul 18 '18 at 10:48
I get error message "Document 1TEvq1qIAAVAeXfiRTj3GE-5SOA3n5ojWAijxwkJVssQ is missing (perhaps it was deleted?)" – Jul 19 '18 at 02:27
@Diego can you edit the question to make your answer useful? I struggle to understand what OP is asking. You seem to have understood, so please edit the question so that others can understand it and your solution. – Cornelius Roemer Jul 03 '21 at 19:47

score 0 · Answer 2 · answered Jul 02 '21 at 19:19

Another way

function getFileNames() {
  var driveApp = DriveApp;
  // SET THE SHEET HERE
  var sheet = SpreadsheetApp.getActive().getSheetByName("Sheet1");
  //SET THE URL LINK COLUMN HERE : From row 2 since row 1 is the header row till last row
  var links = sheet.getRange("P2:P").getValues();

  var filenames = []; 

  for (var i = 0; i < links.length; i++) {
    var fileId = getIdFromUrl(links[i][0]);
    if (fileId != "" && fileId != null) {
      var getfile = DriveApp.getFileById(fileId);
      var filename = getfile.getName();
      Logger.log(filename);
      filenames.push([filename]);
    } else {
      filenames.push([""]); 
    }
  }

  // SET STARTING ROW TO PRINT: From row 2 since row 1 is the header row
  var startRow = 2; 
  // SET WHICH COLUMN TO PRINT : Column A = column 1 / Column B = column 2
  // MAKE SURE THE SHEET LAST COLUMN HEADER IS FILLED + 1 (next column)
  var fileNameColumn = sheet.getLastColumn() + 1;  
  var destination = sheet.getRange(startRow, fileNameColumn, filenames.length, filenames[0].length);
  destination.setValues(filenames);
}

function getIdFromUrl(url) { return url.match(/[-\w]{25,}/); }

score -2 · Answer 3 · answered Jul 18 '18 at 06:48

-2

You can create a custom function in spreadsheets like this.

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

answered Jul 18 '18 at 06:48

Amit Agarwal

10,910
1
32
43

4

The argument should be `url` instead of `name` – Casper Jul 18 '18 at 06:50
The script is not running sir. https://docs.google.com/spreadsheets/d/1UfZuhRRpAOBe8bKKTfELlPmcnV9FjMhbxDVxF3BAvY4/edit#gid=196705363 – Jul 18 '18 at 07:00
1

You can't use a custom function that opens _other_ spreadsheets. https://developers.google.com/apps-script/guides/sheets/functions#using_apps_script_services – Diego Jul 18 '18 at 09:24

Get filename from url google sheet

3 Answers3

Linked