title: Optimization and workarounds for loading and writing large files
body: <p>I want to realise two functions: <code>getState</code> saves the initial values and formulas of a workbook to JavaScript variables, <code>setState</code> restores initial formulas and values of a workbook from JavaScript variables. Normally, my add-in starts from executing <code>getState</code>, then do some operations that may change range formulas and values, and ends by executing <code>setState</code>.</p>
  
  <p><strong>However, I realise that these functions may raise internal errors for large Excel files (where one worksheet may contain several thousands of rows).</strong> For example,</p>
  
  <p><a href="https://i.stack.imgur.com/7yfaK.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/7yfaK.png" alt="enter image description here"></a></p>
  
  <p>Here are code snippets:</p>
  
  <pre><code>var usedRanges;
  
  function getState () {
      return Excel.run(function (ctx) {
          usedRanges = [];
          var worksheets = ctx.workbook.worksheets;
          var usedRangesLocal = [];
          worksheets.load('items');
          return ctx.sync()
              .then(function () {
                  for (var i = 0; i &lt; worksheets.items.length; i++) { 
                      worksheets.items[i].load('name');
                      usedRangesLocal[i] = worksheets.items[i].getUsedRange();
                      usedRangesLocal[i].load(["address", "formulasR1C1", "values"]);
                  }
              })
              .then(ctx.sync)
              .then(function () {
                  for (var i = 0; i &lt; worksheets.items.length; i++) {
                      usedRanges.push({
                          sheet: worksheets.items[i].name,
                          address: usedRangesLocal[i].address,
                          formulasR1C1: usedRangesLocal[i].formulasR1C1,
                          values: usedRangesLocal[i].values
                      });
                  }
              });
      });
  }
  </code></pre>
  
  <p>and</p>
  
  <pre><code>function setState () {
      return Excel.run(function (ctx) {
          var worksheets = ctx.workbook.worksheets;
          worksheets.load('items');
          return ctx.sync()
              .then(function () {
                  for (var i = 0; i &lt; worksheets.items.length; i++) {
                      var r = worksheets.items[i].getRange(usedRanges[i].address);
                      r.values = usedRanges[i].values
                      r.formulasR1C1 = usedRanges[i].formulasR1C1; 
                  }
              });
      });
  }
  </code></pre>
  
  <p>These are <a href="https://www.matrixlead.com/test/Manifest_remote.xml" rel="nofollow noreferrer">Manifest_remote.xml</a>, <a href="https://www.matrixlead.com/test/Home.html" rel="nofollow noreferrer">Home.html</a> and <a href="https://www.matrixlead.com/test/Home.js" rel="nofollow noreferrer">Home.js</a>. In Home.js, I try to do some workarounds by splitting loading of different properties, but the result is still not good.</p>
  
  <p>So I test this add-in under <code>Excel Online</code> against different subsets of a large workbook. The tests show that, bigger a workbook is, more likely the functions fail (<strong>especially <code>setState</code> takes a lot of time and is very likely to stuck, even under Excel Windows</strong>) . Here are the three files: <a href="https://www.matrixlead.com/test/GetNoAllNo.xlsx" rel="nofollow noreferrer">GetNoAllNo.xlsx</a>, <a href="https://www.matrixlead.com/test/GetYesAllNo.xlsx" rel="nofollow noreferrer">GetYesAllNo.xlsx</a> and
  <a href="https://www.matrixlead.com/test/GetYesAllYes.xlsx" rel="nofollow noreferrer">GetYesAllYes.xlsx</a>. </p>
  
  <p>These xlsx files are realistic and not very large. So I really look for solutions or workarounds to make <code>getState</code> and <code>setState</code> work better.</p>
  
  <p>Could anyone help?</p>