如何动态设置指向区域中单元格的链接并使用相邻值 (GAS) 填充目标?

Question

我得到了以下一段代码，将链接设置为仅指向指定范围内的第一个单元格。

function addHyperlink() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var vGeral = ss.getSheetByName("Visão Geral");//where the range whose cells will have a link is
var eSheet = ss.getSheetByName("EditarItem").getSheetId();//destination sheet
var rng = vGeral.getRange(18,2,vGeral.getLastRow(),3).getValues();//range where values to be brought to dest sheet are
var destCell = ss.getSheetByName("EditarItem").getRange("X4").getA1Notation;//destination cell
var rownum = vGeral.getRange("A18:A").getRow();//get rows where setValue(hyperlink) will occur
for (var a = 1; a < rng.length; a++) {
Logger.log(rng[a][0]);
if (rng[a][0] != '') {
var code = rng[a][1];
var item = rng[a][2];
var version = rng[a][3];
vGeral.getRange(rownum,1).setValue('=HYPERLINK("https://docs.google.com/spreadsheets/d/sheetId/edit#gid=2121018263&range=X4:AA4";"Editar")');
}
} 
}

当我单击上面生成的链接时，我将如何从 B 列和 C 列获取值并填充目标单元格？ 谢谢！

Answer 1

没有rng[a][3]var version = rng[a][3];

rng 中只有 3 列：

var rng = vGeral.getRange(18,2,vGeral.getLastRow(),3).getValues();

而罗纳姆总是 18 岁：

vGeral.getRange(rownum,1).setValue('=HYPERLINK("https://docs.google.com/spreadsheets/d/sheetId/edit#gid=2121018263&range=X4:AA4";"Editar")');