URLEXTRACT()

/**
 * Returns a flat array of URLs from hyperlinked text given a range of cells as a string.
 * @param {string} the Range as a string you wish to get the ULS of including sheet name
 * @return A flat array of raw URLs.
 * @customfunction
 */
function URLEXTRACT(range_string) {
  let range = SpreadsheetApp.getActive().getRange(range_string);
  let richTextValues = range.getRichTextValues().flat()
  let results = richTextValues.map(item => {return item.getLinkUrl()}).filter(item => {return item != null})
  return results
}