https://www.pbainbridge.co.uk/2021/06/bulk-create-shared-drives-with.html

Home   »   https://www.pbainbridge.co.uk/2021/06/bulk-create-shared-drives-with.html

/*
This overall script is designed to bulk create Shared drives and add user access
permissions.
*/

function getSpreadsheetData() {

  try {
    // Log starting of the script
    logEvent('Script has started');


    // get current spreadsheet
    var ss = SpreadsheetApp.getActiveSpreadsheet();


    // get TimeZone here so only do once rather than repeatedly when looping through rows
    var timeZone = ss.getSpreadsheetTimeZone();


    // get Config sheet
    var configSheet = ss.getSheetByName('Config');

    // get 'Shared drive link' column number
    var driveLinkCol = configSheet.getRange(1, 2).getValue();

    // get 'Access Added' column number
    var accessAddedCol = configSheet.getRange(3, 2).getValue();


    // get Data sheet
    var dataSheet = ss.getSheetByName('Data');

    // get all data as a 2-D array
    var data = dataSheet.getDataRange().getValues();

    // run 'shift' twice to remove first two Header rows from the data
    data.shift();
    data.shift();


    // create a name:value pair array to send the data to the next Function
    var spreadsheetData = {
      timeZone: timeZone, driveLinkCol: driveLinkCol, accessAddedCol: accessAddedCol, dataSheet: dataSheet, data: data
    };


    // run Function to create Shared drives and return success flag
    var createSharedDriveFlag = createSharedDrive(spreadsheetData);


    // check success status
    if (createSharedDriveFlag) {
      // 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');
  }
  catch (e) {
    logEvent("Problem with 'getSpreadsheetData' Function: " + e);

    // run Function to launch HTML popup
    var popupTitle = "'getSpreadsheetData' Function error";
    var popupMessage = "Problem with 'getSpreadsheetData' Function: " + e;
    htmlPopup(popupTitle, popupMessage);
  }


}


/*
This Function loops through each row and initiates the creation of a Shared drive
and the necessary permissions.
*/

function createSharedDrive(spreadsheetData) {

  try {
    // extract data from name:value pair array
    var timeZone = spreadsheetData['timeZone'];
    var driveLinkCol = spreadsheetData['driveLinkCol'];
    var accessAddedCol = spreadsheetData['accessAddedCol'];
    var dataSheet = spreadsheetData['dataSheet'];
    var data = spreadsheetData['data'];


    // get length of data array for below loop
    var dataLength = data.length;

    // start of loop to go through each row in turn ********************************
    for (var i = 0; i < dataLength; i++) {


      // get the current row from the loop so can be used elsewhere easily
      var currentRow = i + 3;


      // check there is a 'Manager' email before proceeding
      var managerAccEmails = data[i][1];
      if (managerAccEmails != '') {


        // extract values from row of data so easier to work with
        var sharedDriveName = data[i][0];
        var sharedDriveLink = data[i][6];


        // check Shared drive link column is empty before proceeding, so no existing drive
        if (sharedDriveLink == '') {

          // display Toast notification
          toastPopup('Creating Shared drive', sharedDriveName);

          // run Function to create Shared drive and return its Id
          var sharedDriveID = createDrive(sharedDriveName, currentRow);


          // check new Shared drive created successfully
          if (sharedDriveID) {

            // set Shared drive link cell using new Shared drive ID
            var sharedDriveUrl = "https://drive.google.com/drive/folders/" + sharedDriveID;
            var newSharedDriveLink = '=HYPERLINK("' + sharedDriveUrl + '","' + sharedDriveName + '")';
            dataSheet.getRange(currentRow, driveLinkCol).setFormula(newSharedDriveLink);


            // run Function to add Shared drive permissions
            var addPermissionsFlag = addPermissions(timeZone, i, currentRow, dataSheet, data, accessAddedCol, sharedDriveID, sharedDriveName);

            // if problem adding Permissions return for status message
            if (addPermissionsFlag == false) {
              return false;
            }
            else {
              // no problem adding permissions
            }


            // flush spreadsheet to update each row as completed
            SpreadsheetApp.flush();

          }
          else {
            // write error into 'Shared drive link' cell and return false value
            dataSheet.getRange(i + 3, driveLinkCol).setValue('Error creating Shared drive. Please see Logs');
            // new Shared drive not created successfully
            return false;
          }

        }
        else {
          // Shared drive link column not empty so do nothing
          // Log starting of the script
          logEvent('Shared drive link column not empty, skipping row: ' + currentRow);
        }

      }
      else {
        // no 'Manager' email address present in Google Sheet
        logEvent('No Manager email address found for row: ' + currentRow);

        // run Function to launch HTML popup
        var popupTitle = "No 'Manager'";
        var popupMessage = "Please make sure there is a 'Manager' for the new Shared drive on row " + currentRow + ".";
        htmlPopup(popupTitle, popupMessage);

        // return False to signal issue has occurred.
        return false;
      }


    }// end of loop to go through each row in turn **********************************


    // completed successfully
    return true;


  }
  catch (e) {
    logEvent("Problem with 'createSharedDrive' Function: " + e);

    // run Function to launch HTML popup
    var popupTitle = "'createSharedDrive' Function error";
    var popupMessage = "Problem with 'createSharedDrive' Function: " + e;
    htmlPopup(popupTitle, popupMessage);

    return false;
  }

}


/*
Function to create new Shared drive and return its Id.
*/

function createDrive(sharedDriveName, currentRow) {

  try {
    // random request ID for creating a Shared drive
    var requestID = Utilities.getUuid();

    // name for Shared drive in suitable format
    var name = {
      name: sharedDriveName
    };

    // create Shared drive
    var newSharedDrive = Drive.Drives.insert(name, requestID);

    // Log starting of the script
    logEvent('Created Shared drive: ' + sharedDriveName);

    // get ID of new Shared drive
    var newSharedDriveID = newSharedDrive.id;

    // return Shared drive ID to Parent Function
    return newSharedDriveID;
  }
  catch (e) {
    logEvent("Problem with 'createDrive' Function Row " + currentRow + ": " + e);

    // run Function to launch HTML popup
    var popupTitle = "'createDrive' Function error Row " + currentRow;
    var popupMessage = "Problem with 'createDrive' Function: " + e;
    htmlPopup(popupTitle, popupMessage);
    return false;
  }

}


/*
Function to add relevant permissions/access to Shared drive.
Needed to separate out 'Commenter' role as cannot add to Shared drive 
in same manner as others.
https://developers.google.com/drive/api/v2/reference/permissions/insert?hl=en
*/

function addPermissions(timeZone, i, currentRow, dataSheet, data, accessAddedCol, sharedDriveID, sharedDriveName) {

  try {
    // extract data from name:value pair array
    var managerAccEmails = data[i][1];
    var conManagerAccEmails = data[i][2];
    var contributorAccEmails = data[i][3];
    var commenterAccEmails = data[i][4];
    var viewerAccEmails = data[i][5];


    // run Function to sort each email access type and return an array ****************
    var managerAcc = sortEmailArrays(managerAccEmails, currentRow);

    if (managerAcc) {
      var conManagerAcc = sortEmailArrays(conManagerAccEmails, currentRow);
      if (conManagerAcc) {
        var contributorAcc = sortEmailArrays(contributorAccEmails, currentRow);
        if (contributorAcc) {
          var commenterAcc = sortEmailArrays(commenterAccEmails, currentRow);
          if (commenterAcc) {
            var viewerAcc = sortEmailArrays(viewerAccEmails, currentRow);
          }
          else {
            return false;
          }
        }
        else {
          return false;
        }
      }
      else {
        return false;
      }
    }
    else {
      return false;
    }
    // run Function to sort each email access type and return an array ****************


    if (viewerAcc) {
      // organise various roles to be added - as an array of email
      // ignore 'Commenter' for now as requires different process
      var roles = {
        organizer: managerAcc, // Manager
        fileOrganizer: conManagerAcc, // Content Manager
        writer: contributorAcc, // Contributor
        reader: viewerAcc // Viewer
      };

      // go through the above roles
      for (var key in roles) {

        // assign a key for 'organizer, fileOrganizer, ...
        var role = roles[key];

        // go through each role and create a resource for adding permissions
        role.forEach(function (email) {
          var resource = {
            role: key,
            type: "user",
            value: email,
          }

          // optional arguments to work on Shared drive
          var optionalArgs = {
            sendNotificationEmails: false,
            supportsAllDrives: true
          }

          // set Shared drive permissions
          Drive.Permissions.insert(resource, sharedDriveID, optionalArgs);
        });

      };


      // add 'Commenter' access - done separately as requires separate steps ******
      if (commenterAcc.length > 0) {

        // loop through each email address in array and add permission to Shared drive
        commenterAcc.forEach(function (email) {
          var resource = {
            role: 'reader',
            type: "user",
            value: email,
            additionalRoles: ["commenter"]
          }

          // optional arguments to work on Shared drive
          var optionalArgs = {
            sendNotificationEmails: false,
            supportsAllDrives: true
          }

          // set Shared drive permissions
          Drive.Permissions.insert(resource, sharedDriveID, optionalArgs);
        });

        // add 'Commenter' access - done separately as requires separate steps ******
      }
      else {
        // do nothing as there are no 'Commenters' to add
      }


      // Log starting of the script
      logEvent('Added permissions to Shared drive: ' + sharedDriveName);

      // write timestamp into 'Access Added' cell
      var date = new Date;
      var timeStamp = Utilities.formatDate(date, timeZone, "dd/MM/yy @ HH:mm:ss");
      dataSheet.getRange(currentRow, accessAddedCol).setValue(timeStamp);

      // return true as all permissions added successfully
      return true;
    }
    else {
      return false;
    }
  }
  catch (e) {
    // log error
    logEvent("Problem with 'addPermissions' Function Row " + currentRow + ": " + e);

    // run Function to launch HTML popup
    var popupTitle = "'addPermissions' Function error Row " + currentRow;
    var popupMessage = "Problem with 'addPermissions' Function: " + e;
    htmlPopup(popupTitle, popupMessage);

    // return False to signal issue has occurred.
    return false;
  }


}


/*
Function to organise emails from cells in Sheet into arrays for adding
Shared drive permissions.
*/

function sortEmailArrays(accessEmails, currentRow) {

  try {
    // create empty array to push emails into
    var emptyArray = [];

    if (accessEmails) {

      // split up email address array to be able to loop through them separately
      var emailAddresses = accessEmails.split(', ');

      // get length of array for loop
      var emailAddressesLength = emailAddresses.length;

      for (var i = 0; i < emailAddressesLength; i++) {

        // extract each email address and push into array
        var singleEmail = emailAddresses[i];
        emptyArray.push(singleEmail);

      };

    }
    else {
      // do nothing as no emails to handle
    }

    // return array
    return emptyArray;
  }
  catch (e) {
    // log error
    logEvent("Problem with 'sortEmailArrays' Function Row " + currentRow + ": " + e);

    // run Function to launch HTML popup
    var popupTitle = "'sortEmailArrays' Function error Row " + currentRow + ": " + e;
    var popupMessage = "Problem with 'sortEmailArrays' Function: " + e;
    htmlPopup(popupTitle, popupMessage);

    /* 
    return false value to signal Function problem,
    */
    return false;
  }

}


/*
This Function creates a menu item to run this script.
*/

function onOpen() {
  
  SpreadsheetApp.getUi()
  .createMenu('Admin')
  .addItem('Create Shared drives', 'getSpreadsheetData') // label for menu item, name of function to run.
  .addToUi();  
  
}


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


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


/*
Display a modal dialog box with custom HtmlService content.
Does not suspend the script.
Message needs to include '

' tags. */ function htmlPopup(popupTitle, popupMessage) { var htmlOutput = HtmlService .createHtmlOutput(popupMessage) .setWidth(360) .setHeight(180); SpreadsheetApp.getUi().showModalDialog(htmlOutput, popupTitle); }

Leave a Reply

Your email address will not be published. Required fields are marked *