One of my recent projects in Google Apps Script required me to search for a file by name and get its ID. This can be problematic in Google Drive because you can have multiple files of the same name in multiple locations. My solution was to also check the file’s parent folder name as well.
I created a function getFileByName() to handle this. The function takes the following parameters:
fileName – The name of the file you are looking for. (required)
fileInFolder – The parent folder of the file your are searching for. (optional)
The file path would look a little something like this:
getFileByName() returns an object containing :
the ID of the file if the file exists or false if it does not.
the ERROR if there is an error or false if there is not.
The returned object would look like the following:
Google Sheets has a vast library of functions (or formulas) you can use to get your Spreadsheet tasks done. However, there are some instances when you want a specific function that is not available in Google Sheets or want to make a simplified version of some combined function to make life easier for your users. Fortunately, Google has your back with Google Apps Script.
Before we start, Google does a pretty great job explaining the basics of creating a custom function. However, when it comes to explaining how to add all that information that goes into a function when you type it into Sheets it is a bit vague.
Let’s look at the creation process through a recent example I created:
I was working on a Spreadsheet in Google Sheets a few days ago and needed to generate some random codes for my users. To do this, I just used the RANDBETWEEN(start val, end val) function built into Google Sheets.
It goes a little like this. If I want to build a 5 digit random number I would:
But this got me wondering. Is there a function for random Alphabetic strings or Alphanumeric strings or Alphanumeric with Character strings. The answer?
Unfortunately, I could not simply change the last folder name from say, Unit 4 Report to Q4 Unit 4 Report 2018 so it is easily searchable and unique. The other problem is that there are other Unit 4 Reports in other years and quarters so I did not want to accidentally call them instead of the exact one I wanted.
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.