Google Sheet, using deleterows in script keeping formulas

Question

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:

  var tableData = ss.getSheetByName("TableData");
  var rowsToKeep = 149;   
  var totalRows = tableData.getLastRow();
  var numToDelete = totalRows - rowsToKeep;
      if (numToDelete > 0)
       {
         tableData.deleteRows(6, numToDelete);        
}

  var chartData1 = ss.getSheetByName("ChartData1-15");
  var rowsToKeep1 = 146;   
  var totalRows1 = chartData1.getLastRow();
  var numToDelete1 = totalRows1 - rowsToKeep1;
      if (numToDelete1 > 0)
       {
         chartData1.deleteRows(3, numToDelete1);          
}


  var chartData2 = ss.getSheetByName("ChartData10-20");
  var rowsToKeep2 = 146;   
  var totalRows2 = chartData2.getLastRow();
  var numToDelete2 = totalRows2 - rowsToKeep2;
      if (numToDelete2 > 0)
       {
         chartData2.deleteRows(3, numToDelete2);          
}


  var chartData3 = ss.getSheetByName("ChartData15-64");
  var rowsToKeep3 = 146;   
  var totalRows3 = chartData3.getLastRow();
  var numToDelete3 = totalRows1 - rowsToKeep3;
      if (numToDelete3 > 0)
       {
         chartData3.deleteRows(3, numToDelete3);         
}

Formulas repeated in rows and columns are:

=IF(TableData!B$3>=$A$1, TableData!B5,"") =IF(TableData!B$3>=$A$1, TableData!B6,"") =IF(TableData!B$3>=$A$1, TableData!B7,"")

and so on.... - for chartData1

=IF(and(TableData!B$3>=$B$1, TableData!B$3<=$A$1), TableData!B5,"") =IF(and(TableData!B$3>=$B$1, TableData!B$3<=$A$1), TableData!B6,"") =IF(and(TableData!B$3>=$B$1, TableData!B$3<=$A$1), TableData!B7,"")

and so on.... - for chartData2

=IF(TableData!B$3<=$A$1, TableData!B5,"") =IF(TableData!B$3<=$A$1, TableData!B6,"") =IF(TableData!B$3<=$A$1, TableData!B7,"")

and so on... - for chartData3

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)?

Thank you

Xavier J Xavier J · Accepted Answer · 2017-05-30T22:47:34

Copy the values from the cells that the formulas refer to - either up, down, or whatever appropriate. Then overwrite them with new data. This will keep you from having to replace formulas, which would be more tedious.

Google Sheet, using deleterows in script keeping formulas

1 Answers