How to Reference Google Sheet by Filename

google sheetsgoogle-drivegoogle-sheets-custom-functionimportrangeworksheet-function

How to reference a Google Sheet by filename?

I would like to use IMPORTRANGE function, but it can only reference a file by URL not by filename.

Example filename:

  • 2018-11.xlsx
  • 2018-12.xlsx

Best Answer

Google Sheets hasn't a built-in function to make reference a Google Sheet by filename and a custom function can't be used because they ran anonymously and the Google Apps Script Google Drive and Google Drive Advanced services require authorization to use them.

The only way is by using scripts triggered by other means like a macro, a custom menu, dialog or installable triggers or script editor.

You could DriveApp.searchFiles(params) to get a collection of files with the specified name, then use file iterator to loop through that collection.

Example from the above link

// Log the name of every file in the user's Drive that modified after February 28,
// 2013 whose name contains "untitled".
var files = DriveApp.searchFiles(
    'modifiedDate > "2013-02-28" and title contains "untitled"');
while (files.hasNext()) {
  var file = files.next();
  Logger.log(file.getName());
}
Related Topic