daily pastebin goal
62%
SHARE
TWEET

Untitled

a guest Jan 18th, 2019 81 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. #cls
  2.  
  3. # This script creates a hashtable from the COIDs & Facilities List which gives us the COID and the COID's ID value
  4. # which we need to update the Employee Removal List's COID column, which is a lookup column, dynamically. We need
  5. # this value to use to update or add a new item to the List.
  6. # This script also updates the SharePoint Online List Employee Access Removal with values from the HR csv file running as a
  7. # scheduled task each night.
  8.  
  9. # Load SharePoint CSOM Assemblies
  10. Add-Type -Path "C:Program FilesCommon FilesMicrosoft SharedWeb Server Extensions15ISAPIMicrosoft.SharePoint.Client.dll"
  11. Add-Type -Path "C:Program FilesCommon FilesMicrosoft SharedWeb Server Extensions15ISAPIMicrosoft.SharePoint.Client.Runtime.dll"
  12.  
  13. # Variables for Processing
  14. $SiteUrl = "sharepoint.com"
  15. $ListName="Employee Rewards"
  16. $hashTable = @{}
  17. $ListLookup = "COIDs & Facs"
  18.  
  19. # Location on the local computer to where the file is stored
  20. $ImportFile ="c:tempHR.csv"
  21.  
  22. # Username and password of an account that can access the Office 365 site
  23.  
  24. $UserName = "user@sharepoint.com"
  25. $Password = "password"
  26.  
  27. # Define the array to hold all items that are flagged for change
  28. # this will be used to create a log file when we are done
  29. $curItems = @()
  30.  
  31. # Get the current month number and the 4 digit year so we can filter the csv file for items in the current month and year
  32. $curMonth = Get-Date -UFormat "%m"
  33. $curYear = Get-Date -UFormat "%Y"
  34.  
  35. # These variables are used to dynamically get the current month and year so we only process the data for each month
  36. $curDate = "$curMonth/*/"+$curYear
  37.  
  38. #Setup Credentials to connect
  39. $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName,(ConvertTo-SecureString $Password -AsPlainText -Force))
  40.  
  41.  
  42. function GetLookupValues
  43. {
  44.     #Set up the context
  45.     $lookupCTX = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl)
  46.     $lookupCTX.Credentials = $Credentials
  47.  
  48.     $lookupList = $lookupCTX.Web.Lists.GetByTitle($ListLookup)
  49.     $lookupItems = $lookupList.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery())
  50.     $lookupCTX.Load($lookupItems)
  51.     $lookupCTX.ExecuteQuery()
  52.     #$items.Count        
  53.     foreach($item in $lookupItems)
  54.     {
  55.         $ID = $item["ID"].ToString()
  56.         $Facility = $item["Facility"]
  57.         $COID = $item["C0ID"].ToString()
  58.         $hashTable.Add($ID,$COID)
  59.         #Write-Host "ID: $ID | Facility Name: $Facility | COID: $COID"
  60.  
  61.     }
  62.     #$hashTable
  63.     AddData($hashTable)
  64. }
  65.  
  66. function AddData($hashTable)
  67. {
  68.     #Set up the context
  69.     $Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl)
  70.     $Context.Credentials = $credentials
  71.  
  72.     # Get the List
  73.     $List = $Context.web.Lists.GetByTitle($ListName)
  74.  
  75.     # Get the Data from the CSV file and add a header to it
  76.     $data = Import-Csv $ImportFile -Header coid,uid,nameLast,nameFirst,departmentID,departmentDesc,jobTitleID,jobTitle,labels,blank2,email,itemIsARecord,startDate,reasonCode,classificationIDDate,status,nameFull,address1,address2,city,state,zip,description
  77.  
  78.     # Loop through each row in the csv file
  79.     foreach($row in $data) {
  80.  
  81.     # Create a variable for the start date
  82.     $startDate = $row.startDate
  83.  
  84.     # TODO: Add a try/catch block to handle exceptions
  85.  
  86.     # Filter for the current month and year
  87.     if($startDate -like $curDate)
  88.     {
  89.         # Take care of formatting issues of the names look proper
  90.         $fullName = $row.nameFirst.substring(0,1).toUpper()+$row.nameFirst.substring(1).toLower() + " " + $row.nameLast.substring(0,1).toUpper()+$row.nameLast.substring(1).toLower()
  91.  
  92.         # Add items to the List
  93.         $ListItemInfo = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation
  94.         $Item = $List.AddItem($ListItemInfo)
  95.         $Item["COID"] = $row.COID
  96.         # In order to set the value of the COID we have to use the ID we have in our
  97.         # $hashTable variable, this maps to the actual COID we want to update
  98.         # this is bc we are using a lookup column in our list
  99.         $match = $hashTable.GetEnumerator()| Where-Object {$_.value -eq $row.coid}
  100.         if($match)
  101.         {
  102.             # Output the employees name, the current COID, the current value in the hashtable, (these two should match)
  103.             # the hashtable Key has the SharePoint List ID of the current COID, which we will use to create a string
  104.             # dynamicaly and to make sure the values are what we are expecting.
  105.             $fullName
  106.             $row.coid
  107.             $match.Value
  108.             $mKey = $match.Key
  109.             $mKey
  110.             # Create the value to update the COID column in the format "ID;#Value"
  111.             $coidValue = $mKey.ToString() + ";#" + $row.coid.ToString()
  112.             #$coidValue
  113.             $Item["C0ID"] = $row.coid
  114.             $Item["Facility"] = $coidValue.ToString()
  115.             # If the column name in the list has a space then this special char set is added to the column name: _x0020_
  116.             $Item["Full_x0020_Name"] = $fullName
  117.             $Item["Department"] = $row.departmentID
  118.             $Item["Department_x0020_Description"] = $row.departmentDesc
  119.             $Item["Body"] = $row.description
  120.             $Item["Email"] = $row.email
  121.             $Item["Job_x0020_Title"] = $row.jobTitle
  122.             $Item["First_x0020_Name"] = $row.nameFirst.substring(0,1).toUpper()+$row.nameFirst.substring(1).toLower()
  123.             $Item["Last_x0020_Name"] = $row.nameLast.substring(0,1).toUpper()+$row.nameLast.substring(1).toLower()
  124.             $Item["Reason_x0020_Code"] = $row.reasonCode
  125.             $Item["StartDate"] = $row.startDate
  126.             $Item["Status"] = $row.status
  127.             $Item["UID"] = $row.uid
  128.             $Item.Update()
  129.             $Context.ExecuteQuery()
  130.  
  131.             # Add items to the array of what is going to be updated
  132.             $curItems += @($row)
  133.         }
  134.         else
  135.         {
  136.             # Log the error
  137.         }
  138.     }
  139.  
  140. }
  141. # Find out how many records were updated
  142. $importedItems = $curItems.Count
  143. Write-host "$importedItems items to be imported into the Employee Removal SharePoint List. Log file is in the C:Temp directory"
  144. # Create a log file with the users identified to be updated
  145. $today = Get-Date -UFormat "%Y.%m.%d"
  146. $logfilePath = "C:temp"
  147. $logFile = $logfilePath+$today + "-HR-Updates.csv"
  148. $curItems | Export-Csv -Path $logFile -NoTypeInformation -Force
  149. }
  150. # Call the function
  151.  
  152. GetLookupValues
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