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…


Code.gs

/********************************************************************************************************************************************************************************
* 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 Code.gs 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   
  SpreadsheetApp.getUi()
    //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
    .addSeparator()
    //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
    .addToUi();
  
  //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() {
  onOpen();
}

//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');
  SpreadsheetApp.getUi().showSidebar(html);
}

//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');
}

Sidebar.html

<!DOCTYPE html>
<html>
  <!-- Document Head -->
  <head>
    <base target="_top">
    <!-- Add the Google Apps Script CSS file -->
    <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
      
    <!-- 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 -->
      <style>
        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;
       }
    </style>
  </head>
  
  <!-- Document Body -->
  <body>
    <!-- Add 4 Buttons to your sidebar -->	
    <div></div>
    
    <!-- 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="https://developers.google.com/apps-script/add-ons/css">Apps-script CSS</a></button>
    <div></div>
    
    <!-- This button launches a weblink using JavaScript -->
    <button type="button" class="action" onclick="window.open('https://developers.google.com/apps-script/guides/triggers/')">Apps-script Triggers</button>  
    <div></div>
    
    <!-- This button launches a custom script from the script section below -->
    <button type="button" class="action" onclick="displayData()">Display Data</button>
    <div></div>
    
    <!-- This button launches a custom script contained in the Code.gs file - 'sayHello()' -->
    <button type="button" class="action" onclick="google.script.run.sayHello()">Say Hello</button>  
  </body>
    
  <!-- You can place this script in a seperate script file if you prefer -->
  <script>
    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');
    }
  </script>
</html>

Leave a Reply

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