title: Updating Sheets content - preserving cell values and cell formulae
body: <p>I wish to update a large, indeterminate number of cells on a google sheet, based on external conditions.  To maximize efficiency I intend to do a single array read and one more array write to accomplish the update with some jiggery in between to update the array contents.  Unfortunately I haven't figured out how to preserve data values and also formulae in the data range, I would appreciate any pointers.</p>
  
  <p>Here is what I tried:</p>
  
  <p>If I use getDataRange, getValues and SetValues thus Cell A1 correctly accepts the entered formula and cells B1, B3 and B4 accept the entered values, no problem.  Unfortunately all other formulae in the sheet's data range are converted to values (formulae are lost):</p>
  
  <pre><code>function updateRangeValues() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
    var range = sheet.getDataRange();
    var rngValues = range.getValues();
    /* Arbitary changes */
    rngValues [0][0] = "=Sum(b1:d1)";
    rngValues [0][1] = 1;
    rngValues [0][3] = 2;
    rngValues [0][4] = 3;
    range.setValues(rngValues);
  }
  </code></pre>
  
  <p>If on the other hand I capture, change and rewrite the formulae all cells that contain values rather than formulae are cleared, I retain only calcuculated cell content:</p>
  
  <pre><code>function updateRangeFormulae() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
    var range = sheet.getDataRange();
    var rngFormulas = range.getFormulas();
    /* Arbitary changes */
    rngFormulas [1][0] = "=Sum(b2:d2)";
    rngFormulas [1][1] = 1;
    rngFormulas [1][3] = 2;
    rngFormulas [1][4] = 3;
    range.setFormulas(rngFormulas);
  }
  </code></pre>
  
  <p>Explicitly, my question is how do I change the cell values of some cells in a range and change the cell formulae of some other cells in the same range and update all without losing cell content or formulae?  Many thanks in anticipation.</p>