How to Insert rows to the top of spreadsheet when amount of rows copied changes?

[Kevin Carrasco]

I am trying to alter my current script to now paste the range to the top in front of row 3.  I have added the code to add the rows, but sometimes the amount of rows the script find / copies will be different each time it's run.  How would you achieve inserting the rows based on the amount of rows stored inside the Function copy range?

Here is a link to my sample sheet: https://docs.google.com/spreadsheets/d/1VOCw4JNo-4dpeMqJuiwn8qWDVl0GkzNfG3HykEH0Tvg/edit?usp=sharing

Current Code: 

function copyrange() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = ss.getSheetByName('Fe'); //source sheet

  var testrange = sheet.getRange('A:A');

  var testvalue = (testrange.getValues());

  var csh = ss.getSheetByName('Fe'); //destination sheet

  var data = [];

  var j =[];

  //Condition check in A:A; If true copy the same row to data array

for (i=0; i<testvalue.length;i++) {

  if ( testvalue[i] == 'Sup Hourly') {

  data.push.apply(data,sheet.getRange(i+1,1,1,60).getValues());

  //Copy matched ROW numbers to j

  j.push(i);

 }

 }

//Copy data array to destination sheet

 csh.getRange(csh.getLastRow()+1,1,data.length,data[0].length).setValues(data);

  

 //shift all rows down by four from row 3

  sheet.insertRows(3, 4);

  

  //Delete matched rows in source sheet

  for (i=0;i<j.length;i++){

  var k = j[i]+1;

  sheet.deleteRow(k);

    

   //Alter j to account for deleted rows

  if (!(i == j.length-1)) {

  j[i+1] = j[i+1]-i-1;

}

}

}

[Michael O'Shaughnessy]

OK, as long as you don't have a HUGE amount of data... I suggest you use arrays and filters.  First get all the data into an array. Filter it for what you want then filter the data to remove it.  Then concat the arrays.  Here is some sample code:

function filterAndMove() {
  let sht = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("allTheData");
  let theData = sht.getRange(2, 1,sht.getLastRow()-1,sht.getLastColumn()).getValues();
  //filter1 is what you want to be at the "top"
  let filter1 = theData.filter(row => row[3] == 6);
  //filter2 is all the data MINUS filter1
  let filter2 = theData.filter(row => row[3] != 6);
  //put the 2 together
  let newArray = filter1.concat(filter2);
  //add it back to the sheet
  sht.getRange(2, 1,newArray.length,newArray[0].length).setValues(newArray);
}

Here is a link to a practice spreadsheet:

https://docs.google.com/spreadsheets/d/1bR9JKK7F_wQO0R0FmZC-UEUkIoenQrx850Zn6DV_WEc/edit?usp=sharing

Make a copy and run the script.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/b02a131b-7b3e-4f58-a95a-15ddd9baf45ao%40googlegroups.com.

[Jean-Luc Vanhulst]

If you delete the rows from HIGH to LOW you won't have a problem with the row numbers changing - so make it:

  //Delete matched rows in source sheet

for  (var i=j.length-1; i>-1; i--) {
  sheet.deleteRow( j[i]+1 );
}

for insertrows() simply use j.length ? (and you would want it to be in CSH not sheet? ) 

and then you want to insert the rows in those empty rows right? so you want to start at row 3 - not lastrow()

  

 csh.insertRows(3, j.length);
 csh.getRange(3,1,data.length,data[0].length).setValues(data);

Your current example uses the same sheet for both the reading and the writing. (ss and csh) so you probably want to change 

the beginning as well:

  var csh = ss.getSheetByName('Mem'); //destination sheet (not FE)

Here's the whole code:

function copyrange() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = ss.getSheetByName('FE'); //source sheet

  var testrange = sheet.getRange('A:A');

  var testvalue = testrange.getValues();
  var csh = ss.getSheetByName('Mem'); //destination sheet

  var data = [];
  var j =[];

  //Condition check in A:A; If true copy the same row to data array

for (var i=0; i<testvalue.length; i++) {

  if ( testvalue[i] == 'Sup Hourly') {
  data.push.apply(data,sheet.getRange(i+1,1,1,60).getValues());
  //Copy matched ROW numbers to j
  j.push(i);
 }
 }
//Copy data array to destination sheet

  //Delete matched rows in source sheet

for  (var i=j.length-1; i>-1; i--) {
  sheet.deleteRow( j[i]+1 );
}

 csh.insertRows(3, j.length);
 csh.getRange(3,1,data.length,data[0].length).setValues(data);
}

Jean-Luc Vanhulst - President - Write2market.com
Cell: 347-453-7686

On Sun, Sep 6, 2020 at 6:00 PM Kevin Carrasco <kbx...@gmail.com> wrote:

[Kevin Carrasco]

These adjustments worked excellent with only one issue which is what I was struggling with is  testvalue[i] == 'Sup Hourly') is looking for the exact match and is not bringing over Sup Hourly(X)

I see what you pointed out about my destination / Source, but I failed to add that detail (I'm terribly sorry) that the source sheet is actually the destination as well.  

There will be other tabs such as Mem that will have Sup Hourly, but I only want the source as Fe and the destination in Fe in those top above 3.  This only needs to run for FE.  I did make that adjustment in the above shared.