Bulk create Google Drive Folders and add Files

The following Google Apps Script tool was developed to bulk create Google Drive folders with optional files copied in to each one, all from data within a Google Sheet. There is also the option to add specific 'edit' permissions to the newly create Drive folders of which the files would automatically inherit this access level.

The tool is an expansion of the 'Bulk create Google Drive folders 2.0' blog post here, so you may wish to read and watch the video on there first. Also note I have version 2.0 of this tool available now.

Bulk create Google Drive folders and add files, from a Sheet of data

Key Functionality

• Complete the necessary information in the Config sheet before proceeding. Then use the Create folders option from the Admin menu at the top of the Google Sheet.
• Adding permissions is optional - use the Config sheet to change the dropdown as required. If you select 'No' then the usual Google Drive inheritance will occur based on the parent Google Drive folder.
• You can add multiple File IDs into the relevant cell and they can be different for each row. Leaving this cell blank/empty means no files will attempt to be copied into the new folder.
• The original filename will be updated during the copy to append the folder name to the end of it, in order to prevent creating a large number of files with identical names.
• There is a Log sheet to help troubleshoot any errors which may occur when running the tool.

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

Bulk create Google Drive Folders and add Files download here (please use 'File' > 'Make a copy' for your own version).

	/*
	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 overall script is designed to bulk create Google Folders from data within a Google Sheet,
	add any requested Files (if applicable) 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();
	// 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();
	// get 'Permissions Added?' column number
	var permAddedCol = configSheet.getRange(7, 2).getValue();
	// 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,
	the copying of File(s) into it (if applicable), and the necessary permissions (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];
	var folderNameP2 = data[i][1];
	var permissionEmail = data[i][2];
	var fileIDs = data[i][3];
	var folderLink = data[i][4];
	// check Folder Link column is empty before proceeding, so no existing Folder
	if (folderLink == '') {
	// create a name for the new Folder
	var folderName = folderNameP1 + ' - ' + folderNameP2;
	// display Toast notification
	ss.toast(folderName, 'Creating Folder');
	// run Function to create Google Folder and return its Url/Id
	var folderDetails = createFolder(folderName, destinationFolderId);
	// check new Folder created successfully
	if (folderDetails) {
	// extract Url/Id
	var newFolderUrl = folderDetails['newFolderUrl'];
	var newFolderId = folderDetails['newFolderId'];
	// set Folder Link cell using new Folder Url
	var newFolderLink = '=HYPERLINK("' + newFolderUrl + '","' + folderName + '")';
	dataSheet.getRange(i + 1, folderLinkCol).setFormula(newFolderLink);
	// run Function to copy files only if File ID cell not empty
	if (fileIDs != '') {
	// run Function to copy files into new Google Folder
	var copyFileFlag = copyFile(ss, folderName, fileIDs, newFolderId);
	// check status of copyFile Function Flag before proceeding
	if (copyFileFlag == false) {
	// display Toast notification
	ss.toast('Problem copying Files to: ' + folderName, 'Error');
	// error has occured with Function, return 'false' to Parent Function
	return false;
	}
	else {
	// completed successfully - do nothing and proceed with script below
	}
	}
	else {
	// File ID cell empty - do nothing and proceed with script below
	}
	// 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, 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) {
	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 and return to Parent Function
	var newFolderUrl = newFolder.getUrl();
	var newFolderId = newFolder.getId();
	var folderDetails = {newFolderUrl:newFolderUrl, newFolderId:newFolderId};
	return folderDetails;
	}
	catch(e) {
	logEvent('Error creating new Folder: ' + e + e.stack);
	return false;
	}
	}
	else {
	// return false as unable to get destination folder
	return false;
	}
	}
	/*
	Function to copy File(s) into new Google Drive Folder.
	*/
	function copyFile(ss, folderName, fileIDs, newFolderId) {
	try {
	// split up File IDs array to be able to loop through them separately
	var fileIDsArray = fileIDs.split(', ');
	// get length of array for below loop
	var fileIDsArrayLength = fileIDsArray.length;
	// get new Google Drive destination Folder
	var newDestinationFolder = DriveApp.getFolderById(newFolderId);
	}
	catch (e) {
	logEvent('Error splitting IDs or getting destination folder: ' + e + e.stack);
	var newDestinationFolder = false;
	}
	// proceed if successfully got destination folder
	if (newDestinationFolder) {
	// display Toast notification and return false flag
	ss.toast('Starting to copy Files into: ' + folderName, 'Copying Files');
	// loop through each File ID(s) and add to new Google Drive Folder *******************
	for (var i = 0; i < fileIDsArrayLength; i++) {
	try {
	// get single File ID
	var fileID = fileIDsArray[i];
	// get the File
	var file = DriveApp.getFileById(fileID);
	// get the File name
	var filename = file.getName();
	// create new File name using Folder name
	var newFilename = filename + ' - ' + folderName;
	// make a copy of the File into the new Google Drive Folder
	file.makeCopy(newFilename, newDestinationFolder);
	}
	catch (e) {
	// log error
	logEvent('Error copying File: ' + e + e.stack);
	// return 'false' for failure
	return false;
	}
	}
	// loop through each File ID(s) and add to new Google Drive Folder *******************
	// return 'true' for successful completion
	return true;
	}
	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, 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);
	}
	catch(e) {
	logEvent('Error getting destination folder: ' + e + e.stack);
	var newFolder = false;
	}
	// proceed if successfully got destination folder
	if (newFolder) {
	// 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);
	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;
	}
	/*
	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 and add Files.gs hosted with ❤ by GitHub