0

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.

  • @I'-'I Added an explanation of why this is not a duplicate. – BoarGules Mar 21 '18 at 10:23
0

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.

  • When my spreadsheet calls that function I get #ERROR! with the hover text You do not have permission to call getFilesByName. I've already run the function in the script editor and clicked all the security permissions. What else do I need to do? – BoarGules Mar 21 '18 at 11:50
  • I know that a custom function is called from a formula as =myfunc() but I don't know what a script editor function is for, or how I would go about calling it. There is an explanation on Stack Overflow at stackoverflow.com/questions/45330405/… but I'm none the wiser after reading it. So I conclude that what I want is perfectly possible, but requires developer-level skills, that the problem doesn't justify the time needed to acquire. – BoarGules Mar 21 '18 at 13:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.