Bulk create Google Drive Folders and add Files 2.0

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, along with the ability to adjust all folder/file names used.

It is a variation of this Bulk create Google Drive Folders and add Files tool post.

Bulk create Google Drive folders with optional files, from a Google Sheet

Key Functionality

1. Maximum runtime - in order to prevent the tool from reaching the limits imposed by Google you can adjust the number of minutes the tool can run for. Change this in the 'GlobalVariables.gs' file in the Script Editor.
2. Continue from where it left off - if you have a lot of Google folders and files to handle and the above runtime is reached the tool will complete the current row/folder and prompt you to run it again, without duplicating any folders or files already created.
3. Popups - as well as the 'Log' sheet the tool displays a direct popup to the user if it encounters a problem.
4. Full naming control - you have the ability to tweak every single folder and file name that is created by the tool. See the Concatenate section below to understand the true power of this.
5. Clickable links - created by the tool which means you can navigate directly to the new folders and files directly from the Google Sheet.
6. Add more files - the tool currently has columns for 10 files to be added to each folder, but you can actually append more as long as you follow the same convention of 2 columns per file (1 file name and 1 file ID). Do this before you run the tool as you cannot add more files to a folder once it has been created.
7. Add more folders - after running the tool you can either choose to clear all of the data within it (use the 'Reset sheets' option from the 'Admin' menu) and start again or you can simply append further rows/folders and re-run without any duplication (if the 'Folder Link' column is not blank/empty then it will skip that row).

 

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.

Instructions

1. Follow the instructions on the 'Welcome' sheet to help you setup the tool as you require. You will need to enter the ID of the Google/Shared Drive folder where you want the new folders to be created under. A folder ID is the last part of the Url eg https://drive.google.com/drive/folders/this_bit_is_the_ID.
2. Next you will need to go to the 'Folders' sheet and provide the names for the folders you want to create (1 row per folder). Leave the 'Folder Link' column empty. Also add the names and IDs of any files you wish to copy into the new folders. A file ID is typically the long string of random letters/numbers of the Url eg for a Google Doc: https://docs.google.com/document/d/this_bit_is_the_ID/edit.
3. To run the tool either click the 'Start now' button on the 'Welcome' sheet or use the 'Admin' menu and select 'Create folders' (they both achieve the same goal). The first time you run the tool you will need to authorise access to your Google account and then select to run it again.
   

Download

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

	/*
	Some Global Variables to make it easier to keep a track of changing column
	positions, etc.
	*/
	// 'Folder Name' column number
	var folderNameCol = 1;
	// 'Folder Link' column number
	var folderLinkCol = 2;
	// Start of Files column - so getLastColumn() from here will just be all files to upload
	var fileStartCol = 3;
	// Maximum runtime of script in minutes to prevent timeout (5 minutes 30 seconds)
	var maxRuntime = 5.5 * 60 * 1000;
	/*
	This overall script is designed to bulk create Google Folders from data within a Google Sheet.
	*/
	/**
	* @OnlyCurrentDoc
	*/
	function getSpreadsheetData() {
	try {
	// Log starting of the script
	logEvent('Script has started');
	// create error variable to use to Flag any failures
	var errorFlag = false;
	// get current spreadsheet
	var ss = SpreadsheetApp.getActiveSpreadsheet();
	// display Toast notification
	toastPopup('Script has now started', 'Start');
	// get Welcome sheet
	var welcomeSheet = ss.getSheetByName('Welcome');
	// get Parent Google Folder ID for storing created Folders in
	var destinationFolderId = welcomeSheet.getRange(7, 2).getValue().trim();
	// get Folders sheet
	var foldersSheet = ss.getSheetByName('Folders');
	// get all data as a 2-D array
	var data = foldersSheet.getDataRange().getValues();
	// get last Row number
	var lastRow = foldersSheet.getLastRow();
	// get last Column number
	var lastCol = foldersSheet.getLastColumn();
	// create a name:value pair array to send the data to the next Function
	var spreadsheetData = { foldersSheet: foldersSheet, destinationFolderId: destinationFolderId, data: data, lastRow: lastRow, lastCol: lastCol };
	// run Function to create Google Folders
	createFolders(spreadsheetData);
	}
	catch (err) {
	// log event
	logEvent("Error in 'getSpreadsheet' Function: " + err.stack);
	// run Function to launch HTML popup
	var popupTitle = "'getSpreadsheet' Function error";
	var popupMessage = "Problem with 'getSpreadsheet' Function: " + err;
	htmlPopup(popupTitle, popupMessage);
	}
	}
	/*
	This Function loops through each row and initiates the creation of a Google Drive Folder.
	*/
	function createFolders(spreadsheetData) {
	try {
	// create error variable to use to Flag any failures
	var errorFlag = false;
	// extract data from name:value pair array
	var destinationFolderId = spreadsheetData['destinationFolderId'];
	var data = spreadsheetData['data'];
	var lastRow = spreadsheetData['lastRow'];
	var lastCol = spreadsheetData['lastCol'];
	// create counter variable for number of successful folders created
	var folderCounter = 0;
	// create counter variable for number of successful files created
	var fileCounter = 0;
	// get start time so can manage duration of script and avoid timeout
	var startTime = new Date().getTime();
	// start of loop to go through each row in turn ********************************
	for (var i = 1; i < lastRow; i++) {
	// variable for current row number
	var rowNum = i + 1;
	// extract values from row of data so easier to work with
	var folderName = data[i][folderNameCol - 1];
	var folderLink = data[i][folderLinkCol - 1];
	// check Folder Link column is empty before proceeding, so no existing Folder
	if (folderLink == '') {
	// display Toast notification
	toastPopup(folderName, 'Creating Folder');
	// run Function to create Google Folder and return its Url
	var newFolderInfo = createFolder(rowNum, folderName, destinationFolderId);
	// check new Folder created successfully
	if (newFolderInfo) {
	// increment folder creation counter by '1'
	folderCounter++;
	// set Folder Link cell using new Folder Url
	var newFolderUrl = newFolderInfo['newFolderUrl'];
	var newFolderLink = '=HYPERLINK("' + newFolderUrl + '","' + folderName + '")';
	pasteIntoSheet(rowNum, folderLinkCol, newFolderLink);
	// ****************** copy file code *************************
	// get new Folder Id
	var newFolderId = newFolderInfo['newFolderId'];
	// run Function to copy relevant Files into new Folder
	var fileCounter = copyFile(data, lastCol, fileCounter, i, rowNum, newFolderId);
	// check error status to determine if script can continue
	if (fileCounter !== false) {
	// no errors have occurred so can continue
	}
	else {
	// error has occurred, popup message will already be displayed, break out of Function to stop code
	return true;
	}
	// ****************** copy file code *************************
	}
	else {
	// error has occurred, popup message will already be displayed
	// new Folder not created successfully, return error flag
	return true;
	}
	}
	else {
	// 'Folder Link' column not empty so do nothing
	}
	// *************************** perform runtime check ***************************
	// get current time
	var endTime = new Date().getTime();
	// find elapsed time by subtracting from start time
	var elapsedTime = endTime - startTime;
	// check against maximum runtime
	var timeLimitExceeded = elapsedTime >= maxRuntime;
	// check status of runtime
	if (timeLimitExceeded) {
	// runtime has been met/exceeded
	// log message
	logEvent('Runtime has been met/exceeded after row ' + rowNum);
	// run Function to launch HTML popup
	var popupTitle = "Maximum runtime has been met - 5 mins 30 secs";
	var popupMessage = "<p>" + "Successfully completed up to row " + rowNum + "." + "</p>" +
	"<p>" + "You can run the tool again to complete any remaining items - it will resume its progress." + "</p>";
	htmlPopup(popupTitle, popupMessage);
	/*
	even though not specifically an error, set Flag to 'true' to prevent subsequent popups.
	use 'break' here to come out of loop but still send anonymous metrics below.
	*/
	errorFlag = true;
	break;
	}
	else {
	// runtime has not been met/exceeded, script can continue looping through files
	}
	// *************************** perform runtime check ***************************
	}// end of loop to go through each row in turn **********************************
	// determine final outcome
	if (errorFlag != true) {
	// check value of folder creation counter to send anonymous metrics
	if (folderCounter == 0) {
	// no new folders/files were created
	var popupMessage = "No new folders or files were created.";
	// log event
	logEvent("No new folders or files were created");
	logEvent("Script completed successfully");
	}
	else if (folderCounter > 0) {
	// new folders/files were created
	var popupMessage = "Tool successfully completed without errors." +
	"<p>" + folderCounter + " new folders were created and " + fileCounter + " new files." + "</p>";
	// log event
	logEvent("Created " + folderCounter + " folders.");
	logEvent("Created " + fileCounter + " files")
	logEvent("Script completed successfully");
	}
	// run Function to launch HTML popup to inform user of success
	var popupTitle = "Completed";
	htmlPopup(popupTitle, popupMessage);
	}
	else {
	// problems occurred - a popup should already be displayed
	// log event
	logEvent("Script completed with errors");
	}
	}
	catch (err) {
	// log event
	logEvent("Error in 'createFolders' Function: " + err.stack);
	// run Function to launch HTML popup
	var popupTitle = "'createFolders' Function error";
	var popupMessage = "Problem with 'createFolders' Function: " + err;
	htmlPopup(popupTitle, popupMessage);
	// return true as error
	return true;
	}
	}
	/*
	Function to create new Google Drive Folder and return its Url/Id.
	*/
	function createFolder(rowNum, folderName, destinationFolderId) {
	try {
	// get destination Folder
	var destinationFolder = DriveApp.getFolderById(destinationFolderId);
	}
	catch (err) {
	// log error
	logEvent('Error getting destination folder: ' + err.stack);
	// run Function to launch HTML popup
	var popupTitle = "'createFolder' Function error";
	var popupMessage = "Unable to get Parent folder: " + err;
	htmlPopup(popupTitle, popupMessage);
	// return false as unable to get destination folder
	return false;
	}
	try {
	// proceed if successfully got destination folder
	if (destinationFolder) {
	// 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();
	// store Folder items in name:value pair for return to Parent Function
	var newFolderInfo = { newFolderUrl: newFolderUrl, newFolderId: newFolderId };
	return newFolderInfo;
	}
	else {
	// do nothing as script will have returned error above
	}
	}
	catch (err) {
	// log error
	logEvent('Error creating new Folder for row ' + rowNum + ': ' + err.stack);
	// run Function to launch HTML popup
	var popupTitle = "'createFolder' Function error";
	var popupMessage = "Unable to create new folder for row " + rowNum + ": " + err;
	htmlPopup(popupTitle, popupMessage);
	// return false as unable to create new folder
	return false;
	}
	}
	/*
	Function to copy File(s) into Folder.
	*/
	function copyFile(data, lastCol, fileCounter, i, rowNum, newFolderId) {
	try {
	// get new destination Folder
	var newDestinationFolder = DriveApp.getFolderById(newFolderId);
	}
	catch (err) {
	// log error
	logEvent('Error getting new folder for file copy, row ' + rowNum + ': ' + err.stack);
	// run Function to launch HTML popup
	var popupTitle = "'copyFile' Function error";
	var popupMessage = "<p>" + "Unable to get new folder for file copy, row " + rowNum + "." + "</p>" +
	"<p>" + "No files have been copied, but a folder may have been created." + "</p>" +
	"<p>" + "Error is: " + err + "</p>";
	htmlPopup(popupTitle, popupMessage);
	// return true as unable to get new destination folder
	return false;
	}
	try {
	// proceed if successfully got new destination folder
	if (newDestinationFolder) {
	// *********************** create loop to go through all Files ***********************
	// increment 'j' by '2' as each File has 2 columns for it as move along to the right
	for (var j = fileStartCol; j < lastCol; j += 2) {
	// put items into variables so easier to handle. Minus '1' from 'j' as array object
	var fileName = data[i][j - 1];
	var fileId = data[i][j];
	// check values not blank otherwise skip column
	if ((fileName != '') && (fileId != '')) {
	// get the file to copy
	var fileToCopy = DriveApp.getFileById(fileId);
	// make copy of file in new destination folder and get its Url
	var newFileUrl = fileToCopy.makeCopy(fileName, newDestinationFolder).getUrl();
	// set Folder * Name cell using new File Url
	var newFileLink = '=HYPERLINK("' + newFileUrl + '","' + fileName + '")';
	pasteIntoSheet(rowNum, j, newFileLink);
	// increment file creation counter by '1'
	fileCounter++;
	}
	else {
	// skip column as blank values
	}
	}
	// *********************** create loop to go through all Files ***********************
	// return file counter as signal this Function ran successfully even if no files created
	return fileCounter;
	}
	else {
	// do nothing as script will have returned error above
	}
	}
	catch (err) {
	// log error
	logEvent('Error copying file with ID (' + fileId + ') into new folder, row ' + rowNum + ': ' + err.stack);
	// run Function to launch HTML popup
	var popupTitle = "'copyFile' Function error";
	var popupMessage = "<p>" + "Unable to copy file into new folder, row " + rowNum + "." + "</p>" +
	"<p>" + "Problem file ID is: " + fileId + "." + "</p>" +
	"<p>" + "The folder for this row will have been created and some new files may exist within it." + "</p>" +
	"<p>" + "Error is: " + err + "</p>";
	htmlPopup(popupTitle, popupMessage);
	// return true as unable to create new folder
	return false;
	}
	}
	/*
	Reset all data within Google Sheet to be able to start afresh.
	*/
	function reset() {
	try {
	// log that reset Function has been selected
	logEvent("Initiated 'Reset' Function");
	// get Spreadsheet UI
	var ui = SpreadsheetApp.getUi();
	// create Alert popup box
	var result = ui.alert(
	'Reset spreadsheet',
	'Do you wish to remove all inputted information to start again?',
	ui.ButtonSet.OK_CANCEL
	);
	// process user response from Popup Box
	if (result == ui.Button.OK) {
	// log selection
	logEvent("Selected 'OK' to reset spreadsheet");
	// get current spreadsheet
	var ss = SpreadsheetApp.getActiveSpreadsheet();
	// get Welcome sheet
	var welcomeSheet = ss.getSheetByName('Welcome');
	// clear Parent folder ID box
	welcomeSheet.getRange(7, 2).clearContent();
	// get Folders sheet
	var foldersSheet = ss.getSheetByName('Folders');
	// get last row
	var foldersSheetLastRow = foldersSheet.getLastRow();
	// get last column
	var foldersSheetLastCol = foldersSheet.getLastColumn();
	// clear all data (skipping Header row 1)
	foldersSheet.getRange(2, 1, foldersSheetLastRow, foldersSheetLastCol).clearContent();
	// log completion of Function
	logEvent("Completed 'Reset' Function.");
	// display alert popup to user to confirm completion
	ui.alert('Successfully cleared all sheet data.');
	}
	else {
	// User clicked 'No' or 'X' in title bar so do nothing
	logEvent("Selected 'CANCEL' to reset spreadsheet");
	}
	}
	catch (err) {
	// problem clearing sheet data
	logEvent("Problem with 'reset' Function" + err + err.stack);
	// run Function to launch HTML popup
	var popupTitle = "'reset' Function error";
	var popupMessage = "Unable to clear sheet data: " + err;
	htmlPopup(popupTitle, popupMessage);
	}
	}
	/*
	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.
	.addItem('Reset sheets', 'reset') // label for menu item, name of function to run.
	.addToUi();
	}
	/*
	Display a modal dialog box with custom HtmlService content.
	Does not suspend the script.
	*/
	function htmlPopup(popupTitle, popupMessage) {
	var htmlOutput = HtmlService
	.createHtmlOutput(popupMessage)
	.setWidth(360)
	.setHeight(180);
	SpreadsheetApp.getUi().showModalDialog(htmlOutput, popupTitle);
	}
	/*
	Function to create a Toast Popup notification with customised message.
	Created as standalone so can be called from anywhere else within Project.
	*/
	function toastPopup(msg, title) {
	// get Spreadsheet
	var ss = SpreadsheetApp.getActiveSpreadsheet();
	// create Toast Popup
	ss.toast(msg, title);
	}
	/*
	Function that can be passed a row & column number, along with a value,
	to paste something into the Folders Sheet.
	*/
	function pasteIntoSheet(row, column, value) {
	// get spreadsheet
	var ss = SpreadsheetApp.getActiveSpreadsheet();
	// get Folders sheet
	var foldersSheet = ss.getSheetByName('Folders');
	// get relevant cell
	var cell = foldersSheet.getRange(row, column);
	// paste in value
	cell.setValue(value);
	}

view raw Bulk create Google Drive Folders and add Files 2.0.gs hosted with ❤ by GitHub