As an ex-Excel Developer, I tend to resolve any perceived inefficiencies when dealing with any tabular data by automating the snot out of it with Excel and VBA. As a SharePoint developer, there is no shortage of stuff that we need to provision into SharePoint to build business solutions, often repeatedly throughout the development cycle or between environments (Dev, Test, UAT, etc).
PowerShell was always going to be the automation tool of choice, and while PowerShell scripts are commonly fed with CSV files of flat tabular data, editing those has always been painful if you’ve got more than a handful of rows. Excel has always been a far better tool than a text editor for quickly editing tabular data, thanks to features like autofill, but that’s just the tip of the iceberg…
PowerShell Deployment Agent
So, I’ve been using Excel and VBA to deploy things for a long time (about 10 years at best guess). I’ve changed companies a handful of times over that period, and every time I do I create a new and better Excel model for generating stuff in whatever platform I was working with. Last year, I started to open source that model as it had reached a maturity level where I no longer want to start over – it’s pretty darned solid. The simplest model of this was called CSV-Exporter and it did what it said on the tin. I’ve extended it into what I now call my PowerShell Deployment Agent (PSDA), which doesn’t just export CSVs from Excel Tables, but also launches a PowerShell script of your choosing to streamline the deployment process.
By careful script design, Excel filtering, and of course some VBA, this allows for some very fine-grained control over exactly what you want to deploy. To the extent that you can just highlight a bunch of rows in a sheet and hit CTRL-SHIFT-D to deploy them to your target:
When / Why would you use it?
Excel’s always been a good way to store configuration data, but it really comes into it’s own as launching pad for PowerShell when you have rows of data that need to be pumped through the same cmdlet or function.
To prove how easy it is to get started, we’ll go with the the use-case that I’m usually working with: SharePoint.
So let’s open the workbook and pick something simple that we want to deploy. How about folders in a document library?
All we need to do once we’ve input our target site is:
- Find the PnP PowerShell cmdlet we want (Add-PnPFolder in this case)
- Click the ‘New Blank Sheet’ button
- Select the name for our sheet (‘Folders’ in this case – a new .ps1 file with the same name will be created from a template in the ‘Functions’ subfolder under the path to your deployment script)
- Copy and paste the cmdlet signature to let PSDA know what columns to map to the cmdlet parameters,
- Fill in our data
- Double click the URL of the target site we want to deploy to
This is obviously the most basic scenario and there’s a good chance that you’ll want to customize both the auto-generated script and the table (with some of the advanced features below).
Speaking of… what other benefits of using Excel over raw CSVs are there? I’m glad you asked.
You obviously can’t have a calculated value in a CSV file, which means that your PowerShell script is more complex than it needs to be, by performing that calculation on each row at run-time. Excel is clearly the superior tool here – you can see if your calculation is correct right there in the cell.
PSDA Perk – Formulas are exported as values when you are deploying against a target, but are preserved when you want to export your data for versioning, etc.
You obviously can’t have comments in a CSV, but these are very useful in Excel, particularly in column headers to advise what data/format to put in that column.
PSDA Perk – You can preserve column header comments when exporting from the workbook. They will be reinstated when re-importing.
Want to guard against data entry errors while keeping your script clean? Use Excel to prevent erroneous entries before deployment with data validation. You can restrict based on a formula, or a reference cell range (there’s a reference sheet in the workbook for that).
PSDA Perk – Data validation rules will be exported and reinstated on re-import.
In addition to data validation, conditional formatting is a powerful way to show that some data is incorrect or missing under certain conditions. Obviously, from a data entry standpoint, we don’t need anything too fancy here – usually setting the font or background of the cell to red when a formula evaluates to false is all we need to prompt the user.
PSDA Perk – Basic conditional formatting (as per the above) can be exported and reinstated on re-import.
A Note on Credentials
If you went to the trouble of downloading the workbook and poking it with a stick, you’ll note that there is a single column in the launch table on the control sheet for the credential to use for each target environment. Nowhere to put a password. Because you shouldn’t be storing passwords in Excel workbooks. Ever.
Instead, you should be using something like CredentialManager, which leverages the Windows Credential Manager (a safe place to store your admin passwords). Which means that you just refer to the label of the credential in WCM in the workbook. Nice and clean and means that anyone getting your workbook doesn’t have the keys to your environments listed.
If you are using the outstanding PnP PowerShell cmdlets for deploying to SharePoint Online/On Premises, you get this functionality OOTB (no need to use CredentialManager).
Thanks for checking this out and please try out the workbook and let me know what you like or needs improvement.