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 or alternatively the Info Inspired post 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
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
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 and illustrated below:

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 using Apps Script. Much like Apps Script’s built-in Sheets service, 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
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 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 contains a lot of data but buried down the document tree is the read only hyperlink field:

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. 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.
So is this everything you need to know about interacting with hyperlinks in Google Sheets or have I missed something?
Andrew Roberts
Truly geektastic!
Andrew Roberts
I briefly saw something about being able to add multiple hyperlinks to a GSheet cell the other day, but I can’t find anything about it now. Do you know anything about that?
Martin Hawksey
Wasn’t aware of that ability, but if not implemented it looks like the feature is on it’s way or was at least briefly possible https://support.google.com/docs/thread/5130196?hl=en&msgid=27352815.
Martin Hawksey
@Andrew – in case you haven’t spotted yet Tanaike has reported on interacting with multiple hyperlinks in Google Sheets cells https://gist.github.com/tanaikech/d39b4b5ccc5a1d50f5b8b75febd807a6