YouTube In Your Sheets

Embed in Sidebar

Author

Eamonn Cottrell
March 26, 2024

Since last we spoke I’ve…

  1. Run a 100K race in under 12hrs (personal record!)

  2. Hiked in the Smokies

  3. Finished my first Faulkner book

Now to the sheets…

You’ve always wanted to embed a YouTube video in a spreadsheet, haven’t you?

Me too.

In today’s tutorial, we’ll do exactly that using two handy built-in features to Apps Script in Google Sheets: sidebars and modals.

These are both types of pop up boxes that let us render HTML code on top of our spreadsheet. So it’s great if we want to include a personalized message or instructions about a specific spreadsheet right there in the sheet itself.

This takes things a step further than, say, simply sharing the link to the video and having the user leave the sheet to go watch it in another tab.

We’ll do three things:

  1. Make a custom menu to activate the pop ups.

  2. Write the two functions to run the sidebar and modal.

  3. Write the HTML of the sidebar and modal.

Here’s the YouTube walkthrough 👇

The difference between modal and sidebar is that the sidebar only appears on the right side (as a…wait for it…side bar) whereas the modal is a pop up box that is moveable on top of the sheet.

Custom Menu

Now, I’m a sucker for custom menus, and they’re not that hard.

function onOpen(e){
  SpreadsheetApp.getUi()
  .createMenu("YouTube Sidebar")
  .addItem("Open Sidebar", "sidebar")
  .addItem("Open Dialog", "dialog")
  .addToUi();
}

This code creates the menu named, “YouTube Sidebar” and then adds two items to it. The first name is what will appear in the menu, the second is the name of the function that it will run.

screenshot of custom menu options

Now the functions themselves…

Functions

These look similar because they’re doing the same things but using two different built in functions for the two different style embeds we want.

function sidebar(){
  let htmlOutput = HtmlService.createHtmlOutputFromFile('sidebar');
  SpreadsheetApp.getUi()
  .showSidebar(htmlOutput)

function dialog(){
  let htmlOutput = HtmlService.createHtmlOutputFromFile('modal');
  SpreadsheetApp.getUi()
  .showModelessDialog(htmlOutput, "YouTube")
}

We use the HtmlService to build an HTML page from the files we will create in the next step.

All the sidebar and dialog functions do is grab those files and then render them via the built-in ability of Apps Script to create those components. This would take a lot more coding on our own, but we get it for free with Apps Script.

HTML Files

To create an HTML file, we simply click the plus button in Apps Script to add a new file. It lets us choose between Script and HTML

screenshot of creating an HTML file with Apps Script

Then we need to know how to write some HTML. We can make this as complicated or simple as we need to. For these examples, I’m going very simple because we want one simple YouTube embed.

I’ll paste the sidebar file below. The modal is exactly the same except for the width of the <iframe> element. The sidebar is a fixed 300px width, so I made my modal embed a little wider simply because I could.

<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <title>podcast player</title>
    </head>
    <body>
      <div>
        <h1>Sidebar Embed</h1>
        <p>Yep, we embeded a YouTube video in a Google Sheet</p>
        <iframe width="300" height="168" src="https://www.youtube.com/embed/et0iWdbc0ys?si=qpJ4TwFdjiJUPsOy" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" allowfullscreen></iframe>
      </div>
</html>

If you know nothing about HTML, don’t be too intimidated. I copied a boilerplate HTML template and then added a <div> with a paragraph and our embed.

The important part for us is simply seeing how Apps Script will render an actual web page in these sidebars.

If you want to know more about building simple web sites and components with HTML, let me know! 😀

This is all we need to get up and running.

Now, when we click the custom menu, it loads the sidebar or modal with the YouTube video embedded and ready to play.

screenshot of working YouTube embeds

Here’s a link to the demo sheet I used if you’d like to make a copy to mess around with.

Thank you so much!

It means a lot that you’ve read this, and I hope it’s informed and/or entertained you for a few moments today!

Would love to say hi. Here are the best places to find me:

Need More Help?

  • Need a powerful automation tool for Google Sheets? Try Coefficient to automatically import data and sync with your business systems.

  • Hire an expert to help complete your next Google Sheets, Apps Script or Google Workplace project.

Reply

or to participate.