Excel Power Automate

Posted on  by



Note

There is simpler way to create Excel and PDF documents from a template and use the result in Power Automate if needed.

You may be using Power Automate for a while and never had the need to use this action, but it can be quite handy when some of the pre-defined actions don’t cut it. With this action, we can access Sharepoint using the SharePoint’s API.

If you want to automate the generation of purchase orders in your company, this article will help you achieve that.After going through it you will know how to create an XLSX file from a template using Create XLSX document from template action from Plumsail Documents connector in Power Automate (Microsoft Flow) and Azure Logic Apps.

Probably you have some third party system, where you create data for purchase orders.Then you get this data in Power Automate (Microsoft Flow), apply it to our purchase order template and generate a new document.

This is how the final document will look in our case:

  • Microsoft Office – Automate time-consuming business tasks and processes using Microsoft Power Automate, Excel, and Visio.
  • Excel Power View - Sheet - In the previous chapter, you have created a Power View sheet and a Power View on the Power View sheet. Note that you can create multiple Power Views on a Power.
  • Today's tutorial is about Power Automate, Excel and Outlook. In the first use case we update Excel tables with Power Automate and in the second use.

Our template and result document have to be stored somewhere. Power Automate (Microsoft Flow) has a lot of connectors for different systems. Here are just a few of them:

  • SharePoint

  • Box

  • OneDrive

  • Google Drive

  • Dropbox

  • SFTP

  • File System

In this example, we will store our documents in SharePoint. Our flow will use JSON object as a source data for the template, but you can get data from other sources. For example query list items from SharePoint.

This is how the flow looks like:

Flow trigger¶

You can actually pick any trigger. For example, you can start Flow on file creation in a SharePoint document library. We are using “Manually trigger a flow” trigger here to simplify the Flow.

Get file content¶

This action gets file content of the specified file from a SharePoint document library. You can just specify SharePoint site URL and path to your file. We are using this action to get our template’s content.

You can use any other connector to get files from your system.

Create XLSX Document from Template¶

Create XLSX document from template is the action from Plumsail Documents connector. This action is suitable for creating documents from a template.

There are two parameters:

  1. Template file

  2. Template data

In the first parameter ‘Template file’ you can put template’s content from some other action. In our case, we specified the output of the previous action as a template.

Download the template file that we will use in this article.

Plumsail Excel XLSX templates use a different approach than most other templating solutions. It uses a minimal amount of syntax to make your work done.

Read this article to get familiar with the templating engine.

In short, templating engine thinks that everything between these {{}} brackets is basically variables where it will write the data you specified in ‘Template data’.In our case, the example would be {{Order.Date}} and {{Order.Number}} object, it lets the engine know that we want to render the purchase order’s number and date.

But of course, we can implement a more complex scenario, in our template we are referring properties inside simple objects and collections, as well as properties in nested constructions.To select properties of our objects inside of the array (in JSON data) we are using a dot operator:

  • The {{Vendor.CompanyName}}, {{Vendor.Address}}, {{Vendor.Email}}, {{Vendor.Phone}} tags let the engine know that we want to render properties of the Vendor object.

  • The {{ShipTo.CompanyName}}, {{ShipTo.Address}}, {{ShipTo.Email}}, {{ShipTo.Phone}} tags let the engine know that we want to render properties of the ShipTo object.

  • The {{items.product.name}}, {{items.quantity}}, {{items.product.price}}, {{items.cost}} tags get the name, quantity, price and total cost properties in each item’s product object.

The templating engine is smart enough to understand that we refer properties inside a collection.That is how it knows what content we need to be duplicated. It will iterate through all objects in the array to render them and add the rows automatically.

You can learn more about table rendering here.

Please also note that we are using these formulas to calculate the total cost for each item individually and for all of the items:

  • =[Quantity]*[UnitPrice] - for each item

  • =SUM(D14) - for all of the items

In the second parameter, we specified data that is being applied to the template in JSON format:

Create file¶

Now you need to store text file somewhere. In our example, we use ‘Create file’ action from SharePoint connector to store the XLSX document in SharePoint document library.

You can use any other connector to store documents into your system.

Conclusion¶

Now you should have an idea how to use Create XLSX document from template action from Plumsail Documents connector for Power Automate (Microsoft Flow).If you haven’t used it yet, registering an account would be the first step. It is quite easy to get started.

-->

This tutorial teaches you how to use an Office Script for Excel on the web with an automated Power Automate workflow. Your script will automatically run each time you receive an email, recording information from the email in an Excel workbook. Being able to pass data from other applications into an Office Script gives you a great deal of flexibility and freedom in your automated processes.

Tip

If you are new to Office Scripts, we recommend starting with the Record, edit, and create Office Scripts in Excel on the web tutorial. If you are new to Power Automate, we recommend starting with the Call scripts from a manual Power Automate flow tutorial. Office Scripts use TypeScript and this tutorial is intended for people with beginner to intermediate-level knowledge of JavaScript or TypeScript. If you're new to JavaScript, we recommend starting with the Mozilla JavaScript tutorial.

AutomateExcel power automate run script

Prerequisites

Before starting this tutorial, you'll need access to the following:

  • Power Automate.
  • Excel on the web.
  • A Microsoft 365 commercial or EDU plan that includes Office Scripts.

Prepare the workbook

Power Automate shouldn't use relative references like Workbook.getActiveWorksheet to access workbook components. So, we need a workbook and worksheet with consistent names for Power Automate to reference.

  1. Create a new workbook named MyWorkbook.

  2. Go to the Automate tab and select All Scripts.

  3. Select New Script.

  4. Replace the existing code with the following script and press Run. This will setup the workbook with consistent worksheet, table, and PivotTable names.

Create an Office Script

Let's create a script that logs information from an email. We want to know how which days of the week we receive the most mail and how many unique senders are sending that mail. Our workbook has a table with Date, Day of the week, Email address, and Subject columns. Our worksheet also has a PivotTable that is pivoting on the Day of the week and Email address (those are the row hierarchies). The count of unique Subjects is the aggregated information being displayed (the data hierarchy). We'll have our script refresh that PivotTable after updating the email table.

  1. From within the Code Editor task pane, select New Script.

  2. The flow that we'll create later in the tutorial will send our script information about each email that's received. The script needs to accept that input through parameters in the main function. Replace the default script with the following script:

  3. The script needs access to the workbook's table and PivotTable. Add the following code to the body of the script, after the opening {:

  4. The dateReceived parameter is of type string. Let's convert that to a Date object so we can easily get the day of the week. After doing that, we'll need to map the day's number value to a more readable version. Add the following code to the end of your script, before the closing }:

  5. The subject string may include the 'RE:' reply tag. Let's remove that from the string so that emails in the same thread have the same subject for the table. Add the following code to the end of your script, before the closing }:

  6. Now that the email data has been formatted to our liking, let's add a row to the email table. Add the following code to the end of your script, before the closing }:

  7. Finally, let's make sure the PivotTable is refreshed. Add the following code to the end of your script, before the closing }:

  8. Rename your script Record Email and press Save script.

Power Automate Excel Get Rows

Your script is now ready for a Power Automate workflow. It should look like the following script:

Create an automated workflow with Power Automate

Excel Power Automate

  1. Sign in to the Power Automate site.

  2. In the menu that's displayed on the left side of the screen, press Create. This brings you to list of ways to create new workflows.

  3. In the Start from blank section, select Automated flow. This creates a workflow triggered by an event, such as receiving an email.

  4. In the dialog window that appears, enter a name for your flow in the Flow name text box. Then select When a new email arrives from the list of options under Choose your flow's trigger. You may need to search for the option using the search box. Finally, press Create.

    Note

    This tutorial uses Outlook. Feel free to use your preferred email service instead, though some options may be different.

  5. Press New step.

  6. Select the Standard tab, then select Excel Online (Business).

  7. Under Actions, select Run script (preview).

  8. Next, you'll select the workbook, script, and script input arguments to use in the flow step. For the tutorial, you'll use the workbook you created in your OneDrive, but you could use any workbook in a OneDrive or SharePoint site. Specify the following settings for the Run script connector:

    • Location: OneDrive for Business
    • Document Library: OneDrive
    • File: MyWorkbook.xlsx (Chosen through the file browser)
    • Script: Record Email
    • from: From (dynamic content from Outlook)
    • dateReceived: Received Time (dynamic content from Outlook)
    • subject: Subject (dynamic content from Outlook)

    Note that the parameters for the script will only appear once the script is selected.

  9. Press Save.

Your flow is now enabled. It will automatically run your script each time you receive an email through Outlook.

Manage the script in Power Automate

  1. From the main Power Automate page, select My flows.

  2. Select your flow. Here you can see the run history. You can refresh the page or press the refresh All runs button to update the history. The flow will trigger shortly after an email is received. Test the flow by sending yourself mail.

When the flow is triggered and successfully runs your script, you should see the workbook's table and PivotTable update.

Next steps

Complete the Return data from a script to an automatically-run Power Automate flow tutorial. It teaches you how to return data from a script to the flow.

Excel Automate Power Query

You can also check out the Automated task reminders sample scenario to learn how to combine Office Scripts and Power Automate with Teams Adaptive Cards.