google-apps-scriptcopy-pastenonblank

How to find the non-blank value in column I in sheet1 then copy entire row and paste to sheet2


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       

Solution

  • 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: