Google Apps Scripts is a powerful tool that will help you automate tasks across your Google apps and services, like Sheets, Gmail, or Google Drive.

This post will walk you through Google Apps Script step by step, and by the end, you will be an Apps Script pro!

We will explore the code editor, writing code, permissions, triggers, saving scripts, and much more.

If you are finding it hard to understand what that all means right now, don’t be afraid! These topics will all be explained in detail.

Believe me! After going through this article, you will be good to go with Apps Scripts.

Make sure you get a copy of the example workbook used in this post to follow along.

What are Apps Scripts?

The first question that will pop up as soon as you read the title of this article is, what is this Apps Scripts thing?

Apps Scripts is a development platform specifically designed to customize and automate the Google Workspace platform and all apps that are part of it. It will save you time by allowing you to automate tasks across Gmail, Calendar, Meet, Drive, Docs, and Sheets.

This utility tool has customization and automation for them all.

The most noteworthy thing with Apps Scripts is the ability to help automate just about any tasks in google sheets!

What Language is Google Sheets Apps Scripts?

Google Apps Scripts is a coding language based on JavaScript that allows you to automate your tasks across the Google Workspace platform.

What Can I Do with App Script in Google Sheets?

What can’t you do with Apps Scripts, I will ask!

The tool provides various services that you can extend towards Google Sheets and help you save time by allowing automation.

The comprehensive spreadsheet services offered by the tool create a bridge between Google Sheets and Scripts so that this automation happens.

The following are a few examples of tasks that you could automate with Google Apps Scripts.

• Creating and modifying workbooks or sheets.
• Adding or editing values, formulas, or formatting to cells.
• Importing or exporting data from other sheets, Google services, or third-party services.
• Creating custom menus or buttons.
• Sharing and managing controls for Google Sheets.
• Creating custom functions similar to the built-in functions such as SUM, AVERAGE, etc.

Note: To work with App Scripts, you need to be familiar with the basics of JavaScript. Also, you should be familiar with Google Sheets.

How to Open Google Apps Scripts

The Google Apps Scripts is a browser-based script editor. If you are trying to open it, there are two ways, and we will cover each of them.

Opening Google App Script through the official website

The first way of opening the tool is through its official website. Follow the steps below.

Open up any browser, such as Google Chrome, and put script.google.com inside the address box.

After you hit the Enter button, you will go to the home page of Google Apps Script, as shown in the screenshot above.

On the home page, you can see the Start Scripting button in two places. One is on the upper side blue ribbon beside the home tab, and the other is on the main content page below the heading. Click on any of them to navigate towards the Apps Script.

The Apps Script interface looks like the one shown above.

• On the left-hand side, you have a Menu bar with three horizontal lines. Inside it, there are many options associated with each project you create.
  • Starred Projects – Here, you can see the projects you have marked as starred (anything important, you keep it starred). All starred projects have monitoring access which allows you to monitor the statistics, fail rates, graphs, etc., for that project.
  • My Projects – It is a folder that comprises all the projects you own.
  • All Projects – A Folder with projects you do not own but have a view or edit access for.
  • Shared with me – These are the projects that are shared with you. You are not the owner of these.
  • Trash – Comprises projects that are not part of your workspace and removed by you.
  • My Executions – List of all the projects that you have executed until now with their status. It also includes the projects with the status Failed.
  • My Triggers – Lists down all the triggers you have set on multiple projects. They start the script as soon as a specific event happens.
  • Getting Started – A menu that returns the overview and documentation about the tool.
  • Settings – All the settings associated with the tool.
  • Service Status – Navigates you to the support page, where any resolution to a similar issue you face can be found (if raised before). Here you can also ask for help from the developer’s community.
• In the middle, you have the Getting Started menu for Apps Script. You can learn more about it by following the link. They have some exciting Codelabs set up for you where you can sharpen your coding skills.
• Below, you can see the ready to go links of Codelabs, Stack Overflow, Case Studies, Recent blog posts, and References related to the tool.

You have to click on the New Project button to create a new script.

The new app script, where you can write the code, looks like the one shown above.

Right now, ignore what appears inside the script. The idea behind this step is to educate you about opening a script. We will discuss the other things in the latter part of this post.

Opening Google Apps Script Through Google Sheets

You can also navigate towards this tool through Google Sheets. You first have to open a Google Sheet inside the browser.

To access the Apps Script, click on the Tools tab from the menu bar. Click on the Script editor option out of all listed tools.

Once you click on that Script editor option, a new app script will open up. This is just another way you can access the tool.

Note: If you are accessing the Apps Script for the first time, the system will ask you to sign in to your Google account.

How to Create an App Script

As we said earlier, they developed the google app script environment with JavaScript, and it is excellent to have a basic understanding of it.

You can check several courses that cover the basics of JavaScript.

However, if you don’t have exposure to this already, it is fine as well. You can learn the basics from custom app script functions created and shared by other users.

You can search for solutions that may have already been developed for your problems.

If not, that is fine as well. You can start creating an app script and a custom function of your own from scratch. After all, you learn to swim when you are in the water!

In this section, you will create your first app script. This app script should take your name as an input and should return a welcome dialogue box for users who mention their name as an input.

Open a Google Sheet and click on the Tools tab then click on the Script editor option.

The App Script interface will open up, as shown above.

The first thing you should do is rename your project.

1. Click on the Untitled project tab on the upper left side.
2. You will see the Rename Project window appearing on the front.
3. Inside the Project title* box, change the project name to FirstScript.
4. Click on the Rename button to rename this project.

Where to Write Your Code

To write the script, you have a JavaScript-based code editor, and it covers most of the browser. Inside the code editor, you can see the initial code by default, as shown below.

function myFunction() {
 
}

Everything you define under the app script works like a function.

Therefore, at the start of the code itself, you have the function clause. Then, there is the name of the function that is set to be myFunction by default.

Then there are opening and closing curly braces. They hold the code written for that specific function. Right now, it is blank, but you can write your code between those two curly braces.

We want to write a script that can take the user name as input and then welcomes that user with a welcome message through the browser message box.

function welcomeMsg() {
 
}

Start with renaming the function name. The name myFunction doesn’t give you a clear idea about what the function will do. We will rename it as welcomeMsg.

You are free to use any name of your own choice. See the screenshot and code above.

It might look irrelevant right now, but it is crucial to understand naming rules inside the tool. Since Google App Script has an interface developed with JavaScript, naming conventions will follow the JavaScript rules. The following are a few of them to be aware of.

• The function names should be in camel case. This means, they should start with the lowercase word, and any next word should start with the upper case.
• They can have letters, numbers, dollar signs, underscores within them.
• They should not contain any other special character such as asterisk, forward slash, pound, dot, slash etc.

These rules will also apply while defining variables.

var name =  Browser.inputBox("Enter Your Name:")

Now, inside the braces, you will write the code. First, use the inputBox method from the Browser class to take input from the user. The user should provide their name. The code for this is as shown above.

The var clause allows you to define a new variable called name. Then the inputBox method from the Browser class is used to set up an input box. It will ask the user to put their name. For convenience, we show a text to the user (Enter Your Name:) asking for their name.

Note: Everything that appears in blue is a clause, everything in purple is a class, and anything appearing after a dot in a class is known as the method.

Tip: Each code statement ends with a semicolon. But it is ok if you miss it. The code will still execute.

Browser.msgBox("Hello " + name + "!");

Now, you want to show the user a welcome message through the browser message box.

To return a message box, we have the msgBox method from the same Browser class you can use with the name variable defined above and some strings to generate a greeting message. The code is shown above.

Here the msgBox method from the Browser class is used to generate a welcome message. Inside the method, it combined the first word Hello with the value of the name variable and an exclamation mark with the help of the plus operator.

For example, If I mention my name as input, the browser message box should pop up with “Hello Lalit!.”

Now, if you see on the upper side ribbon of the interface, there are six options placed with various tasks.

• The first option is to undo and redo the code edits. This option allows you to go back or forward a step.
• The second option is the Save project. It is to save your work. Now and then, keep a habit of saving your work.
• The third option is to Run the selected function. It executes the code chosen.
• The fourth option is to Debug the selected function. It allows you to debug the code.
• The fifth option is a Select function to run drop-down. It comprises a list of functions defined under the project and allows you to choose between them to run current code.
• The sixth and final option is for the Execution log. It returns the information about the execution process. When it is started, when completed, any errors, etc.

After all this:

1. Hit the Save button to save this code.
2. Then hit the Run button to execute the script we wrote.

This is how to create an app script inside the Google workspace.

Providing Authorization to Run the Script

The Google Apps Scripts has a security protocol to authorize apps that you do not verify.

It asks you to review and allow permission to run the script and make changes inside the Google workspace. You have to go through this process as a mandatory flow when you run a script on your workspace for the first time.

As soon as you hit the Run button to execute the script, the Authorization required window will pop up, as shown above.

You can always hit the Cancel button if you are not so sure about the security permissions. However, to proceed with the execution, you must click on the Review permissions button.

A new browser window will open up, asking you to Sign in with Google. You need to click on the registered Gmail account to proceed further.

Note: If you are not comfortable allowing permissions through the currently showing google account, you can always choose another option. Use another account and input another account details, allowing this script to run.

A new window loads with the warning message as shown above.

1. The first thing that grabs your attention is the warning message. It roughly says that if you don’t trust where this project comes from, just don’t allow it to make changes.
2. In the case you are not aware of the project, you can always choose to go back. Just hit the BACK TO SAFETY button. The system will terminate the execution.
3. If you are sure and verified this script (as it is in our case), hit the small Advanced button on the left-hand side.

Once you click on the Advanced button, a drop-down opens up with again a warning and in the end with an option to Go to Untitled project (unsafe). We will click on this option and proceed further.

On the next screen, hit the Allow button to complete the process and execute the script.

You can see this entire process through this little GIF we created. See it above.

Running Your Script Manually

There are several ways of running your script in Google Sheets. You can create a dedicated button to run the script or set to run from a trigger.

However, the most popular way to run a script manually is through the Apps Script interface.

We will walk you through that process in this section. The above screenshot can show you this illustration.

Now, as soon as the authorization process is complete (from the previous task), you can see inside the script editor that the Execution log is active at the bottom side, and the script has run.

Navigate to the Google Sheet, and you can see the Enter Your Name input box appearing over the screen.

It asks the user to input their name so that the system can take the next action. I will enter my name Lalit inside the input box and then hit the OK button at the bottom.

Once I hit the OK button, the following line of code runs where the msgBox method returns a message Hello Lalit! 

Since I had input the name Lalit inside the input box, that name is stored in the variable and used inside the msgBox method.

In the demo above, you can see that the system starts the execution as soon as we hit the Run button. Then inside the Google Sheet, that input box pops up where the user mentions his name, clicks OK, and the welcome message Hello Lalit! appears.

Congratulations! You have just created a script that takes a user’s name as an input and then returns a welcome message to that user.

Different Ways to Trigger a Script

If you want to automate the script running process, you should know about the triggers available.

They allow the script to run when a specific event happens. For example, if the user opens the spreadsheet, or edits a cell, etc.

There are two types of triggers.

1. Simple Triggers
2. Installable Triggers

Simple Triggers in Apps Scripts

You can trigger a specific script or a piece of code through the app script itself. To do so, use the set of reserved functions such as onOpen(), onEdit(), onChange(). Each of these functions has a specific action associated with them. 

• The onOpen() function automatically runs the script when a user opens the document.
• the onEdit() function allows the script to run automatically as soon as the user edits a specific range of cells.
• If you use the onChange() function, the code will run automatically if a user changes the spreadsheet structure or content.

Using the onOpen() Trigger

You can use the onOpen() trigger inside the app script. This function will automatically run the script if the user opens a sheet, doc, form, or slides with edit permission.

People mostly use this function to create a custom menu inside the tool as soon as it opens.

function onOpen(){
  SpreadsheetApp.getUi()  
  .createMenu("Custom")   
  .addItem("Run Script", "Run") 
  .addToUi()
}

function Run(){
  
}

Suppose you want a custom Run button to appear as soon as anyone opens the Google Sheet. The script is as shown above.

• The first line of code defines the onOpen() reserved function. Anything you develop inside this function will be run when the spreadsheet opens.
• The SpreadsheetApp class is associated with the spreadsheets and allows you to access them. The getUi method will enable you to get the spreadsheet interface.
• Then, there is the createMenu method that allows you to create a new menu button or tab on the Google menu bar. This menu will be named Custom.
• The addItem method allows you to add a customized button inside the menu you created in the previous step. This will be named Run Script.
• The addToUi method allows you to add all this stuff to the Google menu.
• Finally, to make the Run Script button work, you need to connect it with a function that runs a script. The one we define in the last row will do the work for us.

As you can see in the screenshot above, the onOpen reserved function works as soon as we open the spreadsheet in which the code is written.

It creates a Custom ribbon menu button inside which there is the Run Script option. You can connect scripts to this button and automate the script run.

Using the onEdit() Trigger

The reserved function onEdit() triggers the script to run as soon as a user edits any specific range of cells (or any cell) inside the Google Sheet.

However, it is not only restricted to the sheets, but you can also use it with Docs, Forms, Slides, or Gmail, etc.

function onEdit(e){
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
      rng = activeSheet.getRange(e.range.getRow(), 1)
      rng.setFontColor('red')
      rng.setBackground('pink')
}

Suppose you wanted a script to automatically change the font color to red and the cell color to pink as soon as a user edits any row from the first column inside the Google Sheet. The script for this is shown above.

• The onEdit() function is created. Anything in it will be triggered as soon as the user edits the sheet.
• The SpreadsheetApp.getActiveSpreadsheet().getActiveSheet() method gives us the active sheet from the given google sheet. The active sheet means a sheet the user is currently trying to edit. The results are stored inside the activeSheet variable.
• The getRange() method is called upon the activeSheet variable. The (e.range.getRow(), 1) argument returns the current row user is trying to edit within the first column.
• Then we use the setFontColor method to change the font color to red.
• The setBackground method is used to change the cell color to pink.

You can see as soon as you try to edit anything inside the first column (any row), the onEdit() function triggers. It changes the font color as well as the cell color to red and pink, respectively.

Using the onChange() Trigger

The onChange() trigger is trickier than the previous ones.

You can’t just define it; you have to set it up as a separate trigger on the active spreadsheet as it is used after the onEdit() trigger.

This trigger will run automatically as soon as a user makes a change inside the sheet. It can any change such as renaming a sheet, editing cells, sharing the sheet, etc.

Like the previous triggers, you can also use this one with Docs, Slides, Gmail, etc.

Use the code below to see how the onChange() trigger works inside the google sheets.

//Creating the onChange Trigger

function triggerSetUp(){
  ScriptApp.newTrigger('onChange')
  .forSpreadsheet(SpreadsheetApp.getActive())
  .onChange()
  .create()
}

//The onChange Function

function onChange(e){
  var actSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('onChange')
      lastRow = actSheet.getLastRow()
      actSheet.getRange(lastRow, 2).setValue(new Date())
}

• Firstly, you need a function that sets up the onChange() function which we will be defining in the second part of the code.
• The ScriptApp.newTrigger() sets up a new trigger named onChange.
• The forSpreadsheet method takes SpreadsheetApp.getActive() method inside it as an argument to return the currently active spreadsheet.
• The built-in onChange() function sets this trigger up.
• The create() method creates it inside the active google sheet.

Move to the second part of the code now. Here, is where you develop the function that triggers as soon as the user changes something.

• You have a sheet named onChange inside the active spreadsheet. It’s accessed through the SpreadsheetApp.getActiveSpreadsheet.getSheetByName() method and stored inside the actSheet variable.
• The getLastRow() method is applied on the actSheet to get the last edited row in the sheet. The results are stored inside the lastRow variable.
• Then, the next line of code will return a log with date and time that is associated with when the change happened. It will be stored in the second column.

You can see it gets the log like time and date every time a cell changes in the second column.

It may look like something similar to onEdit, but it is different. If I change the name of the sheet, I will get the log. If I delete any entry here, my log will change. It is quite different than onEdit.

Installable Triggers

There is another type of trigger named installable triggers.

They are similar to simple triggers but are easier to set and more flexible than simple ones.

They have some additional options as well while setting up. You don’t always need to run the code to set these triggers up (You could enhance them programmatically).

To set up an installable trigger, click on the Trigger menu. It is the menu item with the clock icon.

Inside it, you will see the Add Trigger button at the bottom side of the screen. Click on it to add a trigger.

You will then see the Add Trigger for FirstScript window popping up, as shown above. Here, you can see several dropdowns to choose operations from.

• The Choose which function to run dropdown allows you to select any function from the available list to select for triggering.
• The Choose which deployment should run is an option for Head or any other deployment to run. Most of the time, it is Head.
• The Select event source specifies the source based on which the trigger should be set. It can be a time-driven trigger, spreadsheet event trigger, or calendar-specific trigger.
• There are four options under the Select event type. You can either set the trigger On open, edit, change, or form submission. These are nothing but types of triggers.
• You could also set the Failure notification settings. It will give you notifications on an hourly, daily, weekly, or monthly basis if the trigger fails. Immediate notifications are also available.

Select the appropriate options from each drop-down to set a trigger. Then hit the Save button. The installable trigger is set in your google sheet.

You could see the trigger is now active and set up. If you right-click on the trigger bar, there are several options to manage it.

This is how we can set the installable triggers inside the Google Sheets.

The benefit of having installable triggers is that you can set them up with the buttons and dropdowns. However, if you want, you can also control them programmatically. This feature makes them versatile and dynamic in use.

Apps Scripts and Recording Macros

What if I tell you that the most effortless way of learning the App Script is recording a macro? Will you believe me? I doubt you will!

But it is the easiest way available out there to learn. Believe me on this!

You can record small programs using the Record macro function, and then the system will convert the steps you follow into small pieces of programming codes inside the Script.

The benefit of it? You can use that piece of code every time the exact requirement comes. Let’s see how this works.

Assume a situation where you are trying to create an employee database with Employee ID and Employee Name. You want to apply some formatting to this database and record a macro to reuse in the future when a similar requirement arrives.

To record a macro inside of Google Sheets,

1. Open the Tools menu.
2. Click on Macro from the options.
3. There is Record macro radio button. click on it to start recording a macro.

You can see the record macro window appearing at the bottom of the sheet and have the Use absolute references option radio button by default. You need to select the Use relative references radio button.

The Absolute References are nothing but fixed cells. They do not change. On the other hand, Relative References change as the user moves from one cell to another.

Once you are done with this step, follow the steps below to record the macro.

1. Select the range A1:B1.
2. Make the text bold.
3. Change the Fill color to Green.
4. Change the text color to white
5. Adjust the column width.

Now, click on the Save button to save this recorded macro.

You will see the Save new macro window popping up. Follow the steps below.

1. Inside the Name section, rename this macro as Header Editing.
2. Assign a shortcut for this macro to run. We choose Ctrl + Alt + Shift + 1. Assigning a shortcut is optional.
3. Click on the Save button.

The recorded macro is now saved as Header Editing.

Now, again navigate towards the Tools menu and then click on the Script editor from the options.

And there it is! All the recorded steps are in the backend converted to the Apps Scripts language and you can see them above.

You can change the range of cells, background color, font color, etc. through this script.

This way is the easiest and most convenient for those who are not aware of the JavaScript language. By recording a macro and then opening it as a script, users can update the script without the effort to write it from scratch.

That’s really cool!

Run a Script Through a Button in Sheets

It is also possible to run your script through a dedicated button inside Google Sheets.

This again is a fantastic feature as you don’t want to run the repetitive tasks through the Apps Scripts editor every time.

Just take the example of the Header Editing script. You recorded a macro, you have the script with you, but it may not be convenient to run it every time from the editor.

Instead, you can create a dedicated button inside Google Sheets and every time you want to apply the Header Editing, just click on that button. That’s it!

To create a button to run the script, follow the steps below.

Navigate towards the Insert menu from the menu ribbon. Click on the Drawing option.

Inside the Drawing window, with the help of appropriate shape, text, fill color, create a button for Header Editing. I have one made, as shown above. Feel free to change the fill color or name of the button.

Click on the Save and Close button at the top. It will save the changes made and close the window.

The button you just draw is created and placed on your google sheet. You are free to move its position anywhere on the sheet.

Now, the part that makes it come alive. Click on the button, and you can see the three horizontal dots menu. Click on it to see the options associated with this button. You have to select the Assign script option to assign a script to this button.

Name the function you want to assign from App Script to this button. In this case, the function is HeaderEditing. Click on the OK button.

Your button for the Header Editing script is now ready to use, so test it out.

There are three headers for testing purposes. Header1, Header2, and Header3. They are placed in cells H1, I1, and J1, respectively.

Select the entire range from H1:J1 and click on the Header Editing button to run the script. It works perfectly, and the headers are edited as per the requirement.

This is how you can run the script through a button inside google sheets.

Conclusions

The article is dedicated to the Google App Scripts and works as a beginner’s guide for people looking at the tool.

Apps Scripts are a powerful tool to manage and automate tasks inside google sheets and other google workspace applications.

You can create the app script by writing a small usable block of codes called functions. These functions are reusable and can be shared and work with other sheets as well.

In fact, the easiest way to write a script is to record a macro and then edit it as a script.

There are many ways to run your scripts, manually using the Run button through the editor, you can set up triggers to run your script in the occurrence of a specific event, or you can create a button inside your sheets and connect it to your script.

Hopefully, this guide will get you started in building powerful automation inside Google Apps Scripts! Let me know in the comments what you end up creating with it.

Check Out These Essential Google Sheets Tutorials

• VLOOKUP and INDEX & MATCH Formulas
• The Definitive Guide to the QUERY Function
• The Definitive Guide to Data Validation
• Everything You Need to Know About Filtering Data
• Everything You Need to Know About Sorting Data
• Everything You Need to Know About Smart Fill