Google Apps Script Tips for Non-Developers

Make Your Code Understandable

Use plenty of white space and comments.

// get data out of spreadsheet
function getData() {
    // code here...
}

Don’t worry about trying to make your code concise when you’re learning, better you understand it when you come back to look the next day or next week.

Log Everything!

Use the Logger.log() method liberally when you’re getting started. It prints out the values of whatever you “log”, for example the output of a function call. It’s super helpful for you to see what’s going on inside your script at different stages.

You can also add notes like this:

Logger.log("Hey, this function X just got called!");

If you see this in your logs, then you know that function X was called.

Understand These Four Fundamental Concepts

Variables

Variables are placeholders for storing data values. You create variables with the var notation and assign values with a single equals sign.

For example, the following expression sets the variable counter to have a value of 0. Anytime you use counter in your code, it will have the value 0, until you change it.

var counter = 0;

Functions

Functions are blocks of code designed to perform a specific task. A function is run (executed) when something calls it (invokes it).

Functions can be declared (created) with the word function followed by the function name, which is getData in the following example:

// get data out of spreadsheet
function getData() {
    // code here...
}

The brackets immediately after the function name are required, and are used to hold optional arguments, in a similar way to how functions are used in Google Sheets.

Arrays

Arrays hold multiple values in a single variable, using a square bracket notation. The order of the values is important. Items are accessed by calling on their position in the array. One other thing to note: the array index starts from 0, not 1!

The following expression creates a new array, called fruitsArray, with three elements in positions 0, 1 and 2.

var fruitsArray = [ "apple", "banana", "pear" ];

Objects

Objects can hold multiple values too, but think of them as properties belonging to the object. They are stored in key/value pairs. For example, here is an object, stored in a variable called book, which has two key/value property pairs:

var book = {
  "title": "Apps Script Book",
  "author": "Ben Collins"
}

The order of the pairs does not matter when you write out objects. The values are accessed by calling on the key names.

Obviously there’s a lot more to Apps Script than just these four concepts, but understanding Variables, Functions, Arrays and Objects, and how to work with them, will go a long way towards you creating functional Apps Script programs of your own.

Learn These Two Loops

Start with the basic for loop to understand how loops work. It lays bare the mechanics of the loop, showing the starting number, how many times to loop and whether you’re increasing the loop counter or decreasing it.

for (var i = 0; i < 10; i++) {
    Logger.log(i);
}

Next up, take some time to learn the more modern looping method: the forEach loop.

This hides the loop mechanics, which makes for cleaner, more readable code. It’s really easy to work with once you get the hang of it.

array.forEach(function(item) {
    Logger.log(item);
});

Basically it grabs all the data from your array and loops over each item in turn. You can do something, by applying a function, to each item during each loop of the array.

Google Sheets Double Array Notation

This is really, really key to using Apps Script to work with Google Sheets. Once you understand the double array notation for Google Sheets data, you open up a huge range of opportunities for extending your Google Sheets work. Spend enough time with this topic, and it’ll become as familiar as the regular A1 notation in Sheets.

Google Apps Script data
On the left, Google Sheets data. On the right, Google Apps Script data.

Understand how Google Sheets <--> Apps Script Transfer Data

Understand how data is passed back and forth between Google Sheets and Apps Script, and how to optimize for that.

Calculations in Google Sheets are done in your browser and are fast. Similarly, calculations done in Apps Script on the Google servers are lightning fast. But passing data back and forth from Sheet to Apps Script or vice versa, oh boy! That’s slow in comparison. (We’re still talking seconds or minutes here, but that’s slow in computing terms.)

To illustrate, here’s a script that retrieves values one cell at a time, performs a calculation in Apps Script and sends the single cell answer back to the Google Sheet. It performs this for one hundred numbers (shown in real time):

Slow data transfer Sheets to Scripts

Contrast that to the equivalent calculation where the script grabs all one hundred numbers in one, performs the calculations and pastes them back en masse, in one go:

Paste data from Apps Scrip to Sheets

Looks almost instantaneous to the human eye. So much faster!

Here’s another image to summarize this optimization process:

Sheets Apps Script data best practice
Slide taken from the Automation With Apps Script course

Try to minimize the number of calls you make between your Apps Script and your Google Sheets.

Use the Documentation

The Apps Script documentation is your friend. It might feel overwhelming at first, but persevere and spend time there. Most likely you’ll find something of value to help you solve your current issue.

It’s full of both code examples and a comprehensive reference, so you can look up the precise type of the return value of function X.

Ask for help

Don’t be afraid to ask for help when you get stuck. I always advocate spending time trying to solve your problems yourself, but past a certain point it’s diminishing returns.

Know when to stop banging your head against the wall and try searching for or asking for help in one of these two places:

Google Apps Script Community Group

Stack Overflow Apps Script Tag

Ready to learn more?

Beginner Tutorials

Guide to simple automation in Google Sheets, using Macros – macros are little Apps Script programs that the computer records for you. They’re a gentle way to start with Apps Script.

Beginner Guide to Coding with Google Apps Script

Online courses

I’ve created two high quality, online course teaching Apps Script from the ground up. They’re the best way to learn Apps Script in the shortest amount of time.

The first course, Apps Script Blastoff, is designed to take you from 0 to 20 and get you started on your Apps Script journey.

The follow-up course, Automation With Apps Script, is designed to take you from 10 to 100 (or wherever you want to go!) and focusses on how to automate workflows in G Suite and connect to external APIs. This course will be available for enrollment again in the fall of 2019.

Leave a Reply

Your email address will not be published. Required fields are marked *