Outlook to Sharepoint Document Set with Microsoft Power Automate
Some of our end users were presented with the problem that their current filing system (Plain Old Folders) wasn't scaling well to the modern-day influx of reports and forms, and needed an interim measure until a proper enterprise workflow was implemented.
Currently, they receive an email per customer, with up to three separate forms which need to be actioned via different workflows. Currently we were tracking this via Excel, and as you can expect, queries were getting stuck and forgotten about for months on end.
Our current prototype is as follows:
Email is moved to a designated folder in the user's Outlook mailbox. This triggers a Power Automate flow.
First, we store some data from the email into more convenient variables. The subject, the person who sent the email, the date, and so forth. We need to do some minor string manipulation on these to make them print friendly for humans.
Next, we check if the unique key for the customer is placed in the email subject. Ideally I'd use RegEx for this, but there does not seem to be a vanilla regex action or function available. We then take what we think might be the customer key, and search in a Sharepoint list containing all the keys. This both checks if the key is valid, as well as returns us the ID of the customer in the Sharepoint list, which we will need later on.
We then need a name for the document set we are going to create for this specific email. If we have the customer ID, we use that, otherwise we fall back to something like "Received from John Doe at 2020/08/20". We cannot use the customer's name yet as our master data still needs some clean up to disambiguate duplicates.
We then check if we already have a document set with this exact name in the document library. We shouldn't have duplicates, but we check in case somehow the flow gets triggered twice for the same email, or the user completing the form sends it twice by accident. If it does not already exist, we are free to create the document set. Unfortunately there is no built-in action, so we need to use 'Send an HTTP request to Sharepoint' along with some minor listdata.svc REST work.
Once this is complete, we Export the triggering email so that we have access to its' content as a binary variable, then we save it into the document set using 'Upload file to folder (Sharepoint)'. We then use a Control/for-each loop to do the same for each of the attachments whose content-type does not begin with 'image'. If any of these files have already been uploaded, the entire flow will stop and exit with a failed status.
Finally, we use Microsoft Teams to send the user a message informing them that the document set was created, along with a link to access it. I chose this over an email to avoid cluttering their inbox too much. They can now access the forms on Sharepoint, where they can update columns such as Status, Approver, Ticket number, and Date Completed.
All of this can then be queried via Export to Excel, or using PowerQuery to Load Data from Sharepoint List. We have created both a new Excel sheet with a few charts (Number of Forms per status, Number of Forms not completed older than 30 days, Forms awaiting on Approval by person, etc.), as well as integrated the live data from Sharepoint into the old tracking sheet.