Get a list of folders created in SharePoint per month using PowerShell

05-May-2020 05-May-2020 957 words 5 minutes

/powershell/get-list-of-folders-created-per-month.png

Contents

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:

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:

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

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

By exporting the results, let’s look at what we have…

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