1

Apparently there is a 5MB limit when making requests in Excel Online (see: https://github.com/OfficeDev/office-js-docs-reference/issues/354).

We are using the Office JavaScript API to write large amounts of data into an Excel worksheet, using the following code:

// Example rows - in our actual code this comes from an API
const rows = [
  ["Date", "Product", "Sales", "Customers"],
  ["13/03/2020", "Chocolate biscuits", 598.00, 93],
  // ... and many more
]

sheet.getRangeByIndexes(0, 0, numRows, numColumns).values = rows;

Exceeding the aforementioned limit will cause this error to be thrown: RichApi.Error: An internal error has occurred.

The number of rows and columns is unknown at build time; the size of the data depends on queries run by users of an add-in.

Is there any reliable way of ensuring our request does not exceed the limit?

I attempted tracking the size of the JSON serialised value arrays, and allowing some overhead factor:

Excel.run(async context => {
  const sheet = context.workbook.worksheets.add();

  // 50% of 5MB: allow 50% of overhead
  const THRESHOLD = 0.5 * (5 * 1000 * 1000);
  let bytes = 0;

  // Example rows - in our actual code this comes from an API
  const numColumns = 4;
  const rows = [
    ["Date", "Product", "Sales", "Customers"],
    ["13/03/2020", "Chocolate biscuits", 598.00, 93],
    // ... and many more
  ];

  for (let rowIndex = 0; rowIndex < rows.length; rowIndex++) {
    const row = rows[rowIndex];
    sheet.getRangeByIndexes(rowIndex, 0, 1, numColumns).values = [row];
    bytes += JSON.stringify([row]).length;

    if (bytes >= THRESHOLD) {
      await context.sync();
      bytes = 0;
    }
  }

  return context.sync();
}

Even allowing a 50% overhead, the call to context.sync() was still throwing RichApi.Error: An internal error has occurred. with some data. Perhaps I could set this to something really low (like 10%) but that would be quite inefficient in most cases. I am hoping there's a more reliable way of computing the payload size, or a way of interrogating the Office API to check the size of the pending request.

extremeandy
  • 503
  • 3
  • 13
  • Can say more about why tracking the size of the JSON is unreliable? What goes wrong? – Rick Kirkham Apr 12 '20 at 17:34
  • Essentially what I did was: ```javascript let bytes = 0; for (const row of rows) { worksheet.getRangeByIndexes(0, 0, numRows, numColumns).values = row.values; bytes += JSON.stringify(row.values).length; if (bytes > THRESHOLD) { await context.sync(); } } ``` I basically had to set `THRESHOLD` to something quite low like `500000` for this to work, and even then it didn't always work, so I figured I must be doing something really wrong in my bytes estimation. I don't know what's going on under the hood, so making some assumptions like this seems risky. – extremeandy Apr 13 '20 at 09:15
  • Thanks, but please edit your original question with the code instead of putting it in a comment, so its readable. And please provide more of the code. I can't tell whether `rows` is an Office object or your own object. Also, please be more specific than phrases like "didn't always work". – Rick Kirkham Apr 13 '20 at 16:10
  • Could you please check if any of cell content is more than 65535 bytes? – Raymond Lu Apr 14 '20 at 10:51

1 Answers1

1

The request payload size is proportional to: -the count of API calls -the count of objects (for example range object) -the length of value to be set

So in order to make improve efficacy of script, need to optimize API calls number as small as possible. If to call Range.Values API for each row, there will be more payload overhead.

Here a sample with optimized API calls for reference: 

    const newValues = [
  ["Date", "Product", "Sales", "Customers"],
  ["13/03/2020", "Chocolate biscuits", 598.00, 93],
  // ... and many more
];

for (let rowIndex = 0; rowIndex < newValues.length;) {
  const row = newValues[rowIndex];
  var bytes = JSON.stringify([row]).length;
  var valuesToSet = [];
  valuesToSet.push(row);

  var rowCountForNextBatch = 1;
  for (; (rowIndex + rowCountForNextBatch) < newValues.length; rowCountForNextBatch++) {
    const nextRow = newValues[rowIndex + rowCountForNextBatch];
    bytes += JSON.stringify([nextRow]).length;

    if (bytes >= THRESHOLD) {
      break;
    }
    valuesToSet.push(nextRow);
  }

  console.log(valuesToSet);
  console.log(rowCountForNextBatch);
  sheet.getRangeByIndexes(rowIndex, 0, rowCountForNextBatch, numColumns).values = valuesToSet;
  await context.sync();

  rowIndex += rowCountForNextBatch;
}
ginger jiang
  • 69
  • 6