Live London Underground Line Status – SharePoint View Formatting & Power Automate

Display on the homepage of your SharePoint intranet a live feed of London Underground line statuses using the publicly available Transport for London (TFL) API. The results are retrieved by Power Automate (Flow), then written to a SharePoint list and using SharePoint view formatting the list view is styled so it looks like a familiar TFL tube status board with correct line colours. It also reports on the Overground, Ferry, TFL Rail and Tram. The status(es) of the lines can then be clicked if there is any further information i.e. delays/suspension/closure etc.

Why not follow this blog post and place this on your SharePoint Intranet today?

Update: Have now updated the solution so you can use Azure Logic Apps for the processing rather than Power Automate. Blog here.

Install Time: 15 mins

Technologies Used

• SharePoint
  • List (used to store the latest TFL line status data)
    • View Formatting (JSON used to style the list view into the familiar TFL Status board)
• Power Automate (used for creating the Tube Status SharePoint list using SharePoint REST API and then on a scheduled basis retrieve the latest TFL tube statuses)

How To Install The Solution

I have attempted to simplify/automate the deployment of the solution as much as possible. I have combined this into two flow templates which with a few very minor changes do all the work for you to deploy and configure this solution!

• 1 x Flow – Run one time to deploy the SharePoint list (creates columns, views, applies view formatting JSON and quick launch navigation links) to save you needing to create all of this manually.
• 1 x Flow – This Flow will then run frequently on schedule and connects to the TFL Status web API and retrieves the status of each line, then writes the data to the SharePoint list if there are any changes.

Prerequisites

• You must be licenced and able to create Power Automate flows
• You must have a valid SharePoint Online license and permission to create lists.
• You must have a modern SharePoint site where you can store the data for the app.
• Download the assets from GitHub (https://github.com/LeonArmston/LondonUnderground)

Create Tube Status Site and/or Tube Status List

This solution requires a SharePoint site to be created or an existing SharePoint site can be used. If you are using an existing modern SharePoint site then preceding section below can be skipped.

Create a SharePoint site

1. Create a new modern SharePoint site from either the SharePoint admin centre (https://youtenantname-admin.sharepoint.com) or from SharePoint home.

2. Once the site is create take a note of the URL of the site as you will need this for future steps.

Import the Tube Status SharePoint list deployment flow

This Flow deploys the Tube Status SharePoint list (columns, views (CustomFormatter) & navigation links) all from the SharePoint list deployment Flow using REST API.

1. Go to flow.microsoft.com
2. Select My Flows from the left navigation pane.
3. Select Import on the command bar
4. Upload the DeploySPList-LondonTubes.zip package from GitHub
5. Add a SharePoint connection for the new flow by selecting the Select during import link and completing the form.

6. If you need to create a new SharePoint connection, select Create new in the Import setup pane.
7. Select New connection on the command bar.

8. Search for the name of the connection SharePoint.
9. Select the connection you created.
10. Select Save.
11. Select Import.

Configure the Tube Status SharePoint list deployment flow

Now the Flow has been imported it needs editing to add the url of the site you wish to install the tube status SharePoint list on.

1. After the import is done, go to My flows and refresh the list of flows.
2. Select the newly imported flow, DeploySPList – London Tubes.
3. Select Edit on the command bar.
4. Open the Initialize variable – Target Site for Lists card.
5. For Value, enter the Url of your SharePoint site.
6. Open the Initialize variable – List Name card.
7. Leave this as “Tube Status” unless you wish to name your list differently.

8. Select Save.

Run the SharePoint list deployment flow

1. Go back to the detail screen for the DeploySPList – London Tubes flow
2. Select Run on the command bar.
3. Select Continue, and then select Run flow.

4. Check the status of the DeploySPList – London Tubes flow in run history to ensure the status is Succeeded.

5. Browse to the SharePoint site you specified and go to Site contents. You will see that a List called Tube Status has been created and links have been added to the quick launch (left hand navigation) to the different SharePoint Views created.

6. Navigate to the All Modes view on the Tube Status list. Click on the view selector in top right hand corner of the list – then select Format current view.

You will now see the View Formatting JSON that styles the list so it looks like a London Underground status board (when the list has data). There is no need to make any changes and instead just click the Cancel button.

Add scheduled Flow to retrieve the latest TFL line statuses and write them to a SharePoint list

The steps below will go through the process of importing and then configuring a Flow which runs on a scheduled basis (currently every 15 mins but this can be adjusted) to connect to the TFL API and write to a SharePoint list.

This Power Automate Flow connects to the TFL API using the endpoint: https://api.tfl.gov.uk/Line/Mode/tube,dlr,overground,tflrail,tram/Status using a HTTP action and the results are given in JSON format which the Flow then parses and sorts to add to the SharePoint list.

Import the Tube Status Scheduled Flow

1. Go to flow.microsoft.com
2. Select My Flows from the left navigation pane.
3. Select Import on the command bar
4. Upload the LondonTubes-TFLTubeStatusAPI.zip package from GitHub
5. Add a SharePoint connection for the new flow by selecting the Select during import link and completing the form.

6. If you need to create a new SharePoint connection, select Create new in the Import setup pane.
7. Select New connection on the command bar.

8. Search for the name of the connection SharePoint.
9. Select the connection you created.
10. Select Save.
11. Select Import.
12. A message will then be displayed on the page saying “import failed for one more package resources“. Click Save as a new flow in the Next steps section.

This failure is expected and is due to the SharePoint connectors in the template still pointing to a site and list in my M365 test tenant, so we will next edit the flow to point the SharePoint connectors to a site in your tenant.

Configure the Tube Status Scheduled Flow

1. The Flow will then be displayed in edit mode and you will see three of the SharePoint action have orange triangle exclamation marks beside them indicating there is a problem with them. Click on the SharePoint action

2. Select an existing connection or create a new one.

Select the username of a user with edit permissions on the SharePoint site being used for the list.

3. The SharePoint action will then change to display to display fields including Site Address and List Name. Update the Site Address to remove my dev SP site address and instead put in the URL of the SharePoint site used earlier by a Flow to install the Tube Status list and views on.
4. Next once a valid Site Address has been entered you will then be able to see valid lists in the List Name dropdown instead of there being a list GUID i.e. 38bc48ed-c681-4e97-aeaa-10b701c6c6e7. Select the Tube Status list

5. Expand the following actions and conditions by clicking on the action so we can find the two remaining SharePoint actions that need to be fixed.

6. Repeat steps 1 to 4 above for the two remaining SharePoint actions. The same Site Address and List Name will be used.

7. The SharePoint actions will then look like this when the connection has been changed and then the Site Address and List Name has been resolved.

8. Next click the Save button to save the Flow in the top right hand corner of the Flow command bar.

Run the Tube Status Scheduled Flow

1. Go back to the detail screen for the London Tubes -TFL Tube Status API flow

2. Select Run on the command bar.
3. Select Run flow.

4. The flow run should then be started successfully, Click on the Flow Runs Page link

5. The running Flow should then be displayed and will update as each action has been activated and then should display Your flow ran successfully as a green bar along the top.

This Flow will now run every 15 minutes and connect to the TFL API, retrieve the latest Tube Statuses and then update the SharePoint list if there are any changes.

View the Tube Status SharePoint List

Finally we will check the SharePoint list to confirm it has been updated with TFL Line (Tube) status data.

We can now see your Tube Status list has been updated and we can see the it has been formatted to look like a TFL status board.

There are also some additional views on the SharePoint list that have been created and are available in the Quick Launch navigation or the View drop down.

• All Modes : This is the default View and it shows all lines styled in TFL style by SharePoint view formatting.

• Tubes Only : View showing just the tube lines styled in TFL style by SharePoint view formatting.

• All Modes (No Styling) : View showing all the Tube Status data without any view formatting. The other views looks like this initially but then SharePoint View formatting magically transforms this data to appear like TFL Status boards.

Add The List View WebPart to a SharePoint page

I have specifically coded the list view formatting so the special styled view will fit into the Vertical section (special right hand side web part zone) of your modern SharePoint page.

1. Edit your SharePoint page

2. Click the plus + button in the top left hand corner of the content section. Then select Vertical section

3. The new Vertical section will appear on the right hand side, then select the plus + button to add a web part to the section.

4. Type list in the new web part selector and then select the List icon to add a List web part.

5. Select the Tube Status list

6. The Tube Status list default view All Modes will then be displayed in the List web part. You may then if you would like to change the List web part properties click on the pencil in the left hand web part properties

I recommend selecting the Hide command bar toggle to hide the list command bar i.e. stop users from changing views. Then you can change the View displayed to show Tubes Only if you are only interested in London Underground lines or leave this as it is to accept the default – All Modes. Once finished click the Apply button.

7. Finally click Republish to save the changes.

The web part is then available on the right hand Vertical section (zone)

Conclusion

Well done you have successfully installed the Tube Status solution and it is now available in your SharePoint site. This list has been styled with SharePoint view formatting and this list could be then presented in SharePoint, Microsoft Teams or even Microsoft Lists etc.

This has been a fun little project that I set myself to learn SharePoint view formatting and was also interesting learning about the TFL tube API then parsing the JSON returned from the TFL API using Power Automate.

I hope you enjoyed this blog and you may find it useful to implement in your organisation.

Please leave comments, questions or feedback below – I would love to hear from you?