You can file this under “better late than never”! Our annual deadline is coming up very quickly, as March 31st is the last day you can enter contributions if you are up for renewal in July 2019. For most of us, that is a 1 year period of April 1st to March 31st but with the monthly MVP awards, there are some who have a longer than 1 year first period before renewal. I know I had a longer period last year for my first renewal…
I have had a change of heart since writing this previous post, about how I enter my contributions. In that post, I indicated that I would enter 1 summarized entry for the year with total blogs written to keep things simple. I realized during this past week’s MVP Summit, having had various conversations with product teams, that they prefer details. So, I am now uploading one contribution per blog post and each one has the appropriate categories of contribution on them. I had not done this in the past because the amount of entries using the website to enter them was going to take far too long to be worth it. I wrote 75 blog posts last year, there was no way I was going to put them in individually!
Then I started to play with the PowerShell commands via the API and I’m now a convert. This post is about how to use the MVP API via PowerShell and upload a CSV file with your contributions.
A couple of MVPs, Francois-Xavier Cat and Emin Atac created a PowerShell module to interact with the Microsoft MVP portal Rest API, to automate submitting contributions (among other things). That post is here…
I’d heard about a way to upload multiple contributions via the API and landed on another page on Francois-Xavier’s site which gave me most of the info I needed to get going, however I was still having some issues as I’m not a PowerShell person. Or a developer.
This post is not going to cover ALL of the core things about the API since the blogs referenced above do a great job of telling you how to get started. What I will cover is where I got stuck and how I got it working to successfully upload my contributions en masse from a CSV file.
What’s covered in the blogs above
These steps I’m not covering in detail, but you need to start here…
- Get a developer key with your MVP site login, here. Specifically, what you need is the Primary key (there are 2, you don’t need the Secondary key for this).
- Follow through the steps from Francois-Xavier’s blog about installing the MVP module in PowerShell etc. The github page he created for this has a lot of useful info on this.
Get your contributions in the right format
Below this is an Excel template I created to make this easier for me to do quickly. The format is clearly laid out on the pages linked above but this is where you can easily spin your wheels if you get something slightly wrong in syntax or formatting. The Excel template has a ReadMe page with the key info you need, like date format.
I didn’t get too fancy with the template, but I did take the time to download all of the contribution types and contribution areas so it is up to date as of this year’s (2019) award category structure. I’ll attempt to update this as things change but keep in mind you may need to do that on your own if this gets out of date! They key thing is the wording needs to be exactly what’s in the drop down lists on the MVP site where we enter community activities.
Long story short: The Full Contributions tab is to list everything including multiple award categories. However, the upload doesn’t accept multiple award categories yet so there is another tab “For CSV” which is simply formulas pulling the columns up to ContributionTechnology but ignoring the multiple award categories columns from the “Full” tab. Some day the PowerShell bits may get updated but today it is single contribution area only. Personally, I used the Full Contributions tab to filter on the contributions I had in multiple areas and manually updated them after uploading. Not ideal but ….
So, once you have your contributions in the spreadsheet, do a “save as” on the “for CSV” tab to save that tab to a CSV file somewhere on your computer which you will navigate to from PowerShell next.
Bear with me if I am not using the 100% proper terminology on this part. As I said above, I’m neither a developer nor a PowerShell guru so this was all new to me. If I’m giving bad advice, please reach out so I can update the post accordingly. I’m attempting to err on the side of caution, at least based on what I know and learned in this process.
Let’s get started. First off, you need to run PowerShell as admin. I don’t know if there is an option not to have to do that, so I ran it as admin.
Saving your MVP key in your PS profile
If you don’t have a PowerShell profile (or don’t know if you do or not), in PS simply type:
… and it will open the profile in Notepad and/or prompt you to create one and save it. While testing this step again for this post, on a different computer, I got a “path doesn’t exist” message and didn’t know where to save my profile or what to call it. If you get that message, then simply type in $profile in PowerShell, it will display what and were your file should be, in order to create the file in that folder with that filename.
I didn’t have a profile so these 3 lines below that I pasted in were the only thing in my profile after this step. The # is a comment, put whatever you want there. Put your primary key in the YOURKEY section in single quotes. The $MVPSubscriptionKey is simply a variable name so make up whatever you want. This is setting a variable then using it to set the key for the MVP module. Putting this in your profile means it does this automatically and you never have to think about your key again.
# MVP Subscription Key
$MVPSubscriptionKey = 'YOURKEY'
Set-MVPConfiguration -SubscriptionKey $MVPSubscriptionKey
Execution Policy to load your profile
Once your profile is saved, you’re done, except that you may need to set the proper execution policy on your machine to allow the profile to be read and loaded upon running PowerShell. This is what I got, initially, until I set the proper execution policy. “…. profile.ps1 cannot be loaded because running scripts is disabled on this system.”.
It seems that this line of code is the bare minimum permissions to let this happen and it needs to be run once only on your machine:
Set-ExecutionPolicy -ExecutionPolicy RemoteSigned
If you open PowerShell without doing that, and don’t have that or higher as the default execution policy, you will see a red error in PS about your profile not being able to be executed.
After doing this, if the execution policy is correctly set, you will now be prompted to log in with your MVP credentials. This will occur each time you launch PowerShell so if you do other work in PS, you may want to comment out the profile bits for every day purposes to avoid having to go through this each time.
Set session-based Execution Policy to run the MVP commands
This is the part that scared me to be honest, as I didn’t want to permanently allow any PowerShell scripts to be executed on my machine (by setting this to unrestricted by default) but you need to set execution policy to unrestricted to run these commands. To do that temporarily – i.e. for that session of PowerShell – you would run this:
powershell.exe -ExecutionPolicy Unrestricted
More info about this can be found here on the docs.microsoft.com site. What I like about this is once I close PowerShell, execution policy reverts back to the default which in my case is RemoteSigned. What I actually did with that line above is put it in my profile with a # sign (as a comment) so I could easily copy and paste the command into PS each time I need it, instead of looking it up each time.
NOW you can test PowerShell to get your profile
Test that everything is working and your key works etc. by pulling your profile:
This should return your profile information to you in the console window. If you haven’t added the subscription key to your profile, then you would need to run this first, before running any Get commands.
Set-MVPConfiguration -SubscriptionKey 'yourprimarykey'
Import from CSV
Finally, the good part! The syntax I’m using here is assuming I have navigated in PowerShell to the directory where my CSV file is located, hence I’m simply referencing a filename, not a path and filename. YMMV here. “YourFileName.csv” is whatever you’ve called your file.
Import-CSV YourFileName.csv | New-MVPContribution
At this point, it should just work, if you’re properly authenticated. If you’re risk-averse, simply put 1 or 2 things in your first CSV file so that it’s easy to delete the info if it’s wrong!
In terms of timing, it took approximately 30 seconds to load in 75 contributions when I did mine. It took more time than that to figure this out! 🙂
I hope this helps you get your contributions uploaded in bulk, I know there were a few details left out of the original developer blogs that weren’t obvious to me and I figured I’m not the only one who may run into the same hiccups!