Add SharePoint List Item Data to a Document

Following our previous post covering the new ‘Populate Word Document‘ action and how to Mail Merge a Word Document with a Microsoft Forms Response.

This post will outline how to obtain SharePoint list item data (including images stored as attachments) before them to a word document.

Supporting Information

We recommend reviewing the following documentation to support the creation of your own solutions built with the ‘Populate Word Document‘ action

• Template Syntax for Populate Word Document
• Hyperlinks
• Images
• Repeating Sections or Paragraphs
• Error Handling

Resources

I have previously created a the following Microsoft Word Document which is used for this example:

Encodian – Template Syntax – SharePoint List Data.docx

The JSON data structure is detailed below:

{
  "companyName": "Encodian",
  "employees": [
  {
    "name": "Alex Rackwitz",
    "role": "Director",
    "profileUrl": "https://www.encodian.com",
    "profileDisplay": "Profile",
    "profilePic": "base64"
  },
  {
    "name": "James Sales",
    "role": "Operations Manager",
    "profileUrl": "https://www.encodian.com",
    "profileDisplay": "Profile",
    "profilePic": "base64"
  },
  {
    "name": "Jay Goodison",
    "role": "Technology Lead",
    "profileUrl": "https://www.encodian.com",
    "profileDisplay": "Profile",
    "profilePic": "base64"
  }
 ]
}

I have configured a SharePoint list as follows:

Insert SharePoint List Item Data and Images into a Word Document

1. Create a new Flow using a trigger relevant to your requirements, for the purposes of this example I will use the ‘Manually trigger a flow‘ trigger action.

2. Add an ‘Initialize Variable‘ action, this variable will be used to store data for each employee

2.a. Name: Set to ‘employees‘

2.b. Type: Set to ‘Array‘

3. Add the SharePoint ‘Get Items‘ action to retrieve SharePoint list item data

3.a. Site Address: Select the relevant SharePoint site

3.b. List Name: Select the relevant SharePoint list

4. Add the SharePoint ‘Get Attachments‘ action to retrieve the list items attachments array

4.a. Site Address: Set as per step 3.a.

4.b. List Name: Set as per 3.b.

4.c. Id: Select the ‘ID‘ property from the SharePoint ‘Get Items‘ action. Upon selecting the ‘ID‘ property, Power Automate will automatically place the SharePoint ‘Get Attachments‘ action inside an ‘Apply to each‘ loop.

5. Add the SharePoint ‘Get attachment content‘ action

5.a. Site Address: Set as per step 4.a

5.b. List Name: Set as per 4.b

5.c Id: Select the ‘ID‘ property from the SharePoint ‘Get Items‘ action

The ‘Get attachments‘ action returns an array (i.e. one or more attachments) but given we only need to obtain a single attachment I’ll use the first() expression to negate Power Automate automatically adding an ‘Apply to each‘ loop.

Expression: first(body(‘Get_attachments’))?[‘Id’]

6. Add the ‘Append to array variable‘ action

6.a. Name: Select the ‘employees‘ variable

6.b. Value: Enter the following value

{
  "name": "",
  "role": "",
  "profileUrl": "",
  "profileDisplay": "",
  "profilePic": ""
}

We now need to populate the variable ‘Value‘ with dynamic data from the SharePoint list item as depicted below:

The ‘Profile‘ hyperlink is made up of two elements, the display value and the URL value. We can obtain the ‘URL‘ value from SharePoint and manually set the display value:

Finally, we need to append the attachment to the ‘profilePic‘ json property using the base64() expression:

Expression: base64(outputs(‘Get_attachment_content’)?[‘body’])

7. Add another ‘Initialize Variable‘ action underneath the ‘Apply to each‘ loop, this variable will be used to store data later sent to the Encodian ‘Populate Word Document‘ action

7.a. Name: Set to ‘data‘

7.b. Type: Set to ‘String‘

7.c. Value: Enter the following value

{
  "companyName": "Encodian",
  "employees": 
}

7.d Assign the ‘employees‘ array variable to the ‘employees‘ json property:

At this stage, the ‘data‘ variable has been fully populated with the correct data.

I have stored the template document within SharePoint which I’ll need to retrieve, populate with data and then save the populated document to SharePoint.

8. Add the SharePoint ‘Get file content‘ action

8.a. Site Address: Select the relevant SharePoint site

8.b. File Identifier: Select the relevant Word document

9. Add the Encodian ‘Populate Word Document‘ action

9.a. File Content: Select the ‘File Content‘ property provided by the ‘Get file content‘ action

9.b. Document Data: Select the ‘data‘ variable

10. Add the SharePoint ‘Create File‘ action

10.a. Site Address: Select the relevant SharePoint site

10.b. Folder Path: Select the relevant folder path

10.c. File Name: Either manually add a name or use the Encodian ‘Filename’ property which will always be set to ‘result.docx’. I have manually set the filename for this example.

10.d. File Content: Select the ‘File Content‘ property provided by the Encodian ‘Populate Word Document‘ action

The flow is now complete and should be similar to this structure:

Test your solution by triggering the flow! Your word document should now be populated with data from the SharePoint list:

Finally…

Hopefully, this post provides a good example of using the Encodian ‘Populate Word Document‘ action to add SharePoint list item data to a Microsoft Word document, please share any feedback or comments – all are welcome!