Month: May 2017

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

eBooks available

Pin It on Pinterest