Office JS API fails to write less than 10000 cell values to a table

Question

We are working with office js API to write data to a table. The code was working fine until the end of November 2016 on excel online and it stopped working after that.

The code just set the values to a table and the total number of cells it is trying to write is less than 10,000 and the method to set the value is:

dataTable.getHeaderRowRange().values = [data.headerValues];
dataTable.getDataBodyRange().formulas = data.values;
dataTable.getTotalRowRange().formulas = [data.totalRow];

The complete code to reproduce the issue is hosted in GitHub

You can run the code in office online using the manifest below:

<?xml version="1.0" encoding="UTF-8"?>
<OfficeApp xmlns="http://schemas.microsoft.com/office/appforoffice/1.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="TaskPaneApp">
  <Id>5B1D06A5-5F14-4B8F-B07D-E698084397F6</Id>
  <Version>1.0.0.0</Version>
  <ProviderName>Provider</ProviderName>
  <DefaultLocale>en-US</DefaultLocale>
  <DisplayName DefaultValue="Testing Office API" />
  <Description DefaultValue="Testing Office API"/>
  <Hosts>
    <Host Name="Workbook" />
  </Hosts>
  <DefaultSettings>
    <SourceLocation DefaultValue="https://renil-abdulkader.github.io/office-js-api-testing/" />
  </DefaultSettings>
  <Permissions>ReadWriteDocument</Permissions>
</OfficeApp>

Clicking on the Load Data button will only write part of the data and console will show below error: {"name":"OfficeExtension.Error","code":"GeneralException","message":"There was an internal error while processing the request.","traceMessages":[],"debugInfo":{"errorLocation":"Range.formulas"}}.

Other issues once this happened are:

1. Adding the binding will fail
2. Once the binding is failed, delete tab will start to fail
3. If there are multiple users on the excel sheet, users will start getting You are no longer connected to the server error message.

Update 1: There are 68 rows we are writing and 136 columns per row. Updated the code to write first 40 rows and last 30 rows and that code is working fine, but not the entire 68 rows. So there shouldn't be an issue with the data itself.

Answer 1

This is due to time-out error on the online platform. As a mitigation step, is it possible to do large formula (or values) update in chunks so that there is no one single request that runs into time out issue? What that chunk size is may depend on the environment. Perhaps 2K cells to begin with? I'll follow-up on this thread if we are able to provide an alternate solution.