There are countless amazing things you can do with the built-in functions and features of Google Sheets. However, there are some things that are not possible by default or require a series of steps to accomplish.
With Google Sheets Scripts, you can automate many tasks and even create new script functions for Google Sheets that don’t exist yet.
In this article, I will cover the basics of Google Apps Script with some simple yet practical examples of using scripts in Google Sheets.
What is Google Apps Script (GAS)?
Google Apps Script is a programming language that allows you to create automations and functions for Google applications (which can include Google Sheets, Google Docs, Google Forms, Drive, Maps, Calendar, etc.).
In this tutorial (and on this site), I will focus on using Script for Google Sheets. However, most of what I cover here can also be used for other Google applications.
This Google Apps Script programming language uses Javascript and is written in the background of these Google Sheets (there is a functional interface that allows you to write or copy/paste code in the background).
Since Google Sheets (and other Google applications) are cloud-based (meaning they can be accessed from anywhere), your Google Apps Script is also cloud-based. This means that if you create code for a Google Sheets document and save it, you can access it from anywhere. It doesn’t reside on your computer/laptop, but on Google’s cloud servers.
Why is Google Apps Script Useful?
There are many good reasons to use Google Apps Scripts in Google Sheets. We will cover some use cases in our Google scripts tutorial, and there are many others, such as:
Allows you to automate tasks
Suppose you regularly download data from a tool or database and need to combine and clean it in Google Sheets.
Typically, cleaning and combining data involve a series of steps.
This may not be a major issue if you only do it a few times, but if you have to do it frequently, automating these steps can save you a lot of time.
And that’s what you can do with Google Apps Script.
You just need to invest time in preparing the code once, and every time you need to perform the same steps, you simply run the script code in Google Sheets and let GAS do all the heavy lifting for you.
Would you prefer a code-free option? Free extensions like Coefficient sync live data from your business systems into Google Sheets. Seamlessly sync and merge data from CRMs, BI tools, databases, and payment platforms directly into your spreadsheet. Think systems like Salesforce, Hubspot, Google Analytics, Tableau, Looker, MySQL, Stripe, and Shopify.
Can you create new functions in the Google Script language?
There are already hundreds of great functions in Google Sheets, and in most cases, these functions should suffice.
But in some cases, you may need an additional feature that cannot be achieved with the built-in function (or it can be, but the formula becomes huge and complicated).
In such cases, you can quickly write a GAS code to create a custom function. These functions can be used just like regular functions in the Google Sheets document and instantly make your work easier.
Can interact with other Google applications
Since the Google Apps Script programming language is used in many Google applications, you can use it to interact with other applications as well.
For example, if you have 10 Google Sheets documents in your Google Drive, you can use GAS to combine them and then delete all those Google Sheets documents.
This is possible because you can use GAS to work with multiple Google applications.
Another useful example of this could be using data in Google Sheets to quickly schedule reminders in your Google Calendar. Since these two applications use GAS, this is possible.
Extends Google Sheets functionality
In addition to task automation and function creation, you can also use GAS to enhance the features of Google Sheets.
While Google Sheets offers many features for everyone, you can also code something that better suits your needs. And since you can reuse the code again and again, it makes you much more productive and efficient.
Getting Started with Google Sheets Script Editor
The script editor in Google Sheets is where you can write scripts and run them.
There will be a separate script editor for different Google applications. For example, in the case of Google Forms, there will be a “script editor” where you can write and run code for Google Forms.
Where is the script editor in Google Sheets?
In Google Sheets, you can find the script editor under the “Extensions” tab.
Anatomy of the Google Sheets Script Editor
Once you click on the “Script Editor” option, it opens the script editor in a new window.
You can change the project name by clicking on the top left part of the screen that says “Untitled Project”. When you click on it, a dialog box opens where you can enter the project name. Changing the name takes a few seconds to apply.
Within a script project, you can have multiple script files. For example, if you have three different things you want to automate in Google Sheets and create three different scripts for them, you can have all three different scripts in the same project file.
In the left pane of the project, you have the default script file – “Code.gs”. This script file is where you can write the code. You can have multiple scripts in the same script file, and you can also have multiple script files.
If you click on the little downward-facing arrow on the right side of the script file name, it shows you the options to rename, delete, and make a copy of the script file.
On the right side of the script file is the code window where you can write the code.
Script Editor Toolbar
The script editor toolbar has the following options:
- Undo/Redo Button: To undo/redo the changes you’ve made to the script.
- Indentation Button: It’s a toggle button, and you can turn indentation on or off by clicking on it. When indentation is enabled, certain parts of your script are automatically indented to make it more readable. This may be the case when you’re using loops or “IF” statements. It automatically indents sets of code inside the loop for readability (if indentation is enabled). This option is enabled by default, and I recommend keeping it.
- Save Button: You can use this button to save the changes you’ve made to your script. You can also use the keyboard shortcut Ctrl + S. Note that unlike Google Sheets, you need to save your project to ensure that the changes are not lost.
- Current Project Triggers Button: When you click on this button, it opens the triggers dashboard that lists all the triggers you have. A trigger is anything that triggers the execution of code. For example, if you want code to run and enter the current date and time into cell A1 every time someone opens the Google Sheets, you will use a trigger for that.
- Run Button: Use this button to run the script. If you have multiple functions, select any line of the one you want to run, and then click the “Run” button.
- Debug Button: Debugging helps you find errors in your code and also provides you with useful information. When you click the “Debug” button, it also displays some additional options related to debugging in the toolbar.
- “Select a function”: This is a dropdown menu that lists all the functions in your script file. This is useful when you have many functions in the script and want to run a specific one. You can simply select the name from this menu and then click the “Run” button (or debug it if you want).
Google Sheets Script Editor Menu Options
In addition to the toolbar, there are many other options available in Google Apps Script in Google Sheets.
If the most commonly used options are already part of the toolbar, there are other options in the menu that you can use when you start working with GAS.
In this section of the article, I will cover each menu option and some of its options. You can explore the different options yourself to better understand them.
- FILE: In the File menu, you can add a new project or a new script file. A project would be a whole new project in a separate window where you can create multiple script files. When you add a new script file, it simply adds it to the same project (you will see it in the left pane, under your current script files). You can also rename and delete projects from here. Another useful option you can find in the File menu is the ability to manage project versions. When you save a project, a version of it is saved, and you can go back and review that version if you want.
- EDIT: The “Edit” option contains useful options that can help you when you’re writing or editing code. For example, there is an option to find and replace text in your code. There are also options like “Word Completion”, “Content Assist”, and “Toggle comments”.
- VIEW: This option contains helpful options when you want to get more information about the script when it was executed or when you want to add logs to help you debug in the future. For example, you can get the execution transcript, which details all the actions performed by your script.
- RUN: There are options to run different functions or debug them. Since these options are also available in the toolbar, they are less likely to be used from the menu.
- PUBLISH: This option contains more advanced features such as publishing your scripts as web apps.
- RESOURCES: This gives you access to advanced options such as libraries and advanced Google services. You can use these options to connect to other Google properties such as Google Forms or Docs.
- HELP: This option contains tutorials and resources that can help you when you’re getting started/working with Google Apps Scripts. One of the most useful options here is the link to the documentation page where you can find many guides and references to learn Google Apps Scripts.
In this article, I covered the basics of Google Apps Script and the general anatomy of the interface. We hope that this tutorial on Google Sheets scripts has been helpful to you.
You may also like the following tutorials:
- Macros in Google Sheets
- Recording a Macro in Google Sheets
- Filtering by Color in Google Sheets
- Inserting Timestamps in Google Sheets
- Counting Cells Based on Cell Color in Google Sheets
- How to Enable Dark Mode in Google Sheets
- How to Format Script in Google Docs (Easy Guide)
