Google Apps Script and the Google Suite

Updated January 2022

You sure can. Google has a fully supported script editor that you integrate with your Sheets, Docs, Forms, Slide, Gmail, Calendar and pretty much every aspect of the Google Suite. Its name: Google Apps Script.

Google Apps Script allows you to do all sorts of things like building short code to modify sheets and docs, create macros, develop add-ons mess around with Gmail and so much more.

Google Apps Scripts is based on Javascript. It can integrate with HTML5, CSS and Javascript well with its HTML service class.

Google has an extensive reference library to get you started.

Let’s get started with a basic example.

Dawh…Your first Google Script

In our first script, we are going to create a menu button that will execute an alert that will pop up on the screen and say something awesome. This is definitely not a Hello World example…definitely…maybe.

On the menu bar click >Extensions>Apps Script.

This will open a new page with Google’s in-build script editor.

Let’s take a quick look at the UI.

Up top, you can see that it says Untitled Project. All Googles Script files are wrapped in a project and are given an identifier. This enables you to share the script with other files or other people for that matter.

On the left, you can see a list of files. Now we only have one Code.gs. the gs stands for Google Script. You can add other files like *.html, *.css or *.js

To the right is the script editor. This is where you will be writing your program.

1. Title your project

Go ahead and title your project in the top box where it says: Untitled Project. I’m going to name mine the same as my spreadsheet: Not Hello World.

I’ll keep the Google Script file name the same and get stuck into the code.

2.  Creating a Custom Menu

We are going to customize our menu by adding a button to run our script on the right-hand side of the Help menu dropdown.

You can also create drop-down lists and child drop-down lists too. Check out Google’s Custom Menu page for instructions.

Before we start the code, let’s delete what is already there. Then add the following:

Let me explain what is going on here.

Line:

1. Creates the function onOpen() . This is just a self-explanatory name to help us remember what this function does. It won’t actually do anything unless we ask it to inside the function.
2. Sets an easy-to-use variable we called, ui (for User Interface), and calls the Google spreadsheetApp and asked it to get the UI for us to work with.
3. We then use that variable ui to create a menu that we have called ‘OMG!’
4. Adding to this menu we are creating we want to add an item (.addItem) that we have titled, ‘This is...‘ that we will click and it with run and alert in another function we will create soon called ‘alertMaker‘.
5. Finally, we want Google to add our menu to the User Interface on our spreadsheet.

When you are ready, hit <ctrl>+<s> to save or go to <File> and click <Save> in the Script Editor. Now click the browser tab with your spreadsheet in it and click refresh.

If all went well, you should have a shiny new menu item on the right-hand side of your help button.

It will look like this:

If you click the “This is…” link it will cause an error because we haven’t created the class yet. Let’s do this now…

3. Making something happen when you Click the Custom Menu Item

Let’s keep it simple, add the following to your code:

Here we have created an alert. When you click the ‘This is…’ link.

In line:

8. We create the function alertMaker() that we referenced back in line 4.
9. Next, we ask Google to call the spreadsheet again, specifically requesting the User Interface. We then ask it to bring up an alert with the word ‘AWESOME!!!’

Go ahead and save the script again and head back to your spreadsheet and navigate to your ‘This is…’ link in your ‘OMG!’ menu (No need to refresh the page).

Give it a click.

That’s it. Your first Hello World script using a menu.

Did you enjoy the tutorial? Want to upskill and get a solid step-by-step course to become a pro at Google Sheets? Check out my course, Google Sheets: Learn the Essentials with Three Detailed Projects. Sign up today.