将文本作为超链接添加到 Google 电子表格

Question

>要求是我必须在具有许多工作表的工作表中创建一个索引，因此我可以从索引表中直接导航到目标。

我写了以下脚本

// 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]);

此脚本将选定的单元格数据发送到"索引"工作表的最后一行 但我无法将其作为超链接插入到所选单元格。这能做到吗？

Answer 1

添加超链接：

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var cell = sheet.getRange("B5");
cell.setFormula('=HYPERLINK("http://www.google.com/","Google")');

Answer 2

// 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
+'")'])