I’m often asked about methods to export data from Power Automate to Excel, so here are my notes on this topic.
What We Want to Accomplish
This article discusses how to export large amounts of data (in this case, 501 items from a SharePoint Online list) to Excel using Power Automate.
In this article, I’ve tested the following three methods:
- Excel Online’s “Add a row into a table” action
- Office Scripts
- Bonus method: CSV export
Note that this is just a sample using Power Automate to export a SharePoint Online list. If you simply need to export a SharePoint list to Excel, using SharePoint’s built-in “Export to Excel” feature is the easiest approach.
Excel Online’s “Add a Row to Table” Action
Let’s first look at the simplest method using Excel Online actions.
Create a table in Excel:
Then use an “Apply to each” loop in Power Automate to add records one by one.
This approach is the most straightforward to set up, but it took 29 minutes to process 501 records.
*Note: Actual time may vary depending on timing and environment.
*Note: Actual time may vary depending on timing and environment.
The output looks like this:
Office Scripts
Next, let’s look at the Office Scripts method. Here’s the code:
/**
* Function to output title row and data to a specified worksheet
* @param workbook - Excel workbook context
* @param sheetName - Name of the sheet where data will be output
* @param data - Array of objects containing title and body properties
*/
function main(workbook: ExcelScript.Workbook, sheetName: string, data: { title: string, body: string }[]): void {
// End processing if there is no data
if (!data || data.length === 0) {
console.log("No data available");
return;
}
// Get the specified sheet (create if it doesn't exist)
let sheet: ExcelScript.Worksheet;
try {
sheet = workbook.getWorksheet(sheetName);
if (!sheet) {
sheet = workbook.addWorksheet(sheetName);
}
} catch (error) {
// Create a new sheet if it doesn't exist
sheet = workbook.addWorksheet(sheetName);
}
// Activate the sheet
sheet.activate();
// Create header row (title row)
let headers = Object.keys(data[0]);
// Output range is fixed for this sample
let headerRange = sheet.getRange("A1:B1");
headerRange.setValues([headers]);
headerRange.getFormat().getFont().setBold(true);
headerRange.getFormat().getFill().setColor("#D9E1F2");
// Create data rows
let dataValues = data.map(item => [item.title, item.body]);
let dataRange = sheet.getRange(`A2:B${data.length + 1}`);
dataRange.setValues(dataValues);
// Auto-adjust column widths
sheet.getUsedRange().getFormat().autofitColumns();
}
When calling from Power Automate, use the “Select” action to prepare data that matches the script parameter types, then execute it.
The result took only 10 seconds, which is dramatically faster than the Excel Online action method.
The output looks like this:
*Note: It’s also possible to format the output as a table. See Bonus Section 2 for details.
*Note: It’s also possible to format the output as a table. See Bonus Section 2 for details.
Of course, you can change the starting position of headers and flexibly modify the output format as needed. For exporting large amounts of data from Power Automate to Excel, Office Scripts is highly recommended as the best approach.
Bonus 1: CSV Output Method
If your requirement is simply “being able to view the exported data in Excel” and you find writing Office Scripts too cumbersome, outputting to CSV format is another viable option.
If you set up your Power Automate flow like this:
The process completes in about 2 seconds:
You can then view the content in Excel.
Note: When outputting Japanese text, character encoding issues may occur. In such cases, add a BOM (Byte Order Mark) to the output.
Note: When outputting Japanese text, character encoding issues may occur. In such cases, add a BOM (Byte Order Mark) to the output.
Bonus 2: Converting the Office Scripts Output Range into a Table
By adding the following code to your Office Scripts output, you can convert the results into a table:
Note: In this example, we’ve used a fixed string (“SampleTable”) for the table name, but you can also retrieve it from an argument or generate it dynamically with a sequential number.
Note: In this example, we’ve used a fixed string (“SampleTable”) for the table name, but you can also retrieve it from an argument or generate it dynamically with a sequential number.
/**
* Function to output title row and data to a specified worksheet
* @param workbook - Excel workbook context
* @param sheetName - Name of the sheet where data will be output
* @param data - Array of objects containing Title and body properties
*/
function main(workbook: ExcelScript.Workbook, sheetName: string, data: { title: string, body: string }[]): void {
// End processing if there is no data
if (!data || data.length === 0) {
console.log("No data available");
return;
}
// Get the specified sheet (create if it doesn't exist)
let sheet: ExcelScript.Worksheet;
try {
sheet = workbook.getWorksheet(sheetName);
if (!sheet) {
sheet = workbook.addWorksheet(sheetName);
}
} catch (error) {
// Create a new sheet if it doesn't exist
sheet = workbook.addWorksheet(sheetName);
}
// Activate the sheet
sheet.activate();
// Create header row (title row)
let headers = Object.keys(data[0]);
let headerRange = sheet.getRange("A1:B1");
headerRange.setValues([headers]);
headerRange.getFormat().getFont().setBold(true);
headerRange.getFormat().getFill().setColor("#D9E1F2");
// Create data rows
let dataValues = data.map(item => [item.title, item.body]);
let dataRange = sheet.getRange(`A2:B${data.length + 1}`);
dataRange.setValues(dataValues);
// All data range (including headers)
let tableRange = sheet.getRange(`A1:B${data.length + 1}`);
// Create table
let table = sheet.addTable(tableRange, true);
// This is a sample so the table name is fixed (need to add sequential numbers dynamically or check for existence)
table.setName('SampleTable');
// Auto-adjust column widths
sheet.getUsedRange().getFormat().autofitColumns();
}
The result looks like this, properly formatted as a table:
コメント