Merge SharePoint data list with PDF documents

Mail-merge is now a ‘grossed up’ term used in digital documentation era and related job automation industry. This is a feature that can slacken off the effort of processing huge chunks of repeated data in hundreds of documents but with very minor changes in certain data, eventually resulting into a customized document template. Let us put it simple- You want to send invitations to many people, but it’s necessary that you address their name in each of the invite you prepare. Well! if it’s a postal era, you have ‘Hobson’s choice’ other than to limit the count of invitee, and start writing it down on each of the invites or hire more writers. In digital world, we have many options to simplify this tiring activity with tools like SharePoint, PDF4me and Power automate.

Let us now step up our imagination to a bigger level with a present-day situation at work. It’s almost impossible to manage and collaborate work, resources and materials without a proper internal tool to support. SharePoint is one such ‘humble’ platform of Microsoft, that is used widely by firms to integrate and store documents. Its key features being ‘sites’ and ‘lists’. SharePoint list can be comfortably used to contain multiple combinations of records and data.

PDF4me is a tool that can be used to create, edit, scan and process any type of documents. In this blog, we provide a picture of how to do mail merging from SharePoint using PDF4me and a pre-existing incomplete document template and help you create a customized reusable document template. You may apply this automation for creating documents like proposals, contract letters, RFP, and more.

The pre-existing template is that incomplete document or a form having filled up fields with a prevailing set of common data, and is awaiting dynamic data from the SharePoint list. The template would be complete when the remaining unfilled fields or spaces are populated with the varying data making it a particular output. This is stored in a folder or a site in the SharePoint for further use.

In this write –up, I shall try to explain a simple automation, wherein a particular form(template) is filled up by the system automatically, as and when a customer detail is entered in the SharePoint list. The output would be a complete form, combined with newly arrived customer data and the pre-entered data available in the template (Here for the time being, we are using a blank document for simplicity). Before we begin, we need to keep in mind three entities of mail-merge here;

• SharePoint list with a customer data entry trigger

• A pre-existing template with generic data in the form of a doc or pdf file

• A storage location in SharePoint to save the output/completed template

The scenario under realization: is to generate reports or fill forms when a new item is added to SharePoint list with ‘PDF4me Mail merge’ actions. SharePoint data collection can be used to create documents (Word and PDF) with PDF4me Mail Merge features. Finally, these documents can be stored to your destination or share this document through emails. Let us consider, here, developing the workflow, with the final output being stored in a particular location.

Figure Start-PDF4me-MailMerge-From-SharePoint-List: How to create the flow.

We begin with the SharePoint list screen of the firm, where we have the customer data entry happening. We proceed further creating a workflow. If you can see in the screenshot, we select the option ‘create a workflow’ from the power automate tab.

We already have a partially filled up template or a form available. We select a template or form like this. When you create you can pre-fill more data or create bigger templates as per your need. Here we are using a simple blank template for illustration.

This Customer data collected will be merged with template documents to generate output document for each list item.

Figure Output-PDF4me-MailMerge-from-SharePoint-List: Output of generated documents.

The entire process of automation takes just seven steps, to create and store documents this way. The process of automation for this particular case can be explained pictorially as shown below, highlighting the seven steps.

Figure Power Automate – Mail Merge with data string (List of Docs) work flow with SharePoint.

Step 1: Trigger Execution

Power Automate has different types of triggers to initiate your work flow. It can also be scheduled to run in regular intervals of time. In this particular scenario, we trigger a workflow when an item is created in the SharePoint List.

You can feed in data for the Site address as your SharePoint URL. For the list name, you may provide the name of the Data Collection List as per your site Address.

Step 2: Select template file content using SharePoint path

This step is to select a document template file which you shall use for merging with the dynamic data in the SharePoint list.

You can save your templates anywhere as you wish. For this use-case we chose a SharePoint location. You can enter the site address as the’ SharePoint URL’. Select the File path from the list of file directories in your SharePoint site, along with the template type you would use to merge.

Step 3: Extract items from the SharePoint List

This step is to pull out data items from the SharePoint List. These list items are later transformed to JSON array type and is used as input for PDF4me Mail Merge action.

Furnish the details in all the fields as described below;

• Site Address – Your SharePoint URL

• List Name – Data collection List name, listed based on your site Address

• Limit Entries to Folder – NA, you may Ignore this for this particular use-case.

• Include Nested Items – NA, you may Ignore this field as well for this case.

• Filter Query – You may specify filter queries, to filter items while searching.

• Order by – Specify the order by which items are pulled from the list.

• Top Count – Total number of list items, to be retrieved.

• Limit Columns by View – NA, Ignore this field for this use-case.

Step 4: Initialize string variable for preparing DataString

In this step we declare string variables to create Datastring that is an input for mail merge.

Step 5: Apply to each – Loop through the list of items and prepare required data string
One of the parameters for Mail merge with Data String Action is “Data String”. This variable can be used to loop through the data list to form the desired data string as below.
Sample JSON Structure (List Array):

[
  ["FieldName1", "FieldName2", "FieldName3"],
  ["FieldValue1", "FieldValue2", "FieldValue3"],
  ["FieldValue4", "FieldValue5", "FieldValue6"]
]

This step is to loop through SharePoint list items and prepare the final “Data string” parameter for mail merge. Result of this step, the concatenated string is assigned to the variable declared in Step 4.

Step 6: PDF4me - Mail Merge with Data String (List of Docs)

In this step, documents are generated with the multiple input data values from the SharePoint list. Each of the item in the list shall have documents generated.

The field entries are made as explained below.

The “Template File Data” field is filled with the “Template file content” from Step 2.

The “Template File Name” will have the name of the template with the file extension (Supported formats are .docx and .pdf)

The “Data String” field will hold dynamic data values for merging with the template. Let us now see how we get the concatenated string value for this field. For this, we map template file data and prepare data string with PDF4me mail merge action. The data string is assigned with the string function as mentioned below.

concat(‘[[“Name”,”City”,”Zip”,”Country”]’,variables(‘DataListString’),‘]’)

The “DataListString” in the function shown is a variable that contains list items in a prepared format.

Step 7: Save output documents to SharePoint location

We save the generated documents to a preferred destination in this step. You can save the files into any space of storage providers associated with power Automate. In this use case, we save it in SharePoint itself.

We give the following details as field entries, as explained below.

The ‘Site Address’ – Your SharePoint URL
The ‘Folder Path’ – Select the folder path where your output documents to be saved.
The ‘File Name’ – Select the File Name from PDF4me Action result.
The ‘File Content’ – Select the File Content from PDF4me action result.

This way, you can use the mail merge feature, to automate your daily workflow requiring customization with a template, in seven steps. You can make a magical process workflow, replacing all your efforts, editing the same document each time, only to replace fields like name, address, or email id and recreate a copy for a particular recipient. This way, you can bring in tremendous improvement in organisation productivity and performance.