Quick Links

If you use Google Apps, then chances are you're not using them to their full extent. With Google Apps Script, you can add custom menus and dialogs, write custom functions and macros, and build add-ons to extend Google Docs, Sheets, and Slides.

What Is Google Apps Script?

Google Apps Script is a cloud-based development platform for creating custom, light-weight web applications. You can build scalable applications directly inside your browser that integrate effortlessly with Google products.

Apps Script uses the JavaScript language and brings together the familiarity of web development and Google products in one place, making it a perfect tool to customize apps for your business, organization, or just to automate mundane tasks.

You can make two types of scrips with Google Apps Script:

  • Standalone: These scripts aren't bound to any service---like Google Docs, Sheets, or Slides. They can perform system-wide functions, sort of like macros. They're not ideal for sharing with a broader audience because you need to copy and paste the code to use them. Examples include searching your Drive for files with specific names or seeing who has access to your shared files and folders in Drive.
  • Bound: These are linked to a Google Docs, Sheets, Forms, or Slides file. Bound scripts extend a file's functionality and perform actions only in that specific file. Examples include adding custom menus, dialogs boxes, and sidebars to a service or a script that emails you notifications any time a particular cell in a Sheet changes.

If you don't know much JavaScript, or maybe you've never heard of it before, don't let that scare you off from developing a script of your own. It's super easy to get started using Apps Script, as it provides a wealth of documentation and examples for you to test out on your own. Below are a couple of simple examples to help you gain an understanding of how they work.

How to Create a Standalone Script

Now that you know what they are let's go ahead and create your first standalone script. We'll be using a code sample from Google to help us get the ball rolling, and we'll provide explanations to the lines of code if you're unfamiliar with GoogleScript or JavaScript.

Head on over to Google Apps Script. In the top left corner, click the hamburger icon, then click "New Script."

A new untitled project opens with an empty function inside, but because we are using sample code from Google, you can go ahead and delete all the text in the file.

An empty function for your bound script

Note: You need to be signed in to your Google account for this script to work.

After you've deleted the code that's preloaded in the file, paste in the following code:

//Initialize your function

function createADocument() {

// Create a new Google Doc named 'Hello, world!'

var doc = DocumentApp.create('Hello, world!');

// Access the body of the document, then add a paragraph.

doc.getBody().appendParagraph('This document was created by Google Apps Script.');

}

Before you can run the code, you have to save the script. Click "File" and then click "Save."

click File, then click on Save to save your script

Rename the project to something that helps you remember what the script does, then hit "OK."

Rename your project to something that tells you what the script does, then click OK

To run your code, click the play icon located in the toolbar.

Click the Run icon

You will have to grant the script some permissions to access your Google account via a popup window after you click "Run" the first time. Click "Review Permissions" to see what it needs to access.

Before the script can run, you have to review the permissions it requires. Click Review Permissions

Because this isn't a Google verified app, you will get another warning. It basically says that, unless you know the developer (us) only proceed if you trust them. Click "Advanced," then click "Go to CreateNewDoc" (or whatever you named this script).

A warning from Google appears stating the app you're running isn't verified by them. Click advanced, then click on Go to CreateNewDoc

Review the permissions the script requires, then click "Allow."

Review the permissions, then click Allow

Great! Now, head over to your Drive and if everything worked out, the "Hello, World!" file should be there. Double-click it to open it.

Navigate to your Drive and double-click on the newly created file

When you open the file, you'll see the line of text from the code adds to your document.

Inside the file is the line of text you added via the script

Now, if you want to get an email notification when the document is created, you can add a few more lines of code to send one to your Google account automatically. Add the following lines of code after doc.getBody().appendParagraph('This document was created by Google Apps Script.'); but before the last curly brace } :

// Get the URL of the document.

var url = doc.getUrl();

// Get the email address of the active user - that's you.

var email = Session.getActiveUser().getEmail();

// Get the name of the document to use as an email subject line.

var subject = doc.getName();

// Append a new string to the "url" variable to use as an email body.

var body = 'Link to your doc: ' + url;

// Send yourself an email with a link to the document.

GmailApp.sendEmail(email, subject, body);

Click the "Run" icon.

Click the Run icon

Because you added a couple of extra lines that require additional permissions, you have to go through the same process as before. Click "Review Permissions."

Before the script can run, you have to review the permissions it requires. Click Review Permissions

Click "Advanced," then click "Go to CreateNewDoc."

A warning from Google appears stating the app you're running isn't verified by them. Click advanced, then click on Go to CreateNewDoc

Note: As Google is warning you about launching unverified apps, you will receive a security alert email notifying you as well. Google does this just in case you weren't the one granting access to an unverified application.

Review the new set of permissions the script requires, then click "Allow."

Review the new permission and click Allow

When the document gets created, you receive an email with a link to the file in your Google Drive.

The email notification that's automatically sent from the script contains a link to the new document

Clicking the link brings you directly to the file, which is inside your Google Drive.

Inside the file is the line of text you added via the script

How to Create a Bound Script

For this next example, let's create a bound script for Google Sheets that parses an existing sheet for duplicate entries in a row and then deletes them.

If you remember from earlier, bound scripts work like an add-on to specific files, so to create one, let's open up an existing Google Sheet spreadsheet that contains at least one duplicate data point.

A Google Sheet with duplicate rows

Click "Tools" then click "Script Editor."

Click Tools, then click on Script Editor

Google Apps Script opens in a new tab with an empty script. This time, however, the script is bound to the Sheet from which it opens.

An empty function for your bound script

Just like before, delete the empty function and paste in the following code:

//Removes duplicate rows from the current sheet.

function removeDuplicates() {

//Get current active Spreadsheet

var sheet = SpreadsheetApp.getActiveSheet();

//Get all values from the spreadsheet's rows

var data = sheet.getDataRange().getValues();

//Create an array for non-duplicates

var newData = [];

//Iterate through a row's cells

for (var i in data) {

var row = data[i];

var duplicate = false;

for (var j in newData) {

if (row.join() == newData[j].join()) {

duplicate = true;

}

}

//If not a duplicate, put in newData array

if (!duplicate) {

newData.push(row);

}

}

//Delete the old Sheet and insert the newData array

sheet.clearContents();

sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);

}

Note: For the script to remove a duplicate, all cells in the row must match.

Save and rename your script, then hit the "Run" icon.

Click the Run icon

Again, as you encountered in the last script you created, you'll have to review the permissions your script requires, and grant it access your spreadsheet. Click "Review Permissions" to see what access this script wants.

Click Review Permissions to view the requested permissions

Accept the prompts and click "Allow" to authorize the script.

Review the permissions, then click Allow

After it finishes running, go back to your Sheet and, just like magic, all duplicate entries vanish from your file!

The duplicate data points have been removed!

Unfortunately, if your data is inside of a table---like the example above---this script will not resize the table to fit the number of entries in it.


Although these are two pretty straightforward examples of how to use Apps Script, the options are almost limitless, and it all depends on what you can dream up with these resources. But, in the meantime, head on over to the GSuite Devs Github page or Digital Inspiration and check out the stack of sample scripts you can deploy within your own services to get a better idea of what Apps Script is truly capable of doing.