[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|||” use_border_color=”off” border_color=”#ffffff” border_style=”solid”]
In the my last post, we looked at the SpreadsheetApp class. Now, let’s look at the next group related to spreadsheets, which is the Spreadsheet class. This class allows us to:
- copy spreadsheets
- work with sheets, such as moving, inserting, and deleting them
- add or remove collaborators to the spreadsheet
- display messages, in the form of the toast message
Go to the Google documentation following this link, and you’ll see all the methods available to the Spreadsheet class.
You will see that there are a lot of methods available but in fact, there are many that can be found in the Sheet class too, which I’ll cover in a future post. So, here I’m going to focus on a few common ones, which are unique to the Spreadsheet class. Here’s a list of ones relating directly to the Spreadsheet class:
Example 1 – Copying a spreadsheet and renaming it & using the toast message
To start, let’s make a copy of a spreadsheet and rename it by getting the name of the original spreadsheet and adding to it. Then we’ll let the user know the process has finished by displaying a toast message in the original spreadsheet.
Line 3: Set up the function and call it example1.
Line 4: First, I want to make a copy of the current spreadsheet that’s open (i.e. the active one) so we use getActiveSpreadsheet() to get it and we store it in the variable ss1.
Line 5: As we’re going to use the name of this spreadsheet, we need to get its name. So, we use the variable ss1 and then use getName(). We then store it in the variable ss1Name.
Line 6: Now, let’s copy the original spreadsheet and we put the name of the new spreadsheet in the brackets. We’re going to name it using a combination of the original spreadsheet’s name and add “-example1” at the end. So, we state the variable ss1Name, use a plus sign to join the two parts together and then in quote marks add the text we want.
Line 7: Finally, let’s let the user know that the spreadsheet has been copied and named using the toast message. As we are working in the original spreadsheet, we get the variable ss1 and then add toast() to it. In the brackets, we add the options we want. Here, I’m using the toast message option which allows us to add a message, a title, and state how many seconds the message will be displayed for.
I often use toast messages, either as a message to show the user the progress of the process, if it’s a particularly long one, or just to let them know when everything is finished. The good things are that the message doesn’t stop the code working in the background, so doesn’t delay it, and it doesn’t require user interaction like an alert message would. Note, when you run the code, go back to the spreadsheet, otherwise you won’t see the toast message, as it only shows while you are in the spreadsheet and not the script editor.
Line 8: Then we close the function with a curly bracket.
Run the code from the toolbar by selecting “Select function”.
Then from the list choose “example1”.
The first time you run the code, you will have to go through the usual authorisation process. Just click, “Review permissions”, the email account you want to use, and “Allow”.
For this example, as soon as you’ve clicked the play button to run the code, open the spreadsheet. When the code has come near the end, you’ll see the toast message pop up in the right-hand corner of the screen.
In your My Drive, you’ll see the new copied spreadsheet, with the name we set up.
Example 2 – Adding editors or viewers to a spreadsheet
When creating a new spreadsheet we sometimes want to share it with certain people. We could do that manually by going to the share settings in the spreadsheet, but a quicker way is to include it in the code, when you are creating the spreadsheet.
Line 11: Set up function example2.
Line 12: Let’s make a copy of an existing spreadsheet. First, let’s open it by its ID.
Line 13: Here, I’m going to make a copy of it so that it appears in your My Drive. Normally in the code you wouldn’t need this step. Let’s make a copy and temporarily call it “NEW”.
Line 14: Here, I’m going to rename it “example2” using the rename() method. Obviously, normally you wouldn’t copy a spreadsheet and give it a name then rename it straight afterwards, but I just wanted to show the rename() method.
Line 15: Now, we’re going to add another editor. This is simply done by using addEditor() and in the brackets adding their email address between quote marks.
Line 16: Similarly, we can add viewers (i.e. those without edit rights). Here, we’re going to add more than one person at the same time, so we use the plural viewers not viewer and we need to add the email addresses as an array. So, as you can see, in the brackets we add square brackets and list the email addresses with a comma between them.
Note, addEditors() also exists as does addViewer().
Run the code, and you’ll see the new spreadsheet in your My Drive. As you can see by the people symbol to the right of the name, it has been shared.
Click on the sharing icon at the top of the screen.
Go to advanced, and you’ll see that we have indeed given edit rights to one user and view access to two other users.
Example 3 – Moving a sheet to a new location
This time, let’s get the sheet called “All”, which is currently the second sheet in the spreadsheet and move it to the last sheet position on the right.
Line 21: Get the active spreadsheet and store it in currentSs.
Line 22: Let’s make a copy of it, so you have a copy in your My Drive, and let’s call it “example3”. We store the new spreadsheet in a variable called ss3.
Line 23: Now, let’s get the sheet we want by using getSheetByName(). So, we use this with the spreadsheet ss3 and add the name of the sheet in the brackets between quote marks.
Line 24: As we’re going to use the moveActiveSheet() method to move the sheet, we first need to activate this sheet. So, we use the variable sheetAll and use activate().
Line 25: Finally, we get the spreadsheet ss3, use moveActiveSheet() and state which position we want the sheet to move to. As here it will be at the end and there are 5 sheets, we need to put 5.
Run the code and you’ll see the new spreadsheet in your My Drive.
Open it and you’ll see the sheet called “All” has been moved to the last position to the right.
To move it to the front we would put 1.
Example 4 – Moving a sheet to a new location using getNumSheets()
Sometimes, we don’t know how many sheets they are in our spreadsheet beforehand or maybe there are a lot to count, so instead of stating a fixed number upfront, we can get the code to count the number of sheets and then place the sheet where we want in relation to that number. Here, we’re going to move the “All” sheet again to the end.
Lines 29-33: As before, we get the active spreadsheet, make a copy, get the sheet “All” and activate it.
Line 34: This time, let’s get the total number of sheets in our new spreadsheet. We use getNumSheets() and we’ll store the number in the variable numOfSheets.
Line 35: Now, let’s move that sheet to the number in numOfSheets, i.e. 5, as there are 5 sheets.
Run the code, and you’ll see the new spreadsheet in your My Drive. As before, we can see that the “All” sheet is in the last position.
This is a useful method, not only to position the sheet at the end but to move it to other positions in relation to the end. For example, we could position it in the penultimate position, just by subtracting one from the number of sheets, i.e. (numOfSheets-1).
Example 5 – Inserting and deleting sheets
In this final example, we’re going to insert a sheet at the end and delete the first sheet in a spreadsheet. We’re also going to see how we can get a sheet by its position, not just by its name.
Lines 39-41: We get the active spreadsheet and make a copy.
Line 42: Let’s get the total number of sheets in our spreadsheet, using getNumSheets() and store the number in numOfSheets.
Line 43: Let’s insert a sheet at the end. To do so, we use the insertSheet() method and then add the variable numOfSheets in the brackets.
Line 44: To delete the first sheet, first we need to get the sheet. Here, we’re going to get the sheet by using its position. We get the spreadsheet and then use getSheets() and in square brackets put the position we want, so in this case, 0. It’s 0 as we’re using an array. getSheets() actually gets all the sheets in the spreadsheet and stores them in an array, which we can access just by stating the array position.
Line 45: Now, we can delete that sheet by using deleteSheet().
As we can see, it’s created the spreadsheet and in the spreadsheet it’s inserted a new sheet called “Sheet2” and deleted the sheet called “Sheet1”.
You can see how easy it is to use the Spreadsheet class to copy and name spreadsheets, and move, insert, and delete sheets.
Click here to make a copy of the spreadsheet and the code within it.
You can find the code on here at GitHub.
Link to Spreadsheet Class page.
[/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]