Month: February 2017

Multiple FILE Maker

Making multiple copies in Google Drive is a slow, boring task. You select the file you want, make a copy, then have to rename it, then repeat this again and again. Here you’ll learn how to make multiple copies of a file and name them individually, really fast using Apps Script. First, we have a Sheet where we will enter the details. There are three parts. In cell A3 we type the fixed part of the file name want, i.e. The part that is the same in all the copies of the file. In column B, we type the variable parts of the file name, i.e. The parts we want to be unique in each file. Finally, in cell C3 we paste the full URL of the file we want to copy. We then choose Make Multiple Files from the Creator menu. Here are the files it made, all individually named: Obviously, it works best when there are a lot of files to make. THE CODE Let’s go through the code step-by-step: Line 1: First, we set up our function, here I’ve called it makeFiles(). Line 4: Then, we get the current active spreadsheet, using getActiveSpreadsheet(). Line 5: Then, we get the active sheet, using getActiveSheet(). Line 6: Now, let’s get the fixed name. It’s in row 3, column 1 (I.e. A3), so in the getRange() method we add...

Read More

Pimp up your Sheet – Programmatically!

When working with spreadsheets we can spend quite a long time making them look good and if we have to make the same sheets over and over again, that’s a lot of wasted time. Here we’ll look at how you can format a Google Sheet via Apps Script. The focus of this post is to show you some of the common methods available to get Apps Script to format your sheets automatically. Quite often, these would be part of larger program which get the data from somewhere and create a new sheet with it, then format the way you want. However, it could also be used where you want consistency between different sheets. Due to their simplicity, I also think it’s a great place to start if you are new to Apps Script. If you are new, please see my earlier posts, which explain how to open the Script Editor, etc. There are 3 scripts. In the first, we’ll cover the following: adding borders changing the font and size setting the cell wrap status setting the horizontal and vertical alignment setting the number format changing to bold changing the cell background colour deleting unwanted rows and columns adjusting the column to specific widths and to adjust to the text In the second and third scripts, we’ll quickly look at hiding rows and columns, to set up the sheet with the information...

Read More

Book inventory

Here we’re going to make a simple book inventory, where we’ll be able to control the location of the books and also find out where a book is. This uses a mixture of GAS code and Sheets functions. We’ve got 3 sheets: Front page – This is what the user will see and use. In the yellow part, we’ll be able to find out the location of a book from its code. They enter the book number in cell B4 and the current location appears in B5. In the green part, we’ll be able to change the location of a book or in fact up to 10 books all in one go. The user adds the book numbers in cells B11 to B20 and enters the location in B10. Then from the menu they’ll run the program to update the list. Coursebooks – On this page we have titles of the books, level, which part of the set it is (e.g. SB: student’s book), its individual reference number, and its current location. Columns D and E are what we’re going to use. Ref – This page is used to create the drop down menus on the Coursebooks page using data validation. In cell A2, I want to list the titles of the books we have on the Coursebooks page, but without duplicating any. So, I use the UNIQUE function and combine it...

Read More

Automatically emailing info from a form submission

Here we’ll look at how to set up automatic emails, which contain information submitted by the Google Form user. The beauty of this is that the information is sent to you (and others) without you having to do anything and without you having to check the spreadsheet to see if there has been a submission. This is building on the code from my previous post, so the areas which are the same I will only briefly describe here. If you want more details see my previous post. This is the email that we’re going to send automatically: Here’s the full code: Let’s look at it step by step. First, we set up the function and then get the active spreadsheet. Then we get the sheet which contains the information we want, which in this case is on ‘Form Responses 1’. We want to get the values on the last row, which is the latest form submission. There are 3 steps to do this. We need to get the last row on the sheet, the last column and then get the range using that information. Line 10: We get the last row of the form responses 1 sheet (using the getLastRow method) and store it in the variable called lastRow. Line 11: Similar to the lastRow we get the last column (using the getLastColumn method) and store it in the variable...

Read More

Archives

eBooks available

Pin It on Pinterest