Trying to append a range to the last row of a separate sheet - Google Script

Question

I am running this function that saves simulated data from another sheet. These ranges are the data that I want saved to the last row of another sheet. So for instance if I run the sim today 12/19 - I want the data to be appended to the last row after the sim data I ran yesterday 12/18.

Problems:

1.It doesn't copy the entire range of "B5:L6" it copy's C5:G6 or something like that

It doesn't append the rows downward, it moves the items down and copy's the last range to the first row IE 10,9,8,7 instead of 7,8,9,10

Any direction with this would be very much appreciated

function saveSimData() {
  var ss = SpreadsheetApp.getActive();
  var s = ss.getSheetByName('Avery Sim 2.0');
  var destSheet = ss.getSheetByName("Test2");
  var simDataList = s.getRangeList(['B5:L6', 'B7:L8','B9:L10','B11:L12','B13:L14','B15:L16','B17:L18','B19:L20','B21:L22','B23:L24','B25:L26','B27:L28','B29:L30','B31:L32','B33:L34']);
  var simData = simDataList.getRanges();
  var numGames = s.getRange('N10').getValue();
  var lastRow = destSheet.getLastRow();
  for (var i=0;i<numGames;i++){
    destSheet.insertRowAfter(lastRow);
    simData[i].copyTo(destSheet.getRange(lastRow +1,2), {contentsOnly:true});
  }
}

Could you please redact any confidential details and share a copy of the spreadsheet with us? — Sourabh Choraria

Cooper Cooper · Accepted Answer · 2019-12-19T18:18:41

function saveSimData() {
  var ss=SpreadsheetApp.getActive();
  var s=ss.getSheetByName('Sheet1');
  var ds=ss.getSheetByName("Sheet2");
  var simDataList=['B5:L6', 'B7:L8','B9:L10','B11:L12','B13:L14','B15:L16','B17:L18','B19:L20','B21:L22','B23:L24','B25:L26','B27:L28','B29:L30','B31:L32','B33:L34'];
  var oA=[];
  for(var i=0;i<simDataList.length;i++) {
    var vA=s.getRange(simDataList[i]).getValues();//get data from each range  
    for(var j=0;j<vA.length;j++) {
      oA.push(vA[j]);//append each row to output array
    }
  }
  ds.getRange(ds.getLastRow()+1,5,oA.length,oA[0].length).setValues(oA);
}

or if you wish to use a RangeList instead:

function saveSimData() {
  var ss=SpreadsheetApp.getActive();
  var s=ss.getSheetByName('Sheet1');
  var ds=ss.getSheetByName("Sheet2");
  var sDL=s.getRangeList(['B5:L6', 'B7:L8','B9:L10','B11:L12','B13:L14','B15:L16','B17:L18','B19:L20','B21:L22','B23:L24','B25:L26','B27:L28','B29:L30','B31:L32','B33:L34']);
  var sRL=sDL.getRanges();
  var oA=[];
  for(var i=0;i<sRL.length;i++) {
    var vA=sRL[i].getValues();//get data from each  range  
    for(var j=0;j<vA.length;j++) {
      oA.push(vA[j]);//append each row to output array
    }
  }
  ds.getRange(ds.getLastRow()+1,5,oA.length,oA[0].length).setValues(oA);
}

Trying to append a range to the last row of a separate sheet - Google Script

1 Answers