Table of Contents
Creating the Power Automate Flow
For this tutorial, we are creating a simple flow that on a weekly basis, lists all Accounts from Dataverse and adds those records to a table in an Excel file. The table has just three columns and still has the default table name of table1. This file is then saved into the current user’s OneDrive.
Step 5 – Creating the Flow
From the Power Apps Maker Portal, click Flows and then New Flow (ideally, you would create this Flow inside a Solution, but for simplicity, we are just creating a standalone Flow). The first part of the Flow is a standard Recurrence Trigger and a List Rows action that returns all Accounts. After these two steps, add a Select action and for the From, choose the value from your List Rows step. Then click the highlight toggle button to switch the Select Map to Text mode:
In between a set of [] square brackets, select the fields from your List Rows action that you want to include in the spreadsheet, in the order they appear in the Excel table, taking care to include a comma at the end of each line (except the last value). If your Excel table has a column that you do not want the Flow to populate, simply enter a line with “” as the value.
Tip
An Excel formula can be inserted into a cell by including the = beforehand
Step 6 – The HTTP Action
This is the key part of the flow that takes the output from our Select action and writes it away to the Excel file. 
The values in the screenshot above should be set accordingly:
Method: POST
URI: This takes the structure of https://graph.microsoft.com/v1.0/users/<USERID>/drive/root:<PATH_TO_FILE>:/workbook/tables/<TABLENAME>/rows/add to write to a file in a user’s OneDrive. If you need to obtain the unique ID for a User, this can be obtained via the Office 365 Admin Portal; click on the required User and their ID will be in the page URL. An example of a completed URI value:
https://graph.microsoft.com/v1.0/users/838d8cea-2c13-44d7-a008-77981ba8edca/drive/root:/Excel%20Tutorial/ExcelTutorialSpreadsheet.xlsx:/workbook/tables/Table1/rows/add
Adapting the URI for a SharePoint location
The URI has a different structure for files stored on SharePointhttps://graph.microsoft.com/v1.0/sites/<SITEID>/drive/root:<PATH_TO_FILE:/workbook/tables/<TABLENAME>/rows/add. However, the <SITEID> is constructed of three parts – Hostname, Site Collection ID and Site ID. The easiest way to find these values is via Graph Explorer. When logged in, use the Search for a SharePoint Site by Keyword
Amend the search to include the name of your SharePoint site and when you run your query, one of the fields is the ID:
which is the hostname followed by two unique IDs. Copy the whole string and apply that to your Flow action. An example of a complete SharePoint URL:
https://graph.microsoft.com/v1.0/sites/mycompanyname.sharepoint.com,d87d0a8f-1363-4d92-954d-22c43ede1c0b,279cabcd-c2e9-4919-866b-083a2afa7f72/drive/root:/ExcelFiles/AccountList.xlsx:/workbook/tables/Table1/rows/add
Headers: accept application/json
Body:
{
"index":null,
"values": <OUTPUT>
}
where <OUTPUT> is the Output from your previous Select action.
Authentication: Active Directory OAuth
Authority: https://login.microsoftonline.com
Tenant: Your Tenant ID copied from earlier
Audience: https://graph.microsoft.com
Client ID: Your Application (Client) ID copied from earlier
Credential Type: Secret
Secret: Your Secret Value copied from earlier
Step 7 – The completed file
Once the flow has completed successfully, you should now have a populated table in your Excel file:
You could then have the Flow send an email with the file attached. If you choose to do this and encounter an issue where sometimes an empty file is sent instead, try introducing a Delay action to ensure that the Graph HTTP action has completed fully first.
Next: Summary
