3 Apr 2019

Setting Row and Column Formats in SharePoint Online with PowerShell

I’m currently spending most of my time working in Office 365, in particular SharePoint Online (along with Microsoft Flow and PowerApps). While the new modern page look is a major improvement on the classic SharePoint on-premise look, customers still want to be able to customize the default look.

To allow customisation of how lists and libraries appear, Microsoft allows you to set view formatting. This uses a JSON object to describe how elements are displayed when a row is loaded in a list view. A useful repository of open source JSON formatting samples is available at https://github.com/SharePoint/sp-dev-list-formatting.

Similarly, you can apply a JSON object to customize how a field (column) is displayed in a list view. Note, neither row or column formatting changes the data in the list item or file; it only changes how it’s displayed to users who browse the list.

While there is a lot of documentation around setting the JSON formatting using the SharePoint Online UI, there is little documentation around using PowerShell to do this. I came across this method of setting the JSON formatting object:

# Get the raw content for the JSON Definition
$listViewFormattingJSON = Get-Content -Raw -Path '.\ViewRowFormat.json';

# Update the List View Formatting Definition
$view = Get-PnPView -List $list -Identity $viewName
$view | Set-PnPView -Values @{CustomFormatter = $listViewFormattingJSON.ToString()}

And similarly, to set the column formatting:

$statusFieldFormattingJSON = Get-Content -Raw -Path '.\StatusColumnFormatting.json';
$statusField = Get-PnPField -List $ListName -Identity 'Status'
$statusField | Set-PnPField -Values @{CustomFormatter = $statusFieldFormattingJSON.ToString()}

These code snippets help to set the customized view below:

List view showing both view and column formatting

Note, I use the SharePoint Patterns and Practices (PnP) PowerShell library to provision and manage my SharePoint Online solutions.

13 Dec 2018

PowerShell Core

I recently read a great post by Paul Cunningham on career advice for IT professionals, and this line stuck with me: ‘Change is a constant’. This really resonated, especially as I belatedly caught up with the recent changes in PowerShell.

I started using PowerShell in 2008, after a more experienced colleague advised me to start spending one afternoon a week learning about new technologies and tools as part of my professional development. This is great advice, by the way, and I would recommend every developer to do it. I also would suggest not asking your manager for permission to do this – if you’re a professional software developer, you need to make time as part of your job to learn new technologies. It is for both for your benefit (professional development) and your company’s (they get a more efficient developer), even if they may not always realise it.

So I started using PowerShell 10 years ago, and I quickly started using it daily, especially as my career started involving more and more SharePoint development, support and administration. However, sometimes I don’t follow my own advice, and I haven’t been keeping up with the major changes happening with PowerShell. It was only when I realised that development had stopped on the AzureRM PowerShell module, and that Microsoft was instead focusing on a new cross platform Az module , that I realised that I had missed a number of major announcements about PowerShell and the direction it was heading. With the release of .Net Core in 2016, Microsoft had started implementing a cross platform version of PowerShell, now known as PowerShell Core 6.0. This was released to general availability back in January 2018. I had missed a major change in an essential developer tool that I use daily.  Change really is constant, and I hadn’t been prepared for this particular one!

The major differences between PowerShell Core and the legacy Windows PowerShell are:

  1. PowerShell Core is cross platform and can be run on Windows, Linux and Mac systems.
  2. PowerShell Core is open source.
  3. Currently, the major breaking changes in PowerShell Core are:
    1. PowerShell Workflows are not available.
    2. The Out-Gridview command is not available in PowerShell Core
    3. PowerShell Core does not support the WMI v1 cmdlets and a large number of other Windows OS specific cmdlets.
    4. While Windows PowerShell ships with the ISE editor, PowerShell Core encourages the use of Visual Studio Code.
    5. PowerShell core is case-sensitive, as it must now run on Unix operating systems.

As PowerShell Core uses the less feature-rich .NET Core and .NET Standard, it currently only offers a subset of the functionality offered by Windows PowerShell. This will change over time as the Powershell Core framework matures, and as more functionality is developed for it. While Windows PowerShell will continue to be maintained (with bug fixes and security updates), there will be no new functionality added to it.

It is pretty easy to get started using PowerShell Core. It can be installed and run alongside your existing Windows PowerShell. I use Chocolatey to install (almost) everything on my Windows PCs, as it allows me to configure automatic updates. To install PowerShell Core using Chocolatey, run:

choco install powershell-core

Once installed, it is worth configuring the following:

  1. Modify your profile. The profile path in PowerShell Core is different to that of Windows PowerShell, and is at C:\Users\{username}\Documents\PowerShell\Microsoft.PowerShell_profile.ps1
  2. Modify VS Code to use the PowerShell Core Integrated Terminal.
  3. Install the Windows Compatibility module to allow PowerShell Core to invoke commands that are currently only available in Windows PowerShell. This will allow you to run existing PowerShell scripts in PowerShell Core without any changes.

As I’m spending a lot of time in Azure, the first new PowerShell Core module I installed was the new Az module, which replaces the older AzureRM modules. Once the module is installed, to ensure compatibility with existing AzureRm scripts you should run the Enable-AzureRmAlias cmdlet, to enable aliases for your existing scripts for the current session only.

8 Nov 2018

PowerShell - Writing to the same line with Write-Host

This is a PowerShell tip that I’m posting for future use. If you want to write information to the same line using the Write-Host cmdlet, you use the –NoNewLine switch:

Write-Host "Countdown - $index seconds remaining" –NoNewline

If you want to overwrite the previous content written, you can use the carriage return character (courtesy of this StackOverflow answer by Dullson):

Write-Host "`rCountdown - $index seconds remaining" –NoNewline

Note, you may need to use spaces to blank out previous content if the previous string is longer than the new content string:

Write-Host "`rCompleted $seconds second countdown.     "

Note, the line above doesn’t use the –NoNewLine switch, so new further content can be written to that line in the console. I’ve used this PowerShell snippet for a simple sleep timer with a visual countdown, when I didn’t want to use the default progress indicator (the Write-Progress cmdlet).

30 Oct 2018

Goodbye to Ubuntu, Hello Windows 10

For the past 18 months, I've been using Ubuntu for my main laptop at home. This is my third attempt to use a Linux distribution as my primary Operating System. However, in the past week, I have went back to using Windows on my laptop. This blog post explains why.

I have been using Ubuntu 16.04 on my laptop, and I have to admit to being impressed initially. Everything. Just. Worked. This was in contrast to previous attempts to using Linux, which had failed due to repeated hardware (driver) issues. This time, my monitors and printers just worked in the same way you would expect on a Windows machine, with no messing around in dot files, or desperate searching of online forums for the correct configuration.

After a while, I started to notice a few pain points. These were mainly around programs that were unavailable for Linux, such as WeChat. I could normally work around these programs but the program I really missed was iTunes. My wife and I both use iPhones and iPads, and as I'm the family IT guy, I'm responsible for backing them up and upgrading them. I created a Windows VM to install iTunes on and used this to backup the iOS devices. However the process for connecting the device to the VM, and forcing the host device to release its connection to the device was convoluted. What should be a 10-15 minute process to backup your iPhone would require an hour or two of configuring and restarting VMs and reconnecting devices.

As I only backed up the devices once a quarter, this was a pain but bearable. But the final nail in the coffin for my current Linux experiment was the news that Dropbox would no longer support (the default) Ubuntu encrypted hard disks from November. I've been a paying customer of Dropbox for seven years, and my workflow is optimised around using it. While I'm deeply unimpressed by their decision to yank support from their Linux customers, I'm not currently prepared to stop using it.

To make sure I would be able to continue to use Dropbox, I've rebuilt my laptop with Windows 10. It is great to be using the same OS both at home and work, and I've built both machines using the same configuration scripts. I had thought about running a dual boot setup at home, but as I already use Windows Subsystem for Linux (WSL), I didn't see it being very useful. If I need a full Linux setup, I can simply spin up a virtual machine.

While I miss the full control I had over my Ubuntu OS (with all the configuration you need to do to try and keep Windows 10 secure and private, you realise that you're very definitely not in full control of it), I do enjoy the ease of using the Windows OS. It is just easier to get stuff done. When I'm at home, I have limited time to work on personal projects, and I don’t want to be messing around trying to fight the OS. While I enjoyed playing with Linux, I'm not sure I'll be rushing to make it my primary OS again. While Linux has dramatically improved (particularly around hardware support), the lack of support for iOS devices, and the lack of certain proprietary programs mean that Linux still isn't ready for widespread use.

29 Aug 2018

No Tea in China...

My family and I were lucky enough to spend June in China visiting my wife's family. We spent a week in Hong Kong, and then crossed the border to spend the next 3 weeks in and around Shenzhen. I thought I post a few random thoughts and observations from my trip...

Hong Kong

I found Hong Kong to be a very friendly city, and I enjoy the opportunity of using my very rudimentary Cantonese. Despite being a incredibly busy city of some 7 million people, it is a great place to visit as a family with a young child (though  we passed on visiting the Peak – the tram didn't look pram friendly). Unfortunately, we spent most of our time organising visas for China, so we didn't see as much of Hong Kong as I hoped. My main impressions were the simply fantastic food (we had our best meal in a little local canteen that most tourists wouldn't look twice  at), the excellent metro service (the Octopus card used widely in Hong Kong instead of cash), and a futuristic and incredibly efficient airport. 

https://sites.google.com/site/andyparkhill/home/blog-post-images/MeiAndMatthewinHK.jpg at a public park in Hong Kong


We spent most of our time in Shenzhen, a city in Guangdong Province, just to the north of Hong Kong. Most of my wife’s family live here, and we spent a lot of our time visiting her relatives and introducing them to our son Matthew for the first time.

Shenzhen is a massive city of over 16 million inhabitants that has sprung up in just 30 years from what was originally a small fishing town. The city is full of skyscrapers (residents are very proud of having the second tallest building in China) and appears to be continually rebuilding itself every few years. People revisiting the city after a few years often get lost, as the cityscape is unrecognisable from the last time they visited.

Shenzhen skyline at Night

Some random thoughts:

  • Rental bikes are absolutely everywhere, littering the streets, but relatively few people seem to be using them. A lot more people appear to be using battery powered bikes.
  • Unsurprisingly in this new city, there is still a significant wealth disparity, with large numbers of migrant workers from the rural China coming to the city for work.
  • Related to the above, you’ll more commonly speak Mandarin than Cantonese – the people you interact day to day with in Shenzhen (in taxis, in the supermarket) will typically be migrant workers and will not speak Cantonese.
  • Like Hong Kong, Shenzhen has a very efficient public transport system (both the local bus network and the growing metro system). The public transport makes up for the traffic in Shenzhen, which is insane.
  • Living costs in Shenzhen seem to be relatively low (for a Westerner), but high relative to the rest of China.

Whilst in Shenzhen, we paid a visit to the Huaqiangbei, the world famous electronics market. It was an interesting place to visit, but speaking to some of Mei’s family, the market seems to be in decline and employs fewer people than in the past.

As I’m currently working in a UK university, we visited a local university campus in Shenzhen. There are a number of different universities in Shenzhen, notably the Southern University of Science and Technology (see also this article). We visited the campus of the Peking University HSBC Business School, which is an international graduate business school, but we also passed by some of the other universities during our travels. It is striking how large the university campuses are, and how much building is going on at them. Higher education is a major focus of the Chinese government, and it is spending a lot of money to turn them into world class institutions. UK universities will not be able to compete on shiny new buildings alone, something which the UK VCs don’t seem to understand. The only way to compete with Chinese universities will be by delivering world class teaching and research – something that UK VCs are actively harming by treating staff as a hindrance rather than an asset. I expect a lot more Chinese students to opt to study in China in the future as the word standing of their universities continue to increase, along with increasing numbers of foreign students.

Enterance to a Hakka Village near Shenzhen

We also got a chance to visit the nearby smaller cities of Pingshan and Dayawan, as well as spending a couple of nights at a local holiday resort.

Other random thoughts:

  • I noticed a lot more use of people and manual labour (in construction and retail) than would be the case in Europe.
  • There is still significant inequality between the city and rural area. This is unsurprising for a country that has changed, and continues to change, so quickly in recent years..
  • There is a very real exercise and fitness culture in China. There were daily dance and Tai Chi sessions in all the local parks that we visited. The parks were filled in the evenings with people exercising (walking and jogging, playing badminton, attending dance classes) after work.

Exercising in an outdoor gym

  • Driving in China (and particularly Shenzhen) is insane. Defensive driving is essential, as drivers will drive very closely in traffic and will change lanes rapidly without indicating. Whilst the Chinese government is promoting self driving cars, they are a long way from ever appearing on China’s roads.  But given that road accidents kill 700 people a day in China, there will be a greater acceptance of self driven cars in China than in Europe or the US.
  • China is a tough place to visit as a foreigner. If you don’t speak or read the language, there is very little allowance given for you, unlike other countries in Asia. Shenzhen was significantly easier for a foreigner than the smaller cities, but still tough. The major street signs in Shenzhen were in both Chinese and English, which was a life saver. I knew a little Cantonese and very little Mandarin, and couldn’t read any Chinese characters. I’m already looking at learning more Mandarin and learning to read Chinese characters in preparation for my next visit.
  • WeChat is an essential app if you’re visiting China, as well as using a VPN to access websites outside of the Great Firewall of China.
  • Despite trying every coffee and tea shop I came across, I was unable to find a decent cup of black tea with milk. If I hadn’t packed my own teabags, I doubt if I would have survived the whole trip.

China is huge country, and I only spent a brief time there. But I really enjoyed our stay, and I came away impressed with the scale of the country’s ambition and the people I met there. I’m looking forward to our next visit to China already.

28 Aug 2018

Reducing Azure Costs

As part of my study for the Microsoft 70-533 exam, I’ve been interested in finding out how to minimize my costs in Azure. This is particularly important as I’m spinning up a large number of virtual machines and web applications as part of my study for the exam. I’ve gathered the following list of tips on how to reduce your Azure costs.


  • Each Azure service is priced differently. Use of the Azure Pricing Calculator to estimate the cost for a specific resource and to identify all the resources/services used to host an application.
  • While is is obvious that different services will have different costs, not everyone is aware that the same service will vary in cost between the different Azure locations. The https://azureprice.net/ site lists the regional pricing for Azure VMs.
  • You’re charged for the resources services you use, so shut down non-production resources when they're not required (like outside working hours). This can be done using either a scheduled script or by using one of the various cloud management applications.
  • Use pre-paid subscriptions to get a discount (even on pay as you go subscriptions) - Microsoft offers discounts of 2.5-5% based on 6 or 12 months pre-payment.
  • If available on your subscription, make use of a spending limit on your Azure account/subscription.
  • Also set up automated email alerts to email you once your account spends more than a set amount.
  • Audit your Azure usage so you know what you are paying for and not using in Azure
    • View your Azure Subscription in the portal
    • Use the free service https://azure-costs.com/ to identify your costs
    • Consider using the Azure Cost Management service offered by Cloudyn, a Microsoft subsidiary.
    • Delete unused resources
  • Use auto-scaling to reduce costs during off hours. These deployment types all support auto-scaling:
    • Cloud Service
    • App Services
    • VM Scale Sets (Including Batch, Service Fabric, Container Service)
  • Scaling could also mean shutting your app down completely. App Services have a feature called AlwaysOn that controls if the app should shut down due to no activity. You could also schedule shutting down your dev/QA servers with something like DevTest Labs. There are also third party services like Park My Cloud.

Virtual Machines

  • Need to shut down and deallocate VMs to stop Azure continuing to reserve the VM's compute resources
    • Use Azure Portal or PowerShell to do this
    • Can also configure the auto shutdown option on the VM
  • Resize over-provisioned VMs
    • Note, the VM will have to be rebooted, so need to avoid when the VM is under peak load
  • Use Azure PaaS features; don’t roll your own on VMs unless you have to.
  • Make use of Dynamic Scaling with VM Scale Sets
  • Use Azure Marketplace VM Images to prevent additional licensing costs (such as for the Windows OS)
  • Use Azure DevTest Labs to automate the start-up and shutdown of VMs, and also implement quotas and policies for VM management
  • Use Reserved Instances to get discounts on the price of VMs - need to reserve for 1 or 3 years
  • Make use of existing Microsoft licences in Azure using the Azure Hybrid Benefit
  • Make use of Azure Site Recovery to back up your VMs
    • Only pay for replication software and the cost of storage

Azure SQL

  • Blob storage offers a cost effective solution to store graphics data. Blob storage of type Table and Queue of 2 GB costs $0.14/month and type block blob costs just $0.05/month. A SQL Database of similar capacity will cost $4.98/month. Hence, use blob storage to store images, videos and text files instead of storing in SQL Database. To reduce the cost and increase the performance, put the large items in the blob storage and store the blob record key in SQL database.
  • If you want to develop, test, or build a proof of concept, then use the freely licensed SQL Server Developer edition. A SQL Server Developer edition VM only incurs charges for the cost of the VM, because there are no associated SQL Server licensing costs.
  • If you want to run a lightweight workload in production (<4 cores, <1 GB memory, <10 GB/database), use the freely licensed SQL Server Express edition. A SQL Server Express edition VM also only incurs charges for the cost of the VM.
  • Use the appropriate edition of SQL Server for other tasks.
  • Use the Pay Per Usage (per second cost of the VM including the SQL Server licence) or the Bring Your Own Licence (BYOL) option as appropriate.
  • Paying the SQL Server licensing per usage is recommended for:
    • Temporary or periodic workloads. For example, an app that needs to support an event for a couple of months every year, or business analysis on Mondays.
    • Workloads with unknown lifetime or scale. For example, an app that may not be required in a few months, or which may require more, or less compute power, depending on demand.
  • Bringing your own SQL licensing through License Mobility is recommended for:
    • Continuous workloads. For example, an app that needs to support business operations 24x7.
    • Workloads with known lifetime and scale. For example, an app that is required for the whole year and which demand has been forecasted.
  • If you have a lot of databases, consider using SQL Elastic pools.
  • Optimize your SQL Database performance

App Services

  • Migrate Apps to Azure App Service, instead of 'Lift and Shift' to a dedicated VM. This is a cheaper option and avoids having to manage a VM.
  • Avoid paying for staging slots on Cloud Services:
    • Delete staging slots as quickly as possible, as these are charged at the same rate as a Production slot
    • Cloud Services cost you money even if the servers are stopped. You have to delete them!
  • Combine web apps with Azure App Service Plans to reduce server count.
  • Stop using Cloud Service Web Roles unless you have to. Use App Services instead - these are also faster to deploy to.
  • Move Worker Roles to Azure Service Fabric or Container Service. This allows you to combine apps and reduce server count and cost.


  • Delete the unused VPN gateway and application gateway as they will be charged whether they run inside virtual network or connect to other virtual networks in Azure. Your account will be charged based on the time gateway is provisioned and available.
  • Use Azure Storage for SMB File Shares, instead of a dedicated VM. Again, this is a cheaper option and avoids having to manage a VM.
  • Improve the performance of your application to reduce the amount of cloud resources it uses. Make use of logging and performance monitoring tools/profilers to identify ways of improving your app.
  • Consider using a CDN like CloudFlare for additional caching.
  • Consider the Azure cold storage option for reduced costs
  • Consider using Azure Automation to automatically build and tear down services/resources when required.