In this post, we’re going to look at the G Suite services, the Google documentation that’s available to help you, and then focus on one particular area, SpreadsheetApp to then create some spreadsheets. We’re also going to see the really useful for in loop in action. It’s important to have an understanding of how the different parts of Apps Script fit together. So, first let’s go to the Google documentation, which can be found at:
https://developers.google.com/apps-script/reference/spreadsheet
On the left, we have a list of the G Suite services available. As you can see below, we can work directly with most of the main apps within G Suite, from calendars to the spreadsheets. As your codes get more complex, you will start working with various services at the same time. So, for example, we could have data in a spreadsheet and create a Google Doc from it, or we could use that data to create a Google Form and to create an event on the calendar.
G Suite Services
Spreadsheet Service
In this post, we’re going to focus on the Spreadsheet service, which will provide an example as to how all this works together. As we can see from the description below, the Spreadsheet service allows us to work with Google Sheets.
Under the Service, we have the Classes. These are basically sub categories of the service. The first time you look at the list below, the number of different classes scares you a little and you wonder where to start! But when you start looking at the details, it’s not that difficult. For example, in the list below, half of the classes just refer to things you can do with charts.
The main four you will use time and time again are at the bottom of the list.
There is a hierarchy to these:
SpreadsheetApp > Spreadsheet > Sheet > Range
In general terms, if you want to edit some cells in a spreadsheet, you need to tell the code which spreadsheet you’re working with, which sheet you want to edit, and then which range.
Let’s focus on just the first of those classes, SpreadsheetApp. On the menu on the left, click on Spreadsheet (if not already open) and SpreadsheetApp.
SpreadsheetApp
This will open the documentation for the SpreadsheetApp. As we can see from the description below, this class is mainly for creating and opening Google Sheets.
Underneath classes we have methods. These are the specific instructions that tell the code to do a specific job. On the right of the page, we can see a list of the methods available under the SpreadsheetApp class. The names of them usually give a good indication as to what they do. If you want to jump to information about a particular method, just click on the method in this list and it will take you to the information for that method.
Here, I’ve clicked on the method create(name):
Sometimes, it gives you an example showing how to use it. Plus, the parameters it needs. So, in this case the name in the brackets needs to be a string. Finally, at the bottom it tells you what is returned. Here it’s a new spreadsheet.
OK, so now we have an overview of services, classes, and methods, let’s jump in and work our way through some examples, showing how the SpreadsheetApp works.
Creating menu – SpreadsheetApp.getUi
First, let’s create a menu to allow us to run all the examples from it without having to go back to the script editor every time. Conveniently, this will also show one of the methods linked to the SpreadsheetApp class.
Line 2: Use the name onOpen() to trigger it to run when the user opens the spreadsheet.
Line 3: Here, we use the SpreadsheetApp class and the getUi() method as we’re editing the user interface of the spreadsheet.
Line 4: We create a menu using createMenu() and give it a name.
Lines 5-11: Then, we add the items in the menu using additem(). In the brackets add, the name of the items and the function names they correspond with.
Lines 12-13: Finally, we add all this to the Ui using addToUi() and end it with a semi-colon and close the function with a curly bracket.
As I’ve mentioned in an earlier post, it’s useful to store this chunk of code in a separate script file, as you’ll probably be using it for various projects, so you’ll want to just copy and paste it in and then edit it to save you time.
Global variables
In some of the following examples we’re going to be using the same spreadsheet and the same sheet, so instead of repeating the code in various examples, we can write them outside the functions, so that the same code can be used in different functions. These are called global variables and this just means that the variables are available to all the functions in this piece of code, not just within a particular function as is often the case. So, we’re going to set up two variables, one for the active spreadsheet and one for the active sheet.
Line 16: Get the active spreadsheet using the SpreadsheetApp class and the getActiveSpreadsheet() method amd store it in the variable ss.
Line 17: Get the active sheet from the variable we just set up, ss, along with getActiveSheet() and store it in the variable sheet.
Example 1 – Creating a new spreadsheet
Let’s start with an easy example. We’re going to create a new spreadsheet and call it “New Spreadsheet1”.
Line 20: We set up the function and call it example1.
Lines 21-22: Start with SpreadsheetApp then use the create() method to make a new spreadsheet. In the brackets, add the name you want to give the file. Then close the function.
Run the function example1 and we can see in our My Drive, the newly created spreadsheet:
Example 2 – Creating a spreadsheet with limited rows & columns
We’re going to create another spreadsheet but this time we’re going to add some extra information in the brackets to set it up with a fixed number of rows and columns.
Line 25: Set up the function called example2.
Line 26: We again use the create() method, but this time there are 3 pieces of information within the brackets: spreadsheet name, number of rows, number of columns.
As we can see it created a spreadsheet as before.
And we have set it up with a fixed number of rows and columns in it.
Example 3 – Creating a spreadsheet with a name from the sheet
Quite often the name we want to give the newly created spreadsheet is taken from some source of data. Here, we’re going to get the name of the spreadsheet from one of the cells in the original spreadsheet.
Line 30: Set up the function example3.
Line 31: First, we need to get the name from the cell on the sheet. We get the sheet (from the global variable sheet we set up earlier), get cell A1 using getRange() and get its value using getValue(). Then we store it in the variable name.
Line 32: Then we use the create() method we used before, but this time use the variable name in the brackets.
As we can see, it has named the new spreadsheet with the name from cell A1.
Example 4 – Creating multiple spreadsheets with different names
This time let’s take it a step further and create 3 different spreadsheets using names from the original sheet. We’ll use a for in loop to make the different spreadsheets.
Line 37: First, we get the names of the spreadsheets from the sheet. These are stored in cells B1 to B3. As there is more than 1 name, we use getValues() to store the names in an array and we’ll call that array names. Here’s what the content of that array looks like:
Now, we start the for in loop. The beauty of the for in loop is that it will automatically work out how many items are in your array and loop that many times around. So, in our names array we have 3 names, so it will go around the loop 3 times, and do whatever we put in between the curly brackets, 3 times. I.e. in this case, it will create 3 different spreadsheets.
The syntax for a for in loop is:
for (counter variable in array name) {
Do something x the number of items in the array
}
Line 38: Here, we’ll use i as the counter and we add names as it’s the array we want to loop through.
Line 39: Each loop we want to create a spreadsheet, so we use the SpreadsheetApp and create() as before. This time in the brackets, we refer to the information in the array name and we need to move position within that array each time we go around. So, the first name will be at position 0 (name[0]), then position 1 (name[1]), and so on. The counter i automatically starts at 0.
Lines 40-41: Then we close the loop and the function.
As we can see, it’s created the 3 spreadsheets and given each one a different name, which has been taken from the cells B1 to B3 in our original sheet. All with very few lines of code!
Example 5 – Getting data from one spreadsheet and adding it to another (URL)
Here, we’re going to see how easy it is to get data from one spreadsheet and to add it to another spreadsheet.
Line 45: First, we need to get the text in cell C1 in the first spreadsheet. Use getRange() and getValue() to get that, using the global variable sheet again. Then store it in the variable text.
Line 46: Now, we need to get the other spreadsheet. We do that by ‘opening’ it. Note, this doesn’t open it for the user but it does on the server-side and tells the code that we are now working with this new spreadsheet. Here, we’re going to open it by its URL. So, use the openByUrl() method and add the complete URL in the brackets between quote marks. Store the spreadsheet in newSS.
Line 47: We then use newSS to tell the code that’s the spreadsheet we want to use, then we get the active sheet and add the text using getRange() and setValue(). This will add the text in cell A1 in our new spreadsheet.
Example 6 – Getting data from one spreadsheet and adding it to another (ID)
This is very similar to the previous example but this time we’re going to use the ID of the new spreadsheet and not the URL. The ID is the part with random looking letters and numbers after the /d/ and before the /edit parts.
Personally, I always try to always work with the ID and not the URL as the format is cleaner to work with.
As we can see, in cell A2 it’s added the text from the original spreadsheet. The text in cell A1 is from the previous example.
Example 7 – Creating multiple spreadsheets with different names and different pieces of text
Finally, let’s create 3 different spreadsheets each with its own name, which we will get from our original sheet. Plus, we will add a different piece of text in each one. This is just an expansion of what we’ve seen so far and you’ll see it’s really easy to do.
Line 59: First, let’s get the list of spreadsheet names from cells D1 to D3 and store it in the array ssNames.
Line 60: Then, let’s get the list of texts we’re going to add into the spreadsheets and store them in the array texts.
Line 62: We’re going to loop down the both lists so let’s set up a for in loop. As both our lists are the same length, we can just use one of them to count the number of times we go around the loop. Here, we’re using the ssNames array.
Line 63: First, we want to create the spreadsheets and give it a name from the array ssNames. As before, we control which name we’re using by the counter i. So, for example, spreadsheet 1 will use the first name in the array. We store the spreadsheet in the variable spreadsheet.
Line 64: Then, we call that spreadsheet and get its active sheet and cell A1, and add the text from the text array at the current position. So, for example, in spreadsheet 1, we’ll use the first piece of text.
As we can see, it’s created the 3 different spreadsheets, given them individual names, and added a different piece of text in each one.
The code can be found here on GitHub.
You can make a copy of the file here.
Link to the google reference page on SpreadsheetApp
More info on for in loops
Want to learn more about Google Workspace and Apps Script? The books below are available on Amazon. Just click on a book! (Affiliate links).
a
1 comment
Comments are closed.