0

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}`)
}
Rubén
  • 34,714
  • 9
  • 70
  • 166
pixelearth
  • 13,674
  • 10
  • 62
  • 110

1 Answers1

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:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • I will give these a try, but as a software engineer cringe at the idea that the second functions needs to know so much about the environment it's being used it. It should be as dumb as possible for maximum flexibility, and dependencies passed to it. Could this second function work with cell references passed to it? I tried something like this as I mentioned and got a circular reference error. I don't have that code anymore, but I'll give this a try. – pixelearth Apr 19 '22 at 14:34
  • @pixelearth Thank you for replying. About `Could this second function work with cell references passed to it?`, in this case, this script cannot be used as the custom function because of the current specification of Google side. `setRichTextValue` cannot be used in the custom function. I apologize for this specification. For example, how about converting the custom function to the result of the script like [this thread](https://stackoverflow.com/a/51254597/7108653)? If this was not your expected result, I apologize. – Tanaike Apr 19 '22 at 23:42