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
In your script, how about using getRun
as follows?
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);
}
When this script is run to your provided Spreadsheet, the following result is obtained.