title: Google Sheet, using deleterows in script keeping formulas
body: <p>I have a script that runs every 10 minutes and deletes 1 row on every iteration to keep always 144 rows for a 24 hours chart. It works pretty well for a sheet without formulas, but the other 3 sheets got formulas in them referring to the first one and when the script runs, they don't do what I would expect. Here's the script:</p>
  
  <pre><code>  var tableData = ss.getSheetByName("TableData");
    var rowsToKeep = 149;   
    var totalRows = tableData.getLastRow();
    var numToDelete = totalRows - rowsToKeep;
        if (numToDelete &gt; 0)
         {
           tableData.deleteRows(6, numToDelete);        
  }
  
    var chartData1 = ss.getSheetByName("ChartData1-15");
    var rowsToKeep1 = 146;   
    var totalRows1 = chartData1.getLastRow();
    var numToDelete1 = totalRows1 - rowsToKeep1;
        if (numToDelete1 &gt; 0)
         {
           chartData1.deleteRows(3, numToDelete1);          
  }
  
  
    var chartData2 = ss.getSheetByName("ChartData10-20");
    var rowsToKeep2 = 146;   
    var totalRows2 = chartData2.getLastRow();
    var numToDelete2 = totalRows2 - rowsToKeep2;
        if (numToDelete2 &gt; 0)
         {
           chartData2.deleteRows(3, numToDelete2);          
  }
  
  
    var chartData3 = ss.getSheetByName("ChartData15-64");
    var rowsToKeep3 = 146;   
    var totalRows3 = chartData3.getLastRow();
    var numToDelete3 = totalRows1 - rowsToKeep3;
        if (numToDelete3 &gt; 0)
         {
           chartData3.deleteRows(3, numToDelete3);         
  }
  </code></pre>
  
  <p>Formulas repeated in rows and columns are:</p>
  
  <blockquote>
    <p>=IF(TableData!B$3>=$A$1, TableData!B5,"")
    =IF(TableData!B$3>=$A$1, TableData!B6,"")
    =IF(TableData!B$3>=$A$1, TableData!B7,"")</p>
  </blockquote>
  
  <p>and so on.... - for chartData1</p>
  
  <blockquote>
    <p>=IF(and(TableData!B$3>=$B$1, TableData!B$3&lt;=$A$1), TableData!B5,"")
    =IF(and(TableData!B$3>=$B$1, TableData!B$3&lt;=$A$1), TableData!B6,"")
    =IF(and(TableData!B$3>=$B$1, TableData!B$3&lt;=$A$1), TableData!B7,"")</p>
  </blockquote>
  
  <p>and so on.... - for chartData2</p>
  
  <blockquote>
    <p>=IF(TableData!B$3&lt;=$A$1, TableData!B5,"")
    =IF(TableData!B$3&lt;=$A$1, TableData!B6,"")
    =IF(TableData!B$3&lt;=$A$1, TableData!B7,"")</p>
  </blockquote>
  
  <p>and so on... - for chartData3</p>
  
  <p>What happens is: in chartData1 and 3 the rows change in =IF(TableData!B$3>=$A$1, #REF!,""), like missing the reference in TableData because it gets deleted; in chartData2 the rows below are translated above carrying the down formulas with them.
  Is there a way to solve my problem? Maybe a script that could recopy again the formulas from the first row (the one not deleted)?</p>
  
  <p>Thank you</p>