0

How can I get the sheet name with formula/custom function by getting the cell range which will contain the URL in a spreadsheet?

I'm using the following code, but I get the following error

TypeError: Cannot read property 'length' of undefined

  function getFileNames() {
        var sheet = SpreadsheetApp.getActive().getSheetByName("Links");
        var links = sheet.getRange("B3:B").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 = 3; // print in row 2 since row 1 is the header row
  var fileNameColumn = 3; // Column B = column 2
  var destination = sheet.getRange(startRow, fileNameColumn, filenames.length, filenames[0].length);
  destination.setValues(filenames);
}
mau
  • 185
  • 11

2 Answers2

1

Length of undefined means that your links is undefined. Check the below script for a sample working code:

Script:

function getFileNames() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("Links");
  var links = sheet.getRange("B3:B").getValues();

  // 1st: get a 1D array representation of the links (flat)
  // 2nd: remove the empty rows (filter)
  // 3rd: return the spreadsheet names of the remaining rows to filenames
  var filenames = links.flat().filter(String).map(link => {
    return [SpreadsheetApp.openByUrl(link).getName()];
  });

  var startRow = 3; // data will start at 3rd row
  var fileNameColumn = 3; // data will be written at 3rd column which is column C
  sheet.getRange(startRow, fileNameColumn, filenames.length, filenames[0].length)
       .setValues(filenames);
}

Output:

output

NightEye
  • 10,634
  • 2
  • 5
  • 24
  • I'm looking more like in a A1 the name and in B1 the URL. Also, the result should be the Spreadsheet name, not the sheet (tab) name. – mau Jan 19 '22 at 22:08
  • Hi @mau, you basically said sheet name so I provided it. File is spreadsheet while tab is sheet. Also, if you are expecting an apps script solution, include the apps script tag. – NightEye Jan 20 '22 at 16:37
  • @mau, I have modified and provided a working script above. See sample output. I modified your comments are they are off. 3 for both row and column will write to C3:C. And since your range inputted was B3:B, I assume the correct orientation is C3:C, not A2:A or C2:C – NightEye Jan 20 '22 at 16:53
0

There was already a similar question in this thread, I believe that you won't be able to call it as a custom function if it requires authorization according to this documentation.

I also found this thread that shows how to retrieve the name of the spreadsheet from cells by having the URL.

Gabriel Carballo
  • 1,278
  • 1
  • 3
  • 9