I am not able to extract URL from these cell values using Google Apps Script

Question

This is the link to the sheet:https://docs.google.com/spreadsheets/d/1uNywYrkfptgy6muJe3ur_5AoB4eIOhhysf9Ljd9x5Ns/edit?gid=0#gid=0 [I have made using a new id so privacy is not an issue here].

Code:

function copyUrlsToColumnD() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var lastRow = sheet.getLastRow(); // Get the last row with data in the sheet
  
  for (var i = 3; i <= lastRow; i++) {
    var cell = sheet.getRange("B" + i);
    var richText = cell.getRichTextValue();
    
    if (richText) {
      var url = richText.getLinkUrl();
      if (url) {
        sheet.getRange("D" + i).setValue(url);
      } else {
        sheet.getRange("D" + i).setValue("No link found");
      }
    } else {
      sheet.getRange("D" + i).setValue("No rich text");
    }
  }
}

Issue: If I single click cell B3 and then click on hyperlink icon in the menu and click apply on hyperlink pop-up and then run script, it gives URL. I have 100s of rows and I can't manually do this. I did in this cell B3 and so it gives output but other rows don't give me URLs.

I have 100s of rows and I can't manually do this.

I have spend several hours with many combinations and going through many questions but it has not worked for me. I don't understand the right syntax. I am a total beginner.

Can someone please give me a custom function to extract URLs from this cell.

I really need help. Pls help.

Answer 1

When I saw your provided Spreadsheet, I noticed that the URL is not set to the whole cell value. By this, richText.getLinkUrl() returns null. I guessed that this might be the reason for your current issue. In this case, how about the following modification?

Modified script:

function copyUrlsToColumnD() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var range = sheet.getRange("B3:B" + sheet.getLastRow());
  var values = range.getRichTextValues().map(([b]) => [[...new Set(b.getRuns().flatMap(r => r.getLinkUrl() || []))].join(",") || ""]);
  range.offset(0, 2, values.length, 1).setValues(values);
}

Testing:

When this script is run to your provided Spreadsheet, the following result is obtained.

Reference:

• getRuns()