Google Sheets Script 101: Beginner’s Guide

The topic for this tutorial is the Google Apps Script (or better known as GAS) and how you can use it to enhance your Google Spreadsheet’s performance as beginner to GAS.

So, let’s get started the Google Apps Script Tutorial!

What is Google Apps Script?

Simply put, Google Apps Script is a platform for scripting where you can use coding language to create automations and your own functions in Google Sheets. In fact, not only will you be able to do this for Google Sheets, but for all the other apps available from Google as well; such as, Google Drive, Google Maps, Google Calendar, Google Docs, Google Slides and Google Forms.

The coding language system of Google Apps Script uses the Javascript and it is Cloud-based. What this means is, if you have created a code for Google Sheets using Google Apps Script, then you can have access to it from any location and any device. Any code that you save for a Google document will be saved on Google cloud servers.

Advantages of using the Google Apps Script

As you may have already guessed, using the Google Apps Script (GAS) in Google Sheets can have a handful of benefits.

A few of them are:

Automation of Tasks

A lot of tasks on Google Sheets require a few steps to accomplish. For instance, downloading data from a database, combining and cleaning it Google Sheets. And while these steps can be easy, repeating the same actions for number of times can be tedious and time-ineffective.

This is where GAS can help you out. All you have to do is, integrate a code for a specific task in Google Sheets. Once the code is read, you can just run the script code and GAS will perform the entire task for you; without you having to go over all of those steps.

Creating your Own Function

While there are a lot of functions available in Google Sheets, there still may be some tasks for which there are no in-built functions and, although you can do your work despite this inconvenience through formulas; these formulas are often times very lengthy and complicated.

It is at these times, that you can build your own customized function in Google Sheets and implement them like any other regular function, using the GAS.

Interaction with other Google Apps

As I have already explained earlier, the coding language of GAS is common for most of the Google Apps; this gives you the added benefit of commanding two or more Google Apps combined.

For instance, you can save a reminder in your Google Calendar, based on your data in Google Sheets.

Altering Existing Google Sheets Functions

Along with creating new functions, you can also alter the existing functions in Google Sheets that is more catered to your needs. This will enhance Google Sheet’s functionality for you and therefore, make your work a lot easier.

Introduction to the Google Sheets Script Editor

The script editor is the space where you write your codes to run them.

Finding the Apps Editor

You can find the Google Apps Script Editor by finding the “Extension” option from the top menu bar and, then from the drop-down menu.

Introduction to the Google Sheets Script Editor

Creating a New Project

Once you click on the Apps Editor option, a new window will open automatically.

Creating a New Project in Script App Editor

On the top-left corner of the new window, you can see a space that says “Untitled project”. You can give a name for your project however you like.

The Script File

The script file is where you write your code. You can choose to either have multiple script files for each script you create, or, have all of your scripts in on script file.

You can see on the project window that there is already a default script file called the Code.gs.  

Google Spreadsheets Script

After you write your code, you have the options to rename the script file for that code, make a copy of that code or delete that code. You can access these options by clicking on the three-dotted line beside “Code.gs”.

In your project file, you need to have saved at least one script file and, if you have only script file in your project file; then you cannot remove it.

The Code

See the big empty space on the right side of the project window? That’s where you write up your code.

The Toolbar in Script Editor

There is a toolbar at the top of the project window. Here is a list of its options and their purposes:

The Toolbars in Script Editor
  1. Undo and Redo – The undo and redo option lets you undo and redo any action you have implemented in your script.
  2. Indentation – This option is turned on by default and it enhances the readability of some parts of the script that you are writing.
  3. Save button – as you can obviously guess, the save option saves any script you have written, or, any changes that you have made in your script as well. (Remember that you need to save anything that you have written as your code).
  4. Current Project Trigger – This option saves all of your triggers. A trigger is any action which triggers your code to run.
  5. Run button – The run option runs your script. In case you have multiple scripts for multiple functions, you can select any line from the script that you want to run and click on this option.
  6. Debug – The debug option searches your script for any errors that it might have.
  7. Select Function – This option is necessary when you have multiple functions in one script. This is a drop-down option and lets you select the specific function that you want to run t debug.  

The Menu Bar in Script Editor

Just above the toolbar, you will find the menu bar. Here is a brief description of the options from the menu bar.

  1. File – from the File option, you can either create a new script file or new project. Adding a new project will open a new window, whereas, adding a new script file will just add a new file to the already existing project.
  2. Edit – You can access options such as, “Find and Replace”, “Word Completion”, Toggle comments and “Content Assist”. You can edit your script as per your requirements.
  3. View – You can use “View” to add logs, debug or, want to view in detail all the actions implemented by your script.
  4. Run – This run option is just as the same as the run option offered in the toolbar and, it can be used to run all kinds of functions and debug them.
  5. Publish – This is a very sophisticated and advanced option which lets you publish your scripts as web-apps.
  6. Help – This option has guidance on how you can use the Google Apps Script (GAS).

Google Sheets Script Live Uses Examples

Let us take a look at a couple of real-life examples of how Google Sheets Script (GAS) can help everyday users.

Automation using GAS: Steps

If you have to do the same action each time on a new, similar set of data; then you can automate that action by using GAS. For instance, suppose you have to create a graph for each month and that will be based on that month’s number of patients diagnosed by a number of doctors.

Automation using GAS
  • Open the Google Sheets Scripts Editor from “Extension”.
  • Enter the relevant code in the space given for writing your script. In this case, the code is
function SalesChart()

{ var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); var sheet = spreadsheet.getSheets()[0]; var SalesChart = sheet.newChart() .setChartType(Charts.ChartType.LINE) .addRange(sheet.getRange('A2:B10')) .setPosition(5, 5, 0, 0) .build(); sheet.insertChart(SalesChart); }
  • Select the “Run” option from the toolbar.
Google Sheets Script Live Uses Examples
  • Go back to your spreadsheet to check whether the script has been run.

How to Create Custom Formulas using Google Apps Script

You can also create your own formulas for your convenience; which are missing from the Google Sheet’s collection of formulas.

  1. Go to the Google Sheets Scripts Editor from “Extensions” as usual.
  2. Give your custom formula a name. I would suggest to use a name which describes the outcome you want to achieve by using this formula. The name you give will start after the equal symbol every time you use this formula.
  3. Give the relevant function in the scripts editor space and select the “Run” option.

And, that’s basically all there is to it; when it comes to the Google Spreadsheet Script for beginners. In this Google Sheets Script tutorial, I covered the basics of Google Apps Script.

I hope my introduction to the Google Apps Scripts (or the GAS) was informative and useful for you.

For more relevant topics from TeqTog, do check out:

Does Google Sheets have Macros

Math in Google Sheets: Add, Sum, Subtract, Multiply, Divide, Square and Square Root

How to Count Colored Cells in Google Sheets (Step-by-Step)

How to Use the Find and Replace Function in Google Sheets

Was this article helpful?
YesNo


Related More Articles

Leave a Comment