Getting Google Sheet URL from Name

Question

I have a google sheet file that needs to find the URL of another google sheet where only its name is known (I need its URL so I can retrieve the data externally through other apps).

To simplify this, I save both g-sheets files under the same folder in Google Drive. I understand I can do this within the same sheet as below, how can I do this when the "ss" refers to another google sheet file and I only know its name.

var ss = SpreadsheetApp.getActiveSpreadsheet();
Logger.log(ss.getUrl());

Ideally I want to do it through a cell formula but will be ok if I need to add a function to "script editor".

Answer 1

If you have the id of the folder both spreadsheets are in, why not run a query on the folder and filter out the file you are looking for by name.. Once you have that file, then grab it's id.. With the ID you can then parse it into a google spreadsheet URL. Hope this helps.

Answer 2

I got it working till here where it runs ok from the Script editor:

function listFiles() {
  var baseFolderObject = DriveApp.getFolderById('0B38VWNq67cFHZVdWcE5KREZXS1U');

  var results = [];
  var types = [MimeType.GOOGLE_SHEETS]; //, MimeType.GOOGLE_DOCS];
  for (var t in types) {
    var files = baseFolderObject.getFilesByType(types[t]);

     var file, data, sheet = SpreadsheetApp.getActiveSheet();

    sheet.appendRow(["Name", "Date", "Size", "URL", "Download", "Description", "Type"]);

    while (files.hasNext()) {
      var file = files.next();
      results.push(file);

    data = [ 
      file.getName(),
      file.getDateCreated(),
      file.getSize(),
      file.getUrl(),
      file.getId()
    ];

    sheet.appendRow(data);
    }

  }
}

The only problem I have now, if I want to run from the Spreadsheet it says "You do not have permission to call "getFolderById".