如何使用公式在Google工作表中插入指向单元格的超链接? [英] How to insert hyperlink to a cell in Google sheet using formula?

问题描述

我试图以一种可以使用'= MATCH()'复制的方式插入到单元的超链接.功能.但是，如果不使用GID，我似乎无法找到一种链接Google表格中单元格的方法.

当我右键单击并获取指向此单元格的链接"时，我得到一个网址为#gid = 1933185132"的网址到底.但是，它没有结构，我不能将其与MATCH公式一起使用并像通常在Excel中那样自动填充.

https://docs.google.com/spreadsheets/d/sheetkey/edit#gid = 1933185132

但是如果有这样的单元格引用

https://docs.google.com/spreadsheets/d/sheetkey/edit#Sheet1！C12

我可以轻松地为MATCH函数重新创建它.

问题:是否有一种替代方法，可以像上面显示的那样链接单元格? 如果不 我可以使用公式提取"Sheet1！C12"的GID吗?

我已尽我所能搜索了Google论坛和堆栈溢出问题，而我看到的唯一解决方案似乎是将脚本与"var sheet"结合使用，我对0的编码知识一无所知.

这应该是一件非常简单的事情，但是我找不到出路.任何对此问题的见解都将受到赞赏.非常感谢.

解决方案

范围

您可以创建这样的链接:

=hyperlink("#gid=1166414895range=A1", "link to A1")

每个标签都有一个唯一的键，称为gid，您可以在链接中找到它:

• #gid将永远不会改变.标签名可能会更改，并且使用gid更加安全，公式也会中断.
您需要使用match，address函数找到
• A1，以获得动态链接.

我找不到有关此主题的文档，也找不到使用选项卡名称的方法.

命名范围

1. 在文档中定义一个命名范围.
2. 在单元格中选择插入链接".
3. 在链接对话框中，选择此电子表格中的命名范围".
4. 选择在步骤1中创建的范围的名称.
5. 点击应用"按钮.
6. 将鼠标指针移到新链接上.弹出式窗口包含类似于#rangeid = nnnnnnnnnn"的链接，将会出现.
7. 右键单击弹出窗口中的链接以打开浏览器的上下文菜单.
8. 选择复制链接地址"或只是复制"功能.

根据您的浏览器，剪贴板现在将包含完整的URL:

https://docs.google.com/spreadsheets/d/xxxxxxxxxx/edit#rangeid=nnnnnnnnnn

否则剪贴板将只有范围ID片段:

#rangeid=nnnnnnnnnn

如果只有片段，则需要将其附加到文档的URL上，以创建范围的完整URL.

也许还有其他一些更简单的方法来获取范围ID，但是我还没有注意到.请参阅相关问题， 回答类似的问题.

PS:复制了指定范围的URL后，您可以按照上述步骤删除创建的链接.

自定义功能

在公式中使用.

简单范围:

=HYPERLINK(getLinkByRange("Sheet1","A1"), "Link to A1")

命名范围:

=HYPERLINK(getLinkByNamedRange("NamedRange"), "Link to named range")

将代码插入脚本编辑器(工具>脚本编辑器"):

function getLinkByRange(sheetName, rangeA1, fileId)
{
  // file + sheet
  var file = getDafaultFile_(fileId);  
  var sheet = file.getSheetByName(sheetName);
  
  return getCombinedLink_(rangeA1, sheet.getSheetId(), fileId, file)    
}

function getLinkByNamedRange(name, fileId)
{
  // file + range + sheet
  var file = getDafaultFile_(fileId);    
  var range = file.getRangeByName(name);  
  var sheet = range.getSheet();
  
  return getCombinedLink_(range.getA1Notation(), sheet.getSheetId(), fileId, file)
      
}


function getDafaultFile_(fileId)
{
  // get file
  var file;
  if (fileId) { file = SpreadsheetApp.openById(fileId); }
  else file = SpreadsheetApp.getActive();      
  return file;      
}

function getCombinedLink_(rangeA1, sheetId, fileId, file)
{
  var externalPart = '';
  if (fileId) { externalPart = file.getUrl(); }    
  return externalPart + '#gid=' + sheetId + 'range=' + rangeA1;   
}

I am trying to insert a hyperlink to a cell in a fashion that can be replicated using '=MATCH()" function. However, I can't seem to figure out a method to link a cell in Google sheets without using the GID.

When I right-click and "Get link to this cell" I get a URL with "#gid=1933185132" in the end. However this has no structure and I can't use it with a MATCH formula and autofill this like I normally do in Excel.

https://docs.google.com/spreadsheets/d/sheetkey/edit#gid=1933185132

However if this is has a cell reference like so

https://docs.google.com/spreadsheets/d/sheetkey/edit#Sheet1!C12

I can easily recreate it for the MATCH function.

Question: Is there an alternate way to link cell like I have shown above? If not Can I use a formula to extract the GID of "Sheet1!C12"?

I have searched the google forums and stack overflow to the best of my extent and the only solutions I saw seemed to use scripts with "var sheet" something which I cant make sense of having 0 knowledge of coding.

It should be a very straightforward thing to do, but I am not able to find a way out. Any insight into the issue is appreciated. Thank you very much.

解决方案

Range

You can create a link like this:

=hyperlink("#gid=1166414895range=A1", "link to A1")

Each tab has a unique key, called gid, you'll find it in the link:

• #gid will never change. Tab name may be changed and the formula will break, using gid is safer.
• A1 is a part you need to find using match, address functions to get dynamic links.

I could not find a documentation on this topic, and could not find a method using tab names.

Named Range

1. Define a named range in the document.
2. In a cell select "Insert link".
3. In the link dialog box, select "Named ranges in this spreadsheet."
4. Select the name of the range created in step 1.
5. Click the "Apply" button.
6. Move mouse pointer over the new link. A pop-up containing a link like "#rangeid=nnnnnnnnnn" will appear.
7. Right click on the link in the pop-up to open your browser's contextual menu.
8. Select the "Copy Link Address" or just "Copy" function.

Depending on your browser, your clipboard will now contain either the full URL:

https://docs.google.com/spreadsheets/d/xxxxxxxxxx/edit#rangeid=nnnnnnnnnn

Or the clipboard will have just the range ID fragment:

#rangeid=nnnnnnnnnn

If you have only the fragment, you'll need to append it to the URL of the document to create a complete URL for the range.

There may be some other, simpler way to get the range ID, but I've not noticed one yet. See related question, answer to a similar question.

PS: After you've copied the URL for the named range, you may delete the link that was created by following the steps above.

Custom functions

Use in a formula.

Simple range:

=HYPERLINK(getLinkByRange("Sheet1","A1"), "Link to A1")

Named range:

=HYPERLINK(getLinkByNamedRange("NamedRange"), "Link to named range")

The code, insert into the script editor (Tools > Script Editor):

function getLinkByRange(sheetName, rangeA1, fileId)
{
  // file + sheet
  var file = getDafaultFile_(fileId);  
  var sheet = file.getSheetByName(sheetName);
  
  return getCombinedLink_(rangeA1, sheet.getSheetId(), fileId, file)    
}

function getLinkByNamedRange(name, fileId)
{
  // file + range + sheet
  var file = getDafaultFile_(fileId);    
  var range = file.getRangeByName(name);  
  var sheet = range.getSheet();
  
  return getCombinedLink_(range.getA1Notation(), sheet.getSheetId(), fileId, file)
      
}


function getDafaultFile_(fileId)
{
  // get file
  var file;
  if (fileId) { file = SpreadsheetApp.openById(fileId); }
  else file = SpreadsheetApp.getActive();      
  return file;      
}

function getCombinedLink_(rangeA1, sheetId, fileId, file)
{
  var externalPart = '';
  if (fileId) { externalPart = file.getUrl(); }    
  return externalPart + '#gid=' + sheetId + 'range=' + rangeA1;   
}

这篇关于如何使用公式在Google工作表中插入指向单元格的超链接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！