Create a Self-Sustaining, Expiring Document Sharing System Using Sharepoint and Microsoft Flow
Image from sodapdf.comIntroduction
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**:
Create a copy of a specific document
Append user initials and date of expiration to generate a unique document ID
Convert a list of file extensions to PDF
Move the document to a separate document library
Create an expiring shareable link
Send an email and Microsoft Team’s notification to the user with the shareable link
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