Q:

add text to google spread sheet as hyper link

Requirement is that I have to create a index in a worksheet which has many sheets, so from index sheet I can directly navigate to target.
I have written following script

// custom menu function
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.addItem('toTablrOfContent','toTablrOfContent')
      .addToUi();
  
}

// function to write selected cell data to index sheet
function toTablrOfContent() {
 
 var selectdCellData = SpreadsheetApp.getActiveSpreadsheet().getActiveCell().getValue();
 var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[11]; // 11 is the index sheet 
 sheet.appendRow([selectdCellData]);
  

This script is sending selected cell data to last row of "index" sheet
 but I am not able to insert this as a hyper link to selected cell. Can this be done ?

A:

// custom menu function
function onOpen() {
   var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
      .addItem('toTablrOfContent','toTablrOfContent')
      .addToUi();
}

// function to write selected cell data to index sheet
function toTablrOfContent() {

 var selectdCellData = SpreadsheetApp.getActiveSpreadsheet().getActiveCell().getValue();

// selected data to be sent to 11th sheet
 var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[11]; 

//I am entring this to 5th column
  sheet.appendRow(['','','','','=HYPERLINK("#gid=' + SpreadsheetApp.getActiveSpreadsheet().getActiveCell().getGridId()
  + '&range=' 
  +SpreadsheetApp.getActiveSpreadsheet().getActiveCell().getA1Notation()
  +'","'
  + selectdCellData
    +'")'])

