Extract Hyperlinks from Text in Google Sheets using Google Apps Script

Welcome!

In this blog we will be looking at extracting HyperLinks from Cell Text in Google Sheets. To do this we are going to be using the RichTextValue class and Google Apps Script.

Recently, I was making a collection of all Medium articles and basically copied the links of the articles and stored them in a Google Sheet. I wanted to extract the links from the cell text and paste them into the adjacent column.

To tackle this issue, we are going to be using the RichTextValue class and Google Apps Script.

So lets get started.

If you want a video version of the blog, you can check out the video tutorial given below.

RichTextValue

The RichTextValue class in Google Apps Script enables advanced text formatting in Google Sheets. It supports various styles, including bold, italic, and coloured text, as well as links. This class helps developers create and manage richly formatted text, enhancing the visual appeal and functionality of spreadsheet content.

To know more RichTextValue check out the link given below.

Class RichTextValue | Apps Script | Google for Developers

Sample Google Sheet

For this blog, I am going to be using a very simple Google Sheet that contains the date of upload, Article name/link and the the URL that needs to be added.

If you want to work with the Google Sheet, you can click here.

Step1: Write the Automation Script

While you are in the Google Sheet, let’s open up the Script Editor to write some Google Apps Script. To open the Script Editor, follow these steps:

1. Click on Extensions and open the Script Editor.

2. This brings up the Script Editor as shown below.

We have reached the script editor lets code.

Now that we have our Google Sheet ready and good to go, lets go ahead and write the code to extract the hyperlinks from the cell text and pasted them to the adjacent column.

function extractAndPasteLinks() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
   var lastRow = sheet.getLastRow();

We are going to start off by creating a new function extractAndPasteLinks(), after which we get the active spreadsheet using the getActiveSpreadSheet() function followed by getting the current Active Sheet.

We then use the getLastRow() function to get the last row that contains data in the Google Sheet. It returns a number, which helps us in iterating over all rows with data.

for (var i = 2; i <= lastRow; i++) {
    var cell = sheet.getRange(i, 2); 
    var richTextValue = cell.getRichTextValue(); 

Now that we have the boundary for the loop that we extracted previously using the getLastRow() function. We now open a loop that starts from the second row of and iterates through each row until the last row with data.

We begin from the second row to avoid the headers in the Google Sheet.

Inside the loop, the we get all the values in Column B using the getRange() function, inside which we have passed the current row i and 2, since the links are stored in Column B.

As discussed the RichTextValue class is used to extract the content and formatting details of a cell. We use the getRichTextValue() function to extract the formatting details and content of that cell.

if (richTextValue) {
      var runs = richTextValue.getRuns(); // Get the runs (segments of text)

We have an if loop that checks if richTextValue is not Null or Undefined, ensuring there is rich text content to process.

Assuming there is rich text content, we use the getRuns() function to retrieve the runs of the text from richTextValue. Runs are segments of text within the cell, each having different formatting or linked URLs.

for (var j = 0; j < runs.length; j++) {
        var url = runs[j].getLinkUrl(); // Get the URL from the text run

We then use a nested loop to iterate over each text run obtained from the richTextValue. For each text run, we extract the URL linked to that particular run of text using the getLinkUrl() function.

if (url) {
          sheet.getRange(i, 3).setValue(url); // Paste the URL in Column C
          break; // Stop after the first URL is found
        }
      }
    }
  }
}

Once we extract the url, we use a conditional check to see if the url is Null or Undefined, meaning a valid URL was found in the current text run.

On checking the URLs validity, we set the value of the cell in Column C of the current row i using the setValue() function.

After pasting the first found URL, we use a Break statement to ensure that only the first URL from each cell is processed and pasted.

Our code is complete and good to go.

Step2: Check the Output

Its time to see if the code is able to extract the links from the cell text and paste them into the corresponding column.

On running the code you should get an output like this in the Execution Log.

Now that we have no error in the execution log, if you switch back to the sheet you can see that the links have been successfully extracted and set into the corresponding column.

Conclusion

In conclusion, by leveraging Google Apps Script, we can efficiently extract URLs from cell text in Google Sheets. This functionality is especially valuable for managing and organising large datasets where links are embedded in cell content. This approach not only saves time but also enhances data accuracy and accessibility.

You can check out the code by clicking on the link given below.

Google-Apps-Script/ExtractURL.js at master · aryanirani123/Google-Apps-Script

Feel free to reach out if you have any issues/feedback at aryanirani123@gmail.com.