Author: bazroberts

Apps Script Basics (10) – Range Class & Triggers

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,...

Read More

Apps Script Basics (9) – Sheet Class

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...

Read More

Apps Script Basics (8) – Spreadsheet Class

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...

Read More

Apps Script Basics (7) – SpreadsheetApp & for in loop

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...

Read More

Apps Script Basics (6) – if, prompt, menu, & onOpen trigger

In this post, we’re going to look at how we can get the computer to react to data in a spreadsheet and to user input. We’re going to look at the following: If, else if, and else statements – to allow the program to make decisions Create your own menu in the spreadsheet to run your programs from Automatically set up the menu using the onOpen trigger Creating a dialogue box to allow the user to enter data using ui.prompt Creating a menu Running scripts from the Script Editor is fine when you’re writing them but you don’t want your user to have to open the Script Editor every time they want to run your code. One of the best and easiest ways is to create a new menu. Here, we’re going to add all the examples to a single menu within our spreadsheet. Open the Script Editor from the Tools menu. Delete the code already in the editor and rename the project. See my earlier post on how to do this. Line 1: Start with the function line and call it onOpen(). It has to be called this as this will tell the computer that you want this function to run every time the spreadsheet is opened. This is called a trigger. Line 2: We need to get the spreadsheet’s user-interface, so we use first the SpreadsheetApp class and...

Read More

Archives

Pin It on Pinterest