How can I retrieve the hyperlink from a data cell in google apps script?
Asked Answered
S

2

10

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>
Stoll answered 12/12, 2012 at 19:18 Comment(0)
B
13

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 -

Cell with hyperlink

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);
}
Barcellona answered 12/12, 2012 at 20:28 Comment(3)
Thanks that led me in the right direction. var links = SpreadsheetApp.getActiveRange().getFormulas(); Very awesome.Stoll
It works but what is the right method of deploy for anyone even anonymous?Claudio
Note that this doesn't work if the HYPERLINK has a dynamic URL.Ragouzis
Q
1

You can use .getRange(...).getRichTextValue().getLinkURL() to get the hyperlink for a cell without parsing any formulas.

Range.getRichTextValue()

RichTextValue.getLinkUrl()

Quern answered 21/6 at 17:22 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.