Extract HyperLink to URL and Text from a string of text and hyperlink

Question

I tried to search but couldn't find someone in the exact scenario as me. Most only does with a cell containing hyperlink only.

I'm trying to edit articles with lots of hyperlink and strings of text from Google Doc. I figured out it wouldn't be possible to extract the Hyperlink and Text only in Google Doc, so I would paste it in Google Sheets.

How do I get the URL and Anchor Text from a string of text + hyperlink ?

https://docs.google.com/spreadsheets/d/1vuDGZ1l2rwqvpI6_fKWlUo4G9-hbAS6jI8XwhJSd8Ck/edit#gid=0

Sheet1!A1:B10 is protected just in case somebody messed it up.

Answer 1

Use the function below. To run it, you must add the Google Sheets API service.

function fillHyperlinks() {
  const ranges = "Sheet1!A6" // must be a single cell

  const ss = SpreadsheetApp.getActive() 
  const res = Sheets.Spreadsheets.get(ss.getId(), {ranges, fields: "sheets/data/rowData/values"});
  const rowData = res["sheets"][0]["data"][0]["rowData"]
  
  const result = []

  if(rowData.length>1 || rowData[0]["values"].length>1){
      throw new Error("the range must be a single cell")
  }
  
  const value= rowData[0]["values"][0]
  const runs = []
  const text = value["formattedValue"]
  const textFormatRuns = value["textFormatRuns"]
  let rows
  if(textFormatRuns){
    for(let r of textFormatRuns){
      const index = r.startIndex?r.startIndex:0
      if(runs.length){
        // if not the first item, save the end position of the previous run element
        runs[runs.length-1].end = index
      }
      runs.push({
        index,
        link:r.format.link?r.format.link.uri:undefined,
        end: text.length  // will be overwritten if not the last item
      })
    }
    const links = runs.filter(run=>run.link).map(run=>({link:run.link, text:text.slice(run.index, run.end)}))
    rows = links.map(link=>[link.text, link.link])
    //rows = links.map(link=>[[link.text], [link.link]])
  } else{
    rows =  ["no links found"]
  }

  // set the destinination
  ss.getSheetByName("Sheet1").getRange(8,1,rows.length, rows[0].length).setValues(rows)
}