Super fast way to create large Excel table from Power Automate

Prologue

Exporting data to Excel file is easy with Power Automate using ‘Add a row into table‘ action. but the main problem is it’s incredibly slow and inefficient.

In this post, I will show more efficient way to perform the same thing but far way faster.

Dataset

I will use my sample dataset called ‘Data100K’ that have 8 columns as shown below

Problem (Slow method)

Let see how long to export 1,000 records of this sample data to Excel with slow method.

This flow is sample, it just get data from our SharePoint list and then add each row to Excel one-by-one using Apply to each

Sure that I’ve enable the concurrency control to 10X

This flow take ~10 minutes for just exporting 1,000 records which is incredibly slow

Solution (Fast method)

Now its time for fast method, This method will leverage Excel Office Script.

What is Excel Office Script?

Excel Office Scripts are a way to automate tasks in Excel using TypeScript-based scripts. They allow users to create, edit, and run scripts to perform repetitive tasks, manipulate data, and interact with Excel workbooks.

Office Script is cloud-based, so you need to use web version of Excel to create a script.

1. Create Excel file

Simply just create new blank excel file in your OneDrive or SharePoint

Then go to ‘Automate‘ tab and click on ‘New Script‘

2. The script

Now you can use any name for your script and then paste this code into Code Editor

function main(workbook: ExcelScript.Workbook,
    tables: string = "[]") {
    // Example JSON array input (replace this with the actual input in a real scenario)
    const data: { [key: string]: string | number | boolean }[] = JSON.parse(tables);

    // Get the active worksheet.
    const sheet = workbook.getActiveWorksheet();

    // Return early if the data array is empty.
    if (data.length === 0) return;

    // Extract the column headers from the first object in the array.
    const headers = Object.keys(data[0]);

    // Insert headers into the first row of the worksheet in batch.
    const headerRange = sheet.getRangeByIndexes(0, 0, 1, headers.length);
    headerRange.setValues([headers]);

    // Prepare the data to be inserted.
    const dataRangeValues = data.map(item => headers.map(header => item[header]));

    // Define the range where data will be inserted.
    const dataRange = sheet.getRangeByIndexes(1, 0, data.length, headers.length);

    // Insert data into the worksheet in batch.
    dataRange.setValues(dataRangeValues);

    // Define the range of the table, including headers and data.
    const lastRow = data.length + 1; // +1 for the header row
    const lastColumn = headers.length;
    const tableRange = sheet.getRangeByIndexes(0, 0, lastRow, lastColumn);

    // Add a table over the range
    sheet.addTable(tableRange, true);
}

Save the script and that’s it.

3. The flow

Let’s move to our Power Automate flow.

Instead of using ‘Add a row into table action’, now we use ‘Run script’.

Now, select a location of Excel file you want to export data to (you can use dynamic file as well)

Office Script is saved in your OneDrive and not tied to any Excel file, script can work with all file, No need to create script for each file.

The important port is the ‘tables’ parameter that will appear after you select this script, here you need to put array you want to write to Excel but wrap it with string() expression

For example, my array is from dynamic content