Google Apps Script – How to Find the Folder ID of a Non-Unique Folder Using File Path Names

Google Apps Script

sighisoara Google Apps Script Tutorial
This tutorial comes from a comfy spot in the center of Sighisoara.

Well, that title is such a mouthful. So I think it needs some explaining…

I’ve recently come across a tasks that requires me to access a particular folder in a directory based on the information in a google Sheet. This means:

  1. I don’t have the folder or the sub-folder ID.
  2. I can get the directory path information from information supplied in a Google Sheet.

Basically what I needed to do was create a Report maker and store it in the following directory path:

My Drive(root) >> Year >>  Quarter >> Unit+ "Report"

For example:

MyDrive >> 2018 >> Q4  >> Unit 4 Report

Boring!!!! Take me to the code!!!

The Problem

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.

So, what to do..?

The Solution

Continue reading “Google Apps Script – How to Find the Folder ID of a Non-Unique Folder Using File Path Names”

Google Apps Script – How to Create Folders in Directories with DriveApp

Google Apps Script – DriveApp

Well, it’s  a rainy day here travelling in Romania, so time for a post.

coding in Romania
Relaxing in a cafe in Cluj Napoca, Romaina on a Rainy. I love this country!!!

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.

Google Drive Folder ID

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.

Getting Started

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.

Simple – Create a folder under the Parent folder ID

Here, we are going to simply add a folder. It doesn’t matter if the folder already exists, we’ll just create another one.

The Code

When the start() function is run on line 8 it creates a variable for the FOLDER_ID and the NEW_FOLDER_NAME. On line 14 we add these two variables to the arguments for the function createFolderBasic .

createFolderBasic(folderID, folderName)

On line 3 of this function we find the parent folder we want to add the sub folder to. We do this by calling the DriveApp class with the getFolderById() method inputting the folderID here. We will keep this in the variable, folder, to be used on the next line.

Line 4 then creates the newFolder by getting the folder variable and applying the createFolder method to it with the argument folderName.

Finally, on line 5 we return the newFolder by id so we can use it later. We have logged the folder ID  in the start() function  so we know it is working.

Each time you run this code, you will get a new  file with the same file name but a different ID in the URL.

Simple Folder Creator - Google Apps Script - DriveApp

Medium – Create a folder only if that folder name does not exists in the Parent folder

In this function we will only create a folder if the folder does not exists. We will return the folder id of either the new folder or the existing folder.

The code

In this example, the start() function is the same. The only change is the function name that will be called is createFolder()on line 33.

createFolder()

First, on line 3 we get the folder ID of the parentFolder using DriveApp.getFolderById(folderID). On the next line we use this parentFolder variable to get the child folders in that directory with the getFolders() method – more on this in a moment. We will put this generator of folders in in the subFolders variable.

Next, on line 5 we create the doesntExists variable and set it to true. This basically says that if the file with the name we have set does not exists then we will say that the statement is true.

the getFolders() method creates a generator listing all the folder in that directory. So we will need to iterate through this generator of folders to check if a folder with the folderName we want to add.  Fortunately getFolders() takes two further methods – hasNext() which, in conjunction with a while loop states that while there is another folder to look at in the list, do something (line 9).

While in the erh… while loop we want to look at each folder so we create the variable folder to look at the next() folder in the loop.

On line 13, we want to check if the file with the same name as our folderName exists. If it does we change our doesntExists variable to false.  We then make our newFolder variable the name of the folder we have found in the loop that contains a match for the folderName and return the id for this folder so we can use it in our code at a later time.

Alternative if we can’t find the file in our subFolder generator (line 20), then we use the createFolder(folderName) method and then return the newFolder ID.

This time, even if we run the script it will only generate a new folder if the folder does not exists in the Parent folder.

Hard-ish – Create a folder. If the name exists, add a counter to the name

This function is a little bit like how Windows deals with multiple file names. The function will create a folder even if the folder exists, but if the folder does exists then it will rename the folder with a counter at the end. For example say we want to create a folder with the name: banana. If that name already exists then we will rename it to: banana(1).

The problem is, what if we already have a file with the name banana(1)? What if we have a file with the name: banana(2)? We will have to deal with this in our code.

Let’s take a look:

The Code

Nothing changes in our start() function except that we use the new function name createFolderWithChange(FOLDER_ID, NEW_FOLDER_NAME).

createFolderWithChange()

We start this function by setting some variables.

Again we find the parentFolder with DriveApp's getFolderById(folderID) (line 3). We then get the subFolders as a generator. Create an exists variable that is set to false (thought I’d change it up a bit 😀  Yagi, you so kwazy!) .  Set an empty newFolder variable to put our new folder ID in once we get it. This time we will also add the folderArray variable to store our list of folders in our parent folder (yeah, we gotta do it a little different this time).

Line 10 starts our iteration through our subFolders generator. It essentially says that while there is another folder in the list (hasNext())then get the next() folder and do something with it. On line 11 we push the folder name to the folderArray variable. We will need to convert these  to string so that we can compare them against our folderName.

In our next step starting on line 15 we want to do three things: 1. If our folderName is in our folderArray list then, 2. check if our folderName also exists with a number counter and 3. check each counter and then add the next consecutive counter as the newFolder. For example if our folderName, “The New Folder” exists then search the array for “The New Folder(1)”, “The New Folder(2)”, etc until we run out and a add the file “The New Folder(#)”.

On line 15 we use the indexOf method to determine if the folder exists with the folderName. If it doesn’t we move to line 27 and simply create a newFolder and return the folder ID for us to work with. If it does exists, we then change exists to true, because, you know, it now is and then set the counter, num  that we will use at the end of our folderName(lines 16 & 17).

Next, we run another while loop which states, while the folderName exists (or is true), then do something (line 18). That something is another if statement that asks if the folderName plus the num counter is in the folderArray simply add 1 to num.

Alternatively, if there is no folder with the same name as folderName plus num (line 21), set exists to false (line 22) and create a newFolder with the next num in the list(line 23). We then return the id of the newFolder for us to use in our code (line 24).

Folder Creator No Doubles - Gogle Apps Script DriveApp

Conclusion

So there you have it. Three approaches to use Google Apps Script’s DriveApp to create folders when you have the id of the Parent Folder. Give it a try.

 

More on DriveApp:

 

 

 

 

 

 

 

 

 

 

 

 

Google Apps Script – Getting Input Data From a Dialog Box in Google Sheets

Google Apps Script, Jquery, Javascript, HTML

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 modules are 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:

Dialog Form in Google Apps Script

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.

In this tutorial we will look at the following :

Continue reading “Google Apps Script – Getting Input Data From a Dialog Box in Google Sheets”

Google Apps Script – How to Automatically Generate a Time From a Four Digit 24 hour Time in Google Sheets.

Google Apps Script, Google Sheets

On a recent board post a Google Sheets user wanted to change a four digit number (for example, 1230) to a time, like 12:30, in the same cell that the item was entered.

Unfortunately the user was not in a position to change the starting values, so they were left with the 4 digits.

There are two ways of doing this with varying levels of complexity:

 

  1. The Google Sheets Formula Approach
  2. The Google Apps Script onEdit Approach

Continue reading “Google Apps Script – How to Automatically Generate a Time From a Four Digit 24 hour Time in Google Sheets.”

How to Hide a Row based on a Cell Value in Google Sheets with Filter or Google Apps Script.

Google Sheets, 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.

We want to hide the "Yes" - Google Sheets

We can hide these two ways:

  1. Create a filter.
  2. Google Apps Script onEdit() trigger.

Continue reading “How to Hide a Row based on a Cell Value in Google Sheets with Filter or Google Apps Script.”

Sections to Sheets – Free Google Add-on That Separates Column Sections Into Individual Sheets for Page-by-Page Export or Printing

What is Sections to Sheets?

Sections to Sheets is a free Google Sheets Add-on that separates rows by column sections and puts them in individual pages or sheets in a spreadsheet in preparation export and printing.

You can find Sections to Sheets at the Google Chrome Webstore.

Chrome Web Store Badge

Example 1

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.

Example 2

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.

Continue reading “Sections to Sheets – Free Google Add-on That Separates Column Sections Into Individual Sheets for Page-by-Page Export or Printing”

Google Apps Script – Copy and Paste Cells While Retaining Column Width in Google Sheets.

Google Apps Script

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.

copied Google Sheet
When you want to copy and paste this…
Paste without column widths.
…but you get this.

Continue reading “Google Apps Script – Copy and Paste Cells While Retaining Column Width in Google Sheets.”

Google Apps Script – Disable Enable Submit Button in Sidebar

Google Apps Script, Javascript, Jquery, HTML

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:

  1. Disable the button and get the data from the client-side Javascript inside my sidebar’s html file.
  2. Do something awesome with it server-side.
  3. Upon the completion of the server-side awesome, call back to the html file and enable the button again.

The Example

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.

Behold!!! The example:

Disable Enable Button in Sidebar Google Apps Script

Continue reading “Google Apps Script – Disable Enable Submit Button in Sidebar”

Google Apps Script – How to create Javascript and CSS files for a Sidebar Project in Google Apps Script

Google Apps Script-templates, Javascript, CSS

So, I started working on a larger Google Sheet Sidebar project in Google Apps Script recently and I quickly realised that it was going to be a mess if I didn’t separate my Javascript, CSS and even some of my HTML into separate files. However, if you have ever noticed in the script editor that there is no way for you to add script or style files to the code. Your only two options are Google Script files *.gs and Html files *.html.

Project File Types - Google Apps Script

Then, what’s the trick?

The trick is to create separate html files for your CSS and Javascript and include or import them into your main html file. Unfortunately, you can’t do this with the standard:

HtmlService.createHtmlOutputFromFile(filename)

Instead, we will need to use the templating method:

html = HtmlService.createTemplateFromFile(filename)

We will  get into a little more detail on templates later in this tutorial.

My main reference for this was the Google Apps Script UI best practice guide, and you will see code snippets of the first example there that I have modified for my own example.

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

The goal of our little sidebar project will be to display a sidebar with colored paragraph text, a list generated with Javascript and a randomly assigned body page the contain the text “Body 1” or “Body 2”.

Take a look at the demo:

Sidebar Example - Google Apps Script

Continue reading “Google Apps Script – How to create Javascript and CSS files for a Sidebar Project in Google Apps Script”

Google Apps Script – How to Alternate Colors in an Ordered List by Column Category. 

Google Apps Script and Google Sheets

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.

The Example

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.

Alternating color by section - Google Apps Script

The Code

Continue reading “Google Apps Script – How to Alternate Colors in an Ordered List by Column Category. “