Payload size limit: ExcelScript

Question

Hi everyone,

I am working with Office Script for first time and I am finding difficulties trying to bypass the limit in ranges and data we can work with. I am working with 72k lines, which throws the following error:

"Line 72: Worksheet getRangeByIndexes: The request payload size has exceeded the limit. Please refer to the documentation: "https://learn.microsoft.com/office/dev/add-ins/concepts/resource-limits-and-performance-optimization#excel-add-ins"."

I tried to implement some of the solutions that appear on that link, but I am afraid they are not meant for ExcelScript. However I always find issues with the length of the arrays I am working with.

I'd like to find a more efficient way than breaking the data in processable chunks.

Is there a solution I can implement? is 'await context.sync()' a possible solution?

Answer 1

Hi Eduardo,
I'm sorry that link is wrong. That's for the Office JS API, not Office Scripts. The equivalent documentation for Office Scripts is here: Platform limits and requirements with Office Scripts.

Here's a sample that should help show you how to split operations into smaller pieces: Write a large dataset.

Under the hood, Office Scripts is trying to optimize your script by reducing the number of calls to the workbook (all that context.sync() stuff is happening on your behalf). To get a script to write a smaller amount, you'll need to trigger that by splitting your write operations into chunks and calling console.log between those calls.