Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save phillypb/fd3fb6091fa192e4b0f8883ef06a5ced to your computer and use it in GitHub Desktop.
Save phillypb/fd3fb6091fa192e4b0f8883ef06a5ced to your computer and use it in GitHub Desktop.
/*
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 subfolders column - so getLastColumn() from here will just be all files to upload
var subFolderStartCol = 3;
// Maximum runtime of script in minutes to prevent timeout (5.5 minutes)
var maxRuntime = 5.5 * 60 * 1000;
/*
This overall script is designed to bulk create Google Folders with optional subfolders from data within a Google Sheet.
*/
/**
* @OnlyCurrentDoc
*/
function getSpreadsheetData() {
try {
// Log starting of the script
logEvent('Script has started');
// 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.stack;
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 subfolders created
var subfolderCounter = 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 Folders');
// 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);
// ****************** create subfolder(s) code *************************
// get new Folder Id
var newFolderId = newFolderInfo['newFolderId'];
// run Function to create subfolder(s) new Folder
var subfolderCounter = createSubFolders(data, lastCol, subfolderCounter, i, rowNum, newFolderId);
// check error status to determine if script can continue
if (subfolderCounter !== false) {
// no errors have occurred so can continue
// flush spreadsheet to update visible progress after each row
SpreadsheetApp.flush();
} else {
// error has occurred, popup message will already be displayed, break out of Function to stop code
return true;
};
// ****************** create subfolder(s) 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 - 29 minutes";
var popupMessage = "<p>" + "Successfully completed up to row " + rowNum + "." + "</p>" +
"<p>" + "You can run the tool again to complete any remaining folders - 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/subfolder were created
var popupMessage = "No new folders or subfolders were created.";
// log event
logEvent("No new folders or subfolders were created");
logEvent("Script completed successfully");
} else if (folderCounter > 0) {
// new folders/subfolders were created
var popupMessage = "Tool successfully completed without errors." +
"<p>" + folderCounter + " new folders were created and " + subfolderCounter + " subfolders." + "</p>";
// log event
logEvent("Created " + folderCounter + " folders.");
logEvent("Created " + subfolderCounter + " subfolders")
logEvent("Script completed successfully");
} else {
// do nothing
};
// 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.stack;
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.stack;
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.stack;
htmlPopup(popupTitle, popupMessage);
// return false as unable to create new folder
return false;
}
}
/*
Function to create subfolder(s) in Folder.
*/
function createSubFolders(data, lastCol, subfolderCounter, 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 = "'createSubFolders' Function error";
var popupMessage = "<p>" + "Unable to get new folder for subfolder creation, row " + rowNum + "." + "</p>" +
"<p>" + "No subfolders have been created." + "</p>" +
"<p>" + "Error is: " + err.stack + "</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 subfolders ***********************
for (var j = subFolderStartCol; j < lastCol; j++) {
// put items into variables so easier to handle. Minus '1' from 'j' as array object
var subFolderName = data[i][j - 1];
// check values not blank otherwise skip column
if (subFolderName != '') {
// create new subfolder
newDestinationFolder.createFolder(subFolderName);
// increment subfolder creation counter by '1'
subfolderCounter++;
} else {
// skip column as blank values
};
}
// *********************** create loop to go through all subfolders ***********************
// return subfolder counter as signal this Function ran successfully even if no subfolders created
return subfolderCounter;
} else {
// do nothing as script will have returned error above
};
}
catch (err) {
// log error
logEvent('Error creating subfolder for row ' + rowNum + ': ' + err.stack);
// run Function to launch HTML popup
var popupTitle = "'createSubFolders' Function error";
var popupMessage = "<p>" + "Unable to create subfolder for row " + rowNum + "." + "</p>" +
"<p>" + "The main folder for this row will have been created and some subfolders may exist within it." + "</p>" +
"<p>" + "Error is: " + err.stack + "</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);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment