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.
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.
The Script Editor is where you write your Apps Script code. It can be accessed from within Google apps to create script that is bound to that file by going to Extensions > Apps Script. You also use the Script Editor if you create unbound scripts directly in Google Drive, though we will not be doing that in this course.
By default, when you open the Script Editor you are in the Editor section, which allows you to write and run Apps Script code. When you first open the Script Editor for a new project, it is advisable to name the project something sensible, and rename the default code.js file to something useful as well, such as the name of the function you will be creating. Code tends to be split into functions in the Script Editor, which are named sections of code that can be run individually as needed.
The Script Editor helps you out with various features:
When you run code from the Script Editor, the Execution Log will appear at the bottom of the screen to show you if your code was successful in running. It will also display any content that you have logged using the Logger.log() command, which allows you to show text on the screen to check things in your code, like the value of a variable. You can also access the Executions page from the left hand menu of the Script Editor, to see all of the execution logs for runs of the project you are in.
Throughout this course, we will use the Logger to 'log' values in our code that we want to check at that point in the script. Checking the logs is a key way of troubleshooting your code if it doesn't seem to run as you expect, as it tells you what was stored in a variable at a certain time in the script's runtime.
We're going to have a go at our first livecoding exercise. This means that we recommend you follow along with the instructions to write out the code yourself, either by watching the video, following the walkthrough document, or both.
In this first livecoding exercise, we are going to try out using the Script Editor by creating a script that will find and display on screen the value of a cell in a spreadsheet.
Comments in coding are a way of adding text that isn't for the computer to follow, but for humans to read. This might be explaining what a section of code does or adding useful notes and information for future you and anyone else who might be looking at the code.
In Apps Script (and JavaScript) you can create a comment using two forward slashes // or a multi-line comment by starting with /* and ending with */
// this is a comment and the computer will not read it
/* this is a longer comment and the computer
will not read it either */
Let's take a look at how to work with Google Sheets spreadsheets in Apps Script. Firstly, you need to know how to get the spreadsheet itself, the individual sheets (tabs) within the spreadsheet, and the cell(s) you want to work with. If you want to work with the data contained in the cells, you will then also need to get the values from those cells. Let's take a look at how to these things.
When working with each Google app, there's an associated Apps Script app. For working with spreadsheets, there's the SpreadsheetApp. Google has their own reference guide to all the commands in each of these apps, which can be very useful for knowing what you can do with each app (but may seem daunting at first!).
You need to get or open a file using Apps Script before you can work with it. When working with a script that is bound (connected) to a particular spreadsheet (as we will be doing throughout this course), you can get the active spreadsheet to be able to work with the spreadsheet that the script is bound to.
To access a different spreadsheet or other file, you need to know its ID or URL to open it - we will look at this in the next section of the course when working with documents.
To get the active spreadsheet, you will need to store it in a variable, usually one with the name ss (though this is just for convention and your code will work even if you don't call the spreadsheet variable "ss"). You then use the SpreadsheetApp to access all of the commands to work with spreadsheets, and use the command getActiveSpreadsheet(), like below:
var ss = SpreadsheetApp.getActiveSpreadsheet();
Spreadsheets are made up of individual sheets, which appear as tabs along the bottom of the screen. To access anything associated with a sheet, including the cells and the data, you need to get the specific sheet.
You can get a sheet in multiple ways. You can get the active sheet, which is the current one that the user is working on when the script runs, or you can get a sheet by name, which allows you to specify the sheet using the name given on the tab. The name of the sheet must be a string, so it must be enclosed in single or double quote marks.
Typically, getting the sheet by name is most useful and reliable, as it means you are always getting a specific sheet. However, there may be instances when you have multiple sheets and want the script to specifically get the one the user is on.
var oneSheet = ss.getActiveSheet();
var anotherSheet = ss.getSheetByName('Another Sheet');
You can get the spreadsheet and sheet in a single line by chaining together commands, but you can only do this if you don't want to access any other elements of the spreadsheet, or get multiple sheets from the spreadsheet.
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("mySheet");
Just like the active sheet, the active cell is the cell the user is currently working in or clicked on when the script runs (the one highlighted when you look at the Sheets interface). This can be useful when you need the user to select something, or to check which cell they are clicked on. To get the active cell, you first need to have the sheet stored in a variable. Then you can use the getActiveCell command to get the active cell to store in another variable.
Once you have the active cell, you can add a command to get the row or column of that cell (getRow and getColumn, respectively), if you need to work with the entire row or column it is in. This will give you a numerical value corresponding to the row or column in question. See the examples below for how this works.
var activeCell = sheet.getActiveCell();
var activeCellRow = activeCell.getRow();
var activeCellColumn = activeCell.getColumn();
// You can also get the row or column in a single line if you don't need the active cell otherwise
var activeRow = sheet.getActiveCell().getRow();
In Apps Script the value of a range of cells is almost always an array with 2-dimensions (so an array with subarrays inside it) containing the values in the cells, even if you are just working with a single row or column. The only exception is a single cell.
To get the values from cells in a spreadsheet to work with them, follow these steps
Let's take a look at parts 2 and 3 of this process in more depth.
Getting the data range is useful when you don't want to specifically define the rows and columns of your data, but want the script to try and do this for you. Bear in mind if your sheet contains multiple tables of data or other unstructured data you may end up geting a lot more data than you expect.
var data = sheet.getDataRange();
Using getRange() instead of getDataRange() allows you to specify the exact subset of data you want. You give the command 2, 3, or 4 numerical values, which correspond with the following:
getRange(startingRow, startingColumn, numberOfRows, numberOfColumns)
So if you give 2 values, just a starting row and a starting column, then you only get one cell. If you give 3 or 4 values, so a starting row and column plus a number of rows and (optionally) a number of columns, then you get a range containing multiple cells. Here are some examples:
var cell = sheet.getRange(3, 1);
var column = sheet.getRange(3, 5, 4);
var manyCells = sheet.getRange(2, 2, 6, 3);
You can still use getRange() when dynamically getting a range, i.e. so you don't have to tell the script what number the last column or row is, but make the script work that out itself.
When using getRange() or other features of Apps Script, you may need to know the last row or column in a sheet that contain data. You can use these methods built into Apps Script to get these values and store them in variables to use as needed.
var lastColumn = sheet.getLastColumn();
var lastRow = sheet.getLastRow();
Both of these commands give you a number that corresponds to a row or column. You can put the variable containing these in the place of the third or four values in getRange(), so they are instead of the number of rows or columns.
The final step is getting the actual values in the range you have, as using getRange() or getDataRange() only gets you the cells themselves, not what is in them. You typically use getValues(), unless you've got a single cell, in which case you use getValue (note that it is only a final 's' that separates them).
var cellValue = cell.getValue();
var rangeValues = range.getValues();
// You can also get the range and the values in a single line
var data = sheet.getDataRange.getValues();
var moreData = sheet.getRange(2, 2, lastRow, lastColumn).getValues();
Using getValues() always gets you a two-dimensional array. If you're only getting a single row of data, you can add a [0] after getValues() to specify that you only want the first element in the array (at index number 0.
var rowData = sheet.getRange(2, 1, 1, 5).getValues()[0];
To use Apps Script with spreadsheets, we are going to have to use arrays to store the values in. These collections of values stored in a single variable are created whenever you get the values from multiple cells in a spreadsheet.
Accessing values stored in an array requires using index numbers to refer to each individual value. These index numbers start from 0 rather than 1, meaning it can be confusing when working with data in a spreadsheet, given that spreadsheet rows and columns have their own numbering (that does not start from 0).
Here are some examples of how to get values from an array (you'll notice the examples contain comments as notes to any humans reading the code, as well):
// This is an array which is one-dimensional
var student = [901,'Charles','Chang',8];
// To get an element out of a single array, we use the index number in square brackets
var foreName = student[1];
// The variable foreName now contains "Charles"
// This is an array with has two-dimensions, so is one array made up of other sub-arrays
var studentList = [[901,'Charles','Chang',8], [902,'Jemma','Hamilton',5], [905,'Dan','Marshall',9]];
// We can get individuals rows of data out of the two-dimensional array and also individual values
var student = studentList[1];
var studentId = studentList[1][0];
// The variable student now contains all of Jemma Hamilton's data and the variable studentId contains "902".
Let's have a look at this in practice in Apps Script, then.
Here is a table of data in a spreadsheet. We have used sheet.getDataRange().getValues(); to store all of the values in a variable called allData.
A | B | C | D | E | F | |
---|---|---|---|---|---|---|
1 | Ref | Entry Date | Action | Owner | Target Date | Comments |
2 | A01 | 03/03/2023 | Update evaculation notices | MD | 10/05/2023 | see visual brand guidelines |
3 | A02 | 04/04/2023 | Arrange safety checks on fire extinguishers | SJ | 12/05/2023 | contact estates first |
4 | A03 | 23/04/2023 | Update of database booklets | MD | 25/05/2023 | |
5 | A04 | 02/05/2023 | Update site pages on databases | SD | 04/06/2023 | |
6 | A05 | 10/05/2023 | Arrange printing of new leaflets | PB | 16/05/2023 | could we 3D print them? |
7 | A06 | 10/05/2023 | Order new door signs | JD | 04/07/2023 | |
8 | A07 | 12/05/2023 | Fit new door signs | SH | 29/07/2023 | remember the screwdriver |
9 | A08 | 25/05/2023 | Order new laptops(x2) | SJ | 10/08/2023 | check IT spec |
10 | A09 | 04/06/2023 | Update personal equipment policy | PB | 09/09/2023 | see meeting minutes |
Which item(s) of data do you think will be gotten using the following commands:
If you would like more practice with how to get content from an array, have a go at our additional arrays exercises:
Now we can get parts of a spreadsheet and the data in cells, we might want to edit the spreadsheets too. There are a range of ways you can edit spreadsheets, sheets, and ranges, but we are going to focusing on moving data around: adding and removing data from sheets. For more things you can do with spreadsheets, see Google's Apps Script Reference, particularly things you can do with a Sheet and a Range.
If you need to copy data to the next available row in a sheet, you can append the row to the sheet rather than specifying the location it should go. The appendRow() command can be used to add a 1-dimensional array of values to the next available row in a sheet.
As you might remember, the array you get from getValues() is a 2-dimensional array. This means that to use appendRow(), you must make sure that you use [0] when getting the values or using them with appendRow(), as in the following example:
var sourceValues = sourceSheet.getRange(startRow, startCol, numRows, numCols).getValues()[0];
destinationSheet.appendRow(sourceValues);
If you get an error when trying to append values, always check if you've used getValues() but not used [0] afterwards. Alternatively, you can use appendRow(sourceValues[0]) - with 'sourceValues' being whatever you have named your variable with your values in.
Just as you can get values from a spreadsheet, you can also set values in a similar fashion. For both, use getRange() and give some numerical values to get the range of cells needed, and then add setValues() or setValue() instead of getValues() or getValue().
You can use getValues() and setValues() together to get cells from one sheet and transfer them to another sheet. The source and destination ranges must be the same size, however: the number of rows and number of columns must match for each range. See the example below:
// getting the values
var sourceValues = sourceSheet.getRange(1, 1, 3, 6).getValues();
// setting the values
destinationSheet.getRange(2, 1, 3, 6).setValues(sourceValues)
You can also do the same with getValue() and setValue() if you are working with a single cell. In that case, you might have something like .setValue('Done') where the string could be anything (or could be a variable containing a value).
You can easily delete rows and columns from a spreadsheet in Apps Script. For example, you might want to move data onto another sheet using append or set, and then delete the row or column from the source. You can use deleteRow() or deleteColumn() to delete a single row or column, giving the number of the row or column in question inside the brackets.
dataSheet.deleteRow(2);
dataSheet.deleteColumn(columnNumber);
You can also use the plural versions deleteRows() and deleteColumns(), but you have to give both a starting number of the first row or column to be deleted, and then the number of rows or columns to be deleted.
dataSheet.deleteRows(1, 4);
dataSheet.deleteColumns(startingCol, 5);
When using Apps Script, you may want to set the value of a cell to be a link, or URL. This is likely to be to a new file or folder created by Apps Script. When doing this, you can just set the value of the cell to be the URL, but also might want to create a more friendly looking link for users to click on, that has descriptive text rather than just a URL. Here's a way of doing this:
var hyperlink = '=HYPERLINK('" + docURL + '","' + docName + '")';
destinationSheet.getRange(5, 3).setFormula(hyperlink);
When writing values to a spreadsheet, you might notice that you don't see those values straight away, and you may need to refresh the whole spreadsheet to see them. To avoid this, you can add SpreadsheetApp.flush() after you update a spreadsheet with Apps Script, which forces updates to appear.
Now, we've going to do another livecoding exercise. In this exercise, we are going to be getting a row of data from a spreadsheet. In later weeks, we are going to build upon this exercise as an ongoing livecoding project to see how Apps Script builds up together.
You will need to use a new spreadsheet for this exercise, but you can work in the same folder as the last one.
Adding a custom menu to your spreadsheet means that people (including you) can run scripts from within the spreadsheet, rather than having to go into the Script Editor. This is more user-friendly and avoids people accidentally changing scripts if they do not know how they work. It also saves time for regular actions.
The menu is built in a special function called onOpen() that runs each time the spreadsheet opens - you do this just by naming the function onOpen(). For the Apps Script menu code we will be using, then you give the name of the menu and the items to go on the menu, plus the function that runs when the menu item is clicked.
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Menu Name')
.addItem('first item name', 'first item function name')
.addItem('second item name', 'second item function name')
.addToUi();
}
You only need to change the string names in quotes in this code to make it run, so you can just copy and paste it from here or a previous project with a menu whenever you need to create one.
Now you've reached the end of section 1, we have a project for you to have a go at. We recommend that you try this project before moving on to section 2, as working on the project allows you to consolidate your knowledge from this section and practice writing Apps Script yourself.
For Project 1, we would like you to create a spreadsheet action log that can archive actions. That means that a user can click on a cell in a row of a sheet and then use a menu item to run the script that moves that row to another sheet in the same spreadsheet. If that sounds familiar to the sorts of things we've been learning on this page, good!
The project has a full guidance document, as well as some supporting files you can use. Start with the guidance document linked below, make your own copy of the template spreadsheet so you can write your Apps Script code in it, and have a go! There's also a solution file you can access - you will need to make a copy of this file to see the Apps Script project bound to it and compare your code. Bear in mind that there are many ways of writing Apps Script code so the solution isn't the only way to do it - if your code works but isn't identical, don't worry!