Google has built some of the most effective productivity tools on the planet. With Gmail, Hangouts, Chrome, Docs, Sheets, and more, odds are you use Google products for at least some of your work. There’s also a good chance, though, you’re missing out on one of Google’s most powerful tool: Google Apps Script

Apps Script lets you build powerful add-ons for Google Apps with snippets of JavaScript code. Take a few minutes to code a script, and you can mould apps like Gmail, Google Sheets, and Google Drive into custom tools for your business. A script could, for example, automatically send email ‘thank-you’ with Gmail and a Google spreadsheet, or automatically add new people from a spreadsheet to Google Contacts.

Let’s cover the basics of Apps Script and how you can start using it—even if you’re not a developer or have only limited JavaScript experience.

Getting Started with Google Apps Script

There are two main types of scripts you can use with Google Apps: standalone and bound scripts.

1) Standalone Scripts

Standalone scripts are not attached to any particular app in your Google account. Instead, think of them as macros that perform system-wide functions.
A standalone script could search your Google Drive for all items with the name ‘untitled’ (the default name Google gives to files when the name isn’t specified), for example, as a handy way to keep your Google Drive clutter-free.

You’ll see standalone scripts alongside your other Google Drive files, and each file contains JavaScript for your particular function.

2) Bound Scripts

Bound scripts extend the functionality of a certain file in one of your Google apps. They perform specific actions directly to that particular file.
For example, you could create a script bound to a specific Google Sheet that checks for and removes duplicate rows.

Google lets you publish both standalone and bound scripts for others to use. In fact, the “Add-ons” menu in a Google Docs document includes dozens of scripts—to translate your document, format tables, add formulas and more—that have been converted into extensions and shared by others.

This chapter’s tutorials will focus on bound scripts—the most common ones in the Google Docs Add-ons menu. First, let’s get our feet wet by building a simple bound script that fills in header values in a spreadsheet automatically.

Exploring the Power of Google Apps

Typing in the header values for a Google Sheet isn’t that much work, so that script isn’t the most valuable unless you setup a lot of new spreadsheets. But, Google Apps Script can do so much more. Here are two additional examples to play around with and give a clear insight to the actual potential of this thing. Trust me, this is more than enough to have you check them out.

Example 1: Automatically Add Contacts From a Google Sheet

Let’s say we had a contacts spreadsheet set up like the example above, with columns for first name, last name, and more, as an easy way to keep track of everyone we met at networking events and conferences. We can actually use Apps Script to automatically turn these rows into Google Contacts so we can connect with them again later.

Now, we could use a function like this to add the contacts to Google Contacts:

function createContact() {
var alreadyAdded = "Already added";
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2; // First row of data to process
var numRows = 2; // Number of rows to process

// Fetch the range of cells A2:G3
var dataRange = sheet.getRange(startRow, 1, numRows, 8)

// Fetch values for each row in the Range.
var data = dataRange.getValues();
for (var i = 0; i < data.length; ++i) {
var row = data[i];
var firstName = row[0]
var lastName = row[1]
var emailAddress = row[2]
var phone = row[3]
var company = row[4]
var notes = row[5]
var addedAlready = row[6];

if (addedAlready != alreadyAdded) {
// Create contact in Google Contacts
var contact = ContactsApp.createContact(firstName, lastName, emailAddress);

// Add values to new contact
contact.addCompany(company, “”);
contact.addPhone(ContactsApp.Field.WORK_PHONE, phone);
sheet.getRange(startRow + i, 7).setValue(alreadyAdded);

Now, we can use some methods provided by the Google Contacts documentation to create a contact and give it the right values.
When you run this code, you should get some contacts added automatically from your spreadsheet!

Example 2: Automating Email Outreach

Let’s say you put on a massive event that was a huge success. You want to thank everyone individually, but with over 200 guests you don’t have the time to email each of them. With Google Apps Script, Google Sheets, and Gmail, though, you can automatically send out emails with custom messages for each person!

To set this up, we’ll want a spreadsheet with each contact’s first name and email address.

Then, we can use a function like this to make the emails:

function sendEmails() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2; // First row of data to process
var numRows = 4; // Number of rows to process

// Fetch the range of cells A2:D5
var dataRange = sheet.getRange(startRow, 1, numRows, 4)

// Fetch values for each row in the Range.
var data = dataRange.getValues();
var emailSent = “Email Sent”;
for (var i = 0; i < data.length; i++) {
var row = data[i];
var firstName = row[0];
var emailAddress = row[1];
var isEmailSent = row[3];
if (isEmailSent != emailSent) {
var message = “Hi ” + firstName + “! Great seeing you last night. Looking forward to connecting soon!”;
var subject = “Great seeing you last night!”;
MailApp.sendEmail(emailAddress, subject, message);
sheet.getRange(startRow + i, 3).setValue(message);
sheet.getRange(startRow + i, 4).setValue(emailSent);

When you run this script on your own spreadsheet, though, it’ll send emails to each of the contacts you’ve added to the spreadsheet.

What else can you do with Google Apps Script?

Your imagination is the only limit on what you can achieve with Google Apps Script. Here are some of my favourite examples:

  • Jame Todd Rubin created a writing tracker script you can copy from GitHub.
  • Google has an example script that generates a monthly report of your Gmail activity (daily usage, emails received, etc).
  • Gmail Snooze allows you to move emails out of your inbox for a specified period of time thanks to an Apps. Then, they’ll reappear in your inbox so you can deal with them when appropriate.
  • Amit Agarwal made a website monitor with Google Docs so you’ll immediately receive alerts if your site goes offline.

There’s hundreds of other things you can do with Google Apps Scripts—your imagination’s the limit. The best way to get started is to review the documentation and start tinkering away. As you get more familiar with the methods and attributes available to you, you’ll start to realise just how much is possible—often without writing any extra code.

Google App Script is merely an extension to Javascript. However, the possibilities to achieve with this magical script are out of bounds. Just play around with the script by going through the documentation and you can see tremendous prospects popping up to expand and scale and get your business’ trivial but necessary jobs done in no time.

Go App Scripting!

Categories: Business


Leave a Reply