I am trying to create a single GSheets Formula in Appscript that will create an output of all worksheets in the file including a hyperlink.

I currently have a formula that creates all sheet names, another that creates the list of GIDs, then I am using the =Hyperlink function in the front end to join them together

I would like to be able to do this by using a single formula to create an array which would have just column C as the output, eg the name of every worksheet and a hyperlink to that sheet.
This is what I have attempted using sheetlink() however it just pastes the formulas without actually running them so there is no link.

function sheetnames() { 
  var out = new Array()
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();

  var indexSheetNames = [];
  var indexSheetIds = [];

   // create array of sheet names and sheet gids
  sheets.forEach(function(sheet){
    
        indexSheetNames.push([sheet.getSheetName() ]);
                        Logger.log(indexSheetNames)                    
  })

  return indexSheetNames;

}

function sheetIDs() { 
  var out = new Array()
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();

  var indexSheetNames = [];
  var indexSheetIds = [];

   // create array of sheet names and sheet gids
  sheets.forEach(function(sheet){
    
    indexSheetIds.push([sheet.getSheetId() ]);
                        Logger.log(indexSheetIds)

                       
  })

  return indexSheetIds;

}
function sheetlink() { 
  var out = new Array()
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();

  var indexSheetNames = [];
  var indexSheetIds = [];

   // create array of sheet names and sheet gids
  sheets.forEach(function(sheet){
    
    indexSheetIds.push(['=hyperlink("#gid=' 
                          sheet.getSheetId() 
                          '","' 
                          sheet.getSheetName() 
                          '")']);
                        Logger.log(indexSheetIds)

                       
  })

  return indexSheetIds;

}

CodePudding user response：

You don't need a third custom function for that, you already got it. Use your two functions like this:

=ArrayFormula(HYPERLINK("#gid="&sheetIds(), sheetNames()))