# Everything a Google Apps Script Developer wanted to know about reading hyperlinks in Google Sheets ... but was afraid to ask

I’ll start off by saying this is a very geeky post and if you’ve landed here because you are struggling with extracting hyperlinks in Google Sheets then you might prefer to jump straight to [Alexander Ivanov’s Extract URL’s Add-on](https://extract-urls.contributor.pw/) or alternatively the [Info Inspired post Extract URLs in Google Sheets Without Script](https://infoinspired.com/google-docs/spreadsheet/extract-urls-in-google-sheets-without-script/).

**Update:** Google are rolling out multiple hyperlinks in Google Sheet cells. Kanshi TANAIKE has provided new notes on [Updated Specification of Google Spreadsheet: Multiple Hyperlinks to a Cell](https://gist.github.com/tanaikech/d39b4b5ccc5a1d50f5b8b75febd807a6)

**The problem: hyperlinks in Google Sheets can be in three different formats** so if you have a script or add-on that relies on handling links in cells it can cause some headaches. Let first look at how cells can hyperlink:

- plain link - text values that Google Sheets detects as links
- [`HYPERLINK`](https://support.google.com/docs/answer/3093313?hl=en-GB) function/formula - hyperlink inside a cell with the option of specifying you own link label
- copy/paste/hidden links - there are scenarios such as copy/pasting links from a website to Google Sheets will link the cell without using HYPERLINK or a plain link

To see these link types in action you can [view them in this Google Sheet](https://docs.google.com/spreadsheets/d/1UAAnqHrIL9fvVSq626NcoBcPwJ5U-jrnmoqeV6pFBD0/edit?usp=sharing) and illustrated below:

![Examples of hyperlinks in Google Sheets](https://hawksey.info/wp-content/uploads/2020/04/hyperlink.gif)When we look at the `.getValues()` and `.getFormulas()` responses for this data we get the following:

## `.getValues()` response

| Plain link | https://developers.google.com/apps-script/ |
|---|---|
| HYPERLINK function/formula | This is a HYPERLINK |
| Copy/paste/hidden links | HOME |

## `.getFormulas()` response

|  |  |
|---|---|
|  | "=HYPERLINK("https://developers.google.com/apps-script/", "This is a HYPERLINK")" |
|  |  |

## Solution - Advanced Sheets Service

Rather than having the headache of combining `.getValues()` and `.getFormulas()` responses, which would also miss the copy/paste/hidden links, there is a better way. Using the Advanced Sheets Service:

> The Advanced Sheets service lets you access the [Sheets API](https://developers.google.com/sheets/api) using Apps Script. Much like Apps Script's [built-in Sheets service](https://developers.google.com/apps-script/reference/spreadsheet), this API allows scripts to read, edit, format and present data in Google Sheets. In most cases, the built-in service is easier to use, but this advanced service provides a few extra features.
> 
> [Advanced Sheets Service Documentation](https://developers.google.com/apps-script/advanced/sheets)

To use the Advanced Sheets Service you need to enable before use. The good news is you don’t have to go through the entire process of enabling the Sheets API in a Cloud Platform (GCP) project as using the built-in service gives you access tokens, you will however **still need to [enable the advanced service](https://developers.google.com/apps-script/guides/services/advanced#enabling_advanced_services) in your script project**.

To get the data back from the Sheets API you need to call:

`Sheets.Spreadsheets.get(String spreadsheetId, Object optionalArgs))`

| Parameters |
|---|
| `spreadsheetId` | `string` - The spreadsheet to request. |

| Optional Arguments |
|---|
| `ranges` | `string` - The ranges to retrieve from the spreadsheet. |
| `includeGridData` | `boolean` - True if grid data should be returned. This parameter is ignored if a field mask was set in the request |

To access the cell hyperlink data includeGridData needs to be set as true. The [CellData response](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/cells#celldata) contains a lot of data but buried down the document tree is the read only hyperlink field:

[![Example JSON response](https://hawksey.info/wp-content/uploads/2020/04/null-2.png)](https://jsoneditoronline.org/#left=cloud.b183bea5db28408dbd66feba8f0cefbb)Example JSON response - [click to explore](https://jsoneditoronline.org/#left=cloud.b183bea5db28408dbd66feba8f0cefbb)To make things a little easier here is a helper function that returns a 2D array of cell hyperlinks:

```

/**
 * Get hyperlinks from cells.
 * @param {string} spreadsheetId - The spreadsheet ID to request
 * @param {string} ranges - The ranges to retrieve from the spreadsheet
 * @return {Object[][]} 2D array of cell hyperlinks
 */
const getHyperlinks = (spreadsheetId, ranges) => {
  const response = Sheets.Spreadsheets.get(spreadsheetId, {includeGridData:true, ranges: ranges});
  const rowData = response.sheets[0].data[0].rowData;
  return rowData.map(row => row.values.map(cell => cell.hyperlink || ''));
}

```

The response from the test data looks like this:

|  | https://developers.google.com/apps-script/ |
|---|---|
|  | https://developers.google.com/apps-script/ |
|  | https://developers.google.com/apps-script/ |

### Caveat - Advanced Sheets Service scopes

So you can now reliably access hyperlinks in Google Sheets cells. Something to note is that while the built-in Sheets service can have it’s scope restricted to .currentonly removing the need to get your script project verified with the Advanced Sheets Service needs broader scopes as [detailed in the documentation](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/get#authorization-scopes). Developing and deploying within your G Suite domain shouldn’t be an issue but for more general use you’ll need to [get your project verified or instruct users about the not verified warning](https://developers.google.com/apps-script/guides/client-verification).

So is this everything you need to know about interacting with hyperlinks in Google Sheets or have I missed something?