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:

• Each page is a section of the course and contains text content and code snippets to read through (and make notes on, if you'd like). The code snippets are to show what commands might look like, but they do not necessarily work as an entire bit of code, as they are designed to give brief examples, not full scripts.
• Each page also contains what we've called livecoding, which means you're going to write the code along with us in the video (or using the accompanying walkthrough document). This allows you to write and run Apps Script code to start getting used to it, in a structured format.
• At the end of each page will be a project, which is an Apps Script activity we highly recommend you do before moving on to the next page. The project will have a scenario and give you something to build in Apps Script, with a support document that goes through what you'll need to include. Use the information on the page (and also previous pages of the course) to help you to complete these.

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.

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!

How Apps Script works

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.

Numbers

var number = 5;

var x = 0;

var favouriteNumber = 569.75;

Text (strings)

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.";

Multiple things (arrays)

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"];

Other things stored in variables

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:

• Certain parts of Apps Script code will appear as different colour text in the Editor, to help you confirm commands are correct.
• If you open a set of brackets, it will automatically add a closing bracket as well.
• When you start typing Apps Script-specific commands (rather than commands from JavaScript), then the Script Editor will autosuggest commands for you. You can also get prompts for the kinds of information you need to give certain commands, or what type of data they will return.
• Some parts of your Apps Script code will be automatically indented for readability, when sections of code happen "inside" other coding functions and features.
• Line numbers help you keep track of your code, and error messages also refer to a line number where the error occured, which can be helpful when diagnosing issues.

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

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!).

Getting a spreadsheet file

var ss = SpreadsheetApp.getActiveSpreadsheet();

Getting sheets within a spreadsheet

var oneSheet = ss.getActiveSheet();

var anotherSheet = ss.getSheetByName('Another Sheet');

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("mySheet");

Getting the active cell (and its row and column)

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();

Getting a range of cells and their values, and the last row and column

var data = sheet.getDataRange();

var cell = sheet.getRange(3, 1);

var column = sheet.getRange(3, 5, 4);

var manyCells = sheet.getRange(2, 2, 6, 3);

var lastColumn = sheet.getLastColumn();

var lastRow = sheet.getLastRow();

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();

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.

Which item(s) of data do you think will be gotten using the following commands:

1. allData[1][1]
2. allData[5][3]
3. allData[7]

Arrays answers

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.

Appending rows to a sheet

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.

Setting values

// getting the values
var sourceValues = sourceSheet.getRange(1, 1, 3, 6).getValues();

// setting the values
destinationSheet.getRange(2, 1, 3, 6).setValues(sourceValues)

Deleting data

dataSheet.deleteRow(2);
dataSheet.deleteColumn(columnNumber);

dataSheet.deleteRows(1, 4);
dataSheet.deleteColumns(startingCol, 5);

Setting a nicely formatted link

var hyperlink = '=HYPERLINK('" + docURL + '","' + docName + '")';
destinationSheet.getRange(5, 3).setFormula(hyperlink);

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!