Can I modify Google Sheets with code? [updated January 2022]

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.

Finding the Google Workspace script editor for the first time from Google Sheets
Click to Expand!

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

Accessing the Google Workspace script editor for the first time.jpg
Click to Expand!

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 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.

Renaming project title to Not Hello World for the first time.jpg
Click to Expand!

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.


  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:

Finding OMG in First Google App Script project
Click to Expand!

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:

  1. We create the function alertMaker() that we referenced back in line 4.
  2. 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.

Google Script Editor Alert
Click to Expand!

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.

Google Sheets Shortcuts Mousepad
Google Sheets Shortcuts Mousepad
Want a fast track to boost your Spreadsheet efficiency? Grab one of these handy Google Sheets Shortcuts mousepads that I created from my store.
Google Sheets Shortcuts Mousepad Gel
Google Sheets Shortcuts Mousepad Ge

One thought on “Can I modify Google Sheets with code? [updated January 2022]”

Leave a Reply

%d bloggers like this: