Oh no! We’re talking about folders again… Yes we are!
But this time, we’ll get some insights as to when they’ve been created, by whom, and adding a little experiment at the end 😇
Let’s get right into it.
The scenario
Let’s imagine that our organization wants users to refrain from creating folders in libraries. We’d like to see how many folders have been created in a site, by whom and more particularly, run some statistics at the end to (maybe) analyse the data we just extracted from SharePoint.
The script - Month number
For this post, we’ll go a little bit more in detail than we usually do. I’ll explain the findings before posting the full script.
The first part is kind of common to some other scripts we’ve seen:
- Connect to the site using Connect-PnPOnline
- Store the libraries and results into variables
- Start looping through all the items in the list…
That’s when we start thinking “OK, I need a insert a condition here, because I’m only interested in the folders, not the files“.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
| #Connect to SPO
Connect-PnPOnline -Url https://<TENANT-NAME>.sharepoint.com/sites/<YOUR-SITE>
#Variables
$results = @()
$allLibs = Get-PnPList | Where-Object {$_.BaseTemplate -eq 101}
foreach($lib in $allLibs){
$allItems = Get-PnPListItem -List $lib.Title
foreach ($item in $allItems){
if($item.FileSystemObjectType -eq "Folder"){
$results += [PSCustomObject][ordered]@{
Type = $item.FileSystemObjectType ## Just for information if we're getting only 'Folders' -- NOT NECESSARY
DocName = $item["FileLeafRef"]
FullPath = $item["FileRef"]
CreatedBy = $item.FieldValues.Author.LookupValue
CreatedDate = $item["Created"]
ModifiedBy = $item.FieldValues.Editor.LookupValue
ModifiedDate = $item["Modified"]
}
}
}
}
$results
|
At this stage, this is what we’ve got:
![/images/powershell-screenshots/get-list-created-folders-per-month-img1.png]( "/images/powershell-screenshots/get-list-created-folders-per-month-img1.png")
Alright, now we have our structure & a few properties we’re familiar with. We need to get more properties as follow.
- Month Created
- Month Modified
- Year Created
- Year Modified
We an see in the above screenshot that, we’d be interested in part of the Created Date/Modified Date for the month number & also the year.
But how are we going to get this data? Split() ? Regex? Hum.. maybe not the last one 😅
In fact, if we dig deeper into the $item, we’ll find something that will help us straight out of the box!
Let’s type $item.FieldValues.Values | Get-Member and under the TypeName: System.DateTime, observe the following properties:
![/images/powershell-screenshots/get-list-created-folders-per-month-img2.png]( "/images/powershell-screenshots/get-list-created-folders-per-month-img2.png")
Useful isn’t it?! Cool, let’s add this to our properties and rerun the script to see the magic!
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
| #Connect to SPO
Connect-PnPOnline -Url https://<TENANT-NAME>.sharepoint.com/sites/<YOUR-SITE>
#Variables
$results = @()
$allLibs = Get-PnPList | Where-Object {$_.BaseTemplate -eq 101}
foreach($lib in $allLibs){
$allItems = Get-PnPListItem -List $lib.Title
foreach ($item in $allItems){
if($item.FileSystemObjectType -eq "Folder"){
$results += [PSCustomObject][ordered]@{
Type = $item.FileSystemObjectType ## Just for information if we're getting only 'Folders' -- NOT NECESSARY
DocName = $item["FileLeafRef"]
FullPath = $item["FileRef"]
CreatedBy = $item.FieldValues.Author.LookupValue
CreatedDate = $item["Created"]
ModifiedBy = $item.FieldValues.Editor.LookupValue
ModifiedDate = $item["Modified"]
MonthCreated = $item.FieldValues.Values.Month
YearCreated = $item.FieldValues.Values.Year
}
}
}
}
$results
|
![/images/powershell-screenshots/get-list-created-folders-per-month-img3.png]( "/images/powershell-screenshots/get-list-created-folders-per-month-img3.png")
Wait.. What? Why do I have two numbers in there?? 😒 That’s exactly what I tried to understand for hours, until I eventually figured it out…
The first number is the Created month/year & the second number is the Modified month/year.
In the above screenshot, we have “MonthCreated” : {4, 3}
Which should actually means MonthCreated = 4 (April) and MonthModified = 3 (March)
Same thing for the year!
As these values are in an array, we simply need to take the first element of the array which will be [0] for both.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
| #Connect to SPO
Connect-PnPOnline -Url https://<TENANT-NAME>.sharepoint.com/sites/<YOUR-SITE>
#Variables
$results = @()
$allLibs = Get-PnPList | Where-Object {$_.BaseTemplate -eq 101}
foreach($lib in $allLibs){
$allItems = Get-PnPListItem -List $lib.Title
foreach ($item in $allItems){
if($item.FileSystemObjectType -eq "Folder"){
$results += [PSCustomObject][ordered]@{
Type = $item.FileSystemObjectType ## Just for information if we're getting only 'Folders' -- NOT NECESSARY
DocName = $item["FileLeafRef"]
FullPath = $item["FileRef"]
CreatedBy = $item.FieldValues.Author.LookupValue
CreatedDate = $item["Created"]
ModifiedBy = $item.FieldValues.Editor.LookupValue
ModifiedDate = $item["Modified"]
MonthCreated = $item.FieldValues.Values.Month[0]
YearCreated = $item.FieldValues.Values.Year[0]
#MonthModified = $item.FieldValues.Values.Month[1] ## If you want the 'Month Modified'
#YearModified = $item.FieldValues.Values.Year[1] ## If you want the 'Year Modified'
}
}
}
}
$results | Export-Csv -Path <YOUR_PATH> -NoTypeInformation
|
![/images/powershell-screenshots/get-list-created-folders-per-month-img4.png]( "/images/powershell-screenshots/get-list-created-folders-per-month-img4.png")
By exporting the results, let’s look at what we have…
![/images/powershell-screenshots/get-list-created-folders-per-month-img5.png]( "/images/powershell-screenshots/get-list-created-folders-per-month-img5.png")
This is pretty much what we wanted right? But what if we want to make it better? Something like…
- Month number into a month name!
- How many nested folders?
- How many documents inside?
And the icing on the cake, create a PowerBI dashboard with this info?? (this is the ‘experiment’ part!)
The script - Month name and others
Below is the full and final script + an extract of what could a dashboard look like.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
| #Connect to SPO
Connect-PnPOnline -Url https://<TENANT-NAME>.sharepoint.com/sites/<YOUR-SITE>
#Variables
$results = @()
$allLibs = Get-PnPList | Where-Object {$_.BaseTemplate -eq 101}
foreach($lib in $allLibs){
$allItems = Get-PnPListItem -List $lib.Title
foreach ($item in $allItems){
if($item.FileSystemObjectType -eq "Folder"){
$results += [PSCustomObject][ordered]@{
Type = $item.FileSystemObjectType ## Just for information if we're getting only 'Folders'-- NOT NECESSARY
DocName = $item["FileLeafRef"]
FullPath = $item["FileRef"]
CreatedBy = $item.FieldValues.Author.LookupValue
CreatedDate = $item["Created"]
ModifiedBy = $item.FieldValues.Editor.LookupValue
ModifiedDate = $item["Modified"]
MonthCreated = (Get-Culture).DateTimeFormat.GetMonthName($item.FieldValues.Values.Month[0])
YearCreated = $item.FieldValues.Values.Year[0]
MonthModified = (Get-Culture).DateTimeFormat.GetMonthName($item.FieldValues.Values.Month[1])
YearModified = $item.FieldValues.Values.Year[1]
DocsInside = $item["ItemChildCount"]
NestedFolders = $item["FolderChildCount"]
}
}
}
}
$results | Export-Csv -Path <YOUR_PATH> -NoTypeInformation
|
![/images/powershell-screenshots/get-list-created-folders-per-month-img6.png]( "/images/powershell-screenshots/get-list-created-folders-per-month-img6.png")
