天道酬勤,学无止境

A script for google spreadsheet to provide multiple hyperlink choice for one cell

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

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.

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:

  • The user click on the desired cell, selecting it.
  • Then he clicks on a custom menu and picks an entry there, e.g. show links
  • A popup will show up (not besides the cell, but centered on the screen) with the links.

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];
}

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.

受限制的 HTML

  • 允许的HTML标签:<a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id>
  • 自动断行和分段。
  • 网页和电子邮件地址自动转换为链接。

相关推荐
  • 指向特定工作表的超链接(Hyperlink to a specific sheet)
    问题 我想从另一个电子表格中的超链接打开 Google 表格的特定工作表。 我的主电子表格中有不同的链接,每个链接都应该有一个指向同一个从电子表格但指向不同工作表的超链接。 我知道超链接功能,但它没有转到特定的工作表。 回答1 您可以使用此自定义脚本(工具 > 脚本编辑器)功能并将其与例如自定义绘图连接(插入 > 绘图... > 保存并关闭,然后右键单击新绘图> 分配脚本... > "goToSheet2") function goToSheet2() { goToSheet("Sheet2"); } function goToSheet(sheetName) { var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName); SpreadsheetApp.setActiveSheet(sheet); } 更新: 在最新版本中,您可以选择单元格并添加链接(插入 > 链接)并直接选择指向特定工作表的链接: 回答2 HYPERLINK函数可以链接到同一工作簿中的另一个工作表; 如果您观察电子表格的 URL,在它的末尾有#gid=x ,其中x对于每个工作表都是唯一的。 问题是,它会在另一个选项卡中将工作表作为电子表格的新实例打开,这可能是不可取的。 解决方法是将图像或绘图作为按钮插入
  • 如何预填Google表单复选框?(How to prefill Google form checkboxes?)
    问题 我已经看过一个问题:“是否可以使用Google电子表格中的数据来“预填” Google表单? 并且答案中提供的代码(感谢Mogsdad)非常适用于文本类型的Google表单问题。 我的问题是:是否可以预先填写复选框类型的Google表单问题? 例如,如果我有一个现有的电子表格,其中有一个“名称”条目,而其中一个条目是“ Fred,Barney”,则可以通过编码预先填写一个带有“ Fred”和“ “名称”复选框下的“ Barney”类型Google表单问题? 谢谢,格雷格 回答1 对于大多数其他类型,可以重复每个响应的基本模式。 例如,这适用于多种选择: item = items[i].asMultipleChoiceItem(); var respItem = item.createResponse(resp); 但是,复选框可能很棘手,因为它可能有一个项目,多个项目,甚至还有“其他”响应。 当响应记录到您的电子表格时,它将显示为逗号分隔的字符串; 当在表单提交事件中(例如在触发器函数中)接收到时,我们得到一个数组(...,其中所有响应都在数组的第一项中,以逗号分隔的字符串)。 checkboxItem的createResponse()方法需要一个有效选择的数组...因此,我们可以为它提供一些JavaScript魔术: item = items[i]
  • 使用Apps脚本在实时Google表单中动态编辑多项选择选项(Dynamically edit multiple choice options in live Google Form using Apps Script)
    问题 我是洛杉矶的一名高中老师,试图使用Apps Script创建课程注册系统。 我需要用于此注册的Google表单才能: 问题1)根据学生当前的回答选择,在新页面上的后续多项选择题中更新可用的选择。 问题2)当多选选项达到其“上限”时,从表单中消除选择。 问题1示例)一个学生在工作坊1中注册“打结”,并被带到新的页面。 脚本根据学生的首选来编辑该新页面上的可用选项,并从该新页面上的可能选项列表中删除“打结”,因此“礼节”是他们唯一的选择。 问题2的示例)学生可以注册“打结”或“礼节”,这两个答案最初都可以在Google表格中找到。 30名学生参加了调查,所有30名学生都注册了“绑扎”工作坊。 Apps脚本引用了响应电子表格,意识到“联系”工作坊已满,然后将其从Google表单的可能选择列表中删除。 学生31去注册,他们唯一的选择是“礼节”。 如果已经提出并回答了我的问题(相信我,我确实进行了搜索!),我将感谢您进行重定向。 回答1 我相信我们可以轻松实现您的第二个目标,并根据当前的响应状态修改表格。 方法是 创建表单并将其与响应电子表格相关联在该响应电子表格中,创建一个具有功能的脚本(例如,updateForm) 将该函数与onFormSubmit事件绑定,请参阅使用特定于容器的可安装触发器。 在updateForm函数中分析响应,并使用Form Service修改您的表单 例如
  • 我可以在 Google Apps 电子表格的消息框中添加超链接吗 [重复](Can I add a hyperlink inside a message box of a Google Apps spreadsheet [duplicate])
    问题 这个问题在这里已经有了答案: 用于打开 URL 的 Google Apps 脚本(5 个回答) 9 个月前关闭。 有没有办法在 Google Apps 电子表格的消息框中添加超链接? 我有这个显示 msgbox 的代码。 // The code below will display a message box Browser.msgBox("Go to this site for help"); } 有没有办法在该消息框中插入超链接? 就像是: // The code below will display a message box Browser.msgBox("Go to this site for help" & <a href="www.google.com">Help</a>); } 回答1 这是显示指向 url 的链接的弹出窗口示例 function showurl() { var app = UiApp.createApplication().setHeight('60').setWidth('150'); app.setTitle("Anchor in a popup ;-)"); var panel = app.createPopupPanel() var link = app.createAnchor('This is your link', 'https
  • 表单上的应用程序脚本如何将额外的数据存储到工作表中?(How can an apps-script on a Form store extra data into the Sheet?)
    问题 问:附加到窗体的AppsScript如何将额外的数据存储到工作表中? 情况:我们有一个(很长的)Google表单,可以将许多数据存储到Google表格中。 通常,条目需要进行编辑,与使用原始表单进行编辑相比,直接尝试在工作表中进行编辑要容易得多。 (其中一些项目是文本,长几个段落。)我想在电子表格中存储另一条数据,尤其是URL,编辑者可以使用该URL来编辑表单中的行条目。 我已经可以获取所有表单数据,并且可以使用formResponse.getEditResponseUrl()获得正确的URL。 我可以通过电子邮件将所有内容发送给用户,通常是收集所有表单条目的编辑。 (由于在StackOverflow中提供了许多有用的答案,这使我走了这一步!)但是编辑器必须手动将URL复制并粘贴到电子表格正确行的另一列中。 我在Sheet类中看到一个接口,可以在电子表格中添加一列,但是我看不到如何为表单刚刚存储的特定行填充该额外的列。 我们已手动添加了该列,并验证了通过表格进行编辑时Google不会覆盖该列。 如何将一小部分数据存储到工作表中? 我想念什么? 任何帮助将不胜感激。 谢谢。 [补充说明2015-02-06] 我们有一个很长的表格,有人可以提交,其他人可以编辑。 编辑是使用表单完成的,而不是直接在电子表格中进行的,因此我们需要允许编辑者重新编辑响应的URL。
  • Google Apps脚本中用于表单的动态选择选项(Dynamic selection options in Google Apps Script for forms)
    问题 我有这个表格: 如果您看到列表项-正在从Google表格填充学生姓名和年级。 我现在要做的是在选择列表项成绩之后,仅在“选择学生”选择中显示属于该特定成绩的那些学生... 因为form仅具有两个触发器,所以如何完成:onOpen和onEdit? 回答1 据我所知,这种类型的客户端事件在Google表单中不存在。 Google Apps脚本的脚本编制机制是专为创建表单和其他文档而设计的,但是对于客户端功能而言却作用不大。 我建议您将表单分为多个页面,并使用“基于答案转到页面”功能来实现所需的功能。 使用form.addPageBreakItem()创建页面,并使用item.createChoice(value, PageBreakItem)导航到正确的页面。 参考: Google Apps脚本表单服务 addPageBreakItem createChoice 回答2 编辑:由于后续评论中的其他信息,该问题已得到澄清,并归结为: “当用户在Google表单中进行选择时,是否可以在该表单上的其他问题中自定义选项?” 答:不能。请参阅使用Apps Script在实时Google表单中动态编辑多项选择选项。 原始答案-这说明了在编辑选项的来源(电子表格)时如何更改表单中显示的选项。 如果您的数据在Google Spreadhsheet中
  • 如何使用其中的 HYPERLINK 将 url 文本移出字段(How to get url text out of field with HYPERLINK in it)
    问题 我有一列带有超链接公式的列,例如: =HYPERLINK("http://example.com", "Link") 我想获得额外的列,其中只有第一列中的 url(作为文本)将出现,即在此示例中: http://example.com 是否有允许从 HYPERLINK 提取 url 的功能? 我也在考虑从第一列中获取公式文本并在最后一列中使用 SPLIT/SUBSTITUTE 进行剪切,但我不确定是否可以将一个字段代码放入另一个字段。 回答1 试试这个公式 A2=index(SPLIT(SUBSTITUTE(FORMULATEXT(A1),"=HYPERLINK(""",""),""","""),1,1) 例子 A1=HYPERLINK("http://example.com", "Link") 结果是 A2=http://example.com 回答2 您可以通过使用 Apps 脚本创建自定义函数来完成此操作。 试试这个: 打开您的 Google 表格。 在菜单栏中,打开工具 > 脚本编辑器... 在Code.gs 中,粘贴以下内容并保存: function EXTRACT_URL(input) { var range = SpreadsheetApp.getActiveSheet().getRange(input); var re = /^.+?\(\"(.+?)\"
  • 使用Google Apps脚本插入方括号和引号(Insertion of brackets and quotation marks using Google Apps Script)
    问题 我最近开始为Google Spreadsheets编写一些脚本。 我没有使用Javascript的经验,但是我有一个关于(我想是)基本问题的问题。 我希望我的脚本将下面显示的数据插入工作表的单元格中。 我应该如何编码才能使其正常工作? komorkaLinku.setValue("=HYPERLINK("http://www.some.link/some/data"+variable+"something","something")"); 我尝试了几种方法,但没有一种起作用。 回答1 您正试图在引号内包含引号。 有两种方法可以做到这一点。 在双引号内使用单引号,反之亦然。 komorkaLinku.setValue('=HYPERLINK("http://www.some.link/some/data'+variable+'"something","something")'); 使用转义的单引号。 komorkaLinku.setValue('=HYPERLINK(\'http://www.some.link/some/data'+variable+'\'something\',\'something\')'); 正如@ScampMichael所评论的那样,在这种情况下使用setFormula()是一个更好的选择。 您仍然需要正确处理嵌入式引号。
  • 获取文档中的所有链接(Get All Links in a Document)
    问题 给定Google文档/驱动器中的“常规文档”(例如,段落,列表,表格),其中包含分散在整个内容中的外部链接,您如何使用Google Apps Script编译存在的链接列表? 具体来说,我想通过在每个URL中搜索oldText并将其替换为每个URL中的newText ,而不是文本来更新文档中所有断开的链接。 我认为我不需要开发文档的替换文本部分,我需要扫描文档的每个元素吗? 我可以只使用editAsText并使用html正则表达式吗? 例子将不胜感激。 回答1 这只是最痛苦的事情! 代码是要点的一部分。 是的,我不会拼写。 getAllLinks 这是一个实用程序功能,可在文档中扫描所有LinkUrl,并将它们以数组形式返回。 /** * Get an array of all LinkUrls in the document. The function is * recursive, and if no element is provided, it will default to * the active document's Body element. * * @param {Element} element The document element to operate on. * . * @returns {Array} Array of objects, vis
  • Can I add a hyperlink inside a message box of a Google Apps spreadsheet [duplicate]
    This question already has answers here: Google Apps Script to open a URL (5 answers) Closed 9 months ago. Is there a way to add a hyperlink inside a message box of a Google Apps spreadsheet? I have this code that displays a msgbox. // The code below will display a message box Browser.msgBox("Go to this site for help"); } Is there a way to insert a hyperlink in that message box as well? Something like: // The code below will display a message box Browser.msgBox("Go to this site for help" & <a href="www.google.com">Help</a>); }
  • 从 Google 电子表格填充 Google 表单回复(Populate a Google Form responses from a Google Spreadsheet)
    问题 我正在尝试(但目前没有成功)使用我从本网站获取的项目以及 Mogsdad 在这里提供的这个非常有用的答案,从 Google 电子表格填充 Google 表单。 理想情况下,我正在寻找: Logger.log (URL) 记录存储在电子表格中的所有数据的 URL,是否可以只记录最后一个条目并使用它来生成 URL? 一旦填充了数据,预填充的 URL 是否可以自动提交? 我在这里找到了这篇有用的文章,它表明可以做到这一点? 存储在 Google 电子表格中的数据是从另一个 Google 表单捕获的数据。 这是因为需要使用 Excel(缺乏 Internet 连接)和连接公式将所有单元格与数据合并为一个。 然后将其提交到另一个 Google 表单上,该表单具有此脚本,可按列拆分数据以准备回答问题。 这会影响提交时自动提交所需的触发器吗? 我可以补充一点,我对此的理解相当有限,所以如果这看起来很容易做到,请放轻松! 回答1 是否可以只记录最后一个条目并使用它来生成 URL? 我不确定我是否在这里关注您 - 在另一个答案中, Logger.log()语句只是为了证明如果您想分发它,您可以生成正确的 URL。 您只需使用变量url的内容,而不是记录它。 但是让我们继续前进,因为我认为这有点偏离您的道路。 一旦填充了数据,预填充的 URL 是否可以自动提交? 有一个更好的起点。
  • 使用Google Apps脚本将所有工作表转换为PDF(Convert all sheets to PDF with Google Apps Script)
    问题 我正在尝试将具有多个工作表的Google电子表格转换为PDF文件。 下面的脚本可以工作,但是只能使用电子表格的最后一页创建PDF。 function savePDFs() { SpreadsheetApp.flush(); var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheets = ss.getSheets(); var url = ss.getUrl(); //remove the trailing 'edit' from the url url = url.replace(/edit$/,''); //additional parameters for exporting the sheet as a pdf var url_ext = 'export?exportFormat=pdf&format=pdf' + //export as pdf //below parameters are optional... '&size=letter' + //paper size '&portrait=false' + //orientation, false for landscape '&fitw=true' + //fit to width, false for actual size '&sheetnames
  • Text to Rows VBA Excel
    I have a spreadsheet with about a good 4000 rows of data, one of the columns of data has unique order numbers that I want separated using "/" as my delimiter. So essentially I want: Name Order# Date Jane 123/001/111 08/15/2013 Gary 333/121 09/01/2013 Jack 222 09/02/2013 To look like this: Name Order# Date Jane 123 08/15/2013 Jane 001 08/15/2013 Jane 111 08/15/2013 Gary 333 09/01/2013 Gary 121 09/01/2013 Jack 222 09/02/2013 I am fairly new to VBA, so I decided to try to Google for a solution where I came upon this nice bit of code. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range
  • Excel超链接大量更新(Excel Hyperlink mass update)
    问题 我有一个包含数千行的电子表格。 每行包含一个带有路径的超链接。 该路径无效,但是可以通过将其第一部分替换为正确的值来轻松修复。 Example: current hyperlink: F:\Help\index.html Needed: P:\SystemHelp\index.html 问题在于标准的“查找/替换”无法“看到”超链接的内容。 是编写宏的唯一方法还是还有另一种方法? 回答1 除了宏之外,我不知道其他方法。 但是看起来有人已经写了一个来做。 Public Sub ReplaceHyperlinkURL(FindString As String, ReplaceString As String) Dim LinkURL As String Dim PreStr As String Dim PostStr As String Dim NewURL As String Dim FindPos As Integer Dim ReplaceLen As Integer Dim URLLen As Integer Dim MyDoc As Worksheet Dim MyCell As Range On Error GoTo ErrHandler Set MyDoc = ActiveSheet For Each MyCell In MyDoc.UsedRange If
  • 使用pdf选项,使用google脚本将新版本的Google电子表格导出(或打印)为pdf文件(Export (or print) with a google script new version of google spreadsheets to pdf file, using pdf options)
    问题 我正在尝试制作一个Google脚本,以使用页面参数(人像/风景,...)将新版本的Google电子表格(或工作表)导出(或打印)为pdf。 我对此进行了研究,并在这里找到了可能的解决方案。 有几种类似的解决方案,但仅适用于旧版本的Google电子表格。 请考虑以下代码: function exportAsPDF() { //This code runs from a NEW version of spreadsheet var oauthConfig = UrlFetchApp.addOAuthService("google"); oauthConfig.setAccessTokenUrl("https://www.google.com/accounts/OAuthGetAccessToken"); oauthConfig.setRequestTokenUrl("https://www.google.com/accounts/OAuthGetRequestToken?scope=https://spreadsheets.google.com/feeds/"); oauthConfig.setAuthorizationUrl("https://www.google.com/accounts/OAuthAuthorizeToken"); oauthConfig
  • Dynamically edit multiple choice options in live Google Form using Apps Script
    I'm a high school teacher in L.A. trying to create a course registration system using Apps Script. I need the Google Form I'm using for this registration to: Question 1) Update the choices available in subsequent multiple choice questions on new pages based on a student's current response choices. Question 2) Eliminate choices from the form when a multiple choice option has reached it's "cap". Question 1 Example) A student registers for “tie-tying” in workshop 1, and gets taken to a new page. The Script edits the available choices on that new page based on the student’s first choice, and
  • 如何仅使用Javascript访问Google表格电子表格?(How can I access Google Sheet spreadsheets only with Javascript?)
    问题 我只想使用JavaScript来访问Google Spreadsheets(不能使用.NET,C#,Java等) 我来到这里时,很震惊地得知,没有用于JavaScript的API可以访问Google表格。 请告诉我如何使用JavaScript或其任何框架(如jQuery)访问(创建/编辑/删除)Google表格。 回答1 我创建了一个简单的JavaScript库,该库通过JSON API检索Google电子表格数据(如果已发布): https://github.com/mikeymckay/google-spreadsheet-javascript 您可以在这里看到它的运行情况: http://mikeymckay.github.com/google-spreadsheet-javascript/sample.html 回答2 2018年1月更新:去年我回答这个问题时,我没有提到使用JavaScript访问Google API的第三种方法,这是使用客户端库通过Node.js应用程序进行的,因此在下面添加了它。 是2017年3月,这里的大多数答案都已过时-现已接受的答案是指使用旧API版本的库。 最新的答案:您只能使用JavaScript访问大多数Google API。 Google提供了3种方法来做到这一点: 正如Dan Dascalescu的回答中所提到的
  • 是否可以通过Google Docs应用脚本打开新文档?(Is there a way to open a new document from a Google Docs app script?)
    问题 我在Google文档中有一个电子表格,其中有一家小型企业的每日库存。 我编写了一个脚本,该脚本创建脚本的副本,然后将期末库存复制到期初库存,然后将各个字段清零(收到的现金等)。 问题在于,似乎没有办法更改用户当前正在查看的文档,因此他们必须运行脚本,然后返回到文件夹并打开新的day文件并进行编辑。 该代码看起来像.. /** * Setup the spreadsheet for a new day */ function newDay(date) { var ss = SpreadsheetApp.getActiveSpreadsheet(); /* Generate localtime version * Do this instead? * newSs.setSpreadsheetTimeZone('Australia/Adelaide'); */ var calTimeZone = 'Australia/Adelaide'; var timeStamp = Utilities.formatDate(date, calTimeZone, 'yyyy/M/d'); var newName = Utilities.formatDate(date, calTimeZone, 'yyyyMMdd'); /* Save new version and set ss to that
  • 如何从Google Apps脚本中的数据单元中检索超链接?(How can I retrieve the hyperlink from a data cell in google apps script?)
    问题 我正在使用SpreadsheetApp.getActiveRange()。getValues(); 获取单元格区域的值,但它不返回与该单元格关联的超链接,仅返回文本。 有什么方法可以获取超链接,还是必须在其中添加带有URL的另一个字段? 谢谢。 更新:这就是我最终使用它来构建链接列表的方式。 <ul> <? var range = SpreadsheetApp.getActiveRange(); var data = range.getValues(); var links = range.getFormulas(); for(var i=1; i < data.length; i++){ if(data[i][0] !== ''){ ?> <li><a href="<?= links[i][0].split("\"")[1]; ?>"><?= data[i][0]; ?></a></li> <? } } ?> </ul> 回答1 A hyperlink associated with a cell text is manifested by a formula. Google Spreadsheets does automagically converts any URL into a clickable hyperlink but in those cases
  • How to insert hyperlink to a cell in Google sheet using formula?
    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