5+ ways to run Google Apps Scripts in Google Sheets

Google Apps Script enables you to extend Google Sheets and automate repetitive tasks. Once you write a script, you'll need to decide how to run it. There are several ways to do this so, in this tutorial, I will walk you through a few options and also help you understand when to use each one.

Run scripts from the Apps Script editor

This is the simplest way to run a script. First, select the function you want to run from the dropdown menu and then click the Run button to run it. This method is most useful for testing scripts as you write them to check if they work correctly or for running one-off scripts that are used infrequently.

The primary disadvantage of this method is that you need to open the script in the editor before running it and this isn't very user friendly. As you'll see below, there are many user friendly options for running scripts directly from the spreadsheet itself.

Run a script when the user selects a menu item from a custom menu

You can also run a function in your script whenever the user selects a menu item in a custom menu. A custom menu lets you extend the user interface of Google Sheets to make it easy for users to run scripts. Each menu item is linked to a function in your script and this function will be run whenever that menu item is selected by the user.

This is a common and user friendly way to enable users to run scripts in Google Sheets. Consider using this approach for scripts that need to be explicitly run by the user. If on the other hand, you want to automatically run scripts, use triggers instead.

Run scripts from a custom sidebar

You can also run scripts when users interact with a custom sidebar in Google Sheets. A sidebar is another way to create a custom user interface in Google Sheets. A sidebar is useful if your script has several options that the user must configure in an interface OR if you need a custom widget that is persistently displayed within the Google Sheets UI.

You can run a function in your script from the sidebar by using the google.script.run API.

Run scripts from a modal dialog

A modal dialog is used to display a custom user interface built using HTML as an overlay. Unlike a sidebar, a modal dialog prevents the user from interacting with the spreadsheet. Therefore, it should only be used when the user must see some contextual information or provide contextual input. You can use the google.script.run API to run a function in your script from a modal dialog.

Run Apps Script with a single click by using an image or a drawing in your spreadsheet

You can also create clickable buttons within your spreadsheet. This is especially useful when you are creating simple applications within a Google Sheets spreadsheet that require the user to run a script to take some action and you want to ensure they do not miss this step.

Unlike sidebars and custom menus, you can place these call-to-action buttons within your spreadsheet itself and you can also style them to ensure they stand out. In the video below, I'm sure you will not miss seeing the green and blue buttons. However unless you look closely, you might miss seeing the custom "Settings" menu.

You can create a clickable button by inserting an image or a drawing in your spreadsheet and assigning a script to it. This will enable users to run the script with a single click. Simple and efficient!

Run the script periodically or whenever some event occurs by using triggers

The previous examples in this tutorial all required the user to take some action to run the script. There are many instances where you might want scripts to be run automatically. For example, you might want to run your script whenever the spreadsheet is edited or whenever a form is submitted. You can achieve this by using triggers. A trighttps://storage.googleapis.com/spreadsheetdev-content/videos/5ways-appsscript-customfunctions-0.mp4ger works by running a function that you specify whenever an event occurs or periodically based on a schedule that you specify.

The video below shows an example of a trigger in action. Here, a toast notification is automatically displayed whenever the spreadsheet is edited.

Run your script as a custom function from a formula in your spreadsheet

Another common use case for scripting in Google Sheets is writing your own functions that can be used within formulas in your spreadsheet. If you've used spreadsheets, you've probably used functions like SUM(), AVERAGE(), etc., in formulas. You can write your own custom functions using Apps Script and then run them from a formula.

In the video below, I'm using a custom function called HELLO() that simply returns the string "Hello!".

Create a script by recording a macro and run it from the menu bar

A macro is a feature in Google Sheets where you can create a script by recording actions such as formatting a range. You can then run the macro to apply the same set of actions in the future to a different range. You can run a previously recorded macro by selecting it from the Tools —> Macros menu.

Run the script from an Apps Script web application

Google Apps Script can also be used to create simple web applications! You can use this feature to create custom dashboards or even internal applications. You can run scripts from these web applications by using the google.script.run API.

In the video below, I've created a simple web application that has a single button that you can click to reset the spreadsheet. The button click runs a function that resets the spreadsheet.

Conclusion

In this tutorial, I walked you through several ways to run scripts in Google Sheets.

• Run one-off scripts from the Apps Script code editor: Use this method to test scripts as you write them or to run one-off scripts on an ad-hoc basis.

• Run the script whenever users select a menu item on a custom menu: This is a common and user friendly way to enable users of your spreadsheet to run custom scripts.

• Run scripts from a custom sidebar by using the google.script.run API: Use this method to add interactivity to your custom sidebar.

• Run scripts from a modal dialog by using the google.script.run API: Use this method to add custom logic or other interactive functionality to a modal dialog.

• Run a script whenever users click a drawing or an image in your spreadsheet: Use this method to create Call-To-Action (CTA) buttons in your spreadsheet.

• Run scripts periodically or whenever a specific event occurs using Triggers: Use triggers to automatically run scripts without any user intervention.

• Run a custom function from a formula in your spreadsheet: Create your own functions using Apps Script that users can run from a formula in the spreadsheet.

• Create and run macros in your spreadsheet: Create a script by recording a macro and run it from the Tools —> Macros menu.

• Run scripts from an Apps Script web application by using the google.script.run API: Use this method to add interactive functionality to an Apps Script web application.