如何使用Office JavaScript API确保Excel Online请求小于5MB

Question

显然，在Excel Online中进行请求时有5MB的限制(请参阅：https://github.com/OfficeDev/office-js-docs-reference/issues/354)。

我们使用Office JavaScript API将大量数据写入Excel工作表，使用以下代码：

// 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;

超过上述限制将导致抛出此错误：RichApi.Error: An internal error has occurred.

在生成时，行数和列数未知；数据的大小取决于外接程序用户运行的查询。

是否有可靠的方法确保我们的请求不超过限制

我尝试跟踪JSON序列化值数组的大小，并允许一些开销因素：

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();
}

即使允许50%的开销，对context.sync()的调用仍然抛出带有一些数据的RichApi.Error: An internal error has occurred.。也许我可以把这个设定得很低(比如10%(，但在大多数情况下效率会很低。我希望有一种更可靠的方法来计算有效负载大小，或者询问Office API来检查未决请求的大小。

Answer 1

请求有效负载大小与以下各项成比例：-API调用的计数-对象的计数(例如范围对象(-要设置的值的长度

所以为了提高脚本的效率，需要优化API调用次数尽可能少。如果为每行调用Range.Values API，则会有更多的负载开销。

这里有一个优化了API调用的示例供参考：

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;
}