Google Sheets – Custom Menu-item and HTML Sidebar (apps-script)

After much (and by much, I mean muuuuuuuuuuch) Googling and YouTube-ing my little monster is finally alive!!!!

This (unpublished) little add-on creates a custom menu item in the Google Sheets ribbon and has two custom items. One launches a custom html side-bar (with quick-launch buttons) and the other runs a custom Apps-Script function.

Have fun with the code below.

Until next time…

* This Add-on contains the following:                                                                                                                                           
*    1. function onOpen()      - This fucntion adds a custom menu - 'Data Tools Menu' - to the Google Sheets ribbon and two custom Items within the menu once the sheet is opened 
*          custom item 1: 'Sidebar Data Tools'                                                                                                                                  
*          custom item 2: 'Say Hello there'                                     
*    2. function onInstall()   - This function runs the 'onOpen()' function once the add-on is installed by the user
*    3. function openSidebar() - This function adds a custom html sidebar -'Sidebar.html' - with 4 buttons:
*          button 1: 'Apps-script CSS'      - Opens a website using an anchor tag <a></a> / link
*          button 2: 'Apps-script Triggers' - Opens a website using JavaScript
*          button 3: 'Display Data'         - Display's a message from a script cotained in the html file 
*          button 4: 'Say Hello'            - Calls the 'sayHello()' function from the file
*    4. function sayHello()    - Opens a message box with the message 'Hello there'

//Adds a custom menu called 'Data Tools Menu' to the Google Sheets ribbon once the sheet is opened
function onOpen(){
  //Call the Ui class   
    //If you don't include a name for your menu below, it will be added under the 'Add-on' tab in the ribbon and the default name will be the name of your project
    .createMenu('Data Tools Menu')
    //Adds an item to the 'Datat Tools Menu' - addItem('Item name','Item function')
    //This item will run the 'openSidebar' function (lower down on this page) and open a sidebar menu in your spreadsheet
    .addItem('Sidebar Data Tools', 'openSidebar')
    //Adds a seperator line between the two menu items - optional
    //This items will run the 'sayHello' function lower down on this page
    .addItem('Say Hello there', 'sayHello')
    //Inserts the 'Data Tools Menue' into the instance of the editor's user interface
  //All the code above can be entered as a single line of code:
  //SpreadsheetApp.getUi().createMenu('Data Tools Menu').addItem('Sidebar Data Tools', 'openSidebar')..addSeparator().addItem('Say Hello there', 'sayHello').addToUi();

//Runs the 'onOpen' function above when the user installs the add-on 
//'onOpen()' below can be replaced with the code above, but less code is better
function onInstall() {

//The Function below displays an HTML Sidebar (with custom HtmlService content')  called 'Sidebar.html' with the heading 'Data Tools'
function openSidebar(){
  var html = HtmlService.createHtmlOutputFromFile('Sidebar').setTitle('Data Tools');

//This function will display a message box with the text 'Hello there'
function sayHello(){
  //In this example we use 'Browser.msgBox' to display a message but in JavaScript we use 'window.alert'
  //You can also use 'window.alert' in App Script, but you will have to call the Ui class first - SpreadsheetApp.getUi().alert('Hello there');;
  Browser.msgBox('Hello there');


<!DOCTYPE html>
  <!-- Document Head -->
    <base target="_top">
    <!-- Add the Google Apps Script CSS file -->
    <link rel="stylesheet" href="">
    <!-- Add Styling to your sidebar -->	
    <!-- You can also refer to an external stylesheet - as with the link above or other css frameworks like Bootstrap or W3School's CSS -->
    <!-- Try not to have styling elements within your html page and rather make of use external stylesheets -->
        body {
          padding-left: 10px;
        a:active {
          color: white;
          text-decoration: none:
       a:hover {
          color: white;
          text-decoration: none:
       a:link {
          color: white;
          text-decoration: none:
       a:visited {
          color: white;
          text-decoration: none:
       div {
          padding: 10px;
  <!-- Document Body -->
    <!-- Add 4 Buttons to your sidebar -->	
    <!-- class="action" is a class contained within the Google Apps Script CSS file that was added in the <head> section -->
    <!-- It adds default styling to our buttons - blue bakcground, with white text -->
    <!-- This button launches a weblink using the standard html anchor/link method -->
    <button type="button" class="action"><a href="">Apps-script CSS</a></button>
    <!-- This button launches a weblink using JavaScript -->
    <button type="button" class="action" onclick="'')">Apps-script Triggers</button>  
    <!-- This button launches a custom script from the script section below -->
    <button type="button" class="action" onclick="displayData()">Display Data</button>
    <!-- This button launches a custom script contained in the file - 'sayHello()' -->
    <button type="button" class="action" onclick="">Say Hello</button>  
  <!-- You can place this script in a seperate script file if you prefer -->
    function displayData(){
      //In Apps Script we would use 'Browser.msgBox' (or the Ui class) to display a message but in JavaScript we use 'window.alert'
      window.alert('Displaying your Data');

Leave a Reply

Your email address will not be published. Required fields are marked *