So far in this series of posts on using Apps Script with Google Sheets, we’ve looked at the classes SpreadsheetApp, Spreadsheet, and Sheet. Now we’re getting to the smallest component, the Range. 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 last count, there were 116 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, and if you’ve been following this little series, it’ll give you a chance to consolidate some of the knowledge you now have on the other spreadsheet-related classes.
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
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.
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. 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.
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 when a form has been submitted. To do this automatically, we need to set up the trigger onFormSubmit. We could do this manually by clicking on the clock icon on the toolbar, 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.
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 add the trigger to. In this case it will be the example2 function, which I’ll explain below. Click here to find out more information about this class. Don’t add a semi-colon at the end, as all the following methods are connected together.
Line 14: Next, we need to tell it which spreadsheet it’s for, by using forSpreadsheet(). In the brackets we add the spreadsheet we want, 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, 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.
Line 22: Let’s get the sheet where the responses will appear, eg2.
Line 23: 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.
The form is similar to before and I’ve linked it to sheet eg3 on the spreadsheet.
Lines 37-42: These are the same as described above.
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.
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. See the Google documentation for details.
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.
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. Often 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.
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.
Line 59: Here, we want to find out where that edit happened, So, we get the range of the event.
Lines 60-61: I also want to find out the row number and column number of that range, so we use getRow() and getColumn() on that range.
Lines 62: We then need to find out what value was entered in that range. That is, later we’re going to look to see if that value is Open or Closed.
Now, this is where we limit the range in which we will react to the edit and change the formatting.
Line 64: We want to check that 3 things are true. 1) The column number is column 3; 2) The row number is between row 2 and 5; 3) The status is “Closed”. 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 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.
Lines 67-70: If the previous if statement was false, then we check the same column and rows but this time check to see 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.
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.
Lines 73-78: These are the same as the example above.
Line 80: The only change I’ve made here from the previous example, is that I’ve removed one of the row conditions. This time it will check any cells from row 2 onwards.
Line 81: 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 column 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 82: Then we add the green formatting as before, only this will now set the background of the cells in columns A to C.
Lines 85-88: 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.
Line 90: Here, I’ve added an extra else if statement that will capture any other change to that column.
Line 91: We highlight the range as before.
Line 92: 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 my post 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.
Line 99: Get sheet eg6.
Line 100: We get the data on our sheet.
Line 101: 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 or descending 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. There is more information here on this method in the Google documentation and it has some good, clear examples of how this can be used.
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.
Line 107: Get sheet eg7.
Line 108: 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.
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 110: First, we get the sheet by its name. The sheet we’re going to create will be called “new”.
Line 111: 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, if not it will ignore lines 112 and 113.
Line 112: If it finds a sheet called “new”, it will delete it using deleteSheet().
Line 115: Now, we’re sure we haven’t got a sheet called “new”, so we can go ahead and insert a sheet called “new”.
Line 116: 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 117: We copy the original range to the new range, using copyTo().
Finally, let’s format the newly added data a little.
Line 119: 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 120: Column 3 I’m going to set a specific width of 400, using setColumnWidth().
Line 121: 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 thought there are over 100 methods, some are in pairs get & set, for example, getBackground() and setBackground(), and some have multiple versions, for example, clear() has 6 variants. Enjoy!
You can find a copy of the code here on GitHub.
You make a copy of the spreadsheet containing the code here.
Read more about the Range class here in Google documentation.
eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:
- “Beginner’s Guide to Google Drive” – iBooks store / Kindle store
- “Beginner’s Guide to Google Forms” - iBooks store / Kindle store
- "Beginner's Guide to Google Sheets" - iBooks store / Kindle store
- "Beginner's Guide to Google Docs" - iBooks Store / Kindle store
- "Beginner's Guide to Google Slides" - iBooks Store / Kindle store
- "Google Sheet Functions - A step-by-step guide" - iBooks Store / Kindle Store