-1

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?

  • Welcome. Can we clarify a few things? First: you gave an example of value="123" to link to sheet "123". Would you confirm whether all the other values are the names of sheets in your spreadsheet? Second: are all these values in a single column? – Tedinoz Nov 20 at 6:04
  • A conventional spreadsheet function won't convert a cell value to a hyperlink; this is because you need to choose a cell in which to design and execute the formula (say the cell in an adjacent column), Even then, the hyperlink in the adjacent cell will be dependant on the value in the original cell. So what? you might ask. Well, it means you need to use a script that will insert a link into the source cell - replacing the value with the link. – Tedinoz Nov 20 at 6:07
  • Yes, all the values in one column contain the names of all other sheets in the document. Adding a new column referencing the value in the original cell would not be a problem. What I see is that there is no formula that hyperlinks to another sheet. – Pere Nov 20 at 17:07
  • 1
    Refer my answer. There are likely to be many possible solutions (such as Hyperlink to a specific sheet. You might also find that, as options become apparent, your specific requirements may modify to take advantage of, or avoid risks, associated with those options. – Tedinoz Nov 20 at 20:42
  • Welcome to Web Applications. Please add a brief description of your search/research efforts as is suggested in How to Ask. – Rubén Nov 20 at 23:25
0

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

Before

After

After

| improve this answer | |
  • Thanks, that solves my problem. Replacing the contents of the actual cell was not a requirement but it's nice addition. – Pere Nov 22 at 17:46
  • _ Replacing the contents of the actual cell was not a requirement_ that's OK, you just choose a different column for writing the final array. – Tedinoz Nov 23 at 1:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.