Google Spreadsheet Button to Run Scripts in 4 Steps

 

If you come from Excel, you might have seen some fancy spreadsheets with clickable buttons that trigger VBA macros. Luckily, Google Spreadsheets and Google Apps Script offer the same functionality.

This tutorial will demonstrate how to add custom buttons to a Google Spreadsheet that run Google Apps Script functions when they are clicked. There are also examples for buttons that sort ranges, add timestamps, send emails, and increment cell values.

 

Let’s Make a Spreadsheet Button

Creating a spreadsheet button is actually easier than creating a custom UI menu. There are just three steps:

 

1. Create Your Custom Button

Image for Google Spreadsheet Button

There are two ways to create a button in Google Spreadsheets. You can either insert an image or insert a drawing. For this example, we will insert this amazing image of a Gorilla high-fiving a Shark. (I bet you’re glad you picked this tutorial now.)

 

 

 

Google Spreadsheet Insert Button

In the Google Spreadsheet top menu, go to Insert > Image or Insert > Drawing.

There are several ways to embed images. You can upload them or add them by URL. That’s how I found this great example.

Creating a drawing is easy. Just make a shape that looks like something you might want to click and click Save & Close. The benefit of drawing a button is that you can write descriptive text for the button to signal what the function does.

 

Now that you have a button image on your spreadsheet. Let’s write the function that it will trigger.

 

2. Create Your Custom Function

For this example, we are going to keep it simple. This custom function, called highFive, simply displays a pop-up box that says “High Five!” Dont’ worry, we will get into more interesting functions later in the tutorial.

Go to Tools > Script Editor and define and insert this function.

That was easy. Now in the Google Apps Script menu and save your script file: File > Save.

 

3. Assign Your Function to you Button

Assign Script to Button

 

Right-click the image or button and at the top right corner, there will be a small drop down menu. Click Assign script…

 

 

 

 

Name the Google Apps Script Button Function

 

 

You will be prompted, “What script do you want to assign?”

Enter the name of the function that you will use. In this case highFive.

 

 

 

4. Click the Button!

And now for the grand finale! Click the button and you will see the High Five Button Click!message box say “High Five!”

If you need to reposition your button, right click the image. The button will then be outlined in light blue and you can drag the button to wherever you might need it.

Placing the button in a frozen row is also a good idea in case you are using a big spreadsheet.

 

More Useful Functions for Buttons

There are many more useful ways to use Google Spreadsheet buttons. Here are a few.

 

Set a Cell Value to the Current Date and Time

If you are using a spreadsheet to keep track of times, you might need to know exactly what time you started or completed a task. In that case, you can make your button trigger a function that sets the value of the active cell to the current time.

 

Send an Email to a Specific Recipient

Using a button is a really convenient way to run a function that takes spreadsheet values as arguments. The following code will allow you to send the contents of the active range as the email address, email subject, and email body.

Email Button From Spreadsheet

Highlight the three cell range, click the button, and your email is on its way. Of course, you can further customize the function with the recipient’s name, for example. But that is for another tutorial.

 

Increment a Cell Value

Sometimes it’s just easier to click buttons than edit the value inside of a cell. This function named increment, will add one to the value of cell A1 every time it is clicked.

 

Sorting a Range

This would be a great functionality to attach to a button click. However, the sort() function seems to have a known bug. Here is how to do this according to the Google Apps Script documentation.

 

Last Notes

Unfortunately, you can not insert the button inside of a cell. The button will always be effectively floating above the spreadsheet. But as I said before, you can right click and drag the button around a freeze the row that the spreadsheet will stay on to keep the button in a static position.

I hope this has helped you understand how to use Google App Script buttons in Google Spreadsheets and maybe even given you some ideas. If you are still looking for another example, tell me in the comments. If you have an interesting use for Google Spreadsheet Buttons, share it in the comments as well!

 

February 9, 2017

Posted In: User Interface

Tags: ,

Leave a Comment

Google Apps Script Custom Menus

 

It is fitting that my first post on this site is about custom menus with Google Apps Script, because, often, custom menus are your users’ first touch with your custom functionality. Your users will be surprised the first time they see the custom menu on their Google App UI. And they will be delighted when the functionality fulfills their expectations. This post will show how to surprise and delight your users with the Google Apps Script UI menus. 

 

Google Apps Script UI Menu

 

 

How Custom Menus Work

Custom menus allow you to execute the Google Apps Script functions that you have defined. Each menu item, when clicked, triggers the Google Apps Script function that corresponds to that menu item. Custom menus are easy to create, and we will get to that in a minute, but let’s get a few questions out of the way first.

Menus can be created and modified*, but menus cannot be deleted.

Most often, Menus are created with the onOpen() function, but they can also be created by an ay trigger or event. For example, you could use a menu item click to create another menu. Similarly, many Google Docs add-ons use the add-on menu to create a menu to offer access to the functions of their add-on.

Unfortunately, Menus cannot be dynamically created. The menu item names can be, but the functions that the menu items trigger can not be. This will become clear later, but the functions that are called by each menu item must be defined in advance and cannot take arguments. If you want to call functions with dynamic inputs, use the PromptResponse class or the value of the active cell if you are using Google Spreadsheets.

Menus can be modified in the sense that they can be replaced with an updated instance (copy) of the same menu. Any time the .createMenu() is used to create a menu with a name that has already been used, the .addToUi() method will then replace that original menu with the new menu.

 

Let’s Make a Custom Menu

Creating custom menus is very easy because the code that creates reads exactly like the menu appears. Each chained method gets a new line to represent the menu feature that the method adds.

 

Create Custom Google Apps Script Menus

 

To create a menu that appears when the Google Spread or Google Docs application is opened, place the call to .createMenu() inside the .onOpen method.

 

The Ui Class and the Menu Class

Custom menus make use of two Google Apps Script classes. The Ui Class offers the ability to add to, and modify the user interface of the Google App that the script is bound to. This is everything from pop-up prompts and alerts, to sidebars, to of course, menus. The Ui class offers the only way to create a Menu object, with the .createMenu() method.

The Menu class is much smaller in scope. The Menu class only offers the ability to define the appearance and functionality of a menu, and that is all.

If you find yourself creating big menus with sub menus, you might want to define each menu individually and pass them to their parent menu, in the .addSubMenu() method. For illustration purposes:

 

Three tier menus are actually not a good idea. Here’s why:

 

Tips on User-Friendly Menus

At a minimum, menus should be easy to use and fast to load. For usability, Google’s Material UI Guidelines, recommends the following:

  • Menu names and menu item names should be short, descriptive, and accurate, with sentence casing.
  • Do not duplicate menu names or menu item names
  • Menus should be nested no more than one level deep. (The example above was for example purposes.)

 

Google Apps Script Menus Hello World

 

Get started! To see how these menus work. Copy and paste one of the scripts into the Google Apps Script editor and press play. I hope this helps keep the users of your custom Google App Script happy!

 

February 3, 2017

Posted In: User Interface

Tags: ,

One Comment