title: How to add a new line to google spreadsheet without affecting existing formulas
body: <p>I am using this Script in a google spreadsheet to add a new line:</p>
  
  <pre><code>var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  sheet.insertRowAfter(1);
  </code></pre>
  
  <p>The problem is that any formula referencing rows gets updated, for example</p>
  
  <pre><code>=A$2
  </code></pre>
  
  <p>becomes</p>
  
  <pre><code>=A$3
  </code></pre>
  
  <p>after the row insert. Is there a way to avoid this?<br>
  (By either changing the forumlas or the script.)</p>
  
  <p>I understand the behaviour, the spreadsheet wants to keep all formulas pointing to the old cells - but it messes with my sums (these should always sum up the whole column from a$2:a$999 (a1 is the header). But those get updated to then sum up a$3:a$1000 (and so on) so they miss the new rows completely.</p>