In the first part of the course, we learn what Apps Script is, how we get started writing Apps Script, and how to work with data in a Google Sheet.
Work through the page in order if you're learning, or use the links below to jump to specific bits:
Welcome to the Essential Apps Script course! If you're working through the full course, we'll now go through how we recommend you work through the course content to get the most out of it.
We've created this guide as a course you can work through at your own pace. The format is as follows:
We expect it will take at least a couple of hours to work through each page and do the livecoding and project work. You don't need to do the entire page at once - in fact, it might be useful to leave a break before doing each project so you have time to digest what you've learnt on the page. If you want to, you can try and do each page of the course each week, to recreate a regular course structure, or you might prefer a different time frame.
If you're new to coding, we recommend you look through our introduction to coding page before starting this course. You may also want to look through our Google Workspace guide to ensure you are up to speed with the workings of Google apps.
A guide to using the Google apps at the University of York, including Gmail, Calendar, Drive, and more.
The guide has been designed with members of the University of York in mind. Some features may differ for other people depending on which version of Google Workspace you are using.
Google Apps Script is a coding language that allows you to connect together Google apps and automate processes using these tools. Commands written in Apps Script can be run, which means the computer follows the instructions given in these commands, either manually or you can set them up to run with certain prompts, known as triggers.
Apps Script is based off the common coding language JavaScript, which is used to make the web interactive. This means that sometimes what you'll be learning are Apps Script features and commands, and sometimes you will need to use JavaScript ones, though when you are learning Apps Script, there isn't a particular need to know the difference between them. It does mean there are more support resources out there for some things you'll be doing in Apps Script, though!
Each Apps Script file, containing Apps Script code, can be referred to as a script. These files can be bound, which means they are attached to another Google file stored in Google Drive, e.g. a Google Sheets file, or unbound, which means that the file is stored directly on Drive and isn't attached to another file. In this course, we are only going to be working with bound scripts, usually connected to a spreadsheet. To access the Apps Script editor and see the script file attached to a Google file, from the Extensions menu choose Apps Script.
Build into Apps Script, there are Apps that allow you to work with specific Google features and products. For example, you use the SpreadsheetApp (note the lack of a space between words there, it'll be important later) to work with Google Sheets files, the DocumentApp to work with Google Docs, the DriveApp to work with Drive, etc. We'll meet some of these throughout the course (and you can explore which ones are available on Google's reference pages for Apps Script.
Using these Apps allows you to get files and/or open on Drive, which is how you can work with them in your code. Throughout the course we'll see lots of commands starting with get as you have to tell the code exactly what to work with.
As you work through this course, you will probably mostly want to follow our explanations to learn what we're doing, but there's a few useful resources that you can use as well if you'd like, especially when working through the projects or if you're wondering how a particular command works.
Google's Apps Script developer site is a great place to look for the full reference list of Apps Script commands and some basic tutorials and guides.
As we mentioned above, Apps Script is based on JavaScript. Some of the things we'll cover in this course are actually features of JavaScript, not Apps Script specifically. You can check out our page of JavaScript support and resources for more guidance (but bear in mind that some elements won't be relevant to Apps Script). w3schools is a particularly useful site for accessing both JavaScript tutorials and a reference list of commands.
If you've looked through our Introduction to coding guide or done any coding before, you might've heard of variables. before. Variables are a key feature of almost any coding language, allowing you to store values by giving them a name, a bit like how you can store files on a computer by giving them a name. You can also change the value stored in a variable, as the name suggests.
In Apps Script, as in JavaScript, you create variables using the keyword var. We'll have a go at this soon, but first, we'll go over some of the kinds of things you might store in variables.
Numerical values can be stored in variables, e.g. 5, 1.5, -23, etc. Apps Script (and JavaScript) doesn't care if the number is an integer or a decimal. Here are some examples of creating variables containing numbers in Apps Script:
var number = 5; var x = 0; var favouriteNumber = 569.75; A string in coding is a sequence of keyboard characters. These could be letters, numbers, punctuation or spaces (but any numbers will not be treated as numbers to do calculations with, just text - like phone numbers, ID numbers etc). To create a variable, you must surround it in single or double quotation marks ' or ". It doesn't matter which you use, but your string must start and end with the same type.
Here are some examples of creating variables containing strings in Apps Script:
var firstName = 'George'; var personID = "546A"; var address = "University of York, York, YO10 5DD"; var question = 'How will you use Apps Script?'; var answer = "I'll try and use Apps Script."; Arrays are a way of storing multiple values in a single variable. These items could be numbers or text, or even other arrays, and you can mix data types within an array (so it could have strings and numbers in, for example). In Apps Script, an array will often contain the contents of a spreadsheet (we'll learn how to do that later), but you can also create them by enclosing the values in square brackets [ ] and separating each individual value with a comma.
Here are some examples of creating arrays from scratch (we'll look at storing the contents of a spreadsheet in one later in this section):
var shoppingList = ["Banana", "Cornflakes", "Rice", "Biscuits", "Toilet paper"]; var favouriteNumbers = [99, 5, 13, 1001], var data = ['Mike', 'Chang', 3, 104, '12 Magic Road']; var lotsOfData = [['Mike', 'Chang', 3, 104, '12 Magic Road'], ['Aleesha', 'Roberts', 2, 56, '77 Digital Avenue'], ['Mickey', 'Mouse', 3, 87, '2 Theme Park Road']]; var firstName = "George"; var theBand = ["John", "Paul", firstName, "Ringo"]; To access individual values within an array, you use index numbers that refer to the position of the item in the array. Confusingly, these index numbers start from 0, so to access the first item in an array you'd use arrayName[0], the second item would be arrayName[1], and so on. We'll look at this in more depth below.
You can also store different types of data and content in variables, and we'll see a few others on this course. A major type of item in Apps Script that you can store in a variable is an actual Google file, or part of that file. For example, you can store an entire spreadsheet, document, or Drive folder in a variable if you use the right command to access it. You can also store things like a range of cells, the body of a document, and many other things using Apps Script commands. We'll learn some of these commands throughout this course.
There's also something called an object, which is a feature of JavaScript that is similar to an array, but which allows you store items in name:value pairs, so they have a a name, or category, as well as a value (e.g. name:'Mickey'). You may need to create these from scratch, but we will see them come up when we work with Google Forms as form responses are stored in Apps Script as an object.
