web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Power Automate - Building Flows
Answered

offfice scritp issue with more than 4000 rows Power Automate

(0) ShareShare
ReportReport
Posted on by 19

Hello,

 

I have the following script that is working fine when i send an array with 4000 or less rows, but when i try to send an array with more than 4000 rows the script shows this error:

 

"We were unable to run the script. Please try again.\nOffice JS error: Line 22: Worksheet getRange: The request failed with status code of 413.\r\nclientRequestId: 07e20fe8-4d2f-406d-8934-3ff0149064fa

 

 

function main(workbook: ExcelScript.Workbook,

 examplevar3: multivariable, //Header of Table
 examplevar4: recordDetail[] //Multiple rows in Table

) {

 //let selectedSheet = workbook.getActiveWorksheet();
 let selectedSheet = workbook.getWorksheet('AD');
 //Create Horizontal Header Row with Multi Variables 
 const formattedHeader = [[examplevar3.multivar3var1, examplevar3.multivar3var2, examplevar3.multivar3var3, examplevar3.multivar3var4, examplevar3.multivar3var5, examplevar3.multivar3var6, examplevar3.multivar3var7,
 examplevar3.multivar3var8, examplevar3.multivar3var9,
 examplevar3.multivar3var10, examplevar3.multivar3var11,
 examplevar3.multivar3var12, examplevar3.multivar3var13,
 examplevar3.multivar3var14, examplevar3.multivar3var15,
 examplevar3.multivar3var16, examplevar3.multivar3var17,
 examplevar3.multivar3var18, examplevar3.multivar3var19,
 examplevar3.multivar3var20, examplevar3.multivar3var21,
 examplevar3.multivar3var22, examplevar3.multivar3var23,
 examplevar3.multivar3var24, examplevar3.multivar3var25]];

 selectedSheet.getRange("A1:Y1").setValues(formattedHeader);

 const inviteRecords = generateRecords(examplevar4);

 //Populate rows below Header Row with Array Variable 
 const starterrow = 2; //starting row for "table" data

 for (let i = 0; i < inviteRecords.length; i++) {
 const currentObject = inviteRecords[i];

 const formattedrow = [[currentObject.SAMAccountName, currentObject.Email, currentObject.CommonName, currentObject.AccountStatus, currentObject.Country, currentObject.Passwordexpiresin,
 currentObject.PasswordExpiryDate, currentObject.WhenCreated,
 currentObject.WhenChanged, currentObject.PwdNeverExpiresFlag,
 currentObject.PasswordStatus, currentObject.PasswordLastSet,
 currentObject.AccountExpiryTime, currentObject.Dayssincepasswordlastset,
 currentObject.Department, currentObject.DistinguishedName,
 currentObject.ObjectClass, currentObject.Title,
 currentObject.OUName, currentObject.Manager,
 currentObject.StateProvince, currentObject.Office,
 currentObject.Company, currentObject.City,
 currentObject.Description]];

 const rowRange = `A${starterrow + i}:Y${starterrow + i}`;
 selectedSheet.getRange(rowRange).setValues(formattedrow);
 }

 //Return a response to the Cloud Flow
 return "Loaded " + examplevar4.length + " Records Loaded into Excel Completed OK"
}

function generateRecords(records: recordDetail[]): arrayvar[] {
 const interviewinvites: arrayvar[] = []

 records.forEach((record) => {
 // compute the finish time of the meeting
 interviewinvites.push({
 SAMAccountName: record.SAMAccountName,
 Email: record['Email Address'] as string,
 CommonName: record['Common Name'] as string,
 AccountStatus: record['Account Status'] as string,
 Country: record.Country,
 Passwordexpiresin: record['Password expires in'] as string,
 PasswordExpiryDate: record['Password Expiry Date'] as string,
 WhenCreated: record['When Created'] as string,
 WhenChanged: record['When Changed'] as string,
 PwdNeverExpiresFlag: record['Pwd Never Expires Flag'] as string,
 PasswordStatus: record['Password Status'] as string,
 PasswordLastSet: record['Password Last Set'] as string,
 AccountExpiryTime: record['Account Expiry Time'] as string,
 Dayssincepasswordlastset: record['Days sincepassword last set'] as string,
 Department: record.Department,
 DistinguishedName: record['Distinguished Name'] as string,
 ObjectClass: record['Object Classs'] as string,
 Title: record.Title,
 OUName: record['OU Name'] as string,
 Manager: record.Manager,
 StateProvince: record['State/Province'] as string,
 Office: record.Office,
 Company: record.Company,
 City: record.City,
 Description: record.Description,
 })
 
 
 })
 return interviewinvites;
}

interface multivariable {
 multivar3var1: string,
 multivar3var2: string,
 multivar3var3: string,
 multivar3var4: string,
 multivar3var5: string,
 multivar3var6: string,
 multivar3var7: string,
 multivar3var8: string,
 multivar3var9: string,
 multivar3var10: string,
 multivar3var11: string,
 multivar3var12: string,
 multivar3var13: string,
 multivar3var14: string,
 multivar3var15: string,
 multivar3var16: string,
 multivar3var17: string,
 multivar3var18: string,
 multivar3var19: string,
 multivar3var20: string,
 multivar3var21: string,
 multivar3var22: string,
 multivar3var23: string,
 multivar3var24: string,
 multivar3var25: string
}


interface recordDetail {
 SAMAccountName: string,
 'Email Address': string,
 'Common Name': string,
 'Account Status': string,
 'Country': string,
 'Password expires in': string,
 'Password Expiry Date': string,
 'When Created': string,
 'When Changed': string,
 'Pwd Never Expires Flag': string,
 'Password Status': string,
 'Password Last Set': string,
 'Account Expiry Time': string,
 'Days since password last set': string,
 Department: string,
 'Distinguished Name': string,
 'Object Class': string,
 Title: string,
 'OU Name': string,
 Manager: string,
 'State/Province': string,
 Office: string,
 Company: string,
 City: string,
 Description: string
}

interface arrayvar {
 SAMAccountName: string,
 Email: string,
 CommonName: string,
 AccountStatus: string,
 Country: string,
 Passwordexpiresin: string,
 PasswordExpiryDate: string,
 WhenCreated: string,
 WhenChanged: string,
 PwdNeverExpiresFlag: string,
 PasswordStatus: string,
 PasswordLastSet: string,
 AccountExpiryTime: string,
 Dayssincepasswordlastset: string,
 Department: string,
 DistinguishedName: string,
 ObjectClass: string,
 Title: string,
 OUName: string,
 Manager: string,
 StateProvince: string,
 Office: string,
 Company: string,
 City: string,
 Description: string
}

 

 

I'll appreciate your support.

 

Thanks

 

I have the same question (0)
  • Verified answer
    v-xiaochen-msft Profile Picture
    on at
    Re: offfice scritp issue with more than 4000 rows Power Automate

    Hi @dvega260 ,

     

    A 413 HTTP error code occurs when the size of a client’s request exceeds the server’s file size limit.

    I think the processed data exceeds the maximum limit.

    vxiaochenmsft_0-1649734098323.png

    https://docs.microsoft.com/en-us/office/dev/scripts/testing/platform-limits#excel

     

    Best Regards,

    Wearsky

  • dvega260 Profile Picture
    19 on at
    Re: offfice scritp issue with more than 4000 rows Power Automate

    Hi,

     

    Thanks for your help. 

     

    Regards

     

  • takolota1 Profile Picture
    4,944 Moderator on at
    Re: offfice scritp issue with more than 4000 rows Power Automate

    @dvega260 

     

    What are you trying to accomplish with the script?

     

    I just want to see if there is another way to accomplish it in the flow.

  • dvega260 Profile Picture
    19 on at
    Re: offfice scritp issue with more than 4000 rows Power Automate

    Hello @takolota,

     

    I ' am trying to copy 10000 rows into a sheet. What i do is to send all rows from Power Automate to excel in an array, Not line by line, because if i send rows line by line, the flow takes more than 4 hours to finish. The problem is that array size is more than 5mb, and the request limit for office scripts is onlye 5 mb.

     

    Thanks

  • takolota1 Profile Picture
    4,944 Moderator on at
    Re: offfice scritp issue with more than 4000 rows Power Automate

    @dvega260 

     

    Are you compiling these into the same sheet every time? Or is it a new Excel file each time?

     

    Because there is this way to send an entire array or table to a new Excel file

    https://www.tachytelic.net/2021/08/power-automate-export-to-excel/?amp

  • dvega260 Profile Picture
    19 on at
    Re: offfice scritp issue with more than 4000 rows Power Automate

    It's a new excel file each time. I will check the solution you provide me.

     

    Thanks

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Chiara Carbone – Community Spotlight

We are honored to recognize Chiara Carbone as our Community Spotlight for November…

Leaderboard > Power Automate

#1
Michael E. Gernaey Profile Picture

Michael E. Gernaey 659 Super User 2025 Season 2

#2
Tomac Profile Picture

Tomac 386 Moderator

#3
chiaraalina Profile Picture

chiaraalina 290

Last 30 days Overall leaderboard