Power Automate Best Practice: Using Office Scripts for Fast Excel Data Processing

I was asked to quickly retrieve large amounts of data from Excel, so I implemented a solution using Office Scripts.

スポンサーリンク

Performance and Stability Concerns with “List Rows Present in a Table”

The most standard way to retrieve a list of data from Excel is to use the “List Rows Present in a Table” action in the Excel connector.
Screenshot of the List Rows Present in a Table action in Power Automate
However, this action has some concerns regarding speed and stability. In my environment, it took about 44 seconds to retrieve 5,000 records.
Screenshot showing the 44-second processing time for retrieving 5,000 records
When trying to retrieve the maximum “100,000 rows,” the action frequently failed.
Screenshot showing an error when attempting to retrieve 100,000 rows

In this article, I’ll demonstrate how to use Office Scripts to retrieve large amounts of data from Excel more reliably.

A Versatile Script for General Use

First, let’s build a function that can retrieve values without depending on the table schema.

Development

Since Office Scripts couldn’t return an array of Objects with the ‘any’ type, I implemented it to return all rows of the table as a JSON string.
Screenshot of Office Script code returning table data as JSON string

Here’s the code.

function main(workbook: ExcelScript.Workbook, tableName:string) {
    // Get the table with the specified name
    const table = workbook.getTable(tableName);

    // Throw an error if the table doesn't exist
    if (!table) {
        throw new Error(`Table "${tableName}" not found.`);
    }

    // Get the header row of the table
    const headers = table.getHeaderRowRange().getValues()[0];

    // Get the data range of the table (excluding header and total rows)
    const dataRange = table.getRangeBetweenHeaderAndTotal();

    // Get the values from the table
    const values = dataRange.getValues();

    // Array to store the results
    let rowObjects: object[] = [];

    // Convert each row to an object and add to the results array
    for (let i = 0; i < values.length; i++) {
        let rowObject = {};
        for (let j = 0; j < headers.length; j++) {
            // Set the cell value using the header name as the key
            rowObject[headers[j].toString()] = values[i][j];
        }
        rowObjects.push(rowObject);
    }
    return JSON.stringify(rowObjects);
}
Run this script and handle the return value in Power Automate using JSON parsing.
Screenshot showing how to parse the JSON return value in Power Automate

Performance Testing

When testing, I found that for a table with 500 records, it took 7 seconds, which isn't much different from the "List Rows Present in a Table" action.
Screenshot showing 7-second execution time for 500 records
For a table with 10,000 records, it also took 7 seconds.
Screenshot showing 7-second execution time for 10,000 records
A table with 100,000 records completed in 26 seconds, and all data was successfully retrieved.
Screenshot showing 26-second execution time for 100,000 records
Screenshot showing the successfully retrieved data from 100,000 records

The script probably takes a few seconds to initialize, so the speed advantage becomes more noticeable as the number of records increases.

Scripts Specialized for Specific Tables

However, returning data as a JSON string is somewhat inefficient, and when simply retrieving date-type data, it becomes a Serial value.
Screenshot showing date values converted to serial numbers in JSON output

Therefore, I recommend creating scripts specialized for the specific table you're working with.

Development

So I created this script specialized for this particular table.
Screenshot of a specialized Office Script for a specific table structure

Here's the code.

// Define the return type
type TableRow = {
    str: string;
    num: number;
    date: string;
};

function main(workbook: ExcelScript.Workbook): TableRow[] {
    // Get the table
    const table = workbook.getTable("table500");

    // Get the range of the table
    const range = table.getRange();

    // Get the values from the table
    const values = range.getValues();

    // Get the indices of the columns to retrieve
    const headers = values[0];
    const strIndex = headers.indexOf("str");
    const numIndex = headers.indexOf("num");
    const dateIndex = headers.indexOf("date");

    // Throw an error if the column indices are not found
    if (strIndex === -1 || numIndex === -1 || dateIndex === -1) {
        throw new Error("Required columns 'str' or 'num' not found in the table");
    }

    // Array to store the results
    const result: { str: string, num: number, date:string }[] = [];

    // Process each row, skipping the header row
    for (let i = 1; i < values.length; i++) {
        const row = values[i];
        const serial_date = Number(row[dateIndex]);
        const date = excelSerialDateToISOString(serial_date);
        result.push({
            str: row[strIndex].toString(),
            num: Number(row[numIndex]),
            date: date
        });
    }

    return result;
}

/**
 * Convert Excel date serial number to ISO string
 * @param serialNumber Excel date serial number
 * @param use1904Windowing Whether to use 1904 as the base year (for legacy Mac support)
 * @returns Date string in ISO format
 */
function excelSerialDateToISOString(serialNumber: number, use1904Windowing: boolean = false): string {
    // Set the base date (1900 or 1904)
    const baseDate = use1904Windowing ? new Date(Date.UTC(1904, 0, 1)) : new Date(Date.UTC(1900, 0, 1));

    // Handle Excel date bug (Excel incorrectly treats 1900 as a leap year)
    let adjustedSerialNumber = serialNumber;
    if (!use1904Windowing && serialNumber >= 60) {
        adjustedSerialNumber--; // Adjust for February 29, 1900 bug
    }

    // Convert serial number to days (in Excel, the integer part is the date, the fractional part is the time)
    const days = Math.floor(adjustedSerialNumber);
    const timeFraction = adjustedSerialNumber - days;

    // Convert to milliseconds and add to the base date
    const milliseconds = Math.round(timeFraction * 24 * 60 * 60 * 1000);
    const date = new Date(baseDate.getTime() + ((days - 1) * 24 * 60 * 60 * 1000) + milliseconds);

    // Return in yyyy-MM-dd format
    const year = date.getFullYear();
    const month = String(date.getMonth() + 1).padStart(2, '0');
    const day = String(date.getDate()).padStart(2, '0');

    return `${year}-${month}-${day}`;
}

Performance Testing

Unfortunately, the speed wasn't significantly different from the generic script. However, this approach eliminates the need for JSON parsing in Power Automate and allows you to retrieve dates in ISO format, so I still recommend creating specialized scripts for each table.
Screenshot showing the performance results of the specialized script

Related Articles

コメント

Copied title and URL