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

.getFormulas() response

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))

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:

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?