How to populate images into Excel using Power Automate

I've come across a bunch of tickets where customers asked how to populate images into Excel using Power Automate, and this seems to be a common question in the the Power Automate community. Here are a few examples of such requests:

I am trying to build a flow that pulls a selected item from a SP list and the attachments (Photos) and insert them into an excel file. ... I'm just a bit stuck on best process and actually getting the images into excel itself.

I am trying to make a flow where, on submission of a Microsoft form (which contains text and pictures) all the data is added to an excel sheet based on specific user inputs to my OneDrive business folder.

That's why I decided to compile everything into a blog post, where I explain how to use a picture formatter in an Excel template to populate images from a SharePoint list and Microsoft Forms submissions.

In the article:

• Set up Excel template with picture formatter
• Populate images into Excel from SharePoint list
  • Step 1: Getting data from SharePoint list item
  • Step 2: Initialize an array variable to store image attachments
  • Step 3: Start document generation process
• Populate images into Excel from Microsoft Forms
  • Step 1: Getting data from Microsoft Form
  • Step 2: Using Parse JSON action
  • Step 3: Initialize an array variable to store uploaded images
  • Step 4: Collect images into the array variable
  • Step 5: Start document generation process
• Get started with Plumsail Documents

Set up Excel template with picture formatter

We need to prepare an Excel template, which will work as a Maintenance/Repair request form. We'll pull information from a SharePoint list containing such requests or from a Microsoft Form and insert it into this template.

To insert pictures, we'll use the picture formatter with the Auto parameter. With this option, you only need to specify one dimension—either width or height. The other dimension will be calculated automatically.

Since we'll insert two or more images, we need to use an array of images. To define an array in the Excel template, we use a token with a dot. Please review the Excel templates syntax.

{{images.image}:picture(300, Auto)}

 

The result looks like this:

 

Populate images into Excel from SharePoint list

The first case is getting data from a SharePoint list and using the data to create a Maintenance/Repair request Excel file.

The SharePoint list item contains the name of the client, their address, a description of the issue, and a few photos as attachments.

 

The entire flow looks like this:

 

Let's review the flow in detail.

Step 1: Getting data from SharePoint list item

Our flow is designed to be started from the SharePoiint list itself: open the SP item menu - Automate - select the Power Automate flow.

 

To link the flow with the SharePoint list we use For a selected item trigger.

 

Next, we're getting information about the selected item using Get Item and Get attachments SharePoint actions:

 

Step 2: Initialize an array variable to store image attachments

To insert the image attachments into our XLSX template, we need to store them in an array variable. We initialize the variable, retrieve the attachments content, and append it to the array in a loop.

 

Step 3: Start document generation process

The last step of the flow is Start document generation process.

 

This action starts a Documents process that creates and sends the generated Excel file via Outlook. To configure the process follow the steps below.

Register or log in to your Plumsail account to start configuring the process.

Then go to the Processes section in your Plumsail account click Add process, and select Start from blank:

 

Select XLSX as the template type:

 

Name the process, click Next and proceed to the Configure template step.

Here we upload the Maintenance/Repair request XLSX template:

 

And configure the settings:

 

The next step is Delivery. We will deliver the result document via Outlook delivery. See all the available delivery methods here.

 

That's all for configuring the process. You can find more details about Processes in the documentation.

Populate images into Excel from Microsoft Forms

The second case is getting data from a Microsoft Form and using the data to create a Maintenance/Repair request Excel file.

The Microsoft Form looks like this:

 

And this is the entire Power Automate flow:

 

There are some new actions in this case. Let's review them.

Step 1: Getting data from Microsoft Form

The flow starts when a Microsoft Forms is submitted. For this we use When a new response is submitted trigger:

 

Next, we're getting the response details using Get response details action:

 

Step 2: Using Parse JSON action

We add Parse JSON action to get the image dynamic content.

 

We generate the scheme from a sample. To get the sample we can run our flow with just Get response details action and copy the line from the Photos field.

 

Then click Generate from sample in the Parse JSON action and insert the copied sample JSON payload.

 

Step 3: Initialize an array variable to store uploaded images

This flow also includes an array variable, similar to the case with a SharePoint list.

 

Step 4: Collect images into the array variable

On the step we retrieve the image file content using the OneDrive for business connector and use id dynamic data from the Parse JSON action. Power Automate automatically adds Apply to each action.

 

Then we append the image content to the array variable using the following expression:

body('Get_file_content')['$content']

 

Step 5: Start document generation process

This is the same action as we use with the SharePoint list example. However, in that case, we select dynamic content from a Microsoft Form. Also, click on the button and then select the array variable.

 

Get started with Plumsail Documents

By following these steps, you can efficiently generate Excel documents with embedded pictures from SharePoint lists or Microsoft Forms submissions. Whether you're managing maintenance requests, processing surveys, or handling other tasks, these methods ensures accuracy and automation.

If you're new to Plumsail Documents, register an account and try the steps yourself. Feel free to book a call with our team to discuss the product and your requirements.