如何从Google应用程序脚本中的数据单元中检索超链接? [英] How can I retrieve the hyperlink from a data cell in google apps script?
问题描述
我正在使用SpreadsheetApp.getActiveRange()。getValues();获取单元格范围的值,但不返回与该单元格关联的超链接,而仅返回文本。有什么方法可以获得超链接,或者我必须添加其他字段的url吗?
谢谢。
UPDATE:这是我最终用它来建立链接列表的结果。
< ul>
<?
var range = SpreadsheetApp.getActiveRange();
var data = range.getValues();
var links = range.getFormulas();
$ b $ for(var i = 1; i< data.length; i ++){
if(data [i] [0]!==''){
? >
< li>< a href =<?= links [i] [0] .split(\)[1]; < / li>><?= data [i] [0];?>< / a>< / li>
<?
}
}
$>
< / ul>
与单元格文本关联的超链接通过公式表现出来,Google Spreadsheets可以自动将任何URL转换为可点击的超链接,但在这些情况下,公式不会被使用,文本和URL也是相同的。 b
$ b
下面是一个非常简单的解决方案(没有错误检查),关于如何通过脚本来获取它,我怀疑你是从Excel或其他东西导入它的,那就是你不容易看到HYPERLINK公式。
如果你有一个看起来像这样的单元格 -
然后这个脚本会让你阅读与之关联的URL -
function getURL(){
var range = SpreadsheetApp.getActiveSheet() .getActiveCell();
// logs - Google
Logger.log(range.getValue());
// logs - = HYPERLINK(http://www.google.com,Google)
Logger.log(range.getFormulaR1C1());
//简单的正则表达式来获得第一个带引号的字符串
var url = /\"(.*?)\"/.exec(range.getFormulaR1C1())[1];
//日志 - http://www.google.com
Logger.log(url);
}
I am using SpreadsheetApp.getActiveRange().getValues(); to get the value of a range of cells, but it doesn't return the hyperlink associated with that cell, only the text. Is there any way to get the hyperlink or do I have to add another field with the url in it?
Thanks.
UPDATE: This is how I ended up using it to build a link list.
<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>
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 formulas are not used and the text and the URL are the same.
Below is a very simple solution (no error checking) as to how you can get this through scripts. I suspect you imported this from Excel or something else and that's you don't readily see the HYPERLINK formula.
If you have a cell that looks like this -
Then this script will let you read the URL associated with it -
function getURL() {
var range = SpreadsheetApp.getActiveSheet().getActiveCell();
//logs - Google
Logger.log(range.getValue());
//logs - =HYPERLINK("http://www.google.com", "Google")
Logger.log(range.getFormulaR1C1());
//simple regex to get first quoted string
var url = /"(.*?)"/.exec(range.getFormulaR1C1())[1];
//logs - http://www.google.com
Logger.log(url);
}
这篇关于如何从Google应用程序脚本中的数据单元中检索超链接?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!