0

In normal operation, I need to download the dataset from my company database and compute it in excel. I would like to automate it by using ExcelOnline and follow this link: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Cross-Reference-and-formatting-two-Excel-files-using-Office/td-p/728535. It is because my company only allows us to use excel...

I tried some OfficeScript. Since my dataset is super big. I can't find a solution in the document.

Can anyone give me some advices?

My code:

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
}

It gives: 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".

Brian Gonzalez
  • 1,178
  • 1
  • 3
  • 15
mike yau
  • 1
  • 1
  • Do it in pages, much like other API’s do it. – Skin Jan 31 '23 at 08:08
  • You can try processing the dataset in batches. You can read an example here: https://learn.microsoft.com/en-us/office/dev/scripts/resources/samples/write-large-dataset – Brian Gonzalez Jan 31 '23 at 17:33

1 Answers1

0

As Brian said, batches is the correct way to do it. https://learn.microsoft.com/en-us/office/dev/scripts/resources/samples/write-large-dataset

I was running into the same issue and ran scripts for individual columns as a quick and dirty work around.

John Ezekwugo
  • 1
  • Thanks in advance. I tried to pass the key by batch to powerautomate flow. it gives Timeout error. Is there any better way to do? or my code is problematic. – mike yau Feb 03 '23 at 06:56