Apps Script Basics – Range Class & Triggers (pt 1)

In this post, we’re going to look at some of the ways you can control the cells on your spreadsheet, using the methods connected to the Range class.

At the time of writing, there were nearly 200 different methods connected to this class. So, as you can imagine there are a lot of different things you can do with ranges. Here, I’m going to take you through some examples, showing you some of the common methods used when working with ranges.

The main areas we’re going to look are:

• activating cells and clearing their content
• setting up triggers automatically (onOpen, onEdit, onFormSubmit)
• adding formulas to cells
• changing the number format of cells
• adding borders and changing the alignment
• changing the background colour based on edits to a range
• sorting a table by multiple columns
• copying part of a range and creating a new sheet with that range

You can make a copy of the spreadsheet that contains the code examples, at the end of this post. So, let’s dive into the first example.

Example 1 – On opening a spreadsheet, highlight a specific cell & clear its content

By default, when we open spreadsheets, the first page is shown and cell A1 is highlighted, but there are times when we want to take the user directly to a specific cell, maybe to enter some information. Here, as a simple example, I want the cell B4 to be highlighted ready for the user to type in the name of the book they are looking for in an inventory.

1.	//EXAMPLE 1 - Open sheet, highlight a certain cell and clear its content
2.	function onOpen() {
3.	  const ss = SpreadsheetApp.getActiveSpreadsheet(),
4.	           sheet1 = ss.getSheetByName("eg1"),  
5.	           cell = sheet1.getRange("B4");
6.	  cell.activate()
7.	       .clearContent();  
8.	}

Line 2: First, we set up the onOpen() trigger. By calling the function ‘onOpen()‘, this will run the function automatically when the spreadsheet is opened. As we saw in an earlier chapter, we often use this to create menus, that are added as soon as the spreadsheet is loaded.

Line 3: Let’s get the active spreadsheet.

Line 4: This file contains various sheets, so let’s get the first one called “eg1“.

Line 5: Now, I need to get the range I want to highlight, which is B4.

Line 6: To highlight it, we need to activate the cell. So, we use the activate() method. We’re going to chain another method to this, so don’t add a semi-colon on the end.

Line 7: As a previous user might have already typed something in that cell, I also want to remove any content in it, to make it ready for this user. To do so, we use clearContent(). This will remove the values in the cell.

There are a few different clear methods, some which clear the format, clear everything, clear notes, etc, but here I just want to clear any text that was left in there.

Open the spreadsheet and you’ll see sheet eg1 will be opened and cell B4 is highlighted.

Notice how we first got the spreadsheet (ss), then the sheet (sheet1), then the specific range, in this case a cell (cell).

Example 2 – Add a formula to a cell when a form is submitted

Here, we’re going to add a formula to a sheet when a form connected to it has been submitted. To do this automatically, we need to set up the trigger onFormSubmit(). We could do this manually by clicking on the triggers menu (clock icon on the menu on the left-hand side), but here let’s set it up programmatically. Below is a function to set up the onFormSubmit trigger. You’ll need to run it before running the function example2.

10.	//EXAMPLE 2 - Set up onFormSubmit trigger
11.	function example2Trigger() {
12.	  const ss = SpreadsheetApp.getActiveSpreadsheet();
13.	  ScriptApp.newTrigger("example2")  
14.	                .forSpreadsheet(ss)
15.	                .onFormSubmit()
16.	                .create();
17.	}

Line 12: Get the active spreadsheet.

Line 13: We use the ScriptApp class to do this, then add the newTrigger() method. In the brackets, refer to the function you want to run when the form is submitted. In this case it will be the example2 function, which I’ll explain below.  Don’t add a semi-colon at the end, as all the following methods are chained together.

Line 14: Next, we need to state where the trigger will come from, and in this case it will be when the form response is submitted and stored in the spreadsheet. We use forSpreadsheet() and state the spreadsheet in the brackets, which in this case is the active one we stored earlier in the variable ss.

Line 15: Then we state the trigger type we want. Here it’s the onFormSubmit one.

Line 16: Finally, we tell it to create the trigger.

Run this function once you’ve written the function example2, otherwise the reference to the function will throw an error.

If we look in the triggers (clicking on the clock icon on the toolbar), we can see it’s been set up for us:

Now, in this simple example, the employee will fill in a form with their start and finish time and this will be recorded on the Sheet. We then want to calculate the hours they have worked by adding a formula on the same response row. We’re going to work with the sheet called eg2, which is where the form has been linked to.

19.	//EXAMPLE 2 - Add formula to last row, which is triggered when a form is submitted
20.	function example2() {
21.	  const ss = SpreadsheetApp.getActiveSpreadsheet(),
22.	       sheet2 = ss.getSheetByName("eg2"),
23.	       lastRow = sheet2.getLastRow();
24.	  sheet2.getRange(lastRow,4).setFormulaR1C1("=R[0]C[-1]-R[0]C[-2]");
25.	}

Line 22: Let’s get the sheet where the responses will appear, eg2.

Line 23: When a form is submitted, the latest response is appended to the next row that doesn’t have any data on it, so is always the last row. We want to get the latest response row, so we use getLastRow().

Line 24: I want to add the formula for the hours worked in column D. So, first we get the last row and column 4 (column D). Then, using the setFormulaR1C1() method we add the formula. We use the R1C1 version as the formula will refer to cells in relation to the formula cell. The formula we want is the cell in column C minus the cell in column B (e.g. C2-B2).

Obviously, as more responses are submitted, the row number will change every time. In the brackets, we write the formula in relation to the formula cell. So, it’s row 0 (the same row) one column to the left (-1), minus row 0 two columns to the left (-2). Remember to add the equals sign and surround all the formula in quote marks.

Submitting a form, we can see the start time and finish time have been recorded and the time worked has been added into column D.

Clicking on cell D2, we can see it’s added the correct formula:

Example 3 – Add a formula when a form is submitted and format the responses

This example is similar to example 2, but this time we’re going to add a little bit of formatting to our responses as they are submitted. We’re going to change the time format, centre the data and add borders to the data.

First, we create a trigger as before, this time referring it to function example3.

27.	//EXAMPLE 3 - Set up onFormSubmit trigger
28.	function example3Trigger() {
29.	  const ss = SpreadsheetApp.getActiveSpreadsheet();
30.	  ScriptApp.newTrigger("example3")  
31.	                .forSpreadsheet(ss)
32.	                .onFormSubmit()
33.	                .create();
34.	}

The form is similar to before and I’ve linked it to sheet eg3 on the spreadsheet.

36.	//EXAMPLE 3 - Add formula, add formatting when a form is submitted
37.	function example3() {
38.	  const ss = SpreadsheetApp.getActiveSpreadsheet(),
39.	           sheet3 = ss.getSheetByName("eg3"),
40.	           lastRow = sheet3.getLastRow();
41.	  
42.	  sheet3.getRange(lastRow,4).setFormulaR1C1("=R[0]C[-1]-R[0]C[-2]");

Lines 37-42: These are the same as described above.

44.	  const times = sheet3.getRange(lastRow,2,1,3);
45.	  times.setNumberFormat("HH:mm");

Now, let’s add some formatting. First, let’s change the times in columns B to D to only show the hours and minutes.

Line 44: First, we get the range of cells using getRange() and in the brackets including 4 parameters: starting row number, starting column number, number of rows, number of columns. So, we’re getting the last row, column 2, only 1 row, and 3 columns in total. I’ve stored that range in the variable times.

Line 45: Then, we use the setNumberFormat() method to state the format we want, which we add in the brackets.

47.	  const data = sheet3.getDataRange();
48.	  data.setHorizontalAlignment("center")
49.	         .setBorder(true, true, true, true, true, true);

Now, to centre all the data and add borders. To make it simple, I’m just going to get all the data on the sheet and apply the same formatting, not just the latest row.

Line 47: Get all the data on the sheet by using getDataRange().

Line 48: To this range, I’ll first centre the text, by setting the horizontal alignment to ‘center’.

Line 49: To the same range, I want to add borders. use the method setBorder() and this takes 6 parameters, depending on which border you want to add. Here we’re adding them to all the sides, so we add the boolean ‘true’ to all 6 parameters.

51.	  sheet3.getRange(lastRow,1).setNumberFormat("DD/MM/YYYY");  
52.	}

Line 51: Finally, let’s change the timestamp to just show the date. Again, we use setNumberFormat() just with a different format in the brackets.

Submit a form and we can see it then formats the cells the way we wanted them.

For more examples of the Range class and triggers, see part 2 of this post HERE.

a