cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
victor_lion
Helper II
Helper II

Exporting large data from excel files

Hello! I'm having a problem with excel scripts while running on Power Automate. The problem is that I need to get the data from one sheet to another file where we have pivot tables that other people check, and the file that contains the data have more than 400 thousand lines and 12 columns. I was splitting 100 thousand lines per run and the first time the script runs it works, but at the second time I get the error: Erro do Office JS: Line 4: Range setValues: The argument is invalid or missing or has an incorrect format.
clientRequestId: 58780659-4968-4838-a704-c28fe9a03d32

 

The script to import the values from a column is:

 

function main(workbook: ExcelScript.Workbook, column: number, sheet: string)
{
    let selectedSheet = workbook.getWorksheet(sheet);

  let rows = selectedSheet.getUsedRange(true).getColumn(column).getValues();

  return rows;
}

 

 

Then at the power automate I split the array in other arrays that have size of 100 thousand, then convert to string and export to the file with the pivot tables using the following script:

 

function main(workbook: ExcelScript.Workbook, sheetName: string, rangeAddress: string, values: string) {
    let sheet = workbook.getWorksheet(sheetName);
    let range = sheet.getRange(rangeAddress);
    range.setValues(JSON.parse(values));
}

 

 

0 REPLIES 0

Helpful resources

Announcements
 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Users online (3,723)