Google sheets: get a list of the sheet names giving a GS file url-CodePudding

I have been trying for many hours to get a script that returns the list of names of the sheets of a GS document after giving it the url of said document or the cell that contains said url in the document I am working on. I previously found this proposal that I modified slightly but it doesn't work for me: Create list of all sheet names in Google Apps Scripts

function getSheetNamesFromOtherGSfile(otherGSfileURL) {
  var spreadSheetAURL = otherGSfileURL;
  var sheetNameArray = [];

  var spreadSheetsInA = SpreadsheetApp.openByUrl(spreadSheetAURL).getSheets();

  sheetNameArray = spreadSheetsInA.map(function(sheet) {
    return [sheet.getName()];
  });

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getRange(1, 1, sheetNameArray.length, 1);
  range.setValues(sheetNameArray);
}

Thanks.

CodePudding user response：

According to the documentation you cannot call Spreadsheet.openById() within a custom function in a cell.

The approach I recommend is to create a container-bound function, see docs here. With these function, which can be called from a custom menu, there are none of those limitations.

CodePudding user response：

Finally I have solved the problem in a more laborious way (and it will give me more work later on if I don't change it) going through the twenty GS files from which I need the names of the sheets and in each one putting a script function that only extracts the names of the sheets and write the list in other sheet of the same file. Then, in the analysis file where I tried to do all that in a single function, using IMPORTRANGE I obtained the names of the sheets of each file from this information I can now extract the information I need. Thanks again for your help, Neven.