automate google sheets_ an introduction to google apps script
Post on 28-Feb-2018
242 Views
Preview:
TRANSCRIPT
-
7/25/2019 Automate Google Sheets_ an Introduction to Google Apps Script
1/20
6/12/2016 Automate Google Sheets: An Introduction to Google Apps Script
https://zapier.com/blog/google-apps-script-tutorial/ 1
Blog Home
Automate Google Sheets: An
Introduction to Google Apps Script
Jeremey DuVall / October 13, 2015
Tw eet+68 Recommend thisShare 125
15Like
Google has built some of the most eective 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. Theres also a good chance,
though, youre missing out on one of Google's most powerful toolsGoogle
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 mold apps
https://zapier.com/https://developers.google.com/apps-script/https://twitter.com/intent/tweet?original_referer=https%3A%2F%2Fzapier.com%2Fblog%2Fgoogle-apps-script-tutorial%2F&ref_src=twsrc%5Etfw&text=Automate%20Google%20Sheets%3A%20An%20Introduction%20to%20Google%20Apps%20Script&tw_p=tweetbutton&url=https%3A%2F%2Fzapier.com%2Fblog%2Fgoogle-apps-script-tutorial%2F&via=zapierhttps://zapier.com/https://zapier.com/blog/ -
7/25/2019 Automate Google Sheets_ an Introduction to Google Apps Script
2/20
6/12/2016 Automate Google Sheets: An Introduction to Google Apps Script
https://zapier.com/blog/google-apps-script-tutorial/ 2
, ,
business. A script could, for example, automatically send email thank-yous
with Gmail and a Google spreadsheet, or automatically add new people from
a spreadsheet to Google Contacts. (Don't worry: Well cover both of those
examples in-depth.)
To start, lets 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.
If youre new to Apps Script, I would recommend checking out Google's documentationin
addition to this guide. Google does a terric job of laying the groundwork, so it's a great place
to start. Then come back here to build your own add-ons.
Getting Started with Google Apps Script
Add Header Rows to a Spreadsheet
Add Contacts From a Google Sheet
Automate Email Outreach
Getting Started with Google Apps Script
There are two main types of scripts you can use with Google Apps: standalone
and boundscripts.
https://developers.google.com/apps-script/ -
7/25/2019 Automate Google Sheets_ an Introduction to Google Apps Script
3/20
6/12/2016 Automate Google Sheets: An Introduction to Google Apps Script
https://zapier.com/blog/google-apps-script-tutorial/ 3
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 les when the name isnt
specied), for example, as a handy way to keep your Google Drive clutter-
free.
You'll see standalone scripts alongside your other Google Drive les, and each
le contains JavaScript for your particular function.
2. Bound scripts
Bound scripts extend the functionality of a certain lein one of your Google
apps. They perform specic actions directly to that particular le.
For example, you could create a script bound to a specic Google Sheet that
checks for and removes duplicate rows.
https://developers.google.com/apps-script/articles/removing_duplicates?hl=enhttps://developers.google.com/apps-script/guides/boundhttps://developers.google.com/apps-script/reference/drive/drive-app#searchFiles(String)https://developers.google.com/apps-script/guides/standalone -
7/25/2019 Automate Google Sheets_ an Introduction to Google Apps Script
4/20
6/12/2016 Automate Google Sheets: An Introduction to Google Apps Script
https://zapier.com/blog/google-apps-script-tutorial/ 4
Google lets you publish both standalone and bound scripts for others to use.
In fact, the "Add-ons" menuin a Google Docs document includes dozens of
scriptsto translate your document, format tables, add formulas and more
that have been converted into extensions and shared by others.
This article will focus on bound scriptsthe most common ones in the Google
Docs Add-ons menu. First, lets get our feet wet by building a simple bound
script that lls in header values in a spreadsheet automatically.
Building your First Script
To get started, visit sheets.google.comand create a new sheet. Under the
"Tools" menu in your new sheet, click the option for Script editor
That'll open a new tab with a blank script le: the Script Editor. It's an easy
https://sheets.google.com/https://docs.google.com/document/create?addon_store -
7/25/2019 Automate Google Sheets_ an Introduction to Google Apps Script
5/20
6/12/2016 Automate Google Sheets: An Introduction to Google Apps Script
https://zapier.com/blog/google-apps-script-tutorial/ 5
Apps.
In this case, since we opened a new sheet rst and thenopened the Script
Editor, were creating a bound script; when we run it, the script will only aect
the new le we opened. By contrast, standalone scripts are created by visiting
script.google.com directly.
When we open the Script Editor from within a Google document for the rst
time, it automatically creates a new Untitled project for us. Lets change that
to something a bit more descriptive, like Create Headers.
Now, its time to write a bit of code. On a basic level, Apps Scripts are a
combination of JavaScript functionsblocks of code designed to perform a
certain task. Often there's functions you can copy and use on your own with
minimal editing, which is what we'll use here.
Google has given us a starting block that looks like this:
function myFunction() {
}
-
7/25/2019 Automate Google Sheets_ an Introduction to Google Apps Script
6/20
6/12/2016 Automate Google Sheets: An Introduction to Google Apps Script
https://zapier.com/blog/google-apps-script-tutorial/ 6
This will be the framework for our rst function. Just like our project title, we
want our function name to describe what the code is actually doing. Lets
change the function name from myFunction to createHeaders , since this script
will be creating column headers for us.
function createHeaders() {
}
Now, we need to put some code inside those culty braces (the { } characters
you'll notice after the function name) that will make this function do what we
want. To get this code, lets head over to the documentation for Google
Sheets. This documentation tells you exactly how you can manipulate Google
Sheets with Apps Script.
If you scroll down, youll notice a method called setFrozenRows that we can
use. If were setting up column headers in the top row of the spreadsheet, it
would be helpful to freeze that row so its always available, no matter how far
we scroll down. Google provides the following example:
var ss = SpreadsheetApp.getActiveSpreadsheet()
var sheet = ss.getSheets()[0]
// Freezes the first row
sheet.setFrozenRows(1)
Lets copy that code and use it in our function:
https://developers.google.com/apps-script/reference/spreadsheet/sheet#setFrozenRows(Integer)https://developers.google.com/apps-script/reference/spreadsheet/sheet -
7/25/2019 Automate Google Sheets_ an Introduction to Google Apps Script
7/20
6/12/2016 Automate Google Sheets: An Introduction to Google Apps Script
https://zapier.com/blog/google-apps-script-tutorial/ 7
var ss = SpreadsheetApp.getActiveSpreadsheet()
var sheet = ss.getSheets()[0]
// Freezes the first row
sheet.setFrozenRows(1)
}
Click the disk icon in the toolbar or press the Savekeyboard shortcut
(Command+S on a Mac or Ctrl+S on Windows) to save the project. Now, its
time to run the function. In the toolbar, you should see a play icon just to the
left of a menu item with the function name we set ( createHeaders). For now,
we only have one function to run. However, if you had multiple functions in
your code project, you could individually select the function you want to run.
Click the play icon to run the function. Youll be prompted with some
authorization dialogue, but once you authorize the script to run, jump back to
your Google Sheet. The rst row should be frozen!
-
7/25/2019 Automate Google Sheets_ an Introduction to Google Apps Script
8/20
6/12/2016 Automate Google Sheets: An Introduction to Google Apps Script
https://zapier.com/blog/google-apps-script-tutorial/ 8
Now, we need to extend our function to actually set the values of the header
row. To do that, lets go back to the Google Sheets documentationto nd
some more code.
We need some way to set the values for a particular range of cells. On the
left-hand side of the Google Sheets documentation, under Classes, youll
notice we have a Range class.
Clicking on that reveals a selection of methods we can use on a specic range
of cells. If you scroll down a bit, you'll nd setValues(values) , which inserts a
set of values into a range; that seems to match what we need! Heres a
portion of the example provided:
varss=SpreadsheetApp.getActiveSpreadsheet()
varsheet=ss.getSheets()[0]
//Thesizeofthetwo-dimensionalarraymustmatchthesizeofthe
varvalues=[
[ "2.000", "1,000,000", "$2.99"]
]
varrange=sheet.getRange("B2:D2")
range.setValues(values)
https://developers.google.com/apps-script/reference/spreadsheet/rangehttps://developers.google.com/apps-script/reference/spreadsheet/ -
7/25/2019 Automate Google Sheets_ an Introduction to Google Apps Script
9/20
6/12/2016 Automate Google Sheets: An Introduction to Google Apps Script
https://zapier.com/blog/google-apps-script-tutorial/ 9
For a non-engineer, that's a lot of code! Here's the part we're most interested
in:
varvalues=[
[ "2.000", "1,000,000", "$2.99"]
]
varrange=sheet.getRange(B2:D2)
range.setValues(values)
This might look a bit foreign if youre not familiar with JavaScript, but in short,
the code creates two variables.
Values , set by var values = stores the values 2.000, 1,000,000, and $2.99.
Range , set by var range = stores the range of cells B2 to D2 based on the
sheet we're working with.
From there, we can call the setValues method on the range variable we set
and give it the values variable. This tells our function to assign our Values
(2.000, 1,000,000, and $2.99) to the cells dened in Range (B2 through D2).
Instead of adding random numbers to our spreadsheet, lets say we wanted
to create a contact list. We might want the following headers:
First Name
Last Name
Email
Phone Number
-
7/25/2019 Automate Google Sheets_ an Introduction to Google Apps Script
10/20
6/12/2016 Automate Google Sheets: An Introduction to Google Apps Script
https://zapier.com/blog/google-apps-script-tutorial/ 10
ompany
Notes (Where did we meet this person? Is there anything special we need
to remember?)
These would be our values. Since there are six dierent values, our range
needs to be six cells wide (like A1:F1 ). We also want to use the rst row of the
spreadsheet.
After updating example code and changing the range and values, add it to
your createHeaders() function. You should end up with something like this
(the // denotes a comment, so you can make inline notes about what yourcode is supposed to do):
function createHeaders() {
var ss = SpreadsheetApp.getActiveSpreadsheet()
var sheet = ss.getSheets()[0]
// Freezes the first row
sheet.setFrozenRows(1)
// Set the values we want for headers
var values =[
["First Name", "Last Name", "Email", "Phone Number", "Company", "
]
// Set the range of cells
var range = sheet.getRange("A1:F1")
// Call the setValues method on range and pass in our values
range.setValues(values)
}
If we run the function again and ip back over to our spreadsheet, we should
-
7/25/2019 Automate Google Sheets_ an Introduction to Google Apps Script
11/20
6/12/2016 Automate Google Sheets: An Introduction to Google Apps Script
https://zapier.com/blog/google-apps-script-tutorial/ 11
Exploring the Power of Google Apps
Typing in the header values for a Google Sheet isnt thatmuch work, so that
script isn't the most valuable unless you setup a lotof new spreadsheets. But,
Google Apps Script can do so much more. Here are two additional examples
to play around with.
Example 1: Automatically Add Contacts From a Google Sheet
Lets say we had a contacts spreadsheet set up like the example above, with
columns for rst name, last name, and more, as an easy way to keep track of
everyone we met at networking events and conferences. We can actually useApps Script to automatically turn these rows into Google Contacts so we can
connect with them again later.
Heres the basic spreadsheet setup:
-
7/25/2019 Automate Google Sheets_ an Introduction to Google Apps Script
12/20
6/12/2016 Automate Google Sheets: An Introduction to Google Apps Script
https://zapier.com/blog/google-apps-script-tutorial/ 12
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
-
7/25/2019 Automate Google Sheets_ an Introduction to Google Apps Script
13/20
6/12/2016 Automate Google Sheets: An Introduction to Google Apps Script
https://zapier.com/blog/google-apps-script-tutorial/ 13
}
}
}
While the code above looks much more complex, were actually doing many
of the same things we did in the original example.
First, we dene a range of cells using dataRange . Then, we look at each one of
those cells and grab the associated value for each property (rst name, last
name, etc).
Now, we can use some methods provided by the Google Contacts
documentationto create a contact and give it the right values. In this
example, we also have a check in place to make sure a contact isnt added
twice; for that, we use alreadyAdded . When you run this code, you should get
some contacts added automatically from your spreadsheet!
Add Contacts from Google Sheets to a CRM using Zapier
Instead
https://developers.google.com/apps-script/reference/contacts/ -
7/25/2019 Automate Google Sheets_ an Introduction to Google Apps Script
14/20
6/12/2016 Automate Google Sheets: An Introduction to Google Apps Script
https://zapier.com/blog/google-apps-script-tutorial/ 14
Google Apps Script helps you automate processes within a specic tool and
create connections between your Google apps. But if you want to avoid
JavaScript troubleshooting, or send data from Google Sheets to a non-Google
app, try Zapier. It can automatically pick up new information in a spreadsheet
and transfer it to any of 500 appsno code required.
For example, you can build the same automation that we covered above,
hooking Google Sheets up to Google Contacts, or connect it to your favorite
CRM appinstead.
Create Google Contacts from new Google
Sheets rowsUse this Zap
Add new Google Sheets rows as Highrise
contactsUse this Zap
Add leads to Salesforce from new rows onGoogle SheetsUse this Zap
Create or update HubSpot contacts from new
Google Sheets rowsUse this Zap
Add new Google Sheets rows to Infusionsoft
as contactsUse this Zap
See more Google Sheets integrationspowered by
Explore more ways to use Google Sheets with Zapier, or learn how to turn Google Sheets into
a customized CRM.
https://zapier.com/blog/spreadsheet-crm/https://zapier.com/zapbook/google-sheets/https://zapier.com/https://zapier.com/zapbook/google-sheets/https://zapier.com/app/min/2230/start?embedded=true&utm_source=widget&utm_medium=embed&utm_campaign=Widget&referrer=Nonehttps://zapier.com/app/min/4654/start?embedded=true&utm_source=widget&utm_medium=embed&utm_campaign=Widget&referrer=Nonehttps://zapier.com/app/min/930/start?embedded=true&utm_source=widget&utm_medium=embed&utm_campaign=Widget&referrer=Nonehttps://zapier.com/app/min/2834/start?embedded=true&utm_source=widget&utm_medium=embed&utm_campaign=Widget&referrer=Nonehttps://zapier.com/app/min/1951/start?embedded=true&utm_source=widget&utm_medium=embed&utm_campaign=Widget&referrer=Nonehttps://zapier.com/learn/ultimate-guide-to-crm-apps/best-crm-app/ -
7/25/2019 Automate Google Sheets_ an Introduction to Google Apps Script
15/20
6/12/2016 Automate Google Sheets: An Introduction to Google Apps Script
https://zapier.com/blog/google-apps-script-tutorial/ 15
Example 2: Automating Email Outreach
Lets say you put on a massive event that was a huge success. You want to
thank everyone individually, but with over 200 guests you dont 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, well want a spreadsheet with each contacts rst name and
email address like this:
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()
-
7/25/2019 Automate Google Sheets_ an Introduction to Google Apps Script
16/20
6/12/2016 Automate Google Sheets: An Introduction to Google Apps Script
https://zapier.com/blog/google-apps-script-tutorial/ 16
var emailSent ="Email Sent"
for (var i =0 i
-
7/25/2019 Automate Google Sheets_ an Introduction to Google Apps Script
17/20
6/12/2016 Automate Google Sheets: An Introduction to Google Apps Script
https://zapier.com/blog/google-apps-script-tutorial/ 17
In the example above, I changed the test emails in the spreadsheet to my
actual email, which is why all of the emails arrived in the same inbox. 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.
Use Zapier to Send Emails via Google Sheets
If you use a dierent email service provider, or you just don't want to build
new JavaScript blocks for each batch of follow-ups, try using these Zaps to
connect Google Sheets to the email app of your choice.
Send a Gmail email for new Google Sheet
spreadsheet rowsUse this Zap
Send Oce 365 emails from new rows in
Google SheetsUse this Zap
Send Mandrill emails when new rows are
added to Google SheetsUse this Zap
https://zapier.com/app/min/4760/start?embedded=true&utm_source=widget&utm_medium=embed&utm_campaign=Widget&referrer=Nonehttps://zapier.com/app/min/2893/start?embedded=true&utm_source=widget&utm_medium=embed&utm_campaign=Widget&referrer=Nonehttps://zapier.com/app/min/4982/start?embedded=true&utm_source=widget&utm_medium=embed&utm_campaign=Widget&referrer=Nonehttps://zapier.com/app/min/133/start?embedded=true&utm_source=widget&utm_medium=embed&utm_campaign=Widget&referrer=None -
7/25/2019 Automate Google Sheets_ an Introduction to Google Apps Script
18/20
6/12/2016 Automate Google Sheets: An Introduction to Google Apps Script
https://zapier.com/blog/google-apps-script-tutorial/ 18
emailsUse this Zap
Send SendGrid emails from new Google Sheets
rowsUse this Zap
See more Google Sheets integrationspowered by
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 favorite examples:
Jame Todd Rubincreated a writing tracker scriptyou can copy from
GitHub.
Google has an example script that generates a monthly report of your
Gmail activity(daily usage, emails received, etc).
Gmail Snoozeallows you to move emails out of your inbox for a specied
period of time thanks to an Apps. Then, theyll reappear in your inbox so
you can deal with them when appropriate.
Amit Agarwal made a website monitor with Google Docsso you'll
immediately receive alerts if your site goes oine.
There's hundreds of other things you can do with Google Apps Scriptsyour
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, youll start to realize just how much
is possibleoften without writing any extra code.
http://www.labnol.org/internet/website-uptime-monitor/21060/http://googleappsdeveloper.blogspot.in/2011/07/gmail-snooze-with-apps-script.htmlhttps://developers.google.com/apps-script/articles/gmail-statshttps://github.com/jamietr1/google-docs-writing-tracker/blob/master/README.mdhttp://www.jamierubin.net/2013/07/11/my-google-writing-tracker-scripts-now-available-on-github/https://zapier.com/https://zapier.com/zapbook/google-sheets/https://zapier.com/app/min/4783/start?embedded=true&utm_source=widget&utm_medium=embed&utm_campaign=Widget&referrer=Nonehttps://zapier.com/app/min/4760/start?embedded=true&utm_source=widget&utm_medium=embed&utm_campaign=Widget&referrer=None -
7/25/2019 Automate Google Sheets_ an Introduction to Google Apps Script
19/20
6/12/2016 Automate Google Sheets: An Introduction to Google Apps Script
https://zapier.com/blog/google-apps-script-tutorial/ 19
Or, if you don't want to mess with JavaScript, let Zapier do the heavy lifting for
you. It has an entire list of Google Sheets automationsthat pass data
between your apps. Zapier transforms the examples we covered above into a
few simple clicks, making each integration painless.
Already familiar with Google Apps Scripts? What are your favorite tips and tricks?
What scripts have you used the most?
About the Author
Jeremey DuVall is a freelance writer focused on health, science and
productivity and a Happiness Engineer at Automattic. When he's not
behind the keyboard, he's usually getting lost in the mountains or in a
good book.
Load Comments...
https://zapier.com/zapbook/google-sheets/ -
7/25/2019 Automate Google Sheets_ an Introduction to Google Apps Script
20/20
6/12/2016 Automate Google Sheets: An Introduction to Google Apps Script
Take the Work out of Workow
Learn More
https://zapier.com/multi-step-zaps/?utm_source=blog&utm_medium=footer&utm_campaign=Workflows%20Blue%20Blog%20Footer
top related