Google Spreadsheets Scripts: An Easy Tutorial to Level Up Your Spreadsheets

With the introduction of Google Sheet Macros, automating a spreadsheet has been a lot easier. But what if there is a particular task you need to do that isn’t available as a macro function? Worry no more because this Google App Script tutorial will make you a master of Google spreadsheets script functions.

What is Google Spreadsheets Script (or Google Apps Script)?

Google Apps Script is a flexible scripting language based on JavaScript that has built-in access to various Google Workspace applications such as Google Docs, Google Sheets, Gmail, and Google Forms.

What’s great with Google Apps Script is that you don’t have to install anything. The Script Editor, which runs on Google’s servers, will always be available on any browser as long as you have a google account.

Some of the features of Google Apps Script include:

• Creating custom functions and macros
• Creating custom menus, dialogs, and sidebars
• Building add-ons for Google Services
• Publishing web apps in Google Sites
• Writing a script to interact with other Google products
• Automating Google Workspace applications such as:
  • AdSense
  • Analytics
  • Drive
  • Gmail
  • Google Apps Script Map Array
  • Calendar

Google Apps Script vs. Javascript

You may have heard of Javascript before as a complex programming language. But, what is the difference between it and the native Google Script Writer?

Google Apps Script is basically a JavaScript cloud scripting language. The two function mostly the same, but Google Apps Scripts is much easier to learn than the latter since it does not use some of the newer syntaxes.

The only difference is that Google Apps Script is used essentially for interacting with Google products and third-party services, while JavaScript works for any cloud language such as HTML and CSS.

Google App Script Tutorial

Let’s do some basic coding using Google Apps Script to show you some of its simple elements and features.

We will write a code in Google Apps Script Editor to make a simple “Hello World”. To do this, follow the instructions below.

1. Open a new spreadsheet using Google Sheets.

2. Click on Tools > Script Editor.

This will open up a separate tab containing the Script Editor of your spreadsheet, which looks like the image below.

You can rename your project, which has a default name is “Untitled project”. To do this, click on the Untitled project,type the name you want, then click Rename.

The default name of the function upon opening the Script Editor is “myFunction”. To change this, just delete the default title and replace it with any name you want.

It is important to remember that coding in Google Apps Scripts follows the camel case format. Whenever you write multiple words, you don’t need to include spaces or punctuations. Instead, separate the words by capitalizing the next word in the phrase, like this one: camelCase.

3. Write the following code in Line 2 of the Script Editor:

var message = “Hello World”;

A variable (var) is storage that contains some value or information. In the code above, our variable is “message” and is equivalent to “Hello World”. Assigning variables is necessary when coding because it’s used as a Google Apps Script reference in each Google app.

4. Press Enter after Line 2, then type the following code in Line 3:

SpreadsheetApp.getUi().alert(message);

Let’s break down the code above.

• var message :  This is the code for storing a value into a variable; “var” followed by the name you want for your variable. Here, our variable is named “message”.
• = : The equal sign indicates that the code we write next to it will be the value of our variable.
• SpreadsheetApp : This is called an object. Every object has its own properties or attributes and methods or procedures.
• A dot is written after every object. This will give you access to the properties and methods of the object.
• getUi() : This is called a method. A method is a procedure or function of an object.
• alert() : This is also a method. It allows the browser to display a pop-up message written inside the parentheses. In this case, our variable “message” contains the words “Hello World”.

Important note: Don’t forget to write a semicolon after every line. Semicolons separate the statements in your code.

As you code, you will see that the descriptions of the methods you’ve written appear in the editor interface. See the example below.

The above image tells us the definition of the getUi() method. So if you’re relatively new to programming, you should not be overwhelmed by lots of code as the Google Script Writer guides you through these definitions.

5. After writing the codes, click the floppy disk icon to save your project.

6. Click Run to run your code.

There will be times when Google will ask for your authorization. If this happens, just click Review Permissions and allow your project to access your data.

After running the code, the Execution log will appear on your window. This window shows the exact time when you started running the code and the action you made.

7. Go back to your spreadsheet. You will see a box with “Hello World”, which is the message we’ve coded, and an “OK” button.

8. Press OK to close the message.

If you go back to the Script Editor, you’ll see that the Execution log added the action you made, saying “Execution completed”.

That’s it! Now, are you ready for some more complicated codes?

You can also use Google Apps Script Reference to create a custom formatting to a spreadsheet and apply it to other spreadsheets. Check out the steps below to learn how to do this.

Automation Using Google Apps Script

What if every day, you have lots of spreadsheet reports that need to be formatted? Google Apps Script has a feature that allows you to automate formatting tasks so that you won’t need to perform repetitive steps.

Let’s use the sales data below as an example.

For the custom format, we will write a code in the Google Apps Script to:

• Get access to the active spreadsheet
• Store values in variables
• Get ranges of cells in the active spreadsheet and edit cell values
• Identify the header and table in the spreadsheet
• Change the format of the header, table, and texts

Accessing the Active Spreadsheet

One feature of Google Sheets or any spreadsheet is creating multiple sheets in a single spreadsheet. Upon opening the spreadsheet, the default is to have one sheet named “Sheet1”.

 To add another sheet, just simply click the + button.

You will add a new sheet to your spreadsheet, and its default name will be “Sheet2”. If you add another sheet, “Sheet3” will be generated, and so on.

You can also rename your spreadsheet by right-clicking the current sheet and selecting Rename.

Now, if you’re given a spreadsheet with multiple sheets, it is important to create a code that indicates you will apply the task to the current or “active” spreadsheet. To do this:

1. Open the Script Editor of your spreadsheet by clicking Tools > Script Editor.

Notice that I renamed the project into “Google Apps Script Tutorial” and the function name into “customFormat”. You can also rename yours by following the steps we did before. Just remember to use camel case for the function name.

2. Type the following code:

var sheet = SpreadsheetApp.getActiveSpreadsheet();

In doing this, we stored our active spreadsheet into a variable “sheet” so that when we run the code, we will apply the functions to our active spreadsheet, which is Sheet1.

GetRange Google Script and Setting Value

The getRange Google Script simply identifies a particular cell/s in the spreadsheet. You can select one cell only or multiple cells. Setting a value to the range you selected allows you to change the data in the spreadsheet. To do this:

1. Press Enter after the last line of your code, then type the following in the Script Editor:

sheet.getRange

We can get the range by:

• getRange(a1Notation) – Returns the range as specified in A1 notation or R1C1 notation.
• getRangeByName – Returns a named range or null if no range with the given name is found. If multiple spreadsheet sheets use the same range name, specify the sheet name without additional quotation marks.
• getRangeList – Returns the RangeList collection representing the ranges in the same sheet specified by a non-empty list of A1 notations or R1C1 notations.

There are other getRange methods, as you will see in the box that appears as you write the code. But for now, let’s focus on the three getRange methods above.

Let’s see first how the getRange(a1Notation) works.

For example, we need to modify the value in cell C2, which shows “Smith”.

Let’s change it to “Morgan”. Continue writing the code by typing the following:

sheet.getRange("C2").setValue("Morgan");

• Click Save, then click Run to see what changed in the spreadsheet.

• Go back to your spreadsheet and look at cell C2. You will see that “Smith”, has changed into “Morgan”.

You can also set another value which can be numbers or symbols. Just don’t forget to add the quotation marks (“ “).

For getRangeList, we need to identify the cells. To illustrate this, let’s make all the data in the “Rep” column to be “Morgan”.

• Identify the cells you are going to change by highlighting the data in your spreadsheet.

You can easily see the cells you selected by looking at the upper left corner of the spreadsheet (C2:C11).

Now that you know the cells you are going to change, write the following code:

sheet.getRangeList( [ 'C2:C11' ] ).setValue( "Morgan" );

• Click Save and then Run.

• Open your spreadsheet.

All the data under “Rep” has now changed to “Morgan”.

You can also modify other cells by writing the additional cell notation in the code. For example:

sheet.getRangeList(['C2:C11', ‘D2:D11’]).setValue("Morgan");

When you run this code, all the data in cells C2:C11 and D2:D11 will change to “Morgan”.

GetRange can do a number of valuable things, such as automation of email sending.

Modifying the Header and Table Through Google Apps Script

Now that you’ve learned how to use getRange, let’s apply it to a usable sheet.

For example, you want to design your spreadsheet. For the header, you want to:

• Add a highlight to the texts
• Change the font color
• Change the font style
• Align the text to the center.

For the contents in the table: you want to:

• Adjust the font size
• Center the texts
• Add borders

1. The first thing you need to do is store the header and table as variables. Type the following code in the Script Editor:

var header = sheet.getRangeList(['A1:G1']);


  var table = sheet.getDataRange();

2. Next is to write the adjustments you want to make, as shown in the code below.

Breaking down the code, we will have the following:

• header.setFontStyle(“italic”); – This sets the font style to italic.
• header.setFontColor(“white”); – This sets the font color to white. You can either write the word of the color or its hex code.
• header.setBackground(“blue”); – This highlights the headers blue.
• header.setHorizontalAlignment(“center”); – This aligns the headers to the center.
• table.setFontSize(16); – This sets the font size of texts in the table to 16.
• table.setHorizontalAlignment(“center”); – This aligns the texts in the table to the center.
• table.setBorder(true,true,true,true,false,true); – This selects which part of the table will have a border. It has the syntax:

setBorder(top: boolean, left: boolean, bottom: boolean, right: boolean, vertical: boolean, horizontal: boolean)

Boolean values are “true” or “false”. If you want to add a border to a specific part of the table, type “true”. Otherwise, write “false”.

Don’t worry so much about what to write in the code. As usual, Google Apps Script guides as we code by showing description boxes in the editor. The image below shows the syntax of the setBorder method.

It also has an indicator (as shown by the bold letters in the box).

3. Click Save, then Run.

4. Go back to your spreadsheet and see the changes you’ve made.

You will see that the actions we’ve coded in the Script Editor applied to our active spreadsheet.

Applying the Script Project to Other Spreadsheet Data

What if you want to apply the custom formatting you made into another spreadsheet?

You can simply copy and paste the code you wrote and just make the necessary adjustments in the code. See the example below.

Here, we have another spreadsheet with a different set of data.

You want to make the format of this spreadsheet the same as the one we’ve made before. Here’s what you need to do:

1. Open the Script Editor of the previous spreadsheet.

2. Copy the whole script project up to the closing bracket by pressing Ctrl + C.

3. Go to the new spreadsheet and proceed to the Script Editor by clicking Tools > Script Editor.

Sheets will direct you to the Script Editor, which has no functions written yet. You may opt to change the project name.

4. Delete the contents of the Script Editor and paste (Ctrl + V) the previous code, which contains the custom formatting.

5. Click Save, then Run.

6. Go back to your spreadsheet.

You have now applied the custom function to another spreadsheet. You can repeatedly do this to other spreadsheets to make formatting easier.

Moving Forward with Google Apps Script

You may be thinking that you need to memorize all the codes available in Google Apps Script. Well, that’s not the case.

Google has a Class Sheet that contains the available codes in Google Apps Scripts.

There are almost no limitations to what you can do in Google Apps Script. You can also try to create and modify other Google Workspace Products such as Google Docs, Google Slides, and Google Forms via Google Apps Script.

You can also learn more about Subscript and Superscript in our blog.

Conclusion

Using a Google Spreadsheets Script offers various features that you can use to maximize the productivity of your spreadsheet. Without writing complex codes, you can use it to automate, format, create custom functions, and much more!