In this post we describe how to use a hidden taxonomy list to allow us to change the format and store metadata terms, plus their parents in SharePoint Online.
Intro
Lists and libraries in SharePoint Online have the ability to show a term or the term plus parents within a managed metadata column by editing said column and changing a neat little option called display format.
You can configure managed metadata columns in SharePoint to show terms plus parents.
However, when you turn on this option your term plus parents displays in the following way: Parent:Term. What if you wanted to change the way the parent plus child terms are displayed into either a different or user friendly format? Let’s do exactly that:
The taxonomy hidden list
There is a secret list within each SharePoint site called the taxonomy hidden list that, as the name suggests is a hidden list that stores cached managed metadata terms used within site. The taxonomy hidden list is unique to the site and each time terms are used within the site, they get stored within the taxonomy hidden list.
This list contains a lot of repeated stuff that might look confusing, but it does specifically contain the full path of a term including it’s parent, which is what we are looking for.
Solution
So in my example I want to replace the colon in the parent plus term with a comma and a space, so it looks like this: Parent, Term. In the below example my managed metadata column is called Department. Here’s how I did it step-by-step:
In your target list/ library, create a new single line of text column to store you formatted parent plus term (DeptCopy in my example).
Create a new flow in Power Automate based on the when a new item is created SharePoint trigger, making sure your site address and list name are added and configure the actions below:
Get item action
Set the site address as the target SharePoint site and the list name as TaxonomyHiddenList. Note: you will need to enter a custom value here and type this in.
Set the Id to equal the managed metadata column (Department) WssId from the when a new item is created trigger.
Initialize variable
Create a new variable to store the full path of the term from the get items action. Give the variable a name set the value to equal Path from the get items action.
Compose (optional)
Use the compose data operation to create an expression to replace the colon with a comma and space. You don’t have to do this step, I just use it for troubleshooting to easily see what the outputs are of my input value.
replace(variables('varDept'),':',', ')
Update item
Update your previously created column to show the newly formatted term plus parent with the output from the compose action. Note: If you don’t use the compose action from the previous step, just write the expression above directly in the column you want to update.
Notes
There is quite a bit online about seeing empty values in your managed metadata column. Although I’ve not personally noticed this, the taxonomy hidden list is set to have unique permissions by default and has the following permissions:
Anonymous users: view items
Everyone: read
There is a good write up of the taxonomy hidden list here.
Although there are a number of dynamic content options available, none of them seem to contain the parent + term like they do in the hidden taxonomy list. For example the path column that’s available from the ‘create an item’ trigger, outputs ‘null’.
In this post we step through how you can use Power Automate to convert modern SharePoint pages into PDF files and save them to a document library.
Intro
Recently I got asked to come up with a way to turn SharePoint pages into PDF files for use in an offline scenario. The converted SharePoint pages didn’t need to be formatted as it was only the body content of a SharePoint page that was needed. Also part of the brief was that when the SharePoint page is updated, the corresponding PDF file also updates.
There are several posts online that cover very topic this that I’ll reference at the end, but they didn’t quite do exactly what I wanted – so here’s my take on how to convert SharePoint pages into PDF files!
What you’ll need
A modern SharePoint site pages library (these come with every SharePoint site!)
A OneDrive location to temporarily store the SharePoint page outputs
Power Automate to build the automation
A document library to store the output PDF files
A note on the site pages library
In my example I didn’t want all the site pages to be converted into PDF files, so I added a choice column to ‘tag’ all the pages that should be converted. I set the default value of the choice column to be ‘Site Page’, so that the only pages that get converted are the ones I’m interested in. This is reflected in the flow below with the condition step.
Add a choice column to ‘tag’ the pages you wish to convert to PDF.
Building the flow
The trigger action for our flow is when a file is created or modified (properties only). This allows us to re-run the flow when SharePoint pages are updated to also update the PDF files.
Select the site you are using to create the SharePoint pages in site address (If you don’t see it listed just press enter custom value and paste the URL in)
Select the Site Pages library under library name
Next, I’ve added a condition to only convert pages that have been tagged ‘Runbook’ to PDF.
Condition: if Document type value is equal to ‘Runbook’.
Note: make sure you select the Value dynamic content for your choice column, rather than the choice column itself as that will break your flow.
If yes, next is a send an HTTP request to SharePoint step. Here I’m using a REST API call to get the body content of the SharePoint page.
Use a send an HTTP request to SharePoint step to get the body content of your page.
Set the site address to the site in question
Set method to GET
Enter the following in Uri:_api/web/lists/GetByTitle('Site%20Pages')/items('ID')/CanvasContent1
Replace ‘ID’ with the dynamic content ID from the when a file is created or modified step
Note: The output of this step generates some additional stuff you probably won’t want in your PDF like this:
"d": { "CanvasContent1": "}}
I used the parse JSON step to remove the unwanted mark up and just get the plain text from the body content.
I added the body dynamic content from the send an HTTP request to SharePoint step in the content field in the parse JSON step
I copied the the output body from send an HTTP request to SharePoint of a successful run in flow history and pasted it into the parse JSON step
Output body from send an HTTP request to SharePoint to paste into the parse JSON step from a successful flow run.
I then pressed generate from sample, which output the following:
From this I then used a create file action to create a temporary HTML file in OneDrive (more on this later), with the following config:
Folder path: / (root of the OneDrive account)
File name: Name from when a file is created or modified step
File content: CanvasContent1 from the parse JSON step
Create file action to create temporary HTML page in OneDrive.
Next, a convert file step to convert the HTML page into a PDF file:
File:ID from the create file step
Target type: PDF
Now we can use a create file action to create a PDF in our output document library in SharePoint:
Set the site address to the site you want to store the PDF files in
Set the folder path to the document library, or navigate to the relevant folder within that library
Set file name to file name from the convert file step
Set file content to file content from the convert file step
The create file action creates the PDF file in the destination document library.
I then used an update file properties action to pass metadata from the site pages library to the destination document library – this step is optional. Finally, a delete file action to delete the temporary HTML file from the OneDrive we created earlier:
Delete file action to remove the temporary HTML file.
Here’s the flow in it’s entirety:
Issues & troubleshooting
Formatting issues with the send an HTTP request to SharePoint
As mentioned above, when just using the send an HTTP request to SharePoint action, the output contains mark up that isn’t going to make sense within the PDF. The parse JSON action cleans this up and just leaves the body content of the page.
Create file action creates corrupt PDF files
When testing this flow out I originally didn’t have the convert file action in place. In the file name I added ‘.PDF’, but every time the output PDF was corrupt and errored like this when trying to open:
The flow also failed on this step and the error said that “Conversion of this file to PDF is not supported. (InputFormatNotSupported / pdf)”. I decided to scrap this approach and create a HTML page and add in the convert file action which worked around this issue.
Overwriting existing PDF files causes flow to fail
During testing of this flow I also noticed that when triggering the flow based off updating a site page, the create file create file action would error with a status 400 error saying “A file with the name [file name] already exists”.
In this post we explore a common error experienced when trying to update existing files in SharePoint using the create file step in Power Automate – and how to resolve it.
The problem
I recently ran into an issue seemingly many others have encountered when trying to update an existing file in SharePoint using the create file step in Power Automate. When running my flow, I received the following error:
Bad Request error in Power Automate when trying to use the create file step to update an existing file in SharePoint.
Bad Request status: 400
Message: A file with the name [NAME OF FILE] already exists. It was last modified by [NAME OF USER] on [DATE]
The solution
The solution to this has already been shared a fair few times via the Power Automate blogs, but here it is:
Press the ellipsis … > settings within the create file step
Open the ellipsis and press settings within the create file step.
Scroll down until Content Transfer > set allow chunking to off
Save and re-run your flow
That’s it, now when I use the create file step to update existing files, it runs successfully!
Note: Chunking of content is used for splitting up large content for downloads/ uploads. You may need to consider this before turning off chunking in your flow. More information on chunking can be found here.
How do I see what folders/ sub-folders my files are in at a glance
– all users everywhere
Out of the box, there aren’t any columns available that you could potentially leverage to display this information in a standard SharePoint 2010 library.
The solution
So, just by adding one value in SharePoint Designer, here’s how you do it:
Navigate to the library you wish to change, create a new view under Library Tools > Library > Create View
Choose the relevant format of your view, give your view a name and press OK
Open SharePoint Designer > Open the site > open the library you were just working in
In the Views pane > click to open the view you just created
In SharePoint Designer, clicking on the view name will open the view in edit mode
In the code editor window, scroll down until you see something like the following:
Add the following field reference in between the opening <ViewFields> and closing </ViewFields>
Add the field reference in the display order you would like it to appear in the view
Add the field reference to the View Fields list
Press the Save icon to save your changes
Press the Preview button to see your view in action in the browser
Now you will notice there is a new column being displayed “Path”, that is showing us the full location of the file or folder in the libary. You’ll also notice that this path will display data when at the library root, or in any folders or sub-folders in the library.
Library root displaying a files pathFile in sub-folder displaying relative location
Bonus
Taking this one step further, what if we wanted to show files of a certain type, then create a view that groups these files by their folder location? Guess what, that’s exactly what I did!
Navigate to your library > create a new view as before, this time base your new view off the one you just created
If you wish to only show files of a particular type, use the filter by settings (for example below is filtered to only shows Word documents)
Make sure “show all items without folders” is selected
Press OK
Filtering to only show word documents, also showing items without folders
Back in SharePoint Designer > Open up the view you just created
Scroll down until you see the opening <Query> tag and add the following beneath it:
With Microsoft retiring SharePoint 2010 designer workflows, plus the movement away from SharePoint Designer in general, a few readers have asked for a solution that works with modern SharePoint.
When researching this I considered whether suggesting to use SharePoint Designer 2013, as the above solution would still work in SharePoint Online using SPD 2013. But, as Microsoft say themselves although SPD 2013 remains supported, it’s depreciated – so I decided to go in a different direction.
The below example walks you through how you can create a flow in Power Automate to update a file after it’s been created to have the folder path shown in the document library view:
The solution
For this solution you will need to have access to create Flows in Power Automate, as well as an existing Document Library created in SharePoint Online:
Navigate to the document library you wish to show the folder path for
Add a single line of text column to the document library > give it a name (I called mine FolderPath)
Under the ellipsis, press Automate > Power Automate > Create a flow
In Power Automate, either use an existing, relevant template or start from blank
The trigger action should be When a file is created (properties only)
Set the Site Address and Library Name where you want to add the folder path
Insert a new step > select Update file properties.
Set the following values for the update file properties step:
Site Address: same as previous step
Library Name: same as previous step
Id: ID
FolderPath: Folder path
NOTE: The FolderPath within the Update file properties step is the custom column we created earlier. The Folder path (highlighted in red) is dynamic content available within the step in the flow. The folder path dynamic content is the path to the folder the item is in, relative to the site address.
Ensure you select the system Folder path dynamic content to pull the right data into the custom FolderPath column.
Here’s the flow in it’s entirety:
At this point test and save your flow to make sure it is working as expected 🙂
Bonus #1 – turn your folder path column into a hyperlink column
So if like me you want to take this one step further, wouldn’t it be good if we could easily make our newly showing folder paths, actual hyperlinks to the folders? Well the good news is you can!
Navigate back to your document library > click on the FolderPath column > Format this column
Under Apply formatting to make sure FolderPath is selected
Paste the following JSON into the custom formatting box:
NOTE: for more information on turning field values into hyperlinks, check out this awesome sample from sp-dev-list-formatting.
Press Save
Your FolderPath column values should now be legitimate hyperlinks that click through to the relevant folders
Bonus #2 – update existing files in the document library
This was another suggestion from a reader with regards to how to update files that existed in the document library before the flow was created.
Running a flow manually for individual files
When I began to consider how to do this I started by looking at ways to manually start the flow.
It appears the only real way to do this is to create a new column that adds a button next to each file, that allows you to run the flow. I’m not really enamoured by this approach as it doesn’t seem ideal to have an extra column to run a flow showing on every file in your library. If this is something you would like to pursue then I would recommend this great article by WonderLaura who has the process of creating a button to trigger a flow covered!
Update our flow to update all files if folder path is empty
My solution to this problem was to update the flow we created earlier to get the properties for all files in the library, then add a condition that checks if the FolderPath column is empty, then if yes runs our flow as before.
First, I added a Get files (properties only) action which gets all the files from the source library
Then I added a new Condition action, which simply checks if the FolderPath column we created is equal to null. You will also notice a new Apply to each action will be created
I then moved the previous Apply to each action into the “If yes” condition
SharePoint Stuff 