Select SharePoint Files and Merge Them into a Single PDF Using Power Automate

Recently, there seems to have been a large number of blog posts related to SharePoint Column Formatting, but one video blog post that impressed me the most was by Microsoft Power Automate Community Super User Damien Bird AKA – damobird365. In his video tutorial, he explains how to run a flow from a Microsoft List or Document Library and also demonstrates how to select items/documents and send them dynamically to the email of your choice.

Taking inspiration from his video tutorial, I have created a solution to show you how you can select SharePoint files and Merge them into a single PDF and then send that PDF in an email.

Problem Statement 

Due to the ongoing COVID situation, teachers have been using Microsoft Teams and SharePoint to deliver training Online. Teachers use the SharePoint Site to share notes with students and the students then use the SharePoint Library to store their notes and assignments. The SharePoint Library has unique permissions such that Student A cannot access the Folder\Files of Student B.

Students usually send their assignments as email attachments to their respective teachers, however the teachers find it difficult to manage the individual attachments.

Solution

To resolve the problem note above, we’ll use SharePoint Column Formatting and The Muhimbi PDF Converter Online to merge multiple files into a single PDF and send it using Power Automate.

Note – This solution works best with the Folder having unique permission, if you have a multi-user scenario then this might not be the right solution for you. That being said, if you’re here to gain knowledge or learn something new just follow along.

Before we begin, please make sure the following prerequisites are in place:

  • Office 365 subscription with SharePoint, Power Automate and Exchange license.
  • Muhimbi PDF Converter Services Online Free, full or trial subscription (Start trial).
  • Appropriate privileges to create Power Automate Flows.
  • Appropriate privileges to Create and Manage SharePoint Libraries.
  • Working knowledge of SharePoint Online and Power Automate.

Setting up the Document Library.

  • Navigate to the Document Library and select the folder you would like to set permissions for. Click on the Options Menu, and then select Details.
  • When the details menu appears on the right side of the screen, select Manage Access and then, when the Manage Access window appears, click on Advanced
  • Under the permissions tab, click on Stop Inheriting Permissions. You will then see a pop-up window asking you to confirm the creation of unique permissions. Click on ‘Ok’. Remove the user or groups which are not needed (Note –  Remove the User\Groups only if you are a 100% confident they do not require access).
  • Under the Permissions tab you will now see a new group of options. Click on Grant Permissions.
  • You will now see the Grant Permissions window and configure it with reference to screenshot below.

Note – With reference to the above screenshot, only Administrators and Debra will have the access to the library and no other users will have the access to it.

Setting up Columns

  • Navigate to the list or library you want to create a column in.
  • To the right of the last column name at the top of the list or library, select + Add column or +.
  • In the dropdown, select the type of column of type “Single Line of Text
  • In the Create a column panel, in the Name field, enter a title or column heading.
  • Select + Add column or +.
  • In the dropdown, select the type of column of type “Yes/No”
  • In the Create a column panel, in the Name field, enter a title or column heading and Set Default value “No“.

SharePoint Column Formatting

In the Modern SharePoint List\Libraries, we can use column formatting to customize how fields in SharePoint lists and libraries are displayed. If you’re interested in knowing more about column Formatting, I would highly recommend you visit the article Use column formatting to customize SharePoint

You can find many more samples from the sp-dev-column-formatting repository at the SharePoint GitHub organization.

  • To add the column-formatting code, go to your list and click on the column header, then Column settings, Format this column

Paste the JSON below for Yes/No Column to display the column as a Yes/No Check- box-

{
   "$schema":"https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
   "elmType":"div",
   "children":[
      {
         "elmType":"div",
         "attributes":{
            "iconName":"=if(@currentField,'CheckboxComposite','Checkbox')",
            "class":"=if(@currentField,'ms-fontColor-neutralPrimary','ms-fontColor-neutralTertiary')+' ms-font-l'"
         },
         "style":{
            "cursor":"pointer"
         },
         "customRowAction":{
            "action":"setValue",
            "actionInput":{
               "IncludeDoc":"=if(@currentField , '0' , '1' )"
            }
         }
      }
   ]
}

Paste the JSON below for the “Single line text” column, the JSON column formatting to create buttons that, when clicked, run Flows on the corresponding list item.  Note – We will change the “Flow id later

{
   "$schema":"https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
   "elmType":"button",
   "customRowAction":{
      "action":"executeFlow",
      "actionParams":"{\"id\": \"d100d3ea-7152-46fc-a5c2-fd0411f27cb9\"}"
   },
   "attributes":{
      "class":"ms-fontColor-themePrimary ms-fontColor-themeDarker--hover"
   },
   "style":{
      "border":"none",
      "background-color":"transparent",
      "cursor":"pointer",
      "visibility":"=if([$IncludeDoc]==true,'visible','hidden')"
   },
   "children":[
      {
         "elmType":"span",
         "attributes":{
            "iconName":"Flow"
         },
         "style":{
            "padding-right":"6px"
         }
      },
      {
         "elmType":"span",
         "txtContent":"Merge Documents!"
      }
   ]
}

Setting up the Power Automate(Flow)

From a high level our Flow looks as follows:

Step 1: For this demo, we will use “For a selected item” SharePoint Trigger action and “Add an input” of type “Email”. We use this trigger to start the Flow from SharePoint Online User Interface.

Step 2: Add the “Get files (properties only)” action.and configure it with reference to the details below:

  • Site Address : Specify the path to the SharePoint Online site-collection which holds the file.
  • Library Name :  Select the Library Name from dropdown
  • Filter Query : IncludeDoc eq 1

Step 3:

  • Initialise a variable of type Array and Name it “FileArr”.
  • Initialise a variable of type Integer and Name “CountFile”.

Step 4: Add the “Get file content” SharePoint action and pass the “Identifier”  (Output of “Get files properties” action) into the ‘File Identifier‘.

Doing this will automatically decorate the action with “Apply to each” loop.

Step 5: Inside the “Apply to each” loop add the Append to array variable action and add the JSON below

{
  "source_file_name": "@{items('Apply_to_each')?['{FilenameWithExtension}']}",
  "source_file_content": @{body('Get_file_content')}
}

Step 6: Add the Increment variable action and set the Value to 1.

Step 7: Add the Update file properties action and configure it with reference to the screenshot below –

  • Site Address : Specify the path to the SharePoint Online site-collection which holds the file.
  • Library Name :  Select the Library Name from dropdown
  • Id: ID the output of the “Get file properties” action.
  • IncludeDoc : No
  • Description: Decription the output of the “Get files properties” action.

Step 8: Outside the “Apply to Each” loop add the “Condition” and configure it with reference to screenshot below –

Step 9: Inside the “If yes” block, add the Muhimbi “Merge documents” action,

  • Source Files : Add the Variable “FileArr”.

Step 10: Add the “Send an email(v2)” action and configure it with reference to the instructions below

  • To: “Email” the output of the “For A Selected file” action
  • Subject: Submission by @{triggerOutputs()[‘headers’][‘x-ms-user-name-encoded’]} on @{utcNow()}
  • Body: Email Body.
  • Attachments Name – 1: “User name” the output of the Attachment Name -1
  • Attachments Content -1: “Processed File Content” the output of “Merge documents” action.

Step 11: Add the “Get file properties” SharePoint action and pass the “ID” output of “For a selected file” action into the “File Identifier”.

Step 12: Add the “Get file content” SharePoint action and pass the “Identifier” output of the “Get file properties” action into the “Id”

.

Step 13:  Add the “Send an email(v2)” action and configure it with reference to the instructions below

  • To: “Email” the output of the “For A Selected file” action
  • Subject: Submission by @{triggerOutputs()[‘headers’][‘x-ms-user-name-encoded’]} on @{utcNow()}
  • Body: Email Body.
  • Attachments Name – 1: “User name”.pdf  the output of the “For a Selected File”
  • Attachments Content -1: “Processed File Content” the output of “Convert documents” action.

Step 14: Configure RUN-ONLY User.

  • In this blog post we used the “For a selected item” trigger to manually start a Flow on a document. This works similarly to Flows that are automatically triggered when a file is created or changed, with one major exception, which is that by default the person starting this Flow will need to authenticate to all services used by it. Although sometimes this is desirable, in general it is not particularly user friendly, especially for non-technical users.
  • To keep things easy for the end-users, we use Flow’s “run-only users” facility to preconfigure the account used to authenticate with Muhimbi’s conversion service. Configure this setting using the “manage run-only users” card, just below the “Owners” card, see the screenshot below.

Step 15: On the Power Automate Configuration screen, copy the ID from the end of the URL to the clipboard. For example https://emea.flow.microsoft.com/manage/environments/Default-444e7b96-eeb2-45d5-a9ff-4bef8830b240/flows/8537634d-2e56-4d98-a494-ed856b68a70a/details

Change the GUID in JSON for theSingle line text” column we mentioned in Step 2.

"customRowAction": {
"action": "executeFlow",
"actionParams": "{\"id\": \"d100d3ea-7152-46fc-a5c2-fd0411f27cb9\"}"
},

All Done! Navigate back to the original library and select documents that you want to Merge. Click on the “Button”. It should open the blade in which you can specify the email address and ‘Run Flow‘. After a few seconds, you should have an email with a PDF attachment.

Subscribe to this blog for the latest updates about SharePoint Online, Microsoft Flow, Power Apps, and document conversion and manipulation.

One thought on “Select SharePoint Files and Merge Them into a Single PDF Using Power Automate

  1. Good article and right to the point. Posting such an informative and valuable blog. Enjoyed reading the article above, this article is really very interesting and effective. Thank you so much for sharing such useful information.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s