I am using office.js to create Excel add-ins. I do have requirement to make Ajax API call on task pane button click and render API response into excel cells. API response contains thousands of object which i am considering as rows like below:
dataApiResponse = [{AccName: "ABC", ID: 173, Name: "FDI",... contains 20 attributes in single object}, .... contains upto 5000 objects in array]
Using below method to render above response array into excel. "outputHeaders" is array of Headers which already rendered on excel cell and wants to render api reseponse on below each header.
const ExcelGetData = () => {
// below is the outputData array contains upto 10000 or 15000 objects and each object represent rows for sheet and each object contains
// upto 20 attributs means in sheet the column id would be from 'A - T'
const outputData = [{AccName: "ABC", ID: 173, Name: "FDI",... contains 20 attributes in single object}, .... contains upto 10000 objects in array];
let range;
let end = 0;
let start = 2;
let newEndRange;
let newStartRange;
const endRow = start + outputData.length;
Excel.run(context => {
const app = context.workbook.application;
const sheet = context.workbook.worksheets.getItem('sheet1');
return context.sync().then(() => {
app.suspendApiCalculationUntilNextSync();
outputData
.map(() => outputData.splice(0, 500))
.map(response => {
newStartRange = `A${start}`;
end = start + 500 - 1;
if (end < endRow) {
start = end + 1;
newEndRange = `T${end}`;
range = sheet.getRange(
`${newStartRange}:${newEndRange}`,
);
} else {
newEndRange = `T${endRow}`;
range = sheet.getRange(
`${newStartRange}:${newEndRange}`,
);
}
range.values = response;
range.format.autofitColumns();
context.sync();
return null;
});
return context.sync();
});
}).catch(err => {
console.log(`error occured${err}`);
});
};
On web version of office365 below excel API is failings with below error:
Error:
{"Message":"There was an error processing the request.","StackTrace":"","ExceptionType":""}
Also checked enter link description here question answer and got to know excel web has payload size limitation. Can you please help to resolve this issue or how i can split this operation? It would be help full as our deliverable are blocked due to this issue.
