Google Sheets add a Background Music

In this tutorial I'll show you how to play a music or an audio directly in your sheet.

In Google Sheet it's not possible to directly add a music and play it. But, with the help of the Google Apps Script Platform, we can embed a sidebar in our sheet, and from there add and listen every audio we want.
Could be a music, a relaxing sound effect or any sound you want!

And we can also play it automatically, when the sheet is open so we don't need to click anything. You open the sheet and the music start to play.

Let's start.

First thing you need to create a new sheet, click on "Extensions" in the menu bar, and click on "Apps Script".

Now copy this function that will run automatically when you open your sheet:

function onOpen(e) {
  //create a menu bar element to open the sidebar
  SpreadsheetApp.getUi()
  .createMenu('Music Player')
  .addItem('Show sidebar', 'showSidebar')
  .addToUi();
}

This function creates a new menu called "Music Player" in the menu bar, creates an element inside this menu called "Show sidebar" and assign the function "showSidebar" to the click on this element.

function showSidebar() {
  //create sidebar layout from file Sidebar.html
  var ui = HtmlService.createTemplateFromFile('Sidebar')
    .evaluate()
    .setSandboxMode(HtmlService.SandboxMode.IFRAME)
    .setTitle("Music Player");

  SpreadsheetApp.getUi().showSidebar(ui);
}

Now we need to define the layout file of the sidebar.
Create a new HTML file, by clicking the "+" symbol near files, and call this file "Sidebar".

<!-- Use a templated HTML printing scriptlet to import common stylesheet -->
<?!= HtmlService.createHtmlOutputFromFile('Stylesheet').getContent(); ?>

<div class="sidebar branding-below">
  <p>
    Play some chill music.
  </p>
  <audio id="player" controls>
    <source src="http://docs.google.com/uc?export=open&id=XXXXXXXXXXXXXXXXXX" type="audio/mp3">
    Your browser does not support the audio tag.
  </audio>
</div>


<div class="sidebar bottom">
  <span class="gray branding-text">PlayMusic</span>
</div>

Let's also add a new HTML file called "Stylesheet", which includes the CSS Google style for our sidebar:

<!-- This CSS package applies Google styling; it should always be included. -->

<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons.css">

<!-- Customize your audio player -->
<style>
  #player {
    width: 95%;
  }
</style>

The file types supported by the audio tag are MP3, WAV, and OGG (Depending on the browser, some types aren't supported).
So in the source link you can insert any link that end with ".mp3", ".wav" or ".ogg".

If you want to use a Google Drive audio, you need to make the file public by sharing it.

And click on "change to everyone with the link":

You will get a link like this:

https://drive.google.com/file/d/XXXXXXXXXXXXXXXXXX/view?usp=sharing

The XXXXXXXXXXXXXXXXXX string is your file ID. But we can't use this link. To obtain a direct link to the mp3, just add that ID to this link:

http://docs.google.com/uc?export=open&id=XXXXXXXXXXXXXXXXXX

Copy this link in the src attribute.

Now everything is ready. Save your project and reload the sheet page. After the loading is complete, you should see the "PlayMusic" menu.

Click on show sidebar and the audio player will show up in the right.

If you want to play the music automatically, without clicking in the menu bar, you need to add the "autoplay" attribute to the audio tag, in the HTML file (you can also add a loop in your audio, with the "loop" attribute after the autoplay attribute).

<audio id="player" controls autoplay>

Another thing you need to change if you want to autoplay the audio, is the onOpen trigger. This because the "onOpen" function (with this specific name) is a default function that is associated with a simple trigger, but all simple trigger have some limitations, so in this case we need to add our "onOpen" trigger. Before this we can modify the name of the function "onOpen" to "onOpenAutoplay", and add inside it a call to our showSidebar function.

function onOpenAutoplay(e) {
  //create a menu bar element to open the sidebar
  SpreadsheetApp.getUi()
  .createMenu('Music Player')
  .addItem('Show sidebar', 'showSidebar')
  .addToUi();
  //on sheet open, show the sidebar that autoplay the audio inside it
  showSidebar();
}

Last thing we need to add our custom trigger. So go in the trigger section on the left (the timer icon), add a new trigger with these settings:

Save, reload the sheet and enjoy your music :D

Other info:

• The first time you run the script, it will ask you to give permissions
• If the sheet is in view mode only, only you can run the script and play the music. If you make the sheet editable for everyone with the link, only non-anonymous user (logged account) will be able to run the script.

Comments

[Eric (Official)]

This is great. I've got it all up and running, but it is possible to have the music play without showing the sidebar? Or to shrink the sidebar so it isn't as intrusive?

[Vryntel]

Hey, unfortunately you can't play music without showing the sidebar and you can't make the sidebar smaller either.