We have same custom SharePoint application pages that enable custom reporting for our support group. Reports are created in plain text (comma separated values / CSV) format and can be opened from the UI via SharePoint application pages. Since some of the reports contain rather complex SharePoint queries, opening them “on-demand” takes some time and system resources as well. While the data in the reports isn’t very volatile, our goal was to automate the report creation, for example, scheduling the task for the midnight hours. We wanted the outcome of the reports to be stored in a SharePoint document library with folder names corresponding to the current date (+ postfix _1, _2, etc. if the folder already exists), the latest version of the reports stored in a dedicated folder called latest.
The “business logic” for the reports was already encapsulated in a custom .NET assembly as static methods with some parameters, we planned the automation however using PowerShell.
I share the results here, as it has some interesting points that one might find useful, like:
– Creating / extending the folder structure in the SharePoint document library using the current date pattern.
– Loading the custom assembly into the context of the PowerShell script.
– Calling static methods of .NET assemblies with parameters from PowerShell.
– Converting the text (String) return value of the methods to byte array to enable uploading the content as a new file into the SharePoint library.
So here is the script with some values that should be updated to match the parameters of your system:
# URL of the SharePoint site
$sitePath = "http://yoursite"
# web of the Reports doc. lib
$webPath = "/ReportsWeb"
# name of the doc. lib.
$docLibName = "Reports"
# name of the ‘latest’ folder
$latestFolderName = "latest"
$site = Get-SPSite($sitePath)
$web = $site.OpenWeb($webPath)
$date = Get-Date -Format "yyyMMdd"
$folderName = $date
$docLib = $web.Lists[$docLibName]
$tryCount = 0
# get the first available folder name corresponding to our date pattern
$newFolderName = $folderName
$folder = $web.GetFolder( ($web.Url, $docLib.RootFolder.Url, $folderName -join "/") )
$folderName = $date + "_" + $tryCount
#creating the new folder
$newFolder = $docLib.Folders.Add("",[Microsoft.SharePoint.SPFileSystemObjectType]::Folder, $newFolderName);
$enc = [system.Text.Encoding]::UTF8
# load the custom assembly (GACed!) into the PowerShell context
Write-Host "Generating report 1"
$rep1 = $enc.GetBytes([MyCompany.ReportHelper.ClassName]::GetReport1($site.ID, "report param 1"))
$newFile = $newFolder.Files.Add($newFolder.Url + "/" + "Report1.csv", $rep1)
Write-Host "Generating report 2"
$rep2 = $enc.GetBytes([MyCompany.ReportHelper.ClassName]::GetReport2($site.ID, "report param 2"))
$newFile = $newFolder.Files.Add($newFolder.Url + "/" + "Report2.csv", $rep2)
# delete the ‘latest’ folder if already exists
Write-Host "Updating latest folder"
$latestFolderUrl = $web.Url, $docLib.RootFolder.Url, $latestFolderName -join "/"
$latestFolder = $web.GetFolder($latestFolderUrl)
# re-create the ‘latest’ folder, copying the actual version
We scheduled the PowerShell script using the Task Scheduler component of Windows. A good introduction into scheduled PowerShell tasks can be found on Dmitry’s PowerBlog.