This post helps you play a YouTube video directly from Google Sheets using Google Script.
I collaborated with a tech event manager who organizes conferences. The goal was to create an automated solution in Google Sheets to streamline the speaker review process for those applying to present at these conferences.
To enhance the user experience, I developed a custom menu that facilitates the execution of different scripts and provides easy access to a training document. This document guides the team through the automation solution, ensuring that everyone is on the same page.
The icing on the cake was adding a YouTube video that plays directly in the Google Sheets document at the start of the review process. This simple addition transformed the spreadsheet into an interactive platform and gave it a professional touch. This video embedding feature can also be used in some contexts to provide users with easy access to training resources directly from Google Sheets.
In this tutorial, we’ll learn how to play a YouTube video in a custom dialog box inside a Google Sheet using Google Apps Script.
1. Open your spreadsheet
First, get a copy of the sample spreadsheet that already contains the required code. In this case, you only need to authorize the script (step 4).
But if you want to use this feature in an existing Google sheet, then go to step 2.
2. Create an HTML file for the YouTube video
In the Google Sheet, click on Extensions > Apps Script. In the Apps Script editor, click on File > New > HTML file and name it “video.html.” Copy and paste the provided code into the “video.html” file. This code creates an HTML file with an embedded YouTube video that autoplays. The IFrame Player API is loaded asynchronously, and the onYouTubeIframeAPIReady function creates a new YouTube player instance. The onPlayerStateChange function listens for changes in the player’s state and closes the dialog when the video ends.
<!DOCTYPE html>
<html>
<body>
<iframe id="videoPlayer" width="560" height="315" src="https://www.youtube.com/embed/VIDEO_ID?autoplay=1&mute=1&enablejsapi=1" frameborder="0" allowfullscreen></iframe>
<script>
// This code loads the IFrame Player API code asynchronously.
var tag = document.createElement('script');
tag.src = "https://www.youtube.com/iframe_api";
var firstScriptTag = document.getElementsByTagName('script')[0];
firstScriptTag.parentNode.insertBefore(tag, firstScriptTag);
// This function creates an <iframe> (and YouTube player)
// after the API code downloads.
var player;
function onYouTubeIframeAPIReady() {
player = new YT.Player('videoPlayer', {
events: {
'onStateChange': onPlayerStateChange
}
});
}
// The API calls this function when the player's state changes.
// The function indicates that when playing a video (state=1),
// The player should play for six seconds and then stop.
function onPlayerStateChange(event) {
if (event.data == YT.PlayerState.ENDED) {
google.script.host.close();
}
}
</script>
</body>
</html>In the above HTML file, the video ID of the URL https://www.youtube.com/embed/VIDEO_ID?autoplay=1&enablejsapi=1 with the ID of the video that you can get from when you play the video on YouTube. The ?autoplay=1&enablejsapi=1 part is necessary for autoplay and for the JavaScript API to work.
3. Create a Google Apps Script function to display the video
In the Apps Script editor, click on File > New > Script and name it Code.gs. Copy and paste the following code into the Code.gs file. This function creates an HTML output from the video.html file, sets its dimensions, and displays it in a modal dialog with the title “Play Video”.
function playVideo() {
var htmlOutput = HtmlService.createHtmlOutputFromFile('video.html')
.setWidth(640)
.setHeight(390);
SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Play Video');
}4. Add a custom menu to trigger the video
In the Code.gs file, add the following code. This function creates a custom menu in the Google Sheet with a single item “Play Video” that triggers the playVideo() function when clicked.
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Custom Menu')
.addItem('Play Video', 'playVideo')
.addToUi();
}Now, save your script and refresh your Google Sheet. You should see a new custom menu called Custom Menu with an item called Play Video. Clicking on this item will display the YouTube video in a dialog box inside the Google Sheet.
The first time you run this function, you will be prompted to grant certain permissions. Follow the prompts and grant the necessary permissions to Google.
Congratulations! You’ve successfully made it possible to play a YouTube video directly from a Google Sheet.
Joseph Asinyo
Google Workspace Developer
I’m Joseph. I love building applications and writing Google scripts to automate Google Workspace for greater productivity. Find more about me.
