In this post, you will learn how to play a YouTube video in a custom dialog box inside Google Sheets and Google Docs using Google Apps Script.
This can be helpful if you want to see a video directly from your Google Sheet or Docs without leaving your documents.
To get started, copy the Google Sheets template and/or the Google Docs template.
Next, you need to open the script editor of these documents, go to the video.html file, and update the VIDEO_ID to the ID of the YouTube video.
When you click on a YouTube video to watch it, the URL of the browser will be in this format:
https://www.youtube.com/watch?v=aKwkMZbeeGo
The video ID is the character string after “?v=“. In this case, it’s aKwkMZbeeGo.
That’s all. You can refresh the sheet or doc and click on Play video in the custom menu to open the video.
In case, you want to set up the script in an existing sheet or doc, you can follow these simple steps.
1. Create an HTML file
In your document (Google Sheets or Google Docs), click on Extensions > Apps Script. In the Apps Script editor, click on File > New > HTML file to create a new HTML file and name it “video.html.” Copy and paste the code below into the “video.html” file.
<!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
}
});
}
</script>
</body>
</html>In the Iframe element, you need to update the URL by replacing VIDEO_ID with the actual ID of your YouTube video.
3. Create a Google Script file
In the Apps Script editor, click on File > New > Script to create a Google Script file and name it Code.gs. Copy and paste the following code into the Code.gs file.
function playVideo() {
var htmlOutput = HtmlService.createHtmlOutputFromFile('video.html')
.setWidth(640)
.setHeight(390);
SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Play Video');
}
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Custom Menu')
.addItem('Play Video', 'playVideo')
.addToUi();
}This code contains two functions:
- playVideo(): This function creates an HTML output from the video.html file, and displays it in a modal dialog.
- onOpen(): This function creates a custom menu in the Google Sheet with a single item “Play Video” that triggers the playVideo() function when clicked.
4. Run the script
Now, save your script and refresh your Google Sheet/Doc. You should see a new custom menu with an item called Play Video. Clicking on this item will display the YouTube video in a dialog box inside your document.
