Inverse of Google Sheets GetSpreadsheetNameById

Question

This post Display sheet name in Google Spreadsheets shows how to get the name of a Google sheet, given its URL.

I have the opposite problem. I know the Spreadsheet name, but I want the URL to use in an importrange() call.

That is because I have a related group of Spreadsheets, one per week, and the week number is in the name of the Spreadsheet, so this week's Spreadsheet can work out the name of last week's Spreadsheet. I'm using importrange() to carry a value forward from last week's Spreadsheet into this week's Spreadsheet. But importrange() wants a URL.

It has been suggested (duplicate proposal) that this will solve my problem:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var url = ss.getUrl());

It won't. That tells me the URL of the active Spreadsheet. But I don't want the URL of the active Spreadsheet, I want the URL of a different Spreadsheet that I know only the name of.

Answer 1

If you know the file name exactly, You can use DriveApp to search the file and getUrl()

function getFile(name) {
  var files = DriveApp.getFilesByName(name);
  while (files.hasNext()) { 
    var file = files.next();
    Logger.log('Name:'+file.getName()+'\nUrl'+ file.getUrl());
    return file.getUrl();
  }
}

If you don't know the name exactly, You can use DriveApp.searchFiles() method.