I'm currently tasked with downloading a large dataset from my company's database and analyzing it in Excel. To streamline this process, I am looking to automate it using ExcelOnline. I found a helpful guide at this link provided by Microsoft Power Automate Cookbook.
Despite attempting to use OfficeScript to handle the data processing, I have encountered difficulties due to the size of the dataset. Unfortunately, I haven't been able to find a solution within the available resources.
Any suggestions or advice would be greatly appreciated!
Here is a snippet of the code I've been working on:
function main(workbook: ExcelScript.Workbook): RawData[] {
let lastRow = workbook.getWorksheet('DETAIL').getUsedRange().getRowCount();
let rows = workbook.getWorksheet('DETAIL').getRange('A3:CQ' + lastRow).getValues();
let records: RawData[] = [];
for (let row of rows) {
let [ORNo,SubNo, ... , LineNo,ProductionLine] = row; //95 Columns 21619rows(including header)
records.push({
ORNo: ORNo as string,
SubNo: SubNo as number,
...,
LineNo: LineNo as string,
ProductionLine: ProductionLine as string
})
}
console.log(JSON.stringify(records))
return records;
}
interface RawData {
ORNo: string
SubNo: number
...
LineNo: string
ProductionLine: string
}
The runtime error message states:
Line 3: Range getValues: The response payload size has exceeded the limit. Please refer to the documentation: "https://docs.microsoft.com/office/dev/add-ins/concepts/resource-limits-and-performance-optimization#excel-add-ins".