July 24, 2018

Create Custom Document Template from SharePoint List using Microsoft Flow

1636 Views
Create Custom Document Template from SharePoint List using Microsoft Flow

Introduction

With Microsoft Flow overshadowing the SharePoint Designer (SPD) workflow, I, like most of the others, wanted to revisit my SPD tasks if I could achieve the same automated tasks as in the erstwhile application.

To be honest, being a non-programmer, the SPD to me was much more than an approval/rejection workflow mechanism. From modifying list values to iterating through each item of a list to driving a custom template-based document library from a list, I’ve enjoyed the company of SPD to mask my inability to create script plugins, yet smooth sailing through accomplishing my tasks.

Quickly jumping to the point, my blog on dynamically populating custom word documents for a custom list, saw a lot of visitors. With folks moving towards Flow, I attempted to achieve the same and hence my blog with the same goal, but a different Flow.

Where to start from?

Let’s start with a simple use case so that we understand better how this flow may help. Say, I have a product which might have a few variable properties that may keep changing over a period. I’ll take an example from our daily life say, a loaf of bread. Based on the departmental store discount or some special offer, there may be some offers running on a daily or weekly basis on this item with a certain item code.

Let’s list down what could be the variables in this case:

  • Quantity
  • Price
  • Date of manufacture/packaging
  • Description

I assume you should now be able to visualize amongst the above listed properties what kind of variations may happen – it could be 2 loaves at the price of 1, or just a discount on each loaf, the date of manufacture/packaging could vary rendering some near to expiry items on a sale, and lastly a description. I find managing versions (item change history) much easier handled in a document library. At the same time, I find changing item properties can be easier done in a list.

Hence, I create my first creating custom word document flow driven by a custom list.

How to start?

Task 0

A first task is to identify the variables in the document that may undergo change in values. I’ve labelled it Task 0 because it’s a onetime activity to do similar flows. You could either trust me or check for yourself what I say is true on the below 2 constraints:

Constraint #1

Document Property

Go to Library Settings > Advanced Settings and Edit Template. The default template file is template.dotx, open it on local MS Word application and save in the same place as ‘./<library name>/Forms/<new template name>.docx’

Create Custom Document Template from SharePoint List using Microsoft Flow

Edit this template file again and go to Insert > Quick Parts > Document Property

Create Custom Document Template from SharePoint List using Microsoft Flow

Since I said this is a custom template, you could add header, footer and some static text wherever and however you wish to format the document template.

Constraint #2

Library columns populated using Flow

The final set of column types that a flow could finally dynamically populate is the intersection of above two constraints.

It wasn’t difficult but a time-consuming process to first create one column of each column type in the document library and the driving list to check which columns can be dynamically populated, to conclude that much like my last blog using the SPD, only the following column types work:

  • Single line of Text
  • Multiple line of Text
  • Currency
  • Number
  • Date and Time

Note: With a few tests, I just discovered that the Multiple line of text works best as a Plain or Rich text in this case since

Enhanced rich text passes on HTML tags to the document with the Flow.

Create Custom Document Template from SharePoint List using Microsoft Flow

Task 1

Once you’ve identified what all are the supported column types, create a mapping between the properties of the item and the column type.

For now, a very basic structure with of the supported column types fits well in the example I mentioned earlier. I could map the variables in a loaf of bread as below:

  1. Available Offer – Single line of Text
  2. Offer Description – Multiple line of Text
  3. Quantity – Number
  4. Price – Current
  5. Date of Packaging – Date and Time
  6. Date of Expiry

Task 2

Customize the list and document library structure to contain those columns.

List Column Settings

Create Custom Document Template from SharePoint List using Microsoft Flow

Where Title could just be the item code that remains unique for each item

Document Library Column Settings

Create Custom Document Template from SharePoint List using Microsoft Flow

Task 3

Modify the template in the document library from Library Settings > Advanced Settings > Edit Template and name it with a .docx extension in the same path.

In my example, my document library is Test Doc Lib, and my custom template file is template1.docx

Create Custom Document Template from SharePoint List using Microsoft Flow

Edit Template and format it – you could use color, font, images…just anything that you want in your static content!

Create Custom Document Template from SharePoint List using Microsoft Flow

Here, all grey text within square brackets are inserted from Quick Parts > Document Property and the text in black is the static text that should remain constant across all documents in the library as a part of the template.

Task 4

Step 1

Start your flow with a blank template to trigger a flow When an item is created or modified

Create Custom Document Template from SharePoint List using Microsoft Flow

Step 2

Add the next step to create a document in the library as a copy of the library template file (./<library name>/Forms/<custom template name>).

Create Custom Document Template from SharePoint List using Microsoft Flow

Special Note

Set the Overwrite Flag as Yes – I’ll explain this later.

Step 3

Add the next action to the Flow to Get file metadata using path (choose the action with SharePoint icon against it from the list of available actions)

Create Custom Document Template from SharePoint List using Microsoft Flow

Each step of a Flow provides some Dynamic content to be used. Since this step is using the Name data from the previous action Copy File, expand to see dynamic content underneath when you click in the text box against File Path.

Step 4

The final step is to update the properties of this file so that the placeholders from the custom document template get filled with real data.

The following screen shot is photo-shopped to show the different Dynamic content used from different actions in the flow.

Create Custom Document Template from SharePoint List using Microsoft Flow

Step 5

As a final step, save and test the flow.

Example

I’ve filled in one list item for you to see how it works.

Create Custom Document Template from SharePoint List using Microsoft Flow

When I check my document library, I see that 1 item is created.

Create Custom Document Template from SharePoint List using Microsoft Flow

On click of document Name, don’t be shocked to see nothing in your word document when you open in Word Online.

Create Custom Document Template from SharePoint List using Microsoft Flow

Instead, Edit In Word to open it with your local machine word application.

Create Custom Document Template from SharePoint List using Microsoft Flow

This is a perfect document the way I wanted it to be.

What else should I know?

As I’d promised in an earlier section, here is the explanation of how setting the Overwrite to Yes benefits us.

Not very common that items/products undergo a change in their unique code or name. Hence, in a case like a departmental store, where the same product can undergo frequent periodic changes, you do not want a new word document to be generated each time with a change.

In this case, one additional setting that you’d be needed to do with setting this flag as Yes is set versioning on the document library to Create major and minor (draft) versions, so that each change adds a new version to the same file.

Create Custom Document Template from SharePoint List using Microsoft Flow

However, since the Flow starts creating a document from a copy of the document library of template and then change its properties based on the list item changes, hence each time a change is made, if an old word document exists in the document library with the same name, a new version is added which is a copy of the template and then the document properties are modified in the next version of the same Flow.

Hence, if you check the Versions history of the document corresponding to any list item, it looks something similar as in the below screenshot.

Create Custom Document Template from SharePoint List using Microsoft Flow

I realize this is quite clumsy, however with the Create major and minor (draft) versions turned ON, when you open Versions History, you could quite easily click on Delete Minor Versions on the top and get rid of all those unwanted versions and your document looks neat and clean as in the following image.

Create Custom Document Template from SharePoint List using Microsoft Flow

I haven’t come across a better workaround than this, so if you readers want to try out this flow, do drop in your comments letting me know how you have improved it!

9 Replies to “Create Custom Document Template from SharePoint List using Microsoft Flow”

        1. It may be some unexpected input at a certain field. Your flow output should detail where the error occurred. For a start you may execute the flow as in my case and thereafter tweak it to your need.

  1. If you got an organizational account, please try signing out and signing back in and make sure you have contribute permissions to the list and document libraries

  2. Hello Supria !
    thanks for sharing your knowledge with us. Just wondering how to load table into word file using Microsoft flow.
    thanks in adance

    1. Hi Parakarma,

      There could be 2 aspects to it:
      Case 1. you have a fixed table
      Case 2. you have a table with dynamic data (in this case you might not have the flexibility to have dynamic number of rows).

      For Case 1 – you could create the table as a part of your document template and the data in it should stay.

      For Case 2 – Create a table structure and in each cell place the fields of the document library that will get data populated from the list when the flow runs. However, you will not be able to change (add/remove) the number of rows in run time, but you could definitely get documents of same table structure with dynamic values.

      Hope this helps!

      1. Hi Supriya,

        i saw your screenshot showing that you can create a field with rich text enabled in the document library when I tried it it doesn’t seem to have this rich text option at all in the document library column settings. do I miss something here?

        Thanks
        Nick

        1. Hey Nick, I see that as a recent change that may have been rolled out in SharePoint. However I trust that the change shouldn’t impact a lot unless you come across a scenario where you have a dire need of a Rich Text multiple line of text column in a document library.

Leave a Reply

Your email address will not be published. Required fields are marked *