How to develop Google spreadsheet add-ons

In my previous article, we looked at extending Google Docs by creating a Google App Script. In today’s article we’ll explore the method of developing Google spreadsheet add-on and additional functionality inside a Google sheet. We’ll create script that changes the orientation of a selection to either vertical or horizontal based on the option the user selected.

We’ll also build a menu and a sidebar for the user to make their selection.

• Creating a new Google spreadsheet addon
• Creating a new Google Spreadsheet Script
• Adding custom menus in Google Sheet add-on
• Adding a sidebar to Google Sheets

Creating a new Google spreadsheet add-on

We’ll start by creating a new spreadsheet add-on inside Google Drive, by clicking on the CREATE button and selecting Spreadsheet from the menu:

This will open a new menu inside your browser which contains the new, blank spreadsheet. Go ahead and give the new spreadsheet a name and enter some data in the first column:

Creating a new Google Spreadsheet Script

To create a new script select the Script Editor… option from the Tools menu.

This will open a new window in your browser which contains the Google Script Editor. Click on Spreadsheet in the Create script for list.

This will create a new script for you with onOpen and readRows functions already inserted in the script editor.

Creating custom menus for Google spreadsheet add-on

To develop a custom menu for our Google spreadsheet add-on, we’ll use a number of UI elements in this script. First, we’ll add a custom menu from which the user can select the current selection’s orientation (either Horizontal or Vertical). To do this change the onOpen function to include the following code:

function onOpen() {
    var ui = SpreadsheetApp.getUi();
    var mainMenu = ui.createMenu("Orientation");
    mainMenu.addItem("Flip Selection Vertically", "flipItMenuVertical");
    mainMenu.addSeparator();
    mainMenu.addItem("Flip Selection Horizontally", "flipItMenuHorizontal");
    mainMenu.addToUi();
    ui.showSidebar(createUI());
};

The code above first retrieves a reference to the spreadsheet’s user interface environment and then creates a menu on the menu bar using the createMenu function. We then add two items and a separator to this menu using the addItem and addSeparator functions. After this, we added the menu to the user interface by calling the addToUi function.

The addItem function accepts two parameters, the first is the menu’s text and the second the name of the function that should be called when the user clicks on the menu item. When running the script you should see the newly created menu:

The two menu items call either the flipItMenuVertical or flipItMenuHorizontal functions depending on which menu item the user clicked on. These functions are similar in the fact that they both first prompt the user for a column name and then call the flipSelection function, passing in the orientation and value the user entered.

To get the target column name, we show a dialog using the prompt function. This will display a modal dialog with a label and a textbox with two buttons:

The code for the two functions is:

function flipItMenuVertical() {
    var ui = SpreadsheetApp.getUi();
    var result = ui.prompt('Destination', 'Please enter the column name to start in e.g. C :', ui.ButtonSet.OK_CANCEL);
    var button = result.getSelectedButton();
    var targetCell = result.getResponseText();
    if (button == ui.Button.OK) {
        flipSelection('vertical', targetCell);
    }
}
 
function flipItMenuHorizontal() {
    var ui = SpreadsheetApp.getUi();
    var result = ui.prompt('Destination', 'Please enter the column name to start in e.g. C :', ui.ButtonSet.OK_CANCEL);
    var button = result.getSelectedButton();
    var targetCell = result.getResponseText();
    if (button == ui.Button.OK) {
        flipSelection('horizontal', targetCell);
    }
}

Building a sidebar for Google Sheets add-on

In the onOpen function we call the showSidebar method and pass in the function name that was used to build the UI for the sidebar. In this example this function is called createUI. In this function, we first set the title of the sidebar by calling the setTitle function and then proceed to create two radio buttons, a normal button and a panel by calling the createRadioButton, createButton and createVerticalPanel function respectively.

In order to display the controls underneath each other we add it to the vertical panel we’ve just created. Using the createServerHandler and addValueChangeHandler functions we add handlers that will be called when the user selects one of the radio buttons and clicks on the button. The code for the createUI function follows:

function createUI() {
    var app = UiApp.createApplication();
    app.setTitle('Flip Selection');
 
    var radioFlipVertical = app.createRadioButton('radioFlip').setId('radioFlipVertical').setText('Flip Selection Vertically.');
    var radioFlipHorizontal = app.createRadioButton('radioFlip').setId('radioFlipHorizontal').setText('Flip Selection Horizontally.');
    var buttonFlip = app.createButton("Flip Selection");
    var panel = app.createVerticalPanel();
 
    panel.add(radioFlipVertical);
    panel.add(radioFlipHorizontal);
    panel.add(buttonFlip);
    app.add(panel);
 
    var handlerRadioButtons = app.createServerHandler('radioButtonsChange');
    radioFlipVertical.addValueChangeHandler(handlerRadioButtons);
    radioFlipHorizontal.addValueChangeHandler(handlerRadioButtons);
 
    var handlerButton = app.createServerHandler('flipIt');
    buttonFlip.addClickHandler(handlerButton);
 
    return app;
}

When the user clicks on the button in the sidebar, the flipIt function is called. This will also prompt the user for a destination column and then flip the orientation of the selection in the sheet based on the radio button that was selected. The code for the flipIt function looks like follows:

function flipIt(e) {
    var ui = SpreadsheetApp.getUi();
 
    var result = ui.prompt('Destination', 'Please enter the column name to start in e.g. C :', ui.ButtonSet.OK_CANCEL);
    var button = result.getSelectedButton();
    var targetCell = result.getResponseText();
    if (button == ui.Button.OK) {
        var selectedOption = ScriptProperties.getProperty('selectedRadio');
        if (selectedOption == 'radioFlipVertical') {
            flipSelection('vertical', targetCell);
        } else {
            flipSelection('horizontal', targetCell);
        }
    }
}

You will notice that the flipSelection function is called based on the user’s selection. This function’s code is as follows:

function flipSelection(orientation, target) {
    var sheet = SpreadsheetApp.getActiveSheet();
    var selectedValues = sheet.getActiveRange().getValues();
    var range;
    var startColIndex = sheet.getRange(target + '1').getColumn();
 
    sheet.getActiveRange().clear();
    if (orientation == "horizontal") {
        for (var i = 0; i < selectedValues.length; i++) {
            range = sheet.getRange(1, startColIndex + i);
            range.setValue(String(selectedValues[i]));
        }
    } else if (orientation == "vertical") {
        var vals = String(selectedValues[0]).split(",");
        var rowCount = 1;
        vals.forEach(function (value) {
            range = sheet.getRange(rowCount, startColIndex);
            range.setValue(value);
            rowCount++;
        });
    }
}

The code above gets a reference to the current active range using the getActiveRange function and the getValues function. We then loop through these values and add it to a new range starting in the column the user specified via the prompt.

The full code listing for the script follows below:

function onOpen() {
    var ui = SpreadsheetApp.getUi();
    var mainMenu = ui.createMenu("Orientation");
    mainMenu.addItem("Flip Selection Vertically", "flipItMenuVertical");
    mainMenu.addSeparator();
    mainMenu.addItem("Flip Selection Horizontally", "flipItMenuHorizontal");
    mainMenu.addToUi();
    ui.showSidebar(createUI());
};
 
function createUI() {
    var app = UiApp.createApplication();
    app.setTitle('Flip Selection');
 
    var radioFlipVertical = app.createRadioButton('radioFlip').setId('radioFlipVertical').setText('Flip Selection Vertically.');
    var radioFlipHorizontal = app.createRadioButton('radioFlip').setId('radioFlipHorizontal').setText('Flip Selection Horizontally.');
    var buttonFlip = app.createButton("Flip Selection");
    var panel = app.createVerticalPanel();
 
    panel.add(radioFlipVertical);
    panel.add(radioFlipHorizontal);
    panel.add(buttonFlip);
    app.add(panel);
 
    var handlerRadioButtons = app.createServerHandler('radioButtonsChange');
    radioFlipVertical.addValueChangeHandler(handlerRadioButtons);
    radioFlipHorizontal.addValueChangeHandler(handlerRadioButtons);
 
    var handlerButton = app.createServerHandler('flipIt');
    buttonFlip.addClickHandler(handlerButton);
 
    return app;
}
 
function flipItMenuVertical() {
    var ui = SpreadsheetApp.getUi();
    var result = ui.prompt('Destination', 'Please enter the column name to start in e.g. C :', ui.ButtonSet.OK_CANCEL);
    var button = result.getSelectedButton();
    var targetCell = result.getResponseText();
    if (button == ui.Button.OK) {
        flipSelection('vertical', targetCell);
    }
}
 
function flipItMenuHorizontal() {
    var ui = SpreadsheetApp.getUi();
    var result = ui.prompt('Destination', 'Please enter the column name to start in e.g. C :', ui.ButtonSet.OK_CANCEL);
    var button = result.getSelectedButton();
    var targetCell = result.getResponseText();
    if (button == ui.Button.OK) {
        flipSelection('horizontal', targetCell);
    }
}
 
function radioButtonsChange(e) {
    ScriptProperties.setProperty('selectedRadio', e.parameter.source);
}
 
function flipIt(e) {
    var ui = SpreadsheetApp.getUi();
 
    var result = ui.prompt('Destination', 'Please enter the column name to start in e.g. C :', ui.ButtonSet.OK_CANCEL);
    var button = result.getSelectedButton();
    var targetCell = result.getResponseText();
    if (button == ui.Button.OK) {
        var selectedOption = ScriptProperties.getProperty('selectedRadio');
        if (selectedOption == 'radioFlipVertical') {
            flipSelection('vertical', targetCell);
        } else {
            flipSelection('horizontal', targetCell);
        }
    }
}
 
function flipSelection(orientation, target) {
    var sheet = SpreadsheetApp.getActiveSheet();
    var selectedValues = sheet.getActiveRange().getValues();
    var range;
    var startColIndex = sheet.getRange(target + '1').getColumn();
 
    sheet.getActiveRange().clear();
    if (orientation == "horizontal") {
        for (var i = 0; i < selectedValues.length; i++) {
            range = sheet.getRange(1, startColIndex + i);
            range.setValue(String(selectedValues[i]));
        }
    } else if (orientation == "vertical") {
        var vals = String(selectedValues[0]).split(",");
        var rowCount = 1;
        vals.forEach(function (value) {
            range = sheet.getRange(rowCount, startColIndex);
            range.setValue(value);
            rowCount++;
        });
    }
}

And finally, this is the end result of the Google Sheets add-on we have just created:

Thank you for reading. Until next time, keep coding!

You may also be interested in:

• Creating Google Gmail sidebar gadgets
• Developing Google Gmail contextual gadgets
• Developing Google Docs add-ons