Skip over previously entered data and add rows above a certain row

Question

I need to move data from two sheets to one separate sheet. My code works provided the sheet is laid out as follows:

However, the sheet must be laid out like this:

I need to insert data above the row containing Amount Total but below the first row containing the headers. I feel this may be possible by adding rows above the Amount Total using the script but my current code uses getLastRow() to skip over the existing data in exports sheet. This does not work when Amount Total is placed as shown in the 2nd image. How can I achieve this? My existing code is below:

function exportData() {

  var ards = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Newtownards");
  var bangor = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Bangor");
  var export = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Export");
  var lastRow = export.getLastRow();
  var nextCell = lastRow + 1;

  //get values for export
   var ardsRefValues = ards.getRange("B12:G12").getValues();
   var ardsAmountValues = ards.getRange("B13:G13").getValues();

   var bangorRefValues = bangor.getRange("B12:G12").getValues();
   var bangorAmountValues = bangor.getRange("B13:G13").getValues();

  for(var i = 0; i<=6; i++){

    var a = ardsRefValues.join().split(',').filter(Boolean);
    var b = ardsAmountValues.join().split(',').filter(Boolean);

    var c = bangorRefValues.join().split(',').filter(Boolean);
    var d = bangorAmountValues.join().split(',').filter(Boolean);


  }//close for loop

  //find length of the arrays
  var aLength = a.length - 1;
  var cLength = c.length - 1;

  //loop through ards data arrays
  for(i = 0; i<=aLength; i++){
     export.getRange(nextCell, 5).setValue(a[i]);
     export.getRange(nextCell, 4).setValue(b[i]);
     nextCell++
      }//close for loop

  //loop through bangor data arrays
  for(i = 0; i<=cLength; i++){
     export.getRange(nextCell, 5).setValue(c[i]);
     export.getRange(nextCell, 4).setValue(d[i]);
     nextCell++
      }//close for loop

  var data = new Array();
  var lastRow = export.getLastRow();
  var total = 0;

  for(var i = 2; i<=lastRow; i++){
    var range = export.getRange(i, 4);
    data = range.getValue();
    total += data;
  }
  export.getRange(2, 7).setValue(total);
}//close function

Update: Good news is that these sheets will be updated every week. So the amount Total must be added into the row just below the last row of data

If the header is always on the first row, you can use `var nextCell = 1;` instead of `var nextCell = lastRow + 1;`. — Titus, Aug 16 '18 at 11:37
@Titus This works if I use `var nextCell = 2;` however, when I run the code again, it just writes over the existing data, rather than skipping to the next available row — Phil135, Aug 16 '18 at 11:46
I see, in that case, you can use something like [THIS](https://stackoverflow.com/a/9102463/1552587) to find the first empty row. — Titus, Aug 16 '18 at 11:56
@Titus Good news is that the export sheet will be updated every week, the total amount can be put in directly underneath the last row of data! I will update the code. Thank you for your help — Phil135, Aug 16 '18 at 12:24

Skip over previously entered data and add rows above a certain row

0 Answers0