-1

I have data in Google sheet1 A:I, I need to copy the row from A:I if column J has value then paste to last row of sheet2. Thank for your help

Exp: I want to copy 3 rows has dog, cat and mango to last row of sheet2

enter image description here

datas   datas   datas   datas   datas   datas       
datas   datas   datas   datas   datas   datas       dog
datas   datas   datas   datas   datas   datas       cat
datas   datas   datas   datas   datas   datas       mango
datas   datas   datas   datas   datas   datas
Kirk Beard
  • 9,569
  • 12
  • 43
  • 47
tt3
  • 31
  • 1
  • 9

1 Answers1

0

Try this:

function myFunction(){
  var sh = SpreadsheetApp.getActiveSpreadsheet();
  var sh1 = sh.getSheetByName("Sheet1");
  var data = sh1.getDataRange().getValues();
  var newArr = [];
  var checkRange = sh1.getRange(1, 10, sh1.getLastRow(), 1).getValues();
  var merged = [].concat.apply([], checkRange);
  var filtered = merged.filter(function (el) {
    return el != "";
  });
  if(filtered.length > 0){
    data.forEach(row => {
      if(row[9]){
        row.pop();
        newArr.push(row);
      }
    })
    var sh2 = sh.getSheetByName("Sheet2");
    sh2.getRange(sh2.getLastRow()+1,1, newArr.length, newArr[0].length).setValues(newArr);
  }else{
    return;
  }
}

Example data:

enter image description here

Output:

enter image description here

References:

Nikko J.
  • 5,319
  • 1
  • 5
  • 14
  • Hi Nikko J. That's perfect but please help: 1// I only need to copy value from column A to column I. 2// How to let scrip stop if there's no value in column J? – tt3 Mar 01 '21 at 20:32
  • @@Nikko J: That's perfect and please help me with another script: ***Starting from row 7 of sheet1: if column E is NOT BLANK and column D is BLANK then I need copy from columns A:L to last empty row of sheet2 – tt3 Mar 01 '21 at 21:38
  • @@Nikko J: Accepted and Upvoting your answer. – tt3 Mar 01 '21 at 23:05
  • How to modify the scrip if I want to start at row# 5 and I don’t want to copy the header. Thanks – tt3 Mar 25 '21 at 09:32