I have a Spreadsheet with a DASHBOARD + multiple (300+) sheets (within the same spreadsheet). Within the DASHBOARD I have a range column with the names of all sheets.
I'd like to automatically hyperlink all cells to sheet with corresponding name.
-
It is unclear why you want to insert hyperlinks in the dashboard. Is this an XY problem?doubleunary– doubleunary2021-12-13 13:56:13 +00:00Commented Dec 13, 2021 at 13:56
-
Sorry, a bit indistinct from me there. I'd like to insert hyperlinks to easily jump to corresponding sheet. The sheets are being used by different resources - so with a link they wont have to scroll through the enire sheet-list to find correct sheet.Fredrik Karlsen– Fredrik Karlsen2021-12-13 14:01:44 +00:00Commented Dec 13, 2021 at 14:01
-
You will find that the name box is much easier to use.doubleunary– doubleunary2021-12-13 14:06:01 +00:00Commented Dec 13, 2021 at 14:06
-
Nope, It is way easier to click onceFredrik Karlsen– Fredrik Karlsen2021-12-15 16:24:16 +00:00Commented Dec 15, 2021 at 16:24
Add a comment
|
3 Answers
Try this:
function convertToLinks() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getSheetByName("DASHBOARD");
var range = sh.getRange("A11:A15");
var data = range.getValues();
var formulaArr = [];
data.forEach(sheet => {
var targetSheet = ss.getSheetByName(sheet[0]);
if(targetSheet){
var url = ss.getUrl() + '#gid=' + targetSheet.getSheetId();
formulaArr.push(['=HYPERLINK("'+ url +'","'+ sheet[0] +'")'])
}else{
formulaArr.push([sheet[0]]);
}
})
range.setValues(formulaArr);
}
This script above will convert cells A11:A15 to hyperlinks of the corresponding sheets.
Example:
Before:
After:
References:
1 Comment
Fredrik Karlsen
Thank you so much! This is what I'm after, and I'll try to implement it as soon as a new DASHBOARD is ready to go (had to nuke the last one due to some other issues). I'll update you, and accept this as solution as soon as I give it a go.
You do not need hyperlinks to quickly jump to another sheet. Instead, use the name box. It is located in the formula bar to the left of the ƒ𝑥 symbol.
Click the name box and type part of a sheet name to quickly locate that sheet, then press Enter to go to the sheet.
3 Comments
Fredrik Karlsen
Thanks for your reply! It is a step towards what I want. Tho would I like to link the cells to corresponding sheet - so that the resources using the Spreadsheet can look at the DASHBOARD to see what "job" (A, B, C...) needs attention, and easily one-click to the correct job (sheet)
doubleunary
That is a different question. Please ask only one question per post. Post a different question regarding your reporting issue.
Fredrik Karlsen
It is only one question in the post, and it is now solved. Anyway, thanks for replying.
Try this script, but also add search for the cell you need to go to (this can be done by a formula in column C) or filter based on some more info ...
function onEdit(event){
var sh = event.source.getActiveSheet();
var cel = event.source.getActiveRange();
if (sh.getName()=='DASHBOARD' && cel.getColumn()==2 && cel.getRow()>1 && cel.getValue()){
try {
cel.setValue(!cel.getValue())
// add the right destination in the targeted sheet ...
SpreadsheetApp.getActiveSpreadsheet().getSheetByName(cel.offset(0,-1).getValue()).activate()
}
catch(err){
}
}
}
