Coda your video hyperlinks
Create linked references in your table
In my previous blog on YouTube I showed a screenshot with hyperlinked timestamps. I could produce that image under two conditions:
- a small text
- working code
When I applied the same methodology to a larger text, my formulas got stopped by Coda.
I had to work out a different solution. The first part I presented in the previous blog. It is about turning a large text into smaller parts we distribute over various rows. Once we have the text available in smaller parts, we can start replacing the time slots with hyperlinked time slots.
In this blog I explain how to turn the time slots into hyperlinks. This blog is not about injecting these hyperlinks into the text. First things first.
Getting the seconds right
we want to be able to get the values I presented below in the helper column.
We need to make a few steps to get there and to keep the exercise motivational I’ll share the code snippet in two parts. We start with getting the seconds per time stamp.
`thisRow.transcript.RegexExtract("(?:\d+:)?\d+:\d+","gi").ForEach(CurrentValue.WithName(stamps,
If(
stamps.Split(':').Count() = 2,
stamps.Split(":").Nth(1) * 60 + stamps.Split(":").Nth(2).Join(""),
stamps.Split(":").Nth(1) * 3600 + stamps.Split(":").Nth(2) * 60 + stamps.Split(':').Nth(3)
)
)
)
This is not so trivial as it seems because time stamps are not uniform. As mentioned before, first I did not see that we had two types of time stamps, the HH:MM:SS and the MM:SS. This is not only relevant for the regex, but also for the calculation.
First we check split on “ : “ and we count the strings, we have at least two. When we have a format like 5:49 we have 5,49, thus 5 minutes and 49 seconds. That is what you see in line two. We multiply the first string by 60 seconds and we add the seconds of the second and last string. The other option deals with hours and has 3 strings. The first one is the hour and thus we multiply by 3600, the second string show the minutes which explains the 60 and the last are the seconds as always.
Together they give us the time stamp in seconds and we need only seconds because the video URL requires seconds as unit.
Building the video link
Our next step is to glue the seconds to the video URL. Each time stamped YouTube video URL follows the same pattern.
.ForEach(
If(CurrentValue = 0,
thisRow.videos.videoURL.Hyperlink('0'),
Format("{1}{2}",
thisRow.videos.sourceURL,
CurrentValue
).Hyperlink(stamps))
)
)
)The tricky thing I noticed, happens when the time stamp value is zero and thus we have two variations. The zero value and all the rest.
I like using Format() to combine variables and I use Concatenate() to store text only as you see below. The source URL is based on the video URL and the bit extra “&t=”. A funny detail, in the begin days of YouTube they used the shorter URL youtu.be and BE is the domain of the country I live in.
Via the function Hyperlink() we give a name to the linked value, in our example we use the time stamp. The word stamps is a named function I created in the first part as you see below.
The outcome of this exercise is we use the stamps in the hyperlink resulting in hyperlinked stamps.
This is step 2 of 3 to inject hyperlinked time stamps into the transcript. Based on this blog and the previous blog, you are able to translate raw text into good to read text and second you can link the time stamps to a link.
This exercise is maybe not for beginners, but no matter your skill level, it is a practical application. To promote Coda as a service, we need more practical applications and examples. This is my contribution to that idea.
My name is Christiaan and blog about Coda. Since the summer of 2023 often (but not only) about how to Coda with AI to support organisations dealing with texts and templates. The latest major Coda AI update was on Dec 7, 2023. With the announcement of Snowflake as partner on April 10, I expect to see a new AI logic put in place before the summer holidays.
Why I focus on Coda AI you can read here: ⤵️
I hope you enjoyed this article. If you have questions feel free to reach out. Though this article is for free, my work (including advice) won’t be, but there is always room for a chat to see what can be done. You find my (for free) contributions to the Coda Community and on Twitter.
Coda comes with a set of building blocks ー like pages for infinite depth, tables that talk to each other, and buttons that take action inside or outside your doc ーso anyone can make a doc as powerful as an app (source).
Not to forget: the Coda Community provides great insights for free once you add a sample doc.
