SHARE
TWEET

Time-Based Offboarding via BetterCloud

dan-bc Jul 2nd, 2019 (edited) 54 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  /*
  2. Created by:
  3. Michael Stone (michael.stone@bettercloud.com)
  4. BetterCloud, 2017
  5. BetterIT Slack Community
  6.  
  7. Updated by:
  8. Dan Gualtieri (daniel.gualtieri@bettercloud.com)
  9. BetterCloud, 2019
  10. BetterIT Slack Community
  11.  
  12. DISCLAIMER: Sample scripts in this guide are not supported under any BetterCloud, Inc. (“BetterCloud”) support program or service and shall be deemed “Beta Services” under any Master Subscription Agreement or any other services agreement entered into with BetterCloud. The sample scripts are provided AS IS without representation or warranty of any kind, whether express or implied. BetterCloud disclaims all implied warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. By using a sample script, you acknowledge and agree: (a) that the entire risk arising out of the use or performance of the sample scripts and documentation remains with you: and (b) that in no event shall BetterCloud, its employees, officers, directors, contractors or agents, or anyone else involved in the creation, production, or delivery of the scripts, be liable for any loss, costs, or damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of or relating to the performance, use of, or inability to use the sample scripts or documentation, even if BetterCloud has been advised of the possibility of such damages.
  13. */
  14.  
  15.  
  16. //Global Variables - Edit lines 17, 19 and 20
  17. var sheetID = "ENTER YOUR SHEET ID HERE"        //Found in the address bar of the sheet
  18. var sheet = SpreadsheetApp.openById(sheetID)
  19. var userSheet = sheet.getSheetByName("Offboarding")  //Ensure you've updated the tabbed name of your sheet
  20. var nameOfOu = "/ENTER YOUR OFFBOARDING OU HERE" //The OU that triggers offboarding in BetterCloud
  21. var logColumn = "I"
  22. var userMovedColumn = "H"
  23. var daysUntilMoveColumn = "G"
  24. var userManagerColumn = "D"
  25.  
  26. // MAIN/CRON FUNCTIONS
  27.  
  28. function dailyUserOffboardWork(){
  29.   dailyFormulaUpdates() //Update the 'Days Until Offboarded Column'
  30.   updateManager()
  31.   var users = getUsers() //get the users from the Sheet
  32.   runUserOffboardCheck(users) //check all users to see if end date is 0 or less days and hasn't been created. If so, offboard the user. If not, do nothing
  33. }
  34.  
  35. function dailyFormulaUpdates(){
  36.   //UPDATES COLUMNS THE CORRECT FORMULA
  37.   var lastRow = userSheet.getLastRow() //Find the last row
  38.   for(var i = 0 ; i < (lastRow-1); i ++){ //Go through every row in the sheet
  39.     userSheet.getRange(daysUntilMoveColumn+(i+2)).setFormula("=F"+ (i+2) +"-Now()") //reset the formula so the date difference is always correct
  40.   }
  41. }
  42.  
  43.  
  44. function runUserOffboardCheck(users){
  45.   var users = getUsers() //get users from the sheet
  46.   for (var i = 0; i < users.length; i++){ //Check all users
  47.     if(users[i].daysUntilOffboard <= 0){ //If offboard date is less than or equal to 0
  48.       if(users[i].userMovedToOU != "YES"){ //If the user has not already been removed
  49.         moveToOU(users[i], nameOfOu, i) //move User to OU
  50.       }
  51.     }
  52.   }
  53. }
  54.  
  55. function getUsers(){
  56.   var rawUsers = userSheet.getRange(2,1,sheet.getLastRow(),sheet.getLastColumn()).getValues() //Get users from sheet
  57.   var userArray = [] //set blank User array
  58.   for (var i = 0; i < rawUsers.length - 1; i++){ //push User Object into an array
  59.     //Logger.log(rawUsers[i][2])
  60.     userArray.push(
  61.       {
  62.         "primaryEmail" : rawUsers[i][2],
  63.         "managerEmail" : rawUsers[i][3],
  64.         "offboardingDate" : rawUsers[i][5],
  65.         "submittedBy" : rawUsers[i][1],
  66.         "daysUntilOffboard" : rawUsers[i][6],
  67.         "userMovedToOU" : rawUsers[i][7]
  68.       }
  69.     )
  70.   }
  71.   return userArray
  72. }
  73.  
  74. function moveToOU(user, ou, row){
  75.   var users = getUsers() //get the users from the Sheet
  76.   var row = row + 2 //Set first Row
  77.   //Logger.log(user)
  78.    try{
  79.      //Logger.log(user.primaryEmail)
  80.      AdminDirectory.Users.update({orgUnitPath: ou, primaryEmail: user.primaryEmail}, user.primaryEmail)
  81.     userSheet.getRange(logColumn+row).setValue("User " + user.primaryEmail + " was moved to " + nameOfOu + ": " + Date()) //Write 'user moved' log to Sheet
  82.     userSheet.getRange(userMovedColumn + row).setValue("YES") //Set 'Offboarded’ value to YES
  83.   } catch(e){ //If the user creation fails...
  84.    //Logger.log(logColumn+row)
  85.     userSheet.getRange(logColumn+row).setValue("User was not moved. Failure reason: " + e) //Write failure reason to Sheet
  86.     userSheet.getRange(userMovedColumn + row).setValue("NO") //Set 'user created' to NO
  87.   }
  88.  
  89. }
  90.  
  91. function updateManager (user, managerEmail, row){
  92.   var users = getUsers() //get the users from the Sheet
  93.   for (var i = 0; i < users.length; i++){ //Check all users
  94.     //Logger.log("MANAGER: " + users[i].managerEmail)
  95.     //Logger.log(users[i].primaryEmail)
  96.     try{
  97.       AdminDirectory.Users.update({"relations": [{"value": users[i].managerEmail, "type": "manager"}]}, users[i].primaryEmail)
  98.     }catch(e){Logger.log(e)}
  99.   }
  100. }
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top