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

Question

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. ```

">

```

Accepted Answer

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);
}
```

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

Solution

Problem