Create Directory of Folders #gas #GAS #GoogleAppScript #Google

create_dir_folders.gs

/*
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);

}