Conditionally Display Microsoft Flow Activating Buttons in Sharepoint Libraries Using Column Formatting

Before Column Formatting:

Image from support.microsoft.com

After Column Formatting:

Image from support.microsoft.com

Introduction

This post goes over how I used Sharepoint’s Column Formatting to solve some points of friction I experienced when executing a specific automated workflow. If you have not read my post on creating an automated file sharing workflow, you can read it here: blog post. This post is a continuation of sorts, but can also be a stand-alone read where you can learn how to use column formatting to solve some of your specific problems.

Column Formatting allows you to customize the look and feel of your Sharepoint library or list. Column Formatting lets you do whatever you want, as long as you follow it’s schema. People have used Column Formatting to make document lists visually meaningful and incorporating useful features such as buttons that simplify activating automated workflows. I will go over how I incorporated a flow activating button to solve some pain points I had in my automated flow.

Problem

Activating flows for selected files is a three click process. You click the three dots to bring out a menu where you can navigate through the Automate dropdown to find the flow you want to run.

This is straightforward for experienced users and for document libraries with a few flows. It may get complicated down the line when there are a large number of flows, which may confuse the user into clicking the wrong flow. Another problem is activating flows on files that are not supported. In my example, my company does not send specific file extension to customers. Having the option to run the flow on unsupported files wastes time and effort.

Solution

The implemented solution can be seen on the right. I created a new column called ‘Create Shareable Link’ where buttons are conditionally displayed on specific file extensions to activate a flow on click.

Notice how the button does not render on folders or certain extensions like .vsdx. This prevents the user from even starting a flow on unsupported files. The button column serves as a fast visible cue on what is supported versus what is not supported, greatly increasing the user’s experience from a multiple click step that may result in a failed flow, to a quick one click solution.

You can start formatting Sharepoint columns by clicking on your column header, going into Column settings, then clicking Format this column. If a column does not exist, you can create one in your list or library settings.

Formatting columns follows a JSON schema. You can find the general format on the official Microsoft Sharepoints page, but you can copy the code below and edit it as a starter. This JSON renders a button that activates a specific flow. The flow is set in the customRowAction field. You will have to replace the id with the id of your own flow. You can see how you can get your flow ID below.

Styles for your button and how it is rendered can be written in the style field. I have added a display and visibility property to filter which files the button will be displayed with. The JSON uses excel like code for conditional statements.

I used a nested if-statement to check file names ($FileLeafRef) for extensions we do not support. If it passes, the button will not be rendered.

The visibility field checks for folders and hides the button if it passes.

You can copy the Schema below:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "button",
  "txtContent": "Create Link",
  "customRowAction": {
    "action": "executeFlow",
    "actionParams": "{\"id\": \"<ID_OF_FLOW_HERE>"}"
  },
  "style": {
    "border": "none",
    "cursor": "pointer",
    "background-color": "#2980b9",
    "color": "#FFF",
    "padding": "4px 4px 4px 4px",
    "border-radius": "5px",
    "display": "=if(indexOf('[$FileLeafRef]', '.vsd') != -1, 'none', if(indexOf('[$FileLeafRef]', '.pro') != -1, 'none', if(indexOf('[$FileLeafRef]', '.msi') != -1, 'none', if(indexOf('[$FileLeafRef]', '.pcm') != -1, 'none', if(indexOf('[$FileLeafRef]', '.pcc') != -1, 'none', if(indexOf('[$FileLeafRef]', '.iso') != -1, 'none', if(indexOf('[$FileLeafRef]', '.cs') != -1, 'none', if(indexOf('[$FileLeafRef]', '.mac') != -1, 'none', if(indexOf('[$FileLeafRef]', '.prt') != -1, 'none', if(indexOf('[$FileLeafRef]', '.csv') != -1, 'none', if(indexOf('[$FileLeafRef]', '.xps') != -1, 'none', 'block')))))))))))",
    "visibility": "=if(((indexOf([$ContentTypeId],'0x0120')) == 0),'hidden', 'visible')"
  }
}

To find the ID of your flow, go to your flow in Microsoft Flow / Power Automate. In the URL, you can find the ID in between Shared and Details. Insert this flow ID into the column formatting JSON.

Conclusion

Document libraries do not have to be boring. With Sharepoint Column Formatting, the applications are endless. From adding character and visual cues to libraries, to implementing buttons, column formatting is an easy tool to add into your workflow that can make document libraries or lists a little more fun and intuitive. In my case, I was able to implement a one click solution to reduce friction in our automated workflow by adding a flow activating button that renders only for supported files. This has greatly increased the user experience for the workflow by preventing wasted time trying to activate flows on unsupported files, only to get a failed flow. Thanks for reading!

For more information on column formatting, here is a link to some documentation from Microsoft: https://docs.microsoft.com/en-us/sharepoint/dev/declarative-customization/column-formatting

Create a Self-Sustaining, Expiring Document Sharing System Using Sharepoint and Microsoft Flow

View fullsize

Image from sodapdf.com

Introduction

Who knew sharing documents could get so complicated. Sharing documents is typically a straight forward process, but it can get tricky in situations involving authenticated vs unauthenticated users, or privacy settings. The latter was the case at my company where sharing sensitive documents to customers outside our organization was commonplace.

In this post, I go over my process in creating an automated document sharing system to address specific company concerns regarding sharing documents. This system uses Microsoft Sharepoint and Microsoft Flow (now called Power Automate). This post is written as a project log / tutorial hybrid. I hope you can learn from my experience and maybe even implement some of the Flows from this post in your own automated workflow.

Definitions

Microsoft Sharepoint:

SharePoint is a web-based collaborative platform that integrates with Microsoft Office. Launched in 2001, SharePoint is primarily sold as a document management and storage system, but the product is highly configurable and usage varies substantially among organizations.

Microsoft Flow:

A process and task automation tool that uses low-code / no-code to help connect different applications and services together.

Defining The Problem

My company sends Sharepoint documents such as product manuals, data sheets, and brochures to our customers using a public sharing link with an expiration date. Sharepoint allows a single public sharing link to be used per document at a time. Problems arise when sharing documents that are already embedded on our website using public sharing links. Creating new sharing links with expiration dates for these documents overwrite existing public links, breaking all previously available links on our website.

The core problem we needed to solve was finding or creating a system that would allow us to share documents with our
customers without breaking links on our website.

Solution Ideation

My initial thought in solving this issue was to create a separate Document Library where users can place copies of the document they wanted to share. Users would be able to place expiration dates on these document copies without disrupting any links the original document may have had.

This iteration of a solution could solve our initial problem. Users would be able to share documents to customers without disrupting already existing links, but would result in a bloated company document library with multiple copies of files laying around, which would be confusing to version control. This iteration would also need a person to manually clean up expired documents which would introduce human labor and error.

After researching Microsoft Flow / Power Automate, I figured I could use Flows to build upon the solution above to create an automated workflow that would address the bloat and human error issues while improving the user experience.

This system uses three separate Microsoft Flows to**:

1. Create a copy of a specific document

2. Append user initials and date of expiration to generate a unique document ID

3. Convert a list of file extensions to PDF

4. Move the document to a separate document library

5. Create an expiring shareable link

6. Send an email and Microsoft Team’s notification to the user with the shareable link

7. Finally delete the document after it expires

**Note that this post was written after completion of a full working prototype. The list above contains the final features of the working prototype that were implemented after user testing and feedback. More about this in the section below titled ‘Testing and Evaluation’.

Solution Implementation

Creating The Document Library

The first step in implementing the solution is to create a separate Document Library on Sharepoint where copies of our documents-to-be-shared would live, and eventually die. You can create the Document Library anywhere in your organization and name it whatever you like. After creating the document library, the next step is implementing automation using Microsoft Flow.

Flow One: Delete Files After X Days

The function of the first flow is cleaning up, or deleting files that are older than X days. This flow activates every day, looping through the expiring document library, checking each file, and applying actions depending on the status of the condition.

The architecture of this flow is simple, with three main actions. This flow is set to trigger once a day, loops through your document library, and applies a condition check to each file.

The flow has a Recurrence Trigger that is set to run at 10:00 AM every day. You can set this to whatever time you like, but a daily trigger is used because we are concerned with the age of the documents in units of Days.

The flow uses a Get files (property only) action which requires the site address for your organization, as well as the specific document library we created where all the expiring documents will live. This action allows us to capture information about each file, which we will feed into the condition check.

An Apply to each action loops through each individual file in your document library. This action checks the condition of whether the document is older than X amount of days. If it passes the condition, the document is deleted using a Delete item Action. The item is ignored if it does not pass the condition.

The variables used in the condition are:

• utcNow(), returns the current time and date

• addDays(), returns the result of a specified time, added with a specified amount of days. In our case, I used the date at which the document was created, added with 7 days. Raw Input: addDays(items('Apply_to_each')?['Created'],7).

The condition checks if the current date and time is greater than the date and time the file was created plus 7 days. If the condition passes, it means the document is older than 7 days and will be deleted using the Delete Item action.

Flow Two: Rename File and Create a Sharing Link

After creating a separate document library where copied files would live, and implementing a Flow that deletes expired files, the next flow created will generate the actual shareable expiration link, as well as rename incoming files with a unique name to prevent confusing duplicates.

The trigger used for this flow is When a file is created. This triggers when a user copies over a file into our created document library.

The next action Get file properties gets meta information about the newly created file that we will use in the flow.

The next set of actions is added in to detect and clean up special characters in file names. In our user testing, we discovered that file names with a period (.) in it would cause the flow to fail during the renaming step.

I initialized three variables. The first variable holds the string name of the file we are cleaning up. The second variable is a Temp variable that will be used in the flow. The third variable is an array of special characters we want to clean up. You can edit this array to contain whatever characters you want to clean up.

createArray('.','@','ß','²','³','µ','`','´','°','^','=','(',')','&','$','§', '~','#','%','*',':','<','>','?','/','|')

The Temp variable will be set with the replace variable which will remove all special characters from our file name stored in Sanitize.

replace(variables('Sanitize'), item(), '')

The file name variable Sanitize will be updated to have the same value as the Temp variable we just cleaned.

A new variable containing our new file name will be created next, using the sanitized file name, and appending to it the initials of the user, calculated using the substring variable, as well as the expiration date.

First Initial:

substring(first(split(outputs('Get_file_properties')?['body/Author/DisplayName'],' ')),0,1)

Last Initial

substring(last(split(outputs('Get_file_properties')?['body/Author/DisplayName'],' ')),0,1)

Date of expiration

addDays(outputs('Get_file_properties')?['body/Created'],7,'yyyy-MM-dd-hhmmsstt')

Now that we have a variable that contains a cleaned and unique file name, the next step is to change the file name to this variable value. Microsoft Flow does not contain any built-in Sharepoint connectors that easily change file names.

To change the file name of a Sharepoint file using Flow, we must change the file’s FileLeafRef metadata field. We do this with a HTTP POST request. To get the correct type to use in the Body of the request, follow the uri in your browser using your site address and document library. This will return a bunch of JSON code that you will need to parse out to get the correct type.

Input the new file name variable in the Body of the request as seen in the screenshot.

After changing your file’s name, the next step is to generate the expiring sharing link. This can be done using the Create sharing link for a file or folder action. This flow can also be used to generate regular shareable links by changing the Link Type and Link Scope.

I initialized a variable CompleteLink that contains an <a> tag that directs to the document with a display text of the File name to make the next step of sending Emails and Microsoft Teams messages cleaner.

The final step for this flow would be sending yourself a link of the new expiring shareable link. You can send it to yourself however you like. I chose email and a Microsoft Teams Message.

Flow Three: Move Specific Files to Expiring Document Library / Converting to PDF

So far, I’ve implemented flows that handle documents that are manually copied over to the document library we have created. This requires the user to use the Copy To document selection in Sharepoint for each individual file. This does not allow for any pre-processing of each individual file before they are fed into the other flows. After testing the two flows with users and stakeholders, we learned that some file extensions should not be allowed to be shared with customers. Some of these file extensions could be shared if they were converted to a read-only type of document such as a PDF.

This calls for a creation of a third flow, which will be run on an individually selected file, where it will be checked for file type, converted to PDF if needed, then copied over to the expiring document library.

This flow contains three main actions, as well as some nested conditions that will test the document’s file extension.

The flow is triggered on a single selected file the user chooses. The Get file property is used to get metadata from the selected file.

The condition used checks to see if the file name contains a list of file extensions we are looking out for. These file extensions include files that we do not want sent to customers, as well as file extensions that need to be converted to PDF before being sent to customers.

If the file fails this condition, meaning the file does not contain a file extension from our list, the file is then copied over to the expiration document library where it will continue with the other two flows.

If the file does contain a file extension that was in our list, a second condition will be checked to discern between files that need to be converted to PDFs and files that we do not send to customers.

If the file passes the condition, meaning the file contains a file extension on our do not send list, an email or Microsoft Team’s message will be sent to the user notifying that the flow does not support the file.

If the file fails the condition, meaning the file must be converted to PDF before sending, the file is sent down a cascade of actions that will convert the file to a PDF.

The OneDrive Create file action creates a copy of the file to be converted to PDF using the Convert file action.

The Create file Sharepoint action is then used to create a Sharepoint file using the contents of the newly created PDF.

A Delete file action is used to delete the newly created OneDrive PDF file that was used as a template

Testing and Evaluation

I am grateful for my coworkers and company stakeholders for testing the early iterations of the workflow. Their feedback was important in the progression and addition of very important features that were not initially a part of the workflow. Having different viewpoints and testing the early versions of the workflow exposed issues that I was not aware of. Problems around processing duplicate files and file names with special characters were some of the issues that were discovered during user testing. This resulted in features added to clean up file names containing special characters, as well as appending file names with a unique identifier made of the user’s initials and date of expiration.

Another discovery was file extensions that were not to be sent to customers such as .vsdx or .doc files. This led to implementing a PDF converter to handle these extensions.

Conclusion

It was a fulfilling experience learning about Microsoft Flow, and implementing a working, automated solution that will be used in the company. I was able to build up a solution to a problem, test, gain feedback, and repeat the cycle until the product was polished and working. Thanks for making it this far in the post. I hope you found it helpful.

One Step Further

After using the workflow a bit, I discovered some friction points that I have implemented new features to alleviate. You can read about it in my post regarding Column Formatting in Sharepoint: blog post