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, Romania 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 are 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 subfolders in. This means I can get the parent folder’s ID and use that as my starting point to add subfolders. 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 subfolders with the same name but all have their own unique id.
  • Medium – Create a folder only if that folder name does not exist 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.

Continue reading “Google Apps Script – How to Create Folders in Directories with DriveApp”

Google Apps Script – Getting Input Data From a Dialog Box in Google Sheets (Updated January 2022)

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 dialogue 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 dialogue box looks like:

Dialog Form in Google Apps Script

Upon “Submit”, the dialogue box returns an array of objects of checked values from the radio buttons that can be used 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 (Updated January 2022)”

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

Google Sheets, Google Apps Script, onEdit

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 for export and printing.

You can find Sections to Sheets at the Google Workspace Marketplace.

 

Google Workspace Marketplace badge

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: SpreadsheetApp, copyTo

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.”