While there are a lot of great things you can do with in-built functions and functionalities in Google Sheets. But there are a few things that are either not possible by default or require you to take a series of steps to achieve it.
With Google Sheets Scripts, you can automate a lot of stuff and even create new functions that you wish existed.
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 coding language and allows you to create automation and functions for Google Apps (which can include Google Sheets, Google Docs, Google Forms, Drive, Maps, Calendar, etc.)
In this tutorial (and on this site), I will be focussing on using Script for Google Sheets. However, most of what I cover here can also be used for other Google Apps.
Since Google Sheets (and other Google Apps) are cloud-based (i.e., can be accessed from anywhere), your Google Apps Script is also cloud-based. This means that if you create a code for a Google Sheets document and save it, you can access it from anywhere. It doesn’t reside on your laptop/system but on Google cloud servers.
What Makes Google Apps Script Useful?
There are many good reasons you may want to use Google Apps Scripts in Google Sheets:
Allows you to automate stuff
Suppose you regularly download data from any tool or database and have to combine and clean it in Google Sheets.
Usually, data cleaning and combining it involves a series of steps.
This may not be a big deal if you have to do it only a few times, but if you have to do it quite often, automating these steps can save you a lot of time.
And that’s what you can do with Google Apps Script.
All you need to do is invest some time in getting the code ready once, and whenever you have to do the same steps again, you simply run the script code in Google Sheets and let GAS do all the heavy lifting for you.
Can Create New Functions in Google Sheets
There are already hundreds of awesome functions in Google Sheets, and in most cases, these function should be enough.
But in some cases, you may want some additional functionality which can not be achieved with the inbuilt function (or it can be but the formula become 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 makes your work easier.
Can Talk To Other Google Apps
Since Google Apps Script is the common coding language behind a lot of Google Apps, you can use it to interact with other apps as well.
For example, if you have 10 Google Sheets documents in your Google Drive, you can use GAs to combine all these and then delete all these Google Sheets documents.
This is possible because you can use GAS to work with multiple Google Apps.
Another useful example of this could be to use data in Google Sheets to quickly schedule reminders in your Google Calendar. Since both of these apps use GAS, this is possible.
Extend the Functionality of Google Sheets
Apart from automating stuff and creating functions, you can also use the GAS to enhance the functionality of Google Sheets.
While Google Sheets provides a lot of functionality for everyone, you can also code something that’s more suited to your needs. And since you can reuse the code, again and again, it makes you a lot more productive and efficient.
Getting Started with Google Sheets Script Editor
The script editor is the place where you can write scripts in Google Sheets and then run it.
There would be a separate script editor for different Google Apps. For example, in the case of Google Forms, there would be a ‘Script Editor’ where you can write and execute code for Google forms.
Anatomy of the Google Sheets Script Editor
In Google Sheets, you can find the Script Editor in the Tools tab.
Once you click on the Script Editor option, it opens the Script Editor in a new window (as shown below).
You can change the name of the Project by clicking on the top-left part of the screen that says ‘Untitled project’. When you click on it, it will open a dialog box where you can enter the name of the project. It takes a few seconds to implement the name change.
Within a script project, you can have multiple script file. For example, if you have three different things that you want to automate in Google Sheets and you create three different scripts for it, you can have all the 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 code. You can have multiple scripts in the same script file and you can have multiple script files as well.
If you click on the small downward-facing arrow at the right of the Script file name, it will show you options to rename, delete, and create 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:
- Redo/Undo button: To redo/undo changes you have done in the script
- Indentation button: This is a toggle button and you can enable or disable indentation by clicking on it. When indentation is enabled, it automatically indents some parts of your script to make it more readable. This can be the case when you’re using loops or IF statements. It will automatically indent sets of codes within the loop to increase the readability (if indentation is enabled). This option is enabled by default and I recommend you keep it that way
- Save button: You can use this button to save any changes to your script. You can also use the keyboard shortcut Control + S. Note that unlike Google Sheets, you need to save your project to make sure the changes are not lost.
- Current Project Trigger’s button: When you click on this button, it will open the Triggers dashboard that lists all the triggers you have. A trigger is anything that triggers the running of code. For example, if you want a code to run and enter the current date and time in cell A1 whenever someone opens the Google Sheets, then you will use a trigger to do this.
- Run button: Use this to run the script. In case you have multiple functions, select any line in the one you want to run and then click on the Run button.
- Debug button: Debug helps you find errors in your code and also gives you some useful information. When you click on the Debug button, it also shows some additional debugging-related options in the toolbar.
- Select Function: This is a drop-down that would list all your functions in the 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 here and then click on the run button (or debug it if you want).
Script Editor Menu Options
Apart from the toolbar, there are many other options available in Google Apps Script in Google Sheets.
While the most used options are already a part of the toolbar, there are some more options in the menu that you may need when you start working with GAS.
In this section of this article, I will cover each menu option and some of the options it has. You can play around with the options on your own to get a better idea.
- FILE: From the File menu, you can add a new project or a script file. A project would be a completely new project in a separate window where you can create more script files. When you add a new script file, it simply adds it to the same project (you’ll be able to see in the left pane below 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 versions of the projects. When you save a project, a version of it is saved and you can go back and revisit that version if you want.
- EDIT: Edit has some useful options that can help when you are writing or editing the code. For example, there is an option to find and replace text in your code. There are also options such as Word Completion, Content Assist, and Toggle comments.
- VIEW: This has options that can be useful when you want to get more information about the script when it has executed or want to add logs to help in debugging in the future. For example, you can get the execution transcript, which details all the actions done by your script.
- RUN: There are options to run different functions or debug them. Since these options are also available on the toolbar, it’s less likely to be used from the menu
- PUBLISH: This has 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 with other Google properties such as Google Forms or Docs.
- HELP: This has tutorials and resources that can help you when you are 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 a lot of guides and references to learn Google Apps Scripts
In this article, I have covered the basics of Google Apps Script and the overall anatomy of the interface.
You May Also Like the following tutorials: