My chops may not be elegant, but it sure was fun this week to tinker with some Google Sheet formulas to deal with a tricky task. Not that this tweet explains it any more, but to me, it’s better meds than demon sperm.
Does this tweet say enough for a blog post? Never.
First, some context, and a current project I am way behind on blogging. I am working with Clint Lalonde on a BCcampus effort supporting five projects each adding H5P practice problems to open textbooks published in Pressbooks.
What remains to be blogged is the H5P PB Kitchen site “I am running (hosted by OpenETC), and the idea behind it’s kitchen metaphor. Hey blogging need not be chronological.
More soon.
But here is the situation we faced recently. One of the more popular H5P content types is the Interactive Video one. With this tool, a video can be augmented with stops on the playback to insert popup information (sort of like annotation) or interactive questions (multiple choice, true false, fill in words, drag and drop, etc).
As H5P tools go, it’s easy, as it takes the link to the YouTube page for a video as the means to set it up. And I would guess, that’s about 99% of the way it is used.
I can just paste in the URL for any public YouTube video, like https://www.youtube.com/watch?v=lkcwelfAuks and the magic happens (uploading videos is not encouraged on busy servers and are likely limited by storage caps).
But here is the problem.
YouTube.
At the end of all YouTube videos, viewers are presented with videos YouTube picks for you. There is no way as a web site publisher when embedding videos you can prevent this (the old trick of adding &rel=0 to the URL was taken away by YouTube in 2018). And also, YouTube, the company harvests data on all viewers.
That is the price for the ease of use. YouTube manages what is shown on screen, and I’ve heard plenty of horror stories of teachers getting in appropriate recommended videos when showing clips in class.
One of our projects is working with a set of 20+ short explainer type videos currently hosted in YouTube, and this all started with the question of how to keep the recommended videos from popping up.
A possible end around is publishing videos to vimeo, a reliable service that works as easily in H5P and does not insert stuff at the end of your videos. You are limited though on free vimeo accounts to uploading only 500Mb per week to vimeo at a max of 5Gb total. Free always has a cost. Actually, H5P only supports Vimeo Pro!
But another possibility arrived. Clint noted that all of the videos for this project were supported by a previous grant and are already available on a BCcampus managed Kaltura server, an open source video server used at many institutions that helps bypass reliance on commercial services.
I don’t know a whole lot about Kaltura, there seems to be different attributed depending on how it is configured at an institution. The HTML embed codes are pretty ugly (I did spend a little bit of time testing if was possible to set up autoembed code for Kaltura content for my SPLOTbox theme, failed there).
The trick is that H5P requires a direct URL to a video’s source, e.g. something that ends in .mp4. And this does not seem to be publically available in the share link options.
But I did find an approach detailed at UW-Madison that suggested there are ways to suss out the direct mp4 link knowing the structure of links at a particular Kaltura site, and doing some wrangling to get the proper ID numbers to swap into a sample web address.
This tutorial shows Kaltura video owners how to generate a direct link to MP4 video files for their uploaded media. This link or URL allows you to use your videos within other contexts like inside of an H5P interactive video or you can decide to use a completely different player like JW Player.
I was on the scent. Clint and I both began doing what savvy web folks do- view source. So for a video hosted on the BCcampus Kaltura site, say the one Open Education Stories: Creating and reusing problems using OER (worth a watch, by the way), we found that the OpenGraph metadata used to enable sharing and other ways of embedding, had the link we were seeking.
- The video provides a public sharable link
https://video.bccampus.ca/media/Open+Education+StoriesA+Creating+and+reusing+problems+using+OER/0_hmwjpdp6but this will not work in H5P - Unde “direct asset ref” in the pages meta data, we find the URL we seek
https://admin.video.ubc.ca/p/122/sp/12200/playManifest/entryId/0_hmwjpdp6/flavorId/0_a6p8whkp/format/url/protocol/http/a.mp4or as shown in source (love me some raw HTML)
Now it is possible to try to explain to someone how to take from the public URL the unique content ID hmwjpdp6 and insert it into the structure of the direct mp4 link, but I can see people going fuzzy already.
I could also go one by one to the videos and extract them for the people working on the project. But that’s giving them (and others) fish instead of fishing lessons.
How to make this more explainable? Since these are OpenGraph tags, a tool like https://opengraphcheck.com/ seemed viable to make it easier to get that mp4 link.
This worked well, maybe a simpler way to get to this than lifting the hood and looking at HTML.
But there was a more important bonus that came into play later — the results are at a direct link that show these results, a link that can be turned into a formula for any URL. Try it.
Another bonus was that it also provide a link to an image- when using a direct link in H5P you will need to upload a poster image to represent the video.
Now this seemed like job for a spreadsheet, and Google Sheets has functions for fetching the full web content from a URL, namely IMPORTXML (I used this helpful guide) which not only can access the full contents, but with some XPath Karate moves, let’s you pull out maybe one value form it like I was seeking.
The challenge was, Spreadsheet Kid, from a BCcampus Kaltura URL, can I devise something that will take that and extract that mp4 URL?
I explain below, but for those who want to cut to the chase, you can make a copy of my spreadsheet and explore/play/criticize.
Paste in the URL in the green cell, and, if it works, the results are shown below.
It might have worked to import the HTML from the Kaltura link, but I decided to create the URL I wanted to use based on the results from https://opengraphcheck.com/ since they are consistently structured in the results page (within an HTML table).
To make things simpler to reference the URL we are working with, I used the Data –> Named Ranges feature to create a reference name for this cell, rather than always have to enter something like D4 in formulas– I named this cell kaltura_url.
The URL I want to use in IMPORTXML needs to be constructed from what is entered here, and passed as a parameter to the opengraphcheck.com address. As it is a URL parameter, it has to be encoded. The address I need to use in IMPORTXML then is
"https://opengraphcheck.com/result.php?url="&ENCODEURL(kaltura_url)
It’s the next part that takes the extra spin moves– figuring out the Xpath query to find a specific element in the source what that URL returns. I’ve only used Xpath passingly, so it took a lot of trial and effort. I start by looking at the HTML source of the results I am working with.
All of the opengraph data is contained in an HTML table defined by a few class names, it looks safe to use just class="table". Each meta data element is in a table row, and the second cell of each row is the value. But I start simple, and just see what happens if I just try to fetch all the data.
Knowing the HTML, I see I can reach into the table, inside the tbody, and grab each row tr using in cell O16
= IMPORTXML("https://opengraphcheck.com/result.php
url="&ENCODEURL(kaltura_url),"//table/tbody/tr")
This fills out cells with each rows contents, each td in a spreadsheet cell.
I thought I could just fill a spreadsheet with this for the one project team. And it least showed me I had access to the data I needed. So now, How do I reach in and find the value for just one of these tags, namely the one for og:video?
Once again a search leading to a StackExchange response got me close to the answer, there is a way to get to a specific row using the xpath position() function.
I count in the table data that the mp4 link is in the 7th row of the table. I can get that entire row using
= IMPORTXML("https://opengraphcheck.com/result.php?
url="&ENCODEURL(kaltura_url),"//table/tbody/tr[position()=7]")
or shorter
= IMPORTXML("https://opengraphcheck.com/result.php?
url="&ENCODEURL(kaltura_url),"//table/tbody/tr[7]")
And I can right to the data I need ( the URL) by adding a selector for the second cell in that row
= IMPORTXML("https://opengraphcheck.com/result.php
url="&ENCODEURL(kaltura_url),"//table/tbody/tr[7]/td[2]")
I got fancy, and made a formula to get the URL for the thumbnail image, in the 5th row (which someone can download and then add to the H5P interface)
= IMPORTXML("https://opengraphcheck.com/result.php
url="&ENCODEURL(kaltura_url),"//table/tbody/tr[5]/td[2]")
And even added one to show the title of the video, just as a check that it was the correct one (the title is in the first table row)
= IMPORTXML("https://opengraphcheck.com/result.php
url="&ENCODEURL(kaltura_url),"//table/tbody/tr[1]/td[2]")
You can see a comparison of the H5P interactive video for the YouTube versions – just a mockup to show the project people the difference. See the same interactions but using the Kaltura version (the YouTube one seems to have a custom thumbnail). The Kaltura version has no recommended videos at the end, but also, as a bonus, by using the H5P field for title and description, we can add a prompt that we cannot do in YouTube:
Anyhow, this whole thing was a fun and unexpected foray into doing some spreadsheet tinkering. And I know I am just scratching the surface with my moves.
Karate moves in motion. Function on. Function off.
Featured GIF: I went a bit overboard on making this GIF, starting with a downloaded version of American Karate Kid from Giphy. This was imported into Photoshop, and every other frame dropped to make smaller. Then a few logos superimposed on frames. I cannot explain why I bother with this, but it fits so well.
Ha ha. I love it. All my favorite things in one post.