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
