Don't leave yet! I know you came to this place to learn about Power Platform products. I do believe that after finishing this challenge, you will consider SharePoint's list functionality for your solutions.
Challenge Objectives
🎯 Learn about SharePoint list formatting
🎯 Apply view formatting to a template list
🎯 Learn from examples
🎯 Learn when formatted SharePoint lists are a good fit
🤓 What Is SharePoint List Formatting?
We all know SharePoint lists. Many of us even started creating Power Apps and Power Automate flows on data stored in a SharePoint list. Just like on regular databases, you can create different views on that list. In practice, this means showing/hiding columns, sorting on a specific column, filtering list items, etc. Those settings are then saved in a view so you can quickly get the perspective on the data that you want.
Those views are still just a list. It uses the Fluent UI framework we learned about in Challenge 007, but aside from that, it is still boring. In many cases, boring is good when you just want to show data, but in some cases, you want some visualization to make the list a bit more expressive.
That is what list formatting can do for you. Roughly speaking there are three sorts of List formatting.
Column Formatting
With column formatting, you can add some visualization to a specific column. This is mostly used for conditional formatting like you might be familiar with when using Excel. You can see an example of this functionality in the image below on the Status column.
Conditional formatting can be set up in the user interface, as shown below. More complex visualizations like the column Effort(Days) and Assigned To require a little bit of JSON code.
View Formatting
When you apply view formatting, you will alternate the way the list items are displayed. You can apply this for simple adjustments like alternating row colors, to complex visualizations like the heat map below.
Form Formatting
Form formatting allows you to adjust the form that you use when you create a new record within SharePoint. You can use this to rearrange fields, add a header/footer (see example below), specify conditional formula to show/hide columns, etc.
In this challenge, we will focus on view formatting. When you know how to do it, the column formatting and form formatting options will be a walk in the park.
First Things First
SharePoint List
Before we start playing around with view formatting, we need a SharePoint list. let's create one.
Go to office.com and log in with your developer account
open the app launcher and select SharePoint
Create a new Site. I selected a Team site and called it Challenge 010
On the Home page of your new SharePoint site, select List under the New Button
Select Travel requests from the Templates list
Add an item to the list
Install SP Formatter
As you've seen, we need to create a JSON file. To make the coding and testing as easy as can be, we will install a helpful tool.
If you are using Edge, install the SP Formatter extension. If you use profiles in your browser, make sure to install it to the profile of your developer account. The chrome extension can be installed here.
You also need to install the SP Formatter extension to Visual Studio Code. Just click on extensions in the primary side bar and search for SP Formatter. If you don't have VS Code installed yet, you can find it here.
Design
If we want to adjust the way a list item is visualized, I prefer to produce a design sketch first. For the Travel request list, I created the wireframe below. I am sure you can create a more compelling design, so feel free to adjust it if you want to.
Format the view
Create a new view
Now that we have everything prepared, we can start formatting the view. I like to create a new view for formatting, so the original is still there if needed. You can do this by pressing the view selector and saving it as a new view.
Set up SP Formatter
Once the new view is created, press the view selector again and select the option Format current view
Press Advanced mode at the bottom
Copy the JSON code that shows up
Open Visual Studio Code and create a new text file
Paste the copied JSON code
Right-click on the code and select SP Formatter: start a new session
In your browser tab where you have the view opened, select the SP Formatter extension and Enable enhanced column formatting for this tab
Every change you make in Visual Studio Code is directly pushed to the SharePoint view, so you can instantly see what it will look like. If you don't use the extension, you need to copy-paste the JSON and press preview each time. Not anymore. If you have a dual monitor set up, it's even easier to make the adjustments.
First formatting
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/row-formatting.schema.json",
}In Visual Studio Code, add a comma after the default string and add a new row (see snippet above)
On the new row, press ctrl + space. The pop-up that is shown is called IntelliSense. It is a list of suggestions that you can select from. When you installed the SP Formatter extension, you added the suggestions to IntelliSense.
Select hideColumnHeader from the list. IntelliSense instantly shows false and true as suggestions. That's because this parameter is a Boolean, so these are the options. Select true.
Have a look at your SharePoint list. Notice the headers are indeed hidden.
Add a new row that hides the selection option and see the difference in your view. By now, your JSON should look like the snippet below.
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/row-formatting.schema.json",
"hideColumnHeader": true,
"hideSelection": true
}Adjust the command bar
There is also the option to change the command bar. You can hide the action, change the text, change the tooltip, change the icon, change the position, etc. You will need to use the commandBarProps parameter for this. You know you can find it by pressing ctrl + space.
If you ask IntelliSense for suggestions again within the curly brackets ({}), you will see the commands property as the only option. Once you've selected it, you will see square brackets ([]). In JSON this means an array is expected. Each item is specified within curly brackets ({}). If you type the first, Visual Studio Code will create the closing curly bracket for you.
Our aim is to adjust the New button on this view, and make it look like the image above. We want the plus icon changed into a rocket and change the text into Challenge 010. First, we have to specify which command we want to adjust. Search for "key" using IntelliSense, and for "new".
Add a comma and search for "text". IntelliSense cannot suggest what to name it, so we must type it ourselves. Enter "Challenge 010". Make sure it has the same structure as the "key" parameter. The button on SharePoint should now show Challenge 010 instead of new. The last thing we need is to change the icon.
For this, we search for "iconName" in IntelliSense. Unfortunately, the different icons are not part of IntelliSense. The JSON expects a Fluent UI icon name. You can find all icons here with a search function. If you search for rocket on that page, you will notice that Fluent UI named it Rocket. So, let's enter that.
Below you can see the code snippet of the result.
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/row-formatting.schema.json",
"commandBarProps": {
"commands": [
{
"key": "new",
"text": "Challenge 010",
"iconName": "Rocket"
}
]
}
}More information about command bar customization can be found on this Microsoft docs page.
Format the row
Now we really get to the cool part. We will start with a snippet of the JSON that I created based on the earlier made design. We will then go through what is there, what it means, and how you can find your way around. Because of the limited width of this page, I recommend copying the snippet and pasting it into a new text file in Visual Studio Code.
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/row-formatting.schema.json",
"hideColumnHeader": true,
"hideSelection": true,
"rowFormatter": {
"__comment__": "DIV MAIN",
"elmType": "div",
"attributes": {
"class": "ms-bgColor-themePrimary"
},
"style": {
"padding": "10px",
"column-gap": "10px",
"align-items": "stretch"
},
"children": [
{
"__comment__": "DIV LEFT",
"elmType": "div",
"defaultHoverField": "[$Requester]",
"attributes": {
"class": "ms-bgColor-themeLight"
},
"style": {
"padding": "inherit",
"text-align": "center",
"display": "flex",
"flex-direction": "column",
"justify-content": "space-between",
"width": "120px"
},
"children": [
{
"__comment__": "IMG REQUESTER",
"elmType": "img",
"attributes": {
"src": "=getUserImage('[$Requester.email]','medium')"
},
"style": {
"border-radius": "50%",
"margin-bottom": "10px" }
},
{
"__comment__": "DIV REQUESTER NAME",
"elmType": "div",
"txtContent": "[$Requester.title]",
"attributes": {
"class": "ms-bgColor-white"
},
"style": {
"text-align": "center",
"font-weight": "500",
"padding": "inherit"
}
}
]
},
{
"__comment__": "DIV MIDDLE",
"elmType": "div",
"attributes": {
"class": "ms-bgColor-themeLight"
},
"style": {
"padding": "inherit",
"align-items": "stretch",
"flex-grow": "1",
"display": "flex",
"flex-direction": "column",
"justify-content": "space-between"
},
"children": [
{
"__comment__": "DIV TITLE",
"elmType": "div",
"txtContent": "= '🎯 ' + [$Title]",
"attributes": {
"class": "ms-bgColor-white ms-fontColor-themePrimary"
},
"style": {
"text-align": "left",
"font-weight": "700",
"padding": "inherit"
}
},
{
"__comment__": "DIV TRAVEL DATES",
"elmType": "div",
"txtContent": "='📅 ' + toLocaleDateString([$TravelStartDate]) + ' - ' + toLocaleDateString([$TravelEndDate]) + ' (' + [$TravelDuration] + ' days)'",
"attributes": {
"class": "ms-bgColor-white"
},
"style": {
"text-align": "left",
"padding": "inherit"
}
},
{
"__comment__": "DIV FLIGHT",
"elmType": "div",
"txtContent": "='✈️ Flight: €' + '[$EstimatedAirfare]' + ' (' + '[$Airline]' + ')'",
"attributes": {
"class": "ms-bgColor-white"
},
"style": {
"text-align": "left",
"padding": "inherit"
}
},
{
"__comment__": "DIV HOTEL",
"elmType": "div",
"txtContent": "='🏨 Hotel: €' + '[$EstimatedHotelCost]' + ' (' + '[$Hotel.DisplayName]' + ')'",
"attributes": {
"class": "ms-bgColor-white"
},
"style": {
"text-align": "left",
"padding": "inherit"
}
},
{
"__comment__": "DIV TOTAL",
"elmType": "div",
"txtContent": "='💵 Total: €' + Number([$EstimatedAirfare] + [$EstimatedHotelCost])",
"attributes": {
"class": "ms-bgColor-white"
},
"style": {
"text-align": "left",
"font-weight": "500",
"padding": "inherit"
}
}
]
},
{
"__comment__": "DIV RIGHT",
"elmType": "div",
"attributes": {
"class": "ms-bgColor-themeLight"
},
"style": {
"padding": "inherit",
"column-gap": "10px",
"display": "flex",
"flex-direction": "column",
"justify-content": "space-between"
},
"children": [
{
"__comment__": "IMG MAP",
"elmType": "img",
"attributes": {
"src": "='https://dev.virtualearth.net/REST/v1/Imagery/Map/Road?mapSize=300,200&pp=' + if([$Destination.Coordinates.Latitude], [$Destination.Coordinates.Latitude], '0') + ',' + if([$Destination.Coordinates.Longitude], [$Destination.Coordinates.Longitude], '0') + '&zoomlevel=12&key=Ap5VTkkVxq1ieQh3L3OKlG_hC16LsKjL_3kTeLdTKwybtpHlEtF5Y5VvrWnpdOZJ'"
},
"style": {
"margin-bottom": "10px"
}
},
{
"__comment__": "DIV APPROVE",
"elmType": "div",
"txtContent": "APPROVE",
"customRowAction": {
"action": "setValue",
"actionInput": {
"Approved": true
}
},
"attributes": {
"class": "ms-bgColor-themePrimary ms-fontColor-white"
},
"style": {
"text-align": "center",
"font-weight": "700",
"padding": "inherit",
"cursor": "pointer"
}
}
]
}
]
}
}rowFormatter
As you can see, most of the formatting is nested in the rowFormatter property. In the previous step, we learned that a squared bracket indicates that an array is expected. The rowFormatter comes with a curly bracket when using IntelliSense, so just a single item is expected. This means that everything is nested into one item.
elmType
If you add the rowFormatter parameter to the JSON file using IntelliSense, it will add the parameter elmType for you. On this Microsoft docs page you can find the different options that valid. If you scroll through the code, you can see that I mainly use the div type.
div
A div is HTML for division. A div is an element that lets you group similar elements to a page. I use quite a few divs for sectioning the page. The image below shows how I translate the design into the divs, and how they are nested.
__comment__
The comment property I use in the JSON does not affect the formatting of the page. It is only there for legibility. You will find the divs and img types outlined in the image above as __comment__ in the JSON text file. This way it is easy to identify which element you are dealing with, as there is quite some nesting going on.
children
The nesting of elements is done with the children property. This time, there is a squared bracket, again delivered by IntelliSense. By now, you should know that this means that multiple elements can be children of one element. DIV LEFT, DIV MIDDLE, and DIV RIGHT are children of DIV MAIN. Those divs have children of them own. If you hover over the line numbers in Visual Studio Code, chevrons will show up. You can use these to collapse and expand the code to ease the process of browsing around.
style
Across the JSON file you will see multiple styling elements. Some of those I use are:
align-stretch in DIV MAIN is used to make the child divs equal of height.
padding you are probably familiar with. If you see inherit, it is the same padding as the element's parent. A nice feature if you want to adjust it later.
font-weight I use to make some text a bit bolder.
align-items in DIV MIDDLE is used to make the elements stretch out. This makes the visual stretch across the screen, and adjust if you switch to a different window size. So a little responsiveness.
cursor changes the way the cursor is shown. DIV APPROVE must act as a button, so that's why I use it.
txtContent
This property is used to show some text. We can either use hard-coded text, or reference some value from the list item. [$Title] in DIV TITLE will show the title of the record. I added an emoji to it, so you can see how to combine hard-coded and referenced text. To find out the exact name of the column, I go to the List settings and select the column. The snippet below shows the URL I got by then. At the end you can find the column name.
https://powerbouwer.sharepoint.com/teams/Challenge010/_layouts/15/FldEdit.aspx?List=%7B0F1BBFA5%2D5095%2D4AFC%2D86F7%2DE0A417DEC060%7D&Field=TitleHTML & CSS
SharePoint uses the JSON file as input for formatting. A web page is built of HTML and CSS code. That is why if you are familiar with HTML and CSS, you will recognize some naming. If you want to format something on SharePoint, you can search online on CSS fora for some answers.
attributes class
That is exactly what I did for the class, which is part of attributes. I didn't want to color of the elements hard-coded but based on the SharePoint theme. I found the link below that is a helpful resource for finding the right CSS class for your elements.
attributes src
the src used for the img elements. You use it to specify the source of the image. For the image of the requester, I reference the Requester column. For the map element, it is a bit trickier. I searched how to get a static map in the Microsoft documentation. I found the link below.
I also noticed that I needed some key. Based on the experience from Challenge 004 I expected that this had to do with an API. After registering and creating a new key I was ready to make it work.
Copy from examples
This is the single most important tip I can give. I tried the example from the Microsoft docs page, but that wasn't working for me. So, I went to this amazing GitHub repo where all sorts of examples are listed. I searched for virtualearth (as that is the URL for the API), and boom. There was an example that worked for me.
The repo is part of the Microsoft 365 Patterns and Practices community page. If you haven't checked it out, do so right now!
defaultHoverField
This parameter makes a hover card pop up when hovering over the specified element. I reference Requester to DIV LEFT. This is the default hover card for a person. I apply it to DIV LEFT because both the image and the name are there. This way you can easily see more detailed information if needed. The page below gives some extra information on formatting hover cards.
customRowAction
The last thing I want to explain. What this thing does is trigger an action when the element is pressed. I have pasted this section below. We set the value of column Approved to true. That's all we need of the end user. clicking a button.
"customRowAction": {
"action": "setValue",
"actionInput": {
"Approved": true
}
}I only set the value of one column, but you can set multiple if you want to.
Result
Below you will see the result of the JSON file formatting the list view. Instead of purple, it is blue. We have learned that it will change according to the site theme. That way all can use this formatting on their site.
Because it is a template SharePoint list, I have made a Pull Request on the pnp list formatting GitHub repo, which has been accepted. You can find the JSON file here with some instructions.
When to use
You might still be wondering what this has to do with the Power Platform, as that is a big part of this site's URL. I will explain why I think every Power Platform developer should know these formatting capabilities of SharePoint lists.
Easy sharing
If a Power App is based on a SharePoint list, both the list and the app must be shared with the end-users. Synchronizing these permissions is not something business users are always keen on.
Another advantage is that in many cases there are already permissions set to the SharePoint list. You only need to focus on how it looks, and not so much on who can use it when. In the example we used, a useful view would be to filter it on Approved = No. That way the item will not be included in the view after approving it. The view will act as an action list. Standard SharePoint functionality you do not have to build yourself.
Flexibility
A SharePoint list can be viewed in many places. You can embed it in teams, view it in the Lists app, add it as a web part to a page, it's responsive by default, etc. You can embed a Power App to a page, but to make that look nice, you need to make it responsive. We have learned in a previous challenge how that works, but it requires significantly more time than formatting a SharePoint view.
Maintainability
Many companies have someone walking around with some SharePoint experience. This is not always the case for Power Platform yet. A few months ago, a client asked me to build an app for them. After understanding their requirements, I suggested a formatted SharePoint list with supporting Power Automate flows. The main reason for this was that they have quite a few SharePoint experts that can help if needed. This way they can maintain the solution themselves.
Or when not to
SharePoint is not a database! You hear this a lot. I am not a database purist whatsoever. If a solution works, it works. However..... the lifecycle management of a SharePoint list is not as streamlined as that of a solution. But when ALM is required, I believe you are already outside of the citizen development scope. So, for business-critical solutions, I would not recommend it.
Summarized
Just formatting a list that is already in use is what it could work for. I know it requires some code, which might be a bit tricky for citizen developers. So, keep it simple, do not go for too fancy. As I mentioned earlier about how I used it, a few support flows for data flow and notifications are a good option.
Opmerkingen