Month: June 2017

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

Archives

eBooks available

Pin It on Pinterest