Apps Script Basics – Range class & triggers (pt 2)

This post continues from the previous post on the Range class and triggers with more examples of how to use it.

• 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

Example 4 – Change cell colour when a specific cell is edited, using onEdit()

In this example, we’re going to use a different trigger called onEdit(). This ‘listens’ for any edits on the spreadsheet and will carry out any actions linked to that event. One of the mistakes sometimes people make when using onEdit() is leaving it open so that it reacts to anything edited on your spreadsheet, which is often not what you want. Normally, you want to react to something happening in a specific range of cells.

So, in the simple example below, we have an action plan and when the action status is changed to ‘Open’ it highlights the cell background in red, and when it’s changed to ‘Closed’ it changes it to green.

57.	function onEdit(e) {
58.	  const sh = SpreadsheetApp.getActiveSheet(),
59.	          status = e.value,
60.	          range = e.range,
61.	          column = range.getColumn(),
62.	          row = range.getRow();

Line 57: We call the function onEdit() to set up the onEdit trigger. To capture the event, we need to add a parameter in the brackets, so we can use it later on. Here, I’ve used “e” for event, which is common to see in programs.

Line 58: Get the active spreadsheet.

Now we need to get the range stored in the event and its value. Here’s what the event contains:

The information is stored as objects with the property of the object assigned to it. For example, we can see that the value is Open and the old value was Closed. To get the property, we need to get the event and then the object. We do this by joining the two together with a dot. For example, to get the value in the cell, we use e.value.

Line 59: We then need to find out what value was entered on the cell that was edited. We get the value of the event.

Line 60: Here, we want to find out where that edit happened, So, we get the range of the event, i.e what cell was edited.

Lines 61-62: I also want to find out the row number and column number of that range, so we use getRow() and getColumn() on that range.

64.	  if (sh.getName() === "eg4" && column === 3 && row > 1 && row < 6) {
65.	    if(status === "Closed"){
66.	     range.setBackground("#66BB6A");
67.	  }

Now, this is where we will react to the edit only if it is within a certain sheet and range and if it does we’ll change the background colour of the cell.

Line 64: First, we want to check that 3 things are true. 1) The edited sheet is called “eg4”; 2) The column number is column 3; 3) The row number is between row 2 and 5.  We use an if statement to check that and notice we’ve used the double ampersands (&&) to check that all these conditions are true.

Line 65: If it is, then we check if the status is “Closed”.

Line 66: If they are all true, it will get the range and set the background to green. In the brackets here, I’ve used a hexadecimal colour reference in quote marks.

68.	  else if(status === "Open"){
69.	   range.setBackground("#EF5350");
70.	  }
71.	 }
72.	}

Lines 68-72: If the previous if statement was false, then we check if the text states “Open”. If it does, we set the background colour to red. If this if statement returns false, then it doesn’t do anything.

To try it out, click on one of the cells in column C and change the status to either Open or Closed. The cell background will change to red or green. Note, I’ve used data validation in column C to show the Open and Closed options as a drop-down menu.

We could of course just use conditional formatting to do the same thing, but we’re here to learn Apps Script right?

Example 5 – Highlight whole row when a cell is edited

To take the previous example a step further, let’s highlight the whole row red or green, when the user changes the status to Open or Closed.

74.	//EXAMPLE5 - Change row to red or green depending on status
75.	function onEdit(e) {
76.	  const ss = SpreadsheetApp.getActiveSheet(),
77.	          status = e.value,
78.	  range = e.range,
79.	  column = range.getColumn(),
80.	  row = range.getRow();

Lines 75-80: These are the same as the example above.

82.	  if(sh.getName() === "eg5" && column === 3 && row > 1 && row < 6){
83.	    if(status === "Closed"){
84.	      const rowToColor = range.offset(0, -2, 1, 3);
85.	      rowToColor.setBackground("#66BB6A");
86.	    }  

Line 82: The only change I’ve made here is change the sheet name to eg5.

Line 83: Then we check if the status is “Closed”.

Line 84: This time we want to highlight the row (A to C), so we need to get the range of the edited cell, then select the other cells in that row. We can do that by using offset(), which takes the original range and selects another range based on its relationship with the original one.

We’re going to use the one with 4 parameters: how many rows we are away from the original one; how many columns away; the number of rows we want; the number of columns we want.

So, let’s go through our example here. We want the same row, so we state 0 rows away. We want to start from column A, which is 2 columns to the left of the edited column C, so we state -2 (minus numbers move to the left, positive ones to the right). We only want 1 row, so we state 1. Finally, we want 3 columns (A, B, C), so we state 3 in the last parameter.

Line 85: Then we add the green formatting as before, only this will now set the background of the cells in columns A to C.

88.	  else if(status === "Open"){
89.	    var rowToColor = range.offset(0, -2, 1, 3);
90.	    rowToColor.setBackground("#EF5350");
91.	  }

Lines 88-91: We repeat the same but for the Open status and red formatting.

In the previous example, there was a limitation. If we deleted the Open or Closed status from the cell, the cell wouldn’t change back to white again. So, let’s add that here.

93.	  else{
94.	    var rowToColor = range.offset(0, -2, 1, 3);
95.	    rowToColor.setBackground("#FFFFFF");
96.	  }
97.	 }
98.	}

Line 93: Here, I’ve added an extra else if statement that will capture any other change to that column.

Line 94: We highlight the range as before.

Line 95: We then set the background colour to white.

Opening the sheet eg5, we can see that the formatting changes for the row as we change the status.

Example 6 – Sort table by multiple columns

Here, we’re going to look at sorting a table of information by more than one column. In the chapter on the Sheet class we saw that we could sort data by one column, but by sorting a specific range we have much more control and we can sort multiple columns in different ways. Here, we have a table showing the exam results of some students. We’re going to sort it by their average mark and then by their name.

100.	//EXAMPLE 6 - Sort table by average mark and then by student's name
101.	function example6() {
102.	  const ss = SpreadsheetApp.getActiveSpreadsheet(),
103.	          sheet6 = ss.getSheetByName("eg6"),
104.	       range = sheet6.getDataRange();
105.	 range.sort([{column: 4, ascending: false}, {column: 1, ascending: true}]);
106.	}

Line 103: Get sheet eg6.

Line 104: We get the range of the data on our sheet.

Line 105: We use the sort() method to sort our data, then we add the details of the columns we want to sort. Note, the use of the brackets, square brackets (it’s an array) and the curly brackets (we’re defining certain parameters). The information is written in pairs, the column number and then whether the sort will be in ascending order. If it’s descending, then you write false. So, here we’re sorting column 4 (the average marks) in descending order (i.e. the highest at the top), and then alphabetically by their names.

Running the code, we can see that it has sorted the list by average mark and as there are 3 students with 80%, we can also see it’s sorted them alphabetically by name.

Example 7 – Copy part of a table to a new sheet

In this final example, we’re going to see how easy it is to copy specific information from one sheet to another. Then, we’ll add some formatting on the new sheet.

We’ll also see how we can check to see if a sheet with the new sheet’s name already exists, and if it does we will delete it, as we can’t have 2 sheets with the same name in the spreadsheet.

Here, we’re going to copy some rows containing information about the available methods to the Range class.

108.	//EXAMPLE 7 - Copy part of a table to a new sheet
109.	function example7() {
110.	  const ss = SpreadsheetApp.getActiveSpreadsheet(),
111.	          sheet7 = ss.getSheetByName("eg7"),
112.	          range = sheet7.getRange(1, 1, 13, 3);

Line 111: Get sheet eg7.

Line 112: Let’s get the part that has been coloured, so the first 13 rows. So, we use getRange() and state we that we want rows 1 to 13 and columns 1 to 3.

114.	  const checkNewSheet = ss.getSheetByName("new"); 
115.	  if (checkNewSheet) {
116.	   ss.deleteSheet(checkNewSheet); 
117.	  }

Now, we need to check that the sheet doesn’t already exist. This is particularly important in this example, and you wouldn’t be able to run this example more than once without it.

Line 114: First, we get the sheet by its name. The sheet we’re going to create will be called “new”.

Line 115: Then, we check if there is a sheet called “new”. We do this just by using the if statement and passing the name of the sheet in the brackets. We don’t need to use an equals operator here, as if there is one it will return true and run the line in the brackets, otherwise it’ll just carry on with the code.

Line 116: If it finds a sheet called “new”, it will delete it using deleteSheet().

119.	  const newSheet = ss.insertSheet("new");
120.	  const newSheetRange = newSheet.getRange(1, 1, 13, 3);
121.	  range.copyTo(newSheetRange);

Line 119: Now, we’re sure we haven’t got a sheet called “new”, so we can go ahead and insert a sheet called “new”.

Line 120: We then get the range on the new sheet. Note, the dimensions of this range need to be the same as the data we’re copying, but it could be in different cell locations.

Line 121: We copy the original range to the new range, using copyTo().

123.	  newSheet.autoResizeColumn(1);
124.	  newSheet.setColumnWidth(3, 400);
125.	  newSheet.getRange(1, 3, 100).setWrap(true);
126.	}

Finally, let’s format the newly added data a little.

Line 123: First, let’s adjust the width of column 1 automatically. Note, this doesn’t always adjust the column width snugly to the data, as we’ll see, but it’s good if you don’t know what width your data will need.

Line 124: I’m going to set a specific width of 400 to column 3, using setColumnWidth().

Line 125: As some cells in column C contain a lot of text, I also want to wrap the text in the cells. Here, I’ve got column 3 and have selected, rather arbitrarily, rows 1 to 100. Then I use setWrap() and state true to set the text wrap on.

Running the code, we’ll see the new sheet has been made, called “new”, and we have the first 13 rows added to it, with some basic formatting. Notice, the copyTo() method also copied the cell background colours and bolding.

There are many methods available in the Range class, allowing you to do most things you want to do. Note, even though there are nearly 200 methods, some are in pairs, get & set, for example, getBackground() and setBackground(), and some have multiple versions, for example, clear() has 6 variants. Enjoy playing around with them!

Find more information on the Range class here:

https://developers.google.com/apps-script/reference/spreadsheet/range

Also, you can find information on the ScriptApp class and triggers here:

https://developers.google.com/apps-script/reference/script/script-app#newTrigger(String)

Here you can find more information on the setBorders() method:

https://developers.google.com/apps-script/reference/spreadsheet/range#setbordertop-left-bottom-right-vertical-horizontal

Plus, there is more information on the sort() method in the Google documentation and it has some good, clear examples of how this can be used:

https://developers.google.com/apps-script/reference/spreadsheet/range#sortsortspecobj

You make a copy of the spreadsheet containing the code here.

a