Search
Close this search box.

Google Sheets Script Editor: Easy Beginner’s Guide

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 Google Sheets script 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.

This Google Apps Script (GAS) coding language uses Javascript and is written in the back-end of these Google Sheets (there is a neat interface that allows you to write or copy/paste the code in the back-end).

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. We’ll cover a few use cases in our Google Script tutorial, and there are plenty more such as how it:

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.

Prefer a no-code option? Free extensions like Coefficient sync live data from your business systems into Google Sheets. Easily sync and blend data from CRMs, BI tools, databases, and payment platforms right inside your spreadsheet. Think systems like Salesforce, Hubspot, Google Analytics, Tableau, Looker, MySQL, Stripe, and Shopify.

Can You Create New Functions in Google Script Language?

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 in Google Sheets is the place where you can write scripts 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.

Where Is the Script Editor in Google Sheets?

In Google Sheets, you can find the Script Editor in the Extensions tab.

Google Apps Script Editor Location

Anatomy of the Google Sheet Script Editor

Once you click on the Script Editor option, it opens the Script Editor in a new window (as shown below).

Google Sheets Script Script Editor Opens in a New Window

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.

Google Sheets Script - Change the name of the Project

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.

Script File in Google Sheets

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.

Script File options

Note: You always need to have at least one script file in the project. If you only have one, it will not let you delete it.

On the right side of the script file is the code window where you can write the code.

Code Window in Google App Script

Script Editor Toolbar

Script Editor Toolbar in Google Sheets

The Script Editor Toolbar has the following options:

  1. Redo/Undo button: To redo/undo changes you have done in the script
  2. 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
  3. 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.
  4. 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.
  5. 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.
  6. 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.
  7. 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).

Google Sheets Script Examples

Let’s take a look at a couple of real-life examples of how Google Sheets Script can help everyday users.

Automatically Generate Charts With Google Spreadsheets Script

For this example, we can pretend that you’re working on similar sheets month to month that come with new data each time. With each new set of data, you want to create an easy-to-read chart without having to go into the charts menu every time. You can accomplish this with Google Sheets Script.

In our imaginary scenario, we’ll pretend you need to compare sales statistics for each employee every month. We’ll use the following data set:

Google Sheets Script Sample Data 1

We could use the following script to automatically create a graph with the data in each new sheet.

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); }

All you have to do is:

  1. Navigate to the Google Sheets Script Editor
    Google Apps Script Editor Location
  2. Enter in the code, if you’re using different cell references you can change them (highlighted in blue) or change the chart type (highlighted in green)
    navigating Google Apps Script Example 2
  3. Click Run (in the red box)
  4. Check back on your sheet to see if the script ran successfully

It looks like ours ran with no issues, so happy days!

Create a Custom Formula With Google Apps Script

Google Sheets comes loaded with plenty of formulas right off the bat. But, what if you work with a formula constantly that is missing from their formula library? You can use a spreadsheet script to save you from having to recreate your formula every time.

A simple example of this is converting Celsius to Fahrenheit. You can add this formula to your spreadsheet with the following steps.

  1. Navigate to the script editor as in the previous example
  2. Name the function, its best to name it the same thing you intend to type after the = sign in sheets
    Screenshot to show where to name your function is Google Apps Script Editor
  3. Copy this function into your script editor (or create your own function) and then click run
function CTOFH (input) {
return input * 1.8 + 32;
}

You should now be able to use the function in your spreadsheet

Note that the shortcut you created won’t come up in the suggested ones, so make sure its memorable for your own sake.

Google 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. We hope this Google Sheets Script tutorial was useful for you.

You May Also Like the following tutorials:

Most Popular Posts

Related Posts

Thanks for visiting! We’re happy to answer your spreadsheet questions. We specialize in formulas for Google Sheets, our own spreadsheet templates, and time-saving Excel tips.

Note that we’re supported by our audience. When you purchase through links on our site, we may earn commission at no extra cost to you.

Like what we do? Share this article!