[et_pb_section fb_built=”1″ admin_label=”section” _builder_version=”3.0.47″][et_pb_row admin_label=”row” _builder_version=”3.0.47″ background_size=”initial” background_position=”top_left” background_repeat=”repeat”][et_pb_column type=”4_4″ _builder_version=”3.0.47″ parallax=”off” parallax_method=”on”][et_pb_text admin_label=”Text” _builder_version=”3.0.47″ background_size=”initial” background_position=”top_left” background_repeat=”repeat” custom_margin=”-100px|||”]
In previous posts, we’ve looked at the SpreadsheetApp and Spreadsheet classes. Now let’s look at the next level down, which is the Sheet class.
Here, we’ll look at how we can work with sheets in a spreadsheet, and in particular, I’ll highlight the following common tasks:
- Copying and renaming a master sheet
- Adding data from one sheet to another
- Hiding, inserting, and deleting rows and columns in a sheet
- Appending data to a list and then sorting that list
- Automatically adjusting the column width
- Getting a row of data from a full list, creating a new sheet and adding the individual’s info
- Speeding up writing to a sheet by using arrays
In the examples below we’ll be using a file which has 4 sheets: name, teachers, classes, and a hidden one called master.
Example 1 – Copying a hidden master sheet and renaming it
Here, we’re going to make a copy of a master sheet (below) for a teacher to fill out, rename it with the teacher’s name, which is on the sheet called “name”, and add their name to a cell on the newly created sheet.
Line 2: Set up the function and we’ll call it example1.
Line 3: Get the active spreadsheet and store it in the variable ss.
Line 4: To tell the code where to put the new sheet, we need the spreadsheet ID. As we’re going to copy the sheet to the same spreadsheet, we just get the active spreadsheet, ss, and get its ID.
Line 5: Getting its ID isn’t enough, we also need to open it by its ID. Let’s store that in the variable destination.
Line 6: Now, let’s get the master sheet we want to copy, by getting it by its name.
Line 7: To copy a sheet, we use the copyTo() method. This can copy a sheet to another spreadsheet or in this case, to the same spreadsheet. We get the master sheet and copy it to the same spreadsheet using the spreadsheet’s ID.
Line 9: As the master sheet in this spreadsheet is hidden, we need to show the newly created sheet. By default, the copied sheet will be hidden if the sheet its being copied from is hidden. The master sheet doesn’t have to be hidden, but I often hide sheets that are not in use for the user.
Line 10: The teacher’s name we want to name the sheet is on the sheet called “name” in cell A1. So, we get the sheet by its name, get the cell A1 and its value.
Line 11: Then we rename the sheet with the setName() method.
Line 12: I also want to add the teacher’s name on the new sheet, so we get cell A1 and set its value to the teacher’s name.
Run the code by selecting function example1 from the toolbar and press play. The first time, you will go through the authorisation process. Follow the instructions and click “Allow” at the end. See my previous Apps Script Basics posts for more details.
Open the spreadsheet and you’ll see the new sheet with the teacher’s name.
Open the sheet and you’ll see it’s copied the master sheet and added the teacher’s name. Note, that by using the copyTo it doesn’t copy over the cell’s background colour. This could be added by getting the range and using setBackground().
Example 2 – Hiding and inserting rows and columns
Here, we’re going to use the list of classes on the classes sheet and hide some rows and columns to display only certain information.
We’re just going to leave the list of teachers, the timetables, the number of students and we’re going to insert an extra column, so that the user can add some notes to the rows.
Lines 17-18: We set up the function and get the active spreadsheet.
Line 19: We then get the sheet called “classes” and store it in the variable classes.
Line 20: To hide columns we use the hideColumns() method and in the brackets we state which column we want to hide. In this case, it’s column B (the second column).
Line 21: I also want to hide the columns E to G. Fortunately, we can hide a range of columns in one go. We use the same hideColumns() method, but this time we include 2 arguments, firstly the position of the first column we want to hide, in this case column 5 (column E). Then, we state how many columns we want to hide, so, in this case 3.
Line 22: We can hide rows in a similar way, this time using hideRows(). Here, I’m hiding the header row.
Line 24: I want to insert a column next to the student numbers in position 5. To do so, we use insertColumns() and add the column position number.
Now, I want to delete all the blank rows below the table. To do so, we need to find out which is the last row with values in it and which is the last row on the sheet.
Line 25: First, let’s find out the last row with information on it. We use getLastRow() to do this. This will return the row number, in this case, 5, and we’ll store it in the variable lastRow.
Line 26: Next, we get the last row on the sheet. We use getMaxRows() to do this. Again this returns the row number, in this case, it’s 1,000 and store it in the variable maxRow.
Line 27: Now, we use these two figures to work out how many rows to delete. We subtract lastRow from maxRow and store it in the variable blankRows.
Line 28: Now, we can delete the rows we want by using deleteRows(). There are 2 arguments, the first, which row we will start from, and the second is how many rows. So, we want to start from the row below the last row with text, so lastRow + 1. Then the number of rows is the figure we calculated before, which is in blankRows.
I also want to delete the blank columns to the right of the table. This is done in a similar way as deleting the rows, but of course, this time we’re working with columns not rows.
Run the example2 function and you’ll be left with this:
If you run example2, you will have to manually reset the sheet, if you want to run it again. Otherwise, you’ll get a different result the second time as a column has been inserted and has changed the position of the columns to the right of it.
Example 3 – Appending a name to a list and sorting it alphabetically
This time we’re going to get a teacher’s name from the “name” sheet and add it to the list of teachers on the “teachers” sheet. Then, we’re going to sort that list alphabetically and also, adjust the column width automatically, so that the names fit in the cells.
This could be useful for example, if the name has been received from a form submission and then you’re adding that name to some kind of master list. To keep the code simple, we’re just going to get it from a specific cell, which is cell A1 on the “names” sheet.
Here’s the current list on the “teachers” sheet.
Lines 38-39: Set up the function and get the active spreadsheet.
Line 40: Let’s get the name we want from the name sheet in cell A1.
Line 41: Now, let’s get the sheet we want to edit, which is the “teachers” sheet.
Line 42: To add the name at the bottom of the list, we use the appendRow() method, which will automatically work out where the bottom of the list is and add the name under it. Note that, it expects an array here, so, the teacher’s name needs to be in the square brackets.
The Google documentation for appendRow() shows want parameter type is required:
As you can see, it states “Object[]” and in the description states it’s an array it needs.
Line 44: Now, let’s adjust the column width so that all the names fit within the cells. We do this with autoResizeColumn() and state which column on the sheet we want to adjust, which in this case is column 1 (column A).
Line 45: Finally, let’s sort our list alphabetically. We do this with the sort() method and in the brackets state which column will be sorted.
Note, if you have other columns with data that’s connected to the column you want to sort, they won’t be changed, so, this could mess up your data. So, only use this sheet sorting for individual columns.
Run the example3 function and as you will see, it’s added the new teacher’s name, sorted the list and made the column width wider to accommodate the longest teacher’s name.
Example 4 – Extracting data from a table and creating a new sheet with that data
Here, we’re going to extract a particular teacher’s data, insert a new sheet, and add that teacher’s data to the new sheet.
Firstly, we’re going to ask for the row number corresponding to the teacher we want. I’ve done this just to keep the code simple and also the user will only have to type in a number, rather than a full name. Lines 50-54 prompt the user to enter a row number and that response is then stored.
Line 50: Access the spreadsheet user-interface with SpreadsheetApp.getUi().
Line 51: Display the prompt dialogue box, using prompt(). In the brackets state the title of the prompt, the question you want to ask, and the buttons you want to show, in this case, there will be an OK and Cancel button.
Line 52: Then, we need to get the response if they clicked the OK button. Here we get the selected button and if it equals the OK button then it will run line 53. If cancel button is clicked, the prompt box closes and nothing happens.
Line 53: We get the text of the response by using getResponseText() and store it in the rowNumber variable.
Line 54: Close the if statement.
Line 56: Get the active spreadsheet.
Line 57: Get the sheet called “classes”.
Line 58: Get all the values on that sheet by using the getDataRange() method and store them in the variable teachers.
Line 60: Now, we just want the specific teacher’s row. We do that by getting a specific ‘row’ and ‘column’ within our array teachers. The first number in the square brackets is the row number, where we will use the variable rowNumber we got from the prompt before. As this is an array we have to minus 1, as for example, row 1 is in position 0 in the array. The second number is the column number, again this is zero-based, so the first column is in position 0.
Line 61: Now, we need to insert the new sheet We do this by using the insertSheet() method and add the name of the sheet in the brackets.
I want to get the headers and then the teacher’s row.
Line 63: To get the header row, we can use the shift() method with our array. This will remove the data in the first position of our array and we’ll then store it in the headers variable.
Line 64: As shift() has removed the headers data from the array, the data in row 2 is now in position 0 in our array. So, to get the teacher’s data we need to get the row number and instead of subtracting one for our array, we need to subtract 2. Here, we just use one set of square brackets as we are going to get the data in the whole row, so we don’t state which column we want.
Now, we need to add the headers row and teacher’s information.
Line 66: First, we append the row with the headers to the new sheet.
Line 67: Finally, we append the row with the teacher’s info.
Run the function example4. Open the spreadsheet and you’ll see the prompt box asking you for the row number. Enter the number you want and click OK.
The new sheet will be added with the teacher’s name.
Open the sheet and you’ll see that the headers and teacher’s information has been added. Note, this is unformatted as we only got the values from the original sheet, but the sheet could easily be formatted.
Example 5 – Extracting data from a table and creating a new sheet with that data (quicker method)
The above code is fine but here I want to show you why we would avoid making multiple appendRow calls. Every time, we interact with the spreadsheet, we are calling for information and that takes an amount of time. What we should try to do in our code, is to get and set the information we want in one go, to reduce the number of calls and therefore, the time taken.
The code is the same as above until line 86.
We’re going to create an array in which we will add the headers and teacher’s information.
Line 88: First, we set up the empty array. We do this by assigning a variable to the empty square brackets.
Line 89: Then, we add the data we want into the array. One way of doing that is to use the push() method. So, first we add the headers info, which will be in position 0 of our array.
Line 90: Then, we push the teacher’s info, which will be in position 1.
Line 92: In the previous example, we appended each row separately, but here we’ll get the range on the new sheet and add the contents of the info array in one go. Note, that we need to get a range that is the same dimensions as our array, otherwise it’ll throw an error. We know it’s 2 rows and 7 columns of data, so here we start in row 1, column 1, then extend it 2 rows high and 7 columns wide, i.e. (1, 1, 2, 7).
Running the function will produce the same result as before. If you ran the previous example before, make sure you either delete the sheet that was made before running this one, or choose a different teacher this time, otherwise it will try to make a sheet with the same name and this isn’t possible, so an error will appear.
The reason I added this last example, was to show you the difference in time taken to complete the same overall task. We can see the times by opening the Execution transcript in the View menu of the Script Editor.
Below is part of the transcript from Example 4. Appending the 2 rows took 0.149 seconds (0.104s + 0.045s).
This is example 5 and as you can see it only took 0.03s to complete the same task.
While in this simple example, we’re only talking a difference of just over a tenth of a second, example 4 was 5 times slower than example 5. In a larger program, this could mean that either the user has to wait longer for the program to finish or at worst, will mean the program reaches the 6 minute runtime limit and stops working. So, it’s important to think of not just how you get something done but the quickest and most efficient way to do it.
This is also why we use getDataRange() (e.g. in line 80) to get all the data in one go and store it in an array. We then work with the array, rather than calling for different ranges from our sheet, which would be slower.
You can find the Sheet class documentation here.
You can find the code here at GitHub.
You can make a copy of the spreadsheet containing the code here.
You can find more information on the push() method here and the shift() method here.
[/et_pb_text][/et_pb_column][/et_pb_row][et_pb_row admin_label=”Row” _builder_version=”3.0.47″ background_size=”initial” background_position=”top_left” background_repeat=”repeat”][et_pb_column type=”4_4″ _builder_version=”3.0.47″ parallax=”off” parallax_method=”on”][et_pb_text admin_label=”PostEnd” _builder_version=”3.0.47″ background_size=”initial” background_position=”top_left” background_repeat=”repeat” use_border_color=”off” border_color=”#ffffff” border_style=”solid” global_module=”22296″ saved_tabs=”all”]
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
Baz Roberts (Google+ / Flipboard / Twitter)
[/et_pb_text][/et_pb_column][/et_pb_row][/et_pb_section]