How to output a hyperlink from Apps Script function into Google Sheet

Question

My first attempt was raw html, but that clearly didn't work.

I found that I'm supposed to use rich text, so I tried:

function youtubeLink(yt_id, start_stamp, end_stamp) {
  const start_secs = toSecs(start_stamp)
  const end_secs = toSecs(end_stamp)
  const href = `https://www.youtube.com/embed/${yt_id}?start=${start_secs}&end=${end_secs}`
  return (
    SpreadsheetApp.newRichTextValue()
    .setText("Youtube Link")
    .setLinkUrl(href)
    .build()
  )
}

I'm calling with:

=youtubeLink(A1,A2,A3)

But that didn't work at all. The field just stayed blank.

I tried with a range, but got a circular reference. It seems like this should be easy. Not sure what I'm missing.

This works, but it is auto-formated and the link text is the same as the link:

function youtubeLink(yt_id, start_stamp, end_stamp) {
  const start_secs = toSecs(start_stamp)
  const end_secs = toSecs(end_stamp)
  return (`https://www.youtube.com/embed/${yt_id}?start=${start_secs}&end=${end_secs}`)
}

Answer 1

Unfortunately, the custom function cannot directly put the RichtextValue and the built-in function to the cell. In this case, that is put as a string value. So, in this case, it is required to use a workaround. In this answer, I would like to propose the following 2 patterns.

Pattern 1:

If you want to use the functions of Spreadsheet, how about the following sample formula?

=HYPERLINK("https://www.youtube.com/embed/"&A1&"?start="&toSecs(B1)&"&end="&toSecs(C1),"Youtube Link")

• In this case, the cells "A1", "B1" and "C1" are yt_id, start_stamp, end_stamp, respectively.
• The function of toSecs is used from Google Apps Script.

Pattern 2:

If you want to use Google Apps Script, how about the following sample script? In this case, this script supposes that the values of yt_id, start_stamp, end_stamp are put in the cells "A1", "B1", and "C1", respectively. Please be careful about this.

function sample() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); // Please set the sheet name.
  const [yt_id, start_stamp, end_stamp] = sheet.getRange("A1:C1").getValues()[0];
  const start_secs = toSecs(start_stamp);
  const end_secs = toSecs(end_stamp);
  const href = `https://www.youtube.com/embed/${yt_id}?start=${start_secs}&end=${end_secs}`
  const richtextValue = SpreadsheetApp.newRichTextValue().setText("Youtube Link").setLinkUrl(href).build();
  sheet.getRange("D1").setRichTextValue(richtextValue);
}

• When this script is run, the values of yt_id, start_stamp, end_stamp are retrieved from the cells "A1", "B1" and "C1", and the text with the hyperlink is put to the cell "D1".

Reference:

• setRichTextValue(value)