Press "Enter" to skip to content

Build a proposal document dynamically using SharePoint and Power Automate

We’ve been working with a few customers recently, helping them to create solutions which dynamically build documents based on user defined selections within an application.

Scenario

This blog post is focused on this following simple scenario:

A SharePoint list is used to capture product proposal requests, and upon posting a request a proposal document should be automatically created containing information for products selected within the request.

Configure SharePoint

This solution uses four separate SharePoint lists details below:

  • SharePoint Library: Products – Word Documents
  • SharePoint List: Proposals
  • SharePoint List: Products
  • SharePoint Library: <Selected Destination Library>

SharePoint Library: Products – Word Documents

This SharePoint library is used to store a document for each specific product, no additional metadata has been added to the library.

SharePoint List: Products

The products list contains a list of all the products. Each product should have a corresponding entry within the ‘Products – Word Documents‘ library and named exactly the same excluding the file extension, for example:

  • Products – Word Documents Entry: ‘Flow Connector.docx
  • Products Entry: ‘Flow Connector

NOTE: As you build this flow you might think you don’t need the ‘Products’ list and that the ‘Products – Word Documents’ library could be used in isolation… you’d be correct! However, the degree of separation will make it a lot easier to scale and add additional capability in future so we recommend separating.

SharePoint List: Proposals

This SharePoint list is used to capture the proposal requests and is configured as follows:

A single Lookup column named ‘Products‘ associated with the ‘Products‘ list has been added with the following configuration:

Upon data entry it should look as follows:

Building the Flow

1. Create a new Flow using the ‘Automated — from blank‘ option

2. Enter a name for the Flow, select the ‘When an item is created‘ SharePoint trigger action, click ‘Create

3. Configure the ‘When an item is created‘ SharePoint trigger action

3.a. Site Address: Enter the location of the SharePoint site where the configured lists are held.

3.b. List Name: Select the ‘Proposals‘ list

4. Add a ‘Initialize variable‘ action

4.a. (Optional): Rename the action to ‘Word Documents for Merging

4.b. Name: Set to ‘ProposalDocuments

4.c. Type: Set to ‘Array

5. Add an ‘Apply to each‘ control action

5.a. Select an output from previous steps: Select the ‘Products‘ property from the ‘When an item is created‘ SharePoint trigger action.

5. Inside the ‘Apply to each‘ action; Add a ‘Get file content using path‘ SharePoint action

5.a. Site Address: Enter the location of the SharePoint site where the configured lists are held.

5.b. File Path: Manually select any file from the ‘Products – Word Documents‘ library

5.c. File Path: Select the filename omitting the extension and then select the ‘Products Value‘ property from the ‘When an item is created‘ SharePoint trigger action

5.d. File Path: The final configured value should appear as follows

6. Add an ‘Append to array variable‘ action

6.a. Name: Select the previously created ‘ProposalDocuments‘ variable

6.c. Value: Copy and paste the following value into the ‘Value‘ field

{
  "fileName": "@{items('Apply_to_each')?['Value']}.docx",
  "fileContent": "@{base64(body('Get_file_content_using_path'))}"
}

NOTE: If you have renamed either the trigger action or the ‘Apply to each‘ action you will need to modify the copied value to reference the updated action names. If you experience issues executing the merge action please review the following article: https://blog.encodian.com/2019/12/errors-processing-document-arrays/

7. Underneath the ‘Apply to each‘ action; Add a ‘Merge Word Documents‘ Encodian action

7.a. Filename: Set the name the proposal document which will be created. For this example we have used the ‘Title‘ property from the ‘When an item is created‘ SharePoint trigger action concatenated with ‘.docx‘.

7.b. Click the ‘Switch to input entire array icon

7.c. Remove any existing text within the ‘Documents‘ field

7.d. Documents: Select the ‘ProposalDocuments‘ variable

8. Add a ‘Create file‘ SharePoint action

8.a. Site Address: Set to the value of the SharePoint site which contains the target document library

8.b. Folder Path: Set to the value of the target SharePoint folder

8.c. File Name: Select the ‘Filename‘ property from the ‘Merge Word Documents‘ Encodian action

8.d. File Content: Select the ‘File Content‘ property from the ‘Merge Word Documents‘ Encodian action

9. The completed flow should follow this construct:

10. Now lets test the flow!

11. Select ‘I’ll perform the trigger action‘, click ‘Save & Test

12. Go to SharePoint and add a new entry into the ‘Proposals‘ list

13. Verify the Flow has executed successfully

14. … the merged document containing the correct documents has been created.

Finally…

Hopefully this post provides a good basic solution which you can build upon to create solutions which dynamically generate documents based on user defined input.

We hope you’ve found this guide useful, and as ever, please share any feedback or comments – all are welcome!

Be First to Comment

Leave a Reply

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