0

So I know I can use the =Hyperlink function to create a link to a website and I know you can manually make a internal link between cells using the manual creation tool but I want to do this using a formula.

I have a script that returns all the names of the other tabs - I now want to make the name in that list a link to that tab.

This is my script

function sheetnames() {
var out = new Array()
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
for (var i=0 ; i<sheets.length ; i++) out.push( [ sheets[i].getName() ] )
return out 
}

TIA

Mark Ainsworth
  • 1

2 Answers2

1

see this example:

=ARRAYFORMULA(IF(B:B="",,HYPERLINK("#gid=1734824104&range=B"&ROW(B:B), B:B)))

0000

where gid number can be found here:

enter image description here

also check: https://stackoverflow.com/a/58712023/5632629

player0
  • 124,011
  • 12
  • 67
  • 124
0

Recommendation:

You can try like this method on your existing script:

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

function sheetlinks() { //Add this custom function to return Spreadsheet URL with unique sheet IDs added at the end
for (var i=0 ; i<sheets.length ; i++) out.push( SpreadsheetApp.getActiveSpreadsheet().getUrl()+"#gid="+[ sheets[i].getSheetId() ]);
return out;
}

function sheetnames() {
for (var i=0 ; i<sheets.length ; i++) out.push( [ sheets[i].getName() ] )
return out; 
}

Then, add this formula =ARRAYFORMULA(HYPERLINK(sheetlinks(),sheetnames())) on a cell.

Result:

enter image description here

SputnikDrunk2
  • 3,398
  • 1
  • 5
  • 17
  • @Mark Ainsworth You're welcome. You can also up-vote this answer so that other community members who are searching for the same solution will be recommended to your post on their searches. – SputnikDrunk2 May 18 '21 at 15:34