Bulk create Google Drive folders with a sub-folder

The following Google Apps Script is designed to bulk create Google Drive folders along with a sub-folder from data given in a Google Sheet (eg to name the folders and add permissions).

This tool and blog post is largely a continuation of this one for bulk creating Google Drive folders. The main difference being that there is an extra column to provide the name of your sub-folder. Note that the tool will try to create a sub-folder regardless of what is entered - as I was just aiming to create a version for the many requests I got from the previous blog post from people who wanted a sub-folder.

Bulk create Google Drive folders from a Sheet of data

Concatenation

Create those useful descriptive folder/file names so items are easier to search for in the future. Concatenate will let you combine values that may exist in different columns in another spreadsheet for example, or take those folder names and prepend/append words around them. Concatenate guide webpage here.

Download

Download the Bulk create Google Drive folders with a sub-folder here. Please use 'File' > 'Make a copy' for your own version.

 

	/*
	This overall script is designed to bulk create Google Folders from data within a Google Sheet
	and add relevant permissions (if applicable).
	*/
	function getSpreadsheetData() {
	// Log starting of the script
	logEvent('Script has started');
	// get current spreadsheet
	var ss = SpreadsheetApp.getActiveSpreadsheet();
	// display Toast notification
	ss.toast('Script has now started', 'Start');
	// get TimeZone
	var timeZone = ss.getSpreadsheetTimeZone();
	// get Config sheet
	var configSheet = ss.getSheetByName('Config');
	// get Parent Google Folder ID for storing created Folders in
	var destinationFolderId = configSheet.getRange(1, 2).getValue();
	Logger.log('destinationFolderId is: ' + destinationFolderId);
	// get 'Edit' permission Flag
	var permissionFlag = configSheet.getRange(3, 2).getValue();
	logEvent('Add Permissions option is: ' + permissionFlag);
	// get 'Folder Link' column number
	var folderLinkCol = configSheet.getRange(5, 2).getValue();
	Logger.log('folderLinkCol is: ' + folderLinkCol);
	// get 'Permissions Added?' column number
	var permAddedCol = configSheet.getRange(7, 2).getValue();
	Logger.log('permAddedCol is: ' + permAddedCol);
	// get Data sheet
	var dataSheet = ss.getSheetByName('Data');
	// get all data as a 2-D array
	var data = dataSheet.getDataRange().getValues();
	// create a name:value pair array to send the data to the next Function
	var spreadsheetData = {ss:ss, timeZone:timeZone, dataSheet:dataSheet, destinationFolderId:destinationFolderId,
	permissionFlag:permissionFlag, folderLinkCol:folderLinkCol, permAddedCol:permAddedCol, data:data};
	// run Function to create Google Folders
	var createFoldersFlag = createFolders(spreadsheetData);
	// check success status
	if (createFoldersFlag) {
	// display Toast notification
	ss.toast('Script complete', 'Finished');
	}
	else {
	// script completed with error
	// display Toast notification
	ss.toast('With errors. Please see Logs', 'Finished');
	}
	// Log starting of the script
	logEvent('Script finished');
	}
	/*
	This Function loops through each row and initiates the creation of a Google Drive Folder
	along with a sub-folder.
	Permissions are added if applicable.
	*/
	function createFolders(spreadsheetData) {
	// extract data from name:value pair array
	var ss = spreadsheetData['ss'];
	var timeZone = spreadsheetData['timeZone'];
	var dataSheet = spreadsheetData['dataSheet'];
	var destinationFolderId = spreadsheetData['destinationFolderId'];
	var permissionFlag = spreadsheetData['permissionFlag'];
	var folderLinkCol = spreadsheetData['folderLinkCol'];
	var permAddedCol = spreadsheetData['permAddedCol'];
	var data = spreadsheetData['data'];
	// get last Row number
	var lastRow = dataSheet.getLastRow();
	// start of loop to go through each row in turn ********************************
	for (var i = 1; i < lastRow; i++) {
	// extract values from row of data so easier to work with
	var folderNameP1 = data[i][0];
	Logger.log('folderNameP1 is: ' + folderNameP1);
	var folderNameP2 = data[i][1];
	Logger.log('folderNameP2 is: ' + folderNameP2);
	var subFolderName = data[i][2];
	Logger.log('subFolderName is: ' + subFolderName);
	var permissionEmail = data[i][3];
	Logger.log('permissionEmail is: ' + permissionEmail);
	var folderLink = data[i][4];
	Logger.log('folderLink is: ' + folderLink);
	// check Folder Link column is empty before proceeding, so no existing Folder
	if (folderLink == '') {
	// create a name for the new Parent Folder
	var folderName = folderNameP1 + ' - ' + folderNameP2;
	Logger.log('Folder name is: ' + folderName);
	// display Toast notification
	ss.toast(folderName, 'Creating Folders');
	// run Function to create Parent Folder and sub-folder
	var folderDetails = createFolder(folderName, destinationFolderId, subFolderName);
	// check new Folders created successfully
	if (folderDetails) {
	// extract Url/Ids
	var newFolderUrl = folderDetails['newFolderUrl'];
	var newFolderId = folderDetails['newFolderId'];
	var subFolderID = folderDetails['subFolderID'];
	// set Folder Link cell using new Parent Folder Url
	var newFolderLink = '=HYPERLINK("' + newFolderUrl + '","' + folderName + '")';
	dataSheet.getRange(i + 1, folderLinkCol).setFormula(newFolderLink);
	// check if Permissions need adding - set in 'Config' sheet
	if (permissionFlag == 'Yes') {
	// run Function to add Folder permissions
	var currentRow = i + 1;
	var addPermissionsFlag = addPermissions(timeZone, dataSheet, permissionEmail,
	newFolderId, subFolderID, currentRow, permAddedCol);
	// if problem adding Permissions return for status message
	if (addPermissionsFlag == false) {
	// display Toast notification and return false flag
	ss.toast('Problem adding Permissions to: ' + folderName, 'Error');
	return false;
	}
	else {
	// no problem adding permissions
	}
	}
	else {
	// do nothing as permissions not required
	}
	// flush spreadsheet to update each row as completed
	SpreadsheetApp.flush();
	}
	else {
	// write error into 'Permission Added?' cell and return false value
	dataSheet.getRange(i + 1, folderLinkCol).setValue('Error creating folder. Please see Logs');
	// new Folder not created successfully
	return false;
	}
	}
	else {
	// Folder Link column not empty so do nothing
	}
	}// end of loop to go through each row in turn **********************************
	// completed successfully
	return true;
	}
	/*
	Function to create new Google Drive Folder and return its Url/Id.
	*/
	function createFolder(folderName, destinationFolderId, subFolderName) {
	try {
	// get destination Folder
	var destinationFolder = DriveApp.getFolderById(destinationFolderId);
	}
	catch (e) {
	logEvent('Error getting destination folder: ' + e + e.stack);
	var destinationFolder = false;
	}
	// proceed if successfully got destination folder
	if (destinationFolder) {
	try {
	// create new Folder in destination
	var newFolder = destinationFolder.createFolder(folderName);
	// get new Drive Folder Url/Id
	var newFolderUrl = newFolder.getUrl();
	var newFolderId = newFolder.getId();
	// create sub-folder and get its Id
	var subFolderID = DriveApp.getFolderById(newFolderId).createFolder(subFolderName).getId();
	// return Folder details for logging in Sheet and adding permissions
	var folderDetails = { newFolderUrl: newFolderUrl, newFolderId: newFolderId, subFolderID: subFolderID };
	return folderDetails;
	}
	catch (e) {
	logEvent('Error creating new Folders: ' + e + e.stack);
	return false;
	}
	}
	else {
	// return false as unable to get destination folder
	return false;
	}
	}
	/*
	Function to add 'Edit' permission to each Folder from the provided
	email address(es).
	*/
	function addPermissions(timeZone, dataSheet, permissionEmail, newFolderId, subFolderID, currentRow, permAddedCol) {
	// split up email address array to be able to loop through them separately
	var emailAddresses = permissionEmail.split(', ');
	Logger.log('emailAddresses array is: ' + emailAddresses);
	// get length of array for loop
	var emailAddressesLength = emailAddresses.length;
	try {
	// get Google Drive Folder
	var newFolder = DriveApp.getFolderById(newFolderId);
	var newSubFolder = DriveApp.getFolderById(subFolderID);
	}
	catch (e) {
	logEvent('Error getting destination folder: ' + e + e.stack);
	var newFolder = false;
	}
	// proceed if successfully got destination folder
	if ((newFolder) && (newSubFolder)) {
	// loop through each email address and add as 'Editor' *******************
	for (var i = 0; i < emailAddressesLength; i++) {
	var emailAddress = emailAddresses[i];
	Logger.log('emailAddress for adding permission is: ' + emailAddress);
	try {
	// add 'Edit' permission using email address
	newFolder.addEditor(emailAddress);
	newSubFolder.addEditor(emailAddress);
	var addEditor = true;
	}
	catch (e) {
	logEvent('Error adding Editor: ' + e + e.stack);
	var addEditor = false;
	}
	if (addEditor) {
	// write timestamp into 'Permission Added?' cell
	var date = new Date;
	var timeStamp = Utilities.formatDate(date, timeZone, "dd/MM/yy @ HH:mm:ss");
	dataSheet.getRange(currentRow, permAddedCol).setValue(timeStamp);
	}
	else {
	// write error into 'Permission Added?' cell and return false value
	dataSheet.getRange(currentRow, permAddedCol).setValue('Error adding Editor. Please see Logs');
	return false;
	}
	}
	// loop through each email address and add as 'Editor' *******************
	}
	else {
	// write error into 'Permission Added?' cell and return false value
	dataSheet.getRange(currentRow, permAddedCol).setValue('Error getting folder. Please see Logs');
	// return false as unable to get Google Drive Folder
	return false;
	}
	// return true as all permissions added successfully
	return true;
	}
	/*
	Function to output messages to the 'Log' sheet.
	Can be called anywhere else in script.
	*/
	function logEvent(action) {
	// get the user running the script
	var theUser = Session.getActiveUser().getEmail();
	// get the relevant spreadsheet to output log details
	var ss = SpreadsheetApp.getActiveSpreadsheet();
	var logSheet = ss.getSheetByName('Log');
	// create and format a timestamp
	var dateTime = new Date();
	var timeZone = ss.getSpreadsheetTimeZone();
	var niceDateTime = Utilities.formatDate(dateTime, timeZone, "dd/MM/yy @ HH:mm:ss");
	// create array of data for pasting into log sheet
	var logData = [niceDateTime, theUser, action];
	// append details into next row of log sheet
	logSheet.appendRow(logData);
	}
	/*
	This Function creates a menu item to run this script.
	*/
	function onOpen() {
	SpreadsheetApp.getUi()
	.createMenu('Admin')
	.addItem('Create folders', 'getSpreadsheetData') // label for menu item, name of function to run.
	.addToUi();
	}

view raw Bulk create Google Drive folders with a sub-folder.gs hosted with ❤ by GitHub