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>
