How to Create a Custom Google Sheets Function to Batch Extract multiple URLs From a Cell Using Apps Script

Google Sheets is a powerful tool for data management, but sometimes its built-in functions aren’t enough to meet your workflow needs. One major limitation is that Google Sheets doesn’t have a native function to extract URLs from cells containing hyperlinks, especially when a single cell has multiple anchor texts with different links. This guide will show you how to create your own custom function using Google Apps Script to solve this problem with complete automation.

The Real-World Problem with Hyperlinks

When working with Google Sheets, you might encounter situations like this:

Cell A1 contains: “Check out Google and YouTube for more information” – where each bolded word is a separate hyperlink leading to different websites. The challenge is how to automatically extract all these URLs into a separate list without manually copy-pasting each link.

Google Sheets has a HYPERLINK() function, but its purpose is to create a hyperlink in a cell, not to extract URLs. This creates a significant gap when you need to process hundreds or thousands of cells containing links.

Why Create Custom Functions

Custom functions in Google Sheets allow you to extend spreadsheet capabilities by writing your own functions using JavaScript through Google Apps Script. When built-in functions like AVERAGE, SUM, or VLOOKUP aren’t sufficient, you can create your own and use them just like native functions.

Benefits of custom functions include:

• Automate complex tasks that standard functions can’t handle
• Save hours of manual work
• Reuse across multiple projects
• Drag formulas down just like regular functions to apply to multiple cells
• Share with colleagues by copying spreadsheets or creating add-ons

Step-by-Step Guide: Creating the GETURLS Function

Step 1: Open Google Apps Script Editor

First, you need to access the Apps Script editor:

1. Open your Google Sheets file
2. In the menu bar, select Extensions > Apps Script
3. A new tab will open with the code editor
4. Delete all default code in the editor

Step 2: Complete GETURLS Function Code

Paste the following code into the Apps Script editor:

/**
 * Extract all URLs from a single cell containing multiple hyperlinks.
 * URLs will be returned separated by line breaks.
 *
 * @param {A1} cell The cell you want to extract URLs from (pass directly, no quotes).
 * @return {string} A string containing all URLs, one per line.
 * @customfunction
 */
function GETURLS(cell) {
  // Get current sheet
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // Get formula of calling cell to parse cell reference
  var formula = SpreadsheetApp.getActiveRange().getFormula();
  var cellRef = formula.match(/GETURLS\(([^)]+)\)/i);
  
  if (!cellRef || !cellRef[1]) {
    return "Error: Cell reference not found.";
  }
  
  // Get range from reference
  var range = sheet.getRange(cellRef[1]);
  var richTextValue = range.getRichTextValue();
  
  if (richTextValue == null) {
    return "";
  }
  
  var runs = richTextValue.getRuns();
  var urls = [];
  
  for (var i = 0; i < runs.length; i++) { var run = runs[i]; var url = run.getLinkUrl(); if (url != null && urls.indexOf(url) === -1) { urls.push(url); } } if (urls.length > 0) {
    return urls.join("\n");
  } else {
    return "";
  }
}

Step 3: Understanding How the Code Works

The code operates based on these principles:

Rich Text Value: Google Sheets stores formatted text (including hyperlinks) as “rich text”. The getRichTextValue() method allows access to all formatting information in a cell.

Text Runs: Each text segment with different formatting in the same cell is called a “run”. The getRuns() method returns an array containing all runs in the cell.

Link URL Extraction: For each run, the getLinkUrl() method returns the URL if that text segment contains a hyperlink, or null if it doesn’t.

Duplicate Removal: The code checks urls.indexOf(url) === -1 to ensure each URL appears only once in the results.

Step 4: Save and Name Your Project

After pasting the code:

1. Click the floppy disk icon (Save project) in the top left corner
2. Name your project, for example: “URL Extractor Functions”
3. Close the Apps Script tab and return to Google Sheets

Step 5: Using Your New Function

Now you can use the GETURLS function like any regular function:

Basic syntax:

=GETURLS(A1)

⚠️ Important Note: Don’t use quotes around the cell reference. Write =GETURLS(A1) not =GETURLS("A1").

Drag formula down: When you drag the formula down to rows below, the cell reference will automatically change:

• Row 2: =GETURLS(A2)
• Row 3: =GETURLS(A3)
• Row 4: =GETURLS(A4)

This is the power of passing parameters as cell references instead of strings.

Real-World Use Cases

Case 1: Managing Link Building Campaigns

If you work in SEO and need to track hundreds of backlinks placed in guest posts, extracting all URLs from content for checking and reporting will save tremendous time.

Case 2: Compiling Reference Sources

When doing research, you often highlight keywords in cells and attach reference links. The GETURLS function helps you quickly create an automatic bibliography from cells containing multiple sources.

Case 3: Checking Affiliate Links

For marketers managing multiple affiliate links in the same spreadsheet, extracting URLs to check tracking parameters or commission rates becomes much simpler.

Optimizing Performance for Custom Functions

When working with thousands of cells, performance becomes a critical issue. Google Sheets makes a separate call to the Apps Script server for each custom function use. If your spreadsheet has hundreds or thousands of function calls, this process can be very slow.

Optimized Version for Ranges

If you need to process a large data range, modify the function to accept a range (2D array) instead of individual cells:

/**
 * Extract all URLs from a range containing multiple cells with hyperlinks.
 *
 * @param {A1:A100} range The cell range you want to extract URLs from.
 * @return {Array} 2D array containing URLs.
 * @customfunction
 */
function GETURLS_RANGE(range) {
  var sheet = SpreadsheetApp.getActiveSheet();
  
  return Array.isArray(range) 
    ? range.map(row => row.map(cell => {
        try {
          var richText = sheet.getRange(cell).getRichTextValue();
          if (!richText) return "";
          
          var runs = richText.getRuns();
          var urls = [];
          
          for (var i = 0; i < runs.length; i++) {
            var url = runs[i].getLinkUrl();
            if (url && urls.indexOf(url) === -1) {
              urls.push(url);
            }
          }
          return urls.join("\n");
        } catch(e) {
          return "";
        }
      }))
    : "";
}

Error Handling and Troubleshooting

“Loading…” Infinite Loop

Problem: If the cell displays “Loading…” indefinitely, it could be due to:

• Function contains non-deterministic built-in functions like NOW() or RAND()
• Code has an infinite loop
• Execution time exceeds 30 seconds

Solution: Review code logic and ensure there are no dependencies on volatile functions.

“You do not have permission to call X service” Error

Problem: Custom functions have limitations on which Apps Script services can be used. Only services that don’t require user authorization are allowed in custom functions.

Solution: If you need other services, create a custom menu instead of a custom function.

“#ERROR! Exceeded maximum execution time”

Problem: Custom functions must return results within 30 seconds. Otherwise, the cell will display this error.

Solution: Optimize code by processing in batches or reducing the number of operations.

Best Practices for Creating Custom Functions

Proper Function Naming

• Function names must differ from built-in functions like SUM(), AVERAGE()
• Must not end with an underscore _ as this denotes private functions
• Uppercase or lowercase both work, but by convention spreadsheet functions are usually written in UPPERCASE

Use JsDoc Comments

Always add JsDoc comments with the @customfunction tag so the function appears in autocomplete suggestions:

/**
 * Brief function description.
 * @param {type} paramName Parameter description.
 * @return Return value description.
 * @customfunction
 */

This helps users easily find and understand how to use your function.

Advanced: Other Useful Custom Functions

Function to Extract Anchor Text

Besides extracting URLs, you can also create a function to get anchor text (display text):

/**
 * Extract all anchor texts from hyperlinks in a cell.
 * @param {A1} cell Cell containing hyperlinks.
 * @return {string} List of anchor texts.
 * @customfunction
 */
function GETANCHORTEXTS(cell) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var formula = SpreadsheetApp.getActiveRange().getFormula();
  var cellRef = formula.match(/GETANCHORTEXTS\(([^)]+)\)/i);
  
  if (!cellRef) return "";
  
  var range = sheet.getRange(cellRef[1]);
  var richText = range.getRichTextValue();
  
  if (!richText) return "";
  
  var runs = richText.getRuns();
  var texts = [];
  
  for (var i = 0; i < runs.length; i++) {
    if (runs[i].getLinkUrl()) {
      texts.push(runs[i].getText());
    }
  }
  
  return texts.join("\n");
}

Function to Validate URLs

Combine with URL Fetch service to check if URLs are working:

/**
 * Check if a URL is valid and accessible.
 * @param {string} url URL to check.
 * @return {string} "OK" or error code.
 * @customfunction
 */
function CHECKURL(url) {
  try {
    var response = UrlFetchApp.fetch(url, {muteHttpExceptions: true});
    return response.getResponseCode() == 200 ? "OK" : "Error " + response.getResponseCode();
  } catch(e) {
    return "Invalid URL";
  }
}

Frequently Asked Questions

Are custom functions paid?

No, Google Apps Script is completely free to use. However, there are limits on execution time and number of triggers.

Can I use custom functions on mobile?

Custom functions will work on the Google Sheets mobile app, but you cannot edit code from mobile. Code must be written and saved from desktop first.

Do custom functions cache results?

Google Sheets has a cache service, but it’s not particularly useful for custom functions. To optimize performance, reduce server calls by processing by range instead of individual cells.

Can I use external APIs?

Yes, you can use URL Fetch service to call external APIs. However, custom functions cannot request user authorization, so you can only call public APIs or APIs where you’ve hardcoded the API key.

Conclusion

Creating custom functions in Google Sheets using Apps Script opens up countless automation possibilities that built-in functions cannot provide. The GETURLS function is a perfect example showing how you can solve real-world problems – extracting multiple URLs from a single cell – in a completely automated and reusable way.

With this knowledge, you can confidently create other custom functions suited to your own workflow, from processing complex text strings, calling APIs, to integrating with external services. Most importantly, understand the guidelines and limitations of custom functions to avoid common errors.

Start experimenting with the code in this article and discover many more interesting applications of Google Apps Script!