Just wanted to share a little tip since Glide doesn’t yet have regular expression matching, and probably a lot of people would like to generate YouTube thumbnails easily for use in Glide.
I needed to extract YouTube IDs from URLs and generate thumbnail images with them. Problem is, users can input all kinds of YouTube URLs, with query params after the ID, etc, so it’s not enough to do a simple replace using Glide templates to get rid of a predictable string like “https://youtu.be/” - if it were a perfect world all URLs would be formatted that way, but in the real world, regular expressions are needed, specifically, REGEXEXTRACT() in Google Sheets. I am not going to go over what regex is and how to use it - there are plenty of resources to Google. But for the uninformed, it is a method of matching patterns given input text, and manipulating that input to either test if it matches a pattern, or even extract pieces of that input, according to a pattern… very powerful.
To use formulae in Google Sheets, it’s common knowledge that you can use the ARRAYFORMULA function to apply the function to an entire column, so that the formula is automatically populated when the data changes, new rows are added, etc. However, I see people doing things like using A2:A as a range, in order to leave out the header row - this will make it so if a user deletes the first item in the sheet, the formula will also be deleted. I also don’t think the right solution is to hide ARRAYFORMULA in an “empty”, hidden row… that’s just asking for problems with sorting and uninformed people editing the sheet, etc. A better strategy is to place a formula like this in the FIRST (i.e. header) row of your new formula column in Google Sheets:
=ARRAYFORMULA(IF(EQ(ROW(E:E),1),"Thumbnail","http://i3.ytimg.com/vi/"®EXEXTRACT(D:D,"(?:youtu\.be\/|watch\?v=)([\w\-]{10,12})")&"/hqdefault.jpg"))
You can see here the formula just uses a simple IF statement to see if it’s the first row with the ROW function, and if so, puts the name of the new column in the IF argument (2nd arg), and then the formula for the rest of the column in the ELSE argument (3rd arg). E:E is the range of my new column, and D:D is the URL column that REGEX is testing.
Voila! Regex capture groups-powered templating in Glide, and in this case, YouTube thumbnails from any YouTube video URL. This example uses ytimg.com to generate the thumbnails.
NOTE: In my case, URL is a required field, so it’s never empty. However, if your reference column can be empty, use the following formula:
=ARRAYFORMULA(IF(EQ(ROW(E:E),1),"Thumbnail",IF(ISBLANK(D:D),"","http://i3.ytimg.com/vi/"®EXEXTRACT(D:D,"(?:youtu\.be\/|watch\?v=)([\w\-]{10,12})")&"/hqdefault.jpg")))
This will ensure that if your reference column is blank in that row, the formula is not applied.