Well, it’s a rainy day here travelling in Romania, so time for a post.
When creating a Google Apps Script’s I often find I am creating new folders and files in specific locations on Google Drive after, say, generating a report or something.
A Note on Folders in Google Drive
All files and folders in Google Drive are allocated a unique key that identifies them.
The file location and all the data about the file is mapped to this ID. This means you can have as many folders or files with the same name even in the same directory without a duplicate error being thrown because they all have their own unique ID for their URL.
More often than not, I know the parent folder that I want to put my sub folders in. This means I can get the parent folder’s ID and use that as my starting point to add sub folders. To do this we use the DriveApp class.
Below are three useful functions for creating folders.
Simple – Create a folder under the Parent folder ID – Duplicates are not checked and there can be multiple sub folders with the same name but all have their own unique id.
Medium – Create a folder only if that folder name does not exists in the Parent folder – No folder is created if the folder already exists.
Hard-ish – Create a folder. If the name exists, add a counter to the name – If the file already exists then add a counter to the end of the file name.
All the functions will take two arguments: folderID – the unique id of the parent folder and folderName – the name you want to call your new folder.
The start() function will simply grab the two variables for the folderID and folderName and run the folder creation function. This is to simulate using the functions in your code.
Feel free to read what you need. I try and write these for a wide range of coding skill in mind.
One of the weekly tasks in my day job as an academic administrator is to look at a number of ‘modules’ or courses that our college is running and complete some analysis and crosschecking. Each week the students complete a ‘unit’. However, during some quarters, not all modulesare doing the same unit. Before I can run my code I need to determine what modules are running and what units we are up to for me to run my automated code.
To do this I created a dialog box when the code is run from the add-on bar. In a few clicks, I can then choose the relevant modules and units and then run the selected code.
Let’s take a look at what the dialog box looks like:
Upon “Submit”, the dialog box returns an array of objects of checked values from the radio buttons that can be uses in the server-side Google Apps Script.
Sometimes, when you are working on a shared Google Sheet you might want to hide a row based on a cell value.
For example, perhaps we don’t want to see row information of orders that have been paid for. Take a look at my D&D miniatures wholesale orders sheet (image below). I know I don’t need to follow up the orders that have been paid, marked with a ‘Yes’ in column F, so I don’t want to see them on my sheet.
Let’s say you want to print out a list of expenditures by department and you want each department to start on a new page. You’ll also want to keep the same header for each page.
You might want to export a list of grades by class number. You sort the grades by class and then export the list with each class starting on a new page with a header and footer.
Sections to Sheets can help you achieve this quickly by creating a new Google Spreadsheet and separating each selection and putting it into a new Sheet(tab) with or without headers and footer. This will enable you to quickly export or print your sheets.
Have you ever copied and pasted something in Google Sheets only to be frustrated with the fact that it stubbornly refuses to paste the column widths? I mean, everything else is perfect, the formatting, the formulas the comments, they all were pasted across just fine. However, that dastardly column width just does not budge.
I was working on a Google Apps Script project lately in Google Sheets that set up parameters in a sidebar and then ran the process once the user clicked the “Submit” button. The problem was that the process was taking a while and that “Submit” Button was ripe to be clicked multiple times by the impatient user, before the server-side code could even finish it’s operation.
To fix this I needed to disable the submit button once it had been clicked and then enable it again once the server-side process was complete. Here, I need to:
Do something awesome with it server-side.
Upon the completion of the server-side awesome, call back to the html file and enable the button again.
I have a very simple Google Sheets Side Bar with a “Submit” and “Cancel” button. When the “Submit” button is clicked it calls the function submittington (can he get any more creative? No. No he can’t).
This function then disables the “Submit” button sends a variable to the client side code.gs and displays it stylishly in the sheet multiple times for the users viewing pleasure. After the code.gs function is executed, it calls back to the client-side submittington function and enables the button.
Then, what’s the trick?
The thing is, the explanations were a bit vague for me to work out clearly so I really needed to create an example of my own to work through how to use it. The example below breaks down the steps to create file relationships to make your code look neater. It also dives into some uses of template statements in html.
The Final Result
Imagine that you have a Google Sheet that you have sorted by a certain column. You might be sorting by the surname of your sales team, class sections or regions. To make the sheet easier to read for your team, you want to alternate the background colors after each category in your sort column is complete.
I have the following list of numbers in column 1 and 2. I have sorted these numbers by my Grouping Column of planets in column 3. After each grouping, I have alternated the background color to make the transition easier to read.
Quite often I will need to get the range of each category in an item and do something with it in Google Sheets. For example, I work in education, I will often have rows of students that are categorized by class sections. I will then be asked to do something like those sections like put each section of students in their own sheet or set alternating colors for each section to make the sheet easier to read.
Alternatively, you may want to grab sales data by region or sales items by a particular category and work with them in Google Apps Script.
Let’s say we want to get the range values of the follow sheet by planets. We will be categorizing our data by the Grouping, column C.
First we don’t want to take into account the headers on the first row. Our first grouping will be Mars, followed by Jupiter, Uranus and Mercury. We want to know which column that each category starts on and how many of that category there are.