How to extract URL from different types of hyperlinked text?

January 16, 2024

I have collected hyperlinked text data from different souces. I have copied and pasted it to cell B5:B (spreadsheet attached). there is two types of data, one is pasted to B5:B11 and other is pasted into B14:B18. To extract the URL, i have created a script

const extractHyperlinksInSheet = () => {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  const hyperlinks = sheet.getRange("B5:B" + sheet.getLastRow()).getRichTextValues().map(([c]) => [c.getLinkUrl() || null]);
  sheet.getRange(5, 4, hyperlinks.length).setValues(hyperlinks);
}

The above code works for the cell B14:B18. But unfortunately, it is not able to extract hyperlink from the cell B5:B11. So, kindly help me to extracting hyperlinked data types as given in B5:B11.
the data given in B5:B11 is

008GPGNIPPON INDIA MUTUAL FUNDD

008MPDNIPPON INDIA MUTUAL FUNDD

111AGGNIPPON INDIA MUTUAL FUNDD

111AMDNIPPON INDIA MUTUAL FUNDD

111DPDNIPPON INDIA MUTUAL FUNDD

111DPRNIPPON INDIA MUTUAL FUNDD

111GPGNIPPON INDIA MUTUAL FUNDD

Thank You

>Solution :

In your script, how about using getRun as follows?

Modified script:

const extractHyperlinksInSheet = () => {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  const hyperlinks = sheet.getRange("B5:B" + sheet.getLastRow()).getRichTextValues().map(([c]) =>
    [c.getRuns().map(r => [r.getLinkUrl() || null]).filter(String).join(",")]
  );
  sheet.getRange(5, 4, hyperlinks.length).setValues(hyperlinks);
}