Generate programmatically hyperlinks to sheets in Google Sheets

Question

I know that is possible to generate hyperlinks programmatically with a script or a formula to a website taking a cell value as a reference. But in this case I need to generate a hyperlink to another existing sheet.

For example I have a cell with a "123" value, and an existing sheet named "123". I can generate the hyperlink with right click > insert a hyperlink in the cell, but I need to do that programmatically with another 200 rows based on its value.

The problem is that I don't know how to do that, as linking to a sheet doesn't generate a formula to copy. Do you know if it is possible to do that?

Answer 1

You have a column of values and each value represents the name of a sheet in the active spreadsheet. You want to convert each value into a hyperlink to the respective sheet.

It is likely that there are several ways to successfully achieve your goal. Please consider this answer as one alternative.

The following script creates a HYPERLINK() formula for each value. In this version of the script the value is replaced by the formula. Each formula is appended to a temporary array, and the spreadsheet is updated using SETFORMULAS() (ref) so that there need only be one instance of writing to the spreadsheet, and that is after every value has been processed.

As noted by @ScottPrive in Hyperlink to a specific sheet, there is a limitation:

• the #gid trick works [... ]AS LONG AS no one deletes any worksheet tabs... very important to note that #gid is a FIXED POSITION number

A simple alternative to this script might be to write the temporary array to an adjacent column (or even a column in another sheet/tab) so that the original value can be retained, and the script re-run in the event of any issues regarding adding, renaming, or deleting sheets.

---

function linkcell() {
   
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourcesheet = "Sheet1"
  var source = ss.getSheetByName(sourcesheet);
  
  // get the values in column A
  // assume that data starts in row 1
  var Acol = source.getRange("A1:A");
  var Avals = Acol.getValues();
  var Alast = Avals.filter(String).length;
  //Logger.log("DEBUG: number of rows of data in Column A = "+Alast)
  
  var range = source.getRange(1, 1, Alast)
  var values = range.getValues();
  //Logger.log("DEBUG: The data range in Column A is "+range.getA1Notation())
  
  // create a temporary array to hold the formula
  var formulaarray=[];
  
  // loop through the vales and create a formula for each 
  for (var i=0;i<Alast;i++){
  
    // convert number to string
    var cellstring = values[i][0].toString();
    // Logger.log("DEBUG: i:"+i+", value = "+cellstring)
    
    // create a variable for the target sheet/cell value
    var target = ss.getSheetByName(cellstring);
    //Logger.log("DEBUG: the sheet id = "+target.getSheetId())
    
    // create a hyperlink formula
    var formula = '=HYPERLINK("#gid='+target.getSheetId()+'","'+cellstring+'")';
    // Logger.log("DEBUG: formula = "+formula);
    
    // push the formula onto the array
    formulaarray.push([formula]);
    
  }
  
  
  // Logger.log(formulaarray); / DEBUG

  // update the range with hyperlinks
  // removes the value and replaces with formula
  // note: this could applied to any column of cells if the original values need to be retained
  range.setFormulas(formulaarray);

  
  return;
}

---

Before

After