Can I modify Google Sheets with code?

Google Apps Script and the Google Suite

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. It’s 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 it’s 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 >Tools>Script editor…

script editor Google

This will open a new page with Googles in-build script editor.

script editor Google open

Lets 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 seen 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.Google Script Editor Name Project

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 customise our menu by adding a button to run our script on the right hand side of the Help menu drop down.

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 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 a 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:

Google Script Editor Menu

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 cilck the ‘This is…’ link.

In line:

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

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

 

 

2 thoughts on “Can I modify Google Sheets with code?”

Leave a Reply

Your email address will not be published. Required fields are marked *