The best way to export data to the Excel quickly using Power Automate with Microsoft Graph API

This tutorial will guide you on exporting data from a source (such as a SharePoint list) into an Excel file in OneDrive using Power Automate. We’ll use the Microsoft Graph API to efficiently add multiple rows to an Excel table in one action, rather than looping through individual rows.

Step 1: Set Up Power Automate Flow

1. Create a new flow:

   • In Power Automate, click on Create and select Manually trigger a flow.
   • Give your flow a name and click Create.

2. Create an Excel file:

   • Add the Create file action from OneDrive for Business.
   • Specify the Folder Path where the file should be stored (e.g., /Data Export). If this folder doesn’t exist, it will be created.
   • For File Name, dynamically name the file using the expression:

     formatDateTime(utcNow(), 'yyyy-MM-ddTHH:mm:ss')
     

     This appends the current timestamp, ensuring each file has a unique name. Add the .xlsx extension (e.g., StudentData_@{formatDateTime()}.xlsx).
   • In File Content, add a space or leave it empty, as the file will be created first.

Step 2: Create a Table in the Excel File

3. Add a table to the Excel file:
   • Use the Create Table action from Excel Online (Business).
   • Set Location to OneDrive for Business and select the File dynamically from the Create File action.
   • For Table Range, define the range using the A1:G1 notation (adjust if your table has more or fewer columns).
   • Name the table (e.g., StudentTable).
   • Enter the Column Names (separated by commas) that match your data source (e.g., StudentID,Name,Region,Class,Manager,EnrolledDate,Active).

Step 3: Retrieve Data from the Source

4. Get data from a data source (e.g., SharePoint):
   • Add the Get Items action to retrieve data from your SharePoint List.
   • Select the SharePoint site and the List containing the data.
   • Enable Pagination under settings if your list contains more than 100 rows, and set a maximum threshold (up to 100,000).

Step 4: Format Data for Bulk Upload

5. Format data for bulk insertion:
   • Use the Select action to create an array of arrays (needed for the Graph API).
   • In the From field, choose Body from the Get Items action.
   • Switch to Text mode and format the array in JSON like this:

     [
       [
         <Dynamic content from Get Items for each column>
       ]
     ]
     

   • Ensure you map each SharePoint column (e.g., ID, Name, Region) to the correct Excel column.

Step 5: Set Up the Graph API Request

6. Call the Microsoft Graph API using HTTP Request:

   • Add the Send an HTTP Request action from Office 365 Users (this allows you to access the Graph API).
   • In Method, choose POST.
   • Use the following Graph API endpoint to add rows to the Excel table:

     https://graph.microsoft.com/v1.0/me/drive/items/{driveItem-id}/workbook/tables/{table-name}/rows
     

   • Replace {driveItem-id} with the file identifier dynamically retrieved from the Create Table action. You can extract the file ID by using an expression like this:

     split(outputs('Create_table')?['body/id'], '.')[1]
     

   • Replace {table-name} with the name of your table (e.g., StudentTable).

7. Set up the request body:

   • In Request Body, you need to pass the array of arrays you formatted earlier. Use the Body output from the Select action:

     {
       "values": @{body('Select')}
     }
     

8. Link to Graph API Documentation:

   • You can find more detailed information about this endpoint here: Microsoft Graph API – Add Rows to Table.

Step 6: Additional Actions (Optional)

9. Add a delay (Optional):

   • Since it takes a few seconds for the file to become fully available, add a Delay action (e.g., 30 seconds) to ensure the file is ready before any subsequent actions.

10. Get the file content:

• Use the Get File Content action from OneDrive for Business to retrieve the content of the Excel file.

11. Create a Share Link (Optional):

    • If you want to share the Excel file, add the Create Share Link action from OneDrive for Business and configure it to generate a view link.

12. Send an Email with Attachment:

    • Add the Send an Email action from Office 365 Outlook to email the Excel file as an attachment.
    • For Attachment Name, use a dynamic file name (e.g., StudentData.xlsx).
    • Attach the file by selecting the dynamic File Content from the Get File Content action.
    • Include the share link in the email body if needed.

Step 7: Test and Verify

13. Test your flow:

    • Click Test and manually trigger the flow.
    • After execution, verify that the file is created in OneDrive and that the data is correctly inserted into the Excel table.

14. Review the results:

    • Open the Excel file from OneDrive and check if all the rows from your data source have been added correctly.
    • If you set up the email, check your inbox for the attached file and shared link.