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.
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.
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.
For a table with 10,000 records, it also took 7 seconds.
A table with 100,000 records completed in 26 seconds, and all data was successfully retrieved.
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.
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.
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.
コメント