Create Excel file from SharePoint list in Power Automate flow
This article demonstrates how to populate an Excel template with data from a SharePoint list in Power Automate (Microsoft Flow).
The case is the following: we add a new order to the list, this data fills a document template, then we send the resulting document by email.
Content of the article:
Upload and prepare Excel document template
First, we will prepare a document template in Plumsail Documents.
Create a free account for Plumsail Documents. In the account, add a new document generation process. There are two options: create it from a template or a blank. We will create the process from the ready-to-go template for a purchase order.
Otherwise, you can start from blank, upload your document and transform it into a reusable template. Make sure to select XLSX format for further work with the Excel document.
To transform your document into a reusable template, mark all places you usually copy and paste with double curly brackets. These will be dynamic placeholders, also called {{tokens}}. Your custom data will automatically fill them. See how they look in our purchase order template.
Plumsail templating syntax is simple yet powerful. It can process arrays and automatically create tables, charts, multiple sheets, and other elements with repeating data. It supports loops and nested constructions, merging cells, using formulas, hiding particular blocks based on conditions, and much more.
Once the document template is ready, we go to the settings of the output file.
Configure output and delivery settings
We set XLSX as the output file format. You can select PDF to get the resulting document in PDF. Use {{tokens}} to make the output filename dynamic and receive new documents with unique names.
Next, we add deliveries—where to send and store the resulting document. You may include as many as you desire. SharePoint, CRMs, chats, and other software, can be found there. There is a list of native integrations with third-party software to sign, deliver, and store your documents. We added an Outlook delivery to send generated documents by email.
When you use native integration with a third-party app for the first time, you have to connect your account from the app with the Plumsail account. It takes a few clicks and is not so hard, so we will not describe the process in detail. Let’s look at the configured Outlook delivery.
We also use tokens in the email body. Read the article to learn more about delivery options. After configuring deliveries, go to the Power Automate account.
Prepare SharePoint list
In our example, one purchase order is one item in the SharePoint list. Since the order may include several products or services, one column in the row contains a table. We used Plumsail Forms for SharePoint to adjust the list to our needs. Your case might be different without data tables, so you can skip this chapter and your SharePoint list as is.
Learn how to adjust SharePoint forms and lists with Plumsail Forms.
In our document template, we also have a table to list the products and their properties. We use nested tokens with a dot operator {{products.Name}}, {{products.Quantity}}, and {{products.Price}} to render product properties from the SharePoint list. The Plumsail templating engine is smart enough to understand what content to duplicate. It will iterate through all objects in the array to render them and add the rows automatically.
Populate Excel template in Power Automate flow
We decided to use the “When an item is created” trigger from the SharePoint connector to launch the flow. You can use any other trigger from your system, for example:
an event happened in CRM;
a new row is created in the cloud sheet;
web forms are submitted;
a client made a payment;
and more;
After the trigger, we added the Parse JSON action to extract the data array with product properties from the DataTable from the SharePoint list. The entire flow in our example is shown in the screenshot below. You can skip the Parse JSON action if you don’t get DataTable or data arrays from the trigger outputs.
After you set the trigger, add a new step, and find and select the Plumsail Documents connector.
You’ll be asked for “Connection Name” and “Access Key”. You can type any name for the connection. For example, “Plumsail Documents”. Then create an API key in your Plumsail account page, and copy and paste it to the “Access Key” field.
After that, you will see a lot of actions from the Plumsail Documents connector. Find and select “Start document generation process”. It has two parameters:
Process name. Here we select the process created in Plumsail Documents.
Template data. Here we map tokens from the Excel template with output from the trigger in the JSON format.
Note
As we emphasized in the screenshot above, do not use ” ” marks when putting outputs from the “Parse JSON” action into JSON format.
The flow is done, let’s test it and see the result. We added a new order to the SharePoint list.
After that, we automatically got the email with the document you can see in the screenshot below.
Now, you know how to instantly create Excel file with data from your favorite apps using Power Automate and Plumsail Documents.