如何在 Google Sheets App Script 中创建范围的超链接? [英] How to create hyperlink to range in Google Sheets App Script?

问题描述

我试图在单元格中填充超链接到 Google Sheets 应用程序脚本中的范围，其结果与我在 GUI 中完成时得到的结果相同.我设法以gid = ..."的形式创建到工作表的超链接，其中......是一个工作表ID.但是我很难获得在 GUI 中生成超链接时使用的 rangeID，例如

I'm trying to fill cells with hyperlinks to ranges in Google Sheets app script with the same desired outcome I would get had I done it in GUI. I managed to create hyperlinks to sheet in the form of "gid=..." with the ... being a sheetID. But I struggle to get the rangeID that is used when generating the hyperlink in GUI e.g.

HYPERLINK("#rangeid=1420762593";"'List 4'!F2:F15") 

是否可以在应用脚本中创建指向范围的超链接?

Is it possible to create hyperlinks to ranges in app script?

推荐答案

是的，您可以在 App Script 中执行此操作.这是一个非常简单的实现，其中 HYPERLINK 函数被构建并附加到一个单元格:

Yes, you can do this in App Script. Here's a very simple implementation where the HYPERLINK function is built and appended to a cell:

function hyperlinkRange() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName("Sheet1");
  var sheet2 = ss.getSheetByName("Sheet2").getSheetId();

  sheet1.getRange("A1").setValue('=hyperlink("#gid='+sheet2+'&range='+sheet1.getRange('A1:A10').getA1Notation()+'", "Click to jump to Sheet 2")');
}

您可以将其与循环结合使用，以设置跨多个工作表的链接值.

You can combine this with loops to set a value of links across multiple sheets.

这篇关于如何在 Google Sheets App Script 中创建范围的超链接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！