Google电子表格脚本，可为一个单元格提供多个超链接选择 [英] A script for google spreadsheet to provide multiple hyperlink choice for one cell

问题描述

我有一个Google电子表格.在某些单元格中，它具有多个我想与单个超链接关联的名称(字符串).

I have a google spreadsheet. In some cells, it has multiple names(strings) that I would like to associate with individual hyperlinks.

例如如果我有一个像"Charles Darwin"这样的单元格，那么通过执行类似的操作就可以很容易地用该名称创建超链接 =Hyperlink(VLOOKUP("Charles Darwin", People!$A$1:$B$738, 2, false), "Charles Darwin") (请注意，我有一个人"表，可以从中获取超链接)

E.g. if I have a cell such as "Charles Darwin", it's easy for me to create a hyperlink out of this name by doing something like =Hyperlink(VLOOKUP("Charles Darwin", People!$A$1:$B$738, 2, false), "Charles Darwin") (note that I have a "People" sheet from which I grab the hyperlink)

但是，如果我恰好在该单元格中有多个条目，请说;".或换行符分隔开的位置，例如查尔斯·达尔文；乔治·华盛顿"，我无法做到这一点.我想让用户能够单击单元格，将单元格的内容发送(作为参数)到某种脚本，然后让该脚本在我的人员"表中找到那些超链接字符串，然后向用户显示该单元格旁边的弹出窗口"，可以在其中单击所需的超链接.

But if I happen to have multiple entries in that cell, say ";" or newline separated, e.g., "Charles Darwin; George Washington", I can't do that. I'd like to give the user an ability to click on the cell, have the contents of the cell be sent (as argument) to some kind of script, and for that script to find the hyperlinks in my "People" sheet for those strings, and then to present the user with a little "pop-up" right next to that cell, where the desired hyperlink could be clicked on.

我试图在此站点上找到与之类似的东西，但似乎没有类似的事情出现.也许有人可以为我提供一两个链接(或基本示例代码)，以尝试解决此问题? (我认为这是可能的.)

I tried to find something along those lines on this site, but nothing similar seemed to come up. Might someone have a link or two for me (or basic example code) that I could start with to try to solve this? (I am assuming this is possible).

推荐答案

在同一单元格上不能有两个超链接.

It's not possible to have two hyperlinks on the same cell.

可以向Google Spreadsheets编写脚本，但是我不确定它是否适合您的用例.我看到的解决方案是这样的:

It is possible to write scripts to Google Spreadsheets, but I'm not sure it's going to suit your use case well. The solution I see would be like this:

• 用户单击所需的单元格，将其选中.
• 然后，他单击自定义菜单并在其中选择一个条目，例如显示链接
• 将显示一个带有链接的弹出窗口(不是单元格之外，而是居中显示在屏幕上).

您认为这样还好吗?代码如下所示(打开菜单工具>脚本编辑器)

Do you think this is fine? The code would look like this (open the menu Tools > Script Editor)

function onOpen() {
  SpreadsheetApp.getActive().
    addMenu("Test", [{name: 'Show Links', functionName:'showLinks'}]);
}

function showLinks() {
  var values = SpreadsheetApp.getActiveRange().getValue().split(';');

  var app = UiApp.createApplication().setTitle('Links'); 
  var grid = app.createGrid(values.length, 2);

  for( var i = 0; i < values.length; ++i ) {
    var url = findLink(values[i]);
    grid.setWidget(
      i, 0, app.createLabel(values[i])).setWidget(
      i, 1, url ? app.createAnchor(url, url) : app.createLabel('Not Found'));
  }

  app.add(grid);
  SpreadsheetApp.getActive().show(app);
}

var mapName2Url = null;
function findLink(name) {
  if( mapName2Url == null ) { //lazy load
    mapName2Url = {};
    var data = SpreadsheetApp.getActive().getSheetByName('People').getDataRange().getValues();
    for( var i = 1; i < data.length; ++i ) //skipping the header
      mapName2Url[data[i][0]] = data[i][1];
  }
  return mapName2Url[name];
}

将其粘贴到脚本编辑器后，运行两次onOpen函数以对其进行授权并为您创建菜单.下次打开电子表格时，应自动创建菜单.

After you paste it on the script editor, run the onOpen function twice to authorize it and have the menu created for you. Next time you open the spreadsheet, the menu should be created automatically.

顺便说一句，我没有测试这段代码，所以它可能包含一些愚蠢的错误.

By the way, I have not tested this code, so it might contain dumb mistakes.

这篇关于Google电子表格脚本，可为一个单元格提供多个超链接选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！