How do you extract urls from a column of cells containing linked text with numeric values in Google Sheets?

Question

Edit/Update: This question is helpful for the specific use case where you are trying to extract urls from cells containing numeric values with linked URLs.

I have a spreadsheet with a column of cells copied from a website containing text with linked urls (i.e. the urls are not stored within a hyperlink formula). Here is a sample spreadsheet. I would like to extract the urls from the linked text (column A) into a new column in the spreadsheet.

Last year (2020) I found the custom function javascript code shared in the comments of this other StackOverflow question which worked well. However when I tried this with a new spreadsheet yesterday, this code was no longer working -- instead of returning the url, it returns nothing. There is no error message, it just returns an empty value.

Other things I've tried: I was able to successfully retrieve the url in some cases by using this other javascript function:

function GETLINK(input) {
  return SpreadsheetApp.getActiveSheet().getRange(input).getRichTextValue().getLinkUrl();
}

But it only worked correctly on cells where the text was not formatted as a number AND it requires entering the input as either a firm cell reference by putting it in quotes i.e. =getlink("A2") or by spelling out the address, i.e. =getlink(ADDRESS(ROW(A2), COLUMN(A2)))

I would like to figure out what has broken with the original code, which does not require these additional workarounds.

Answer 1

Explanation:

From the documentation, getRichTextValue() will return null if the value of the cell is not formatted as text. Most of the codes on the reference question use this method, so it returns error once getLinkUrl() is called. Thus the only workaround for this is to force text formatting by putting single quote before the cell value:

This should also work with linkURL().