google-apps-script

Adding a for/next loop to existing script [solved]


I have this simple code that takes my range and providing there is a value in colDK [0], it will copy the contents onto my destination sheet.

function send_purchase_invoices() {
  var ss = SpreadsheetApp.getActive();
  var srcSheet = ss.getSheetByName("MAIN");
  var dstSheet = ss.getSheetByName("purchase_invoices");

  var data_range = srcSheet.getRange('DK4:DR18'); 
  var data_data = data_range.getValues();
  var data_clean  = data_data.filter(function (r) {return r[0]});

  var lr = dstSheet.getLastRow(); 

  dstSheet.getRange(lr+1, 1,data_clean.length,8).setValues(data_clean); 
}

I just want to change it, so it only copies the data if there ISN'T a value in colDK.

I assume it is this row here that needs changing, but I don't know how.

  var data_clean  = data_data.filter(function (r) {return r[0]});

Any constructive advice is appreciated.


Solution

  • You may use logical NOT

    Complete code:

    function send_purchase_invoices() {
      var ss = SpreadsheetApp.getActive();
      var srcSheet = ss.getSheetByName("MAIN");
      var dstSheet = ss.getSheetByName("purchase_invoices");
    
      var data_range = srcSheet.getRange('DK4:DR18'); 
      var data_data = data_range.getValues();
      
      var data_clean  = data_data.filter(function (r) {return !r[0]});
      var lr = dstSheet.getLastRow(); 
    
      dstSheet.getRange(lr + 1, 1, data_clean.length, 8).setValues(data_clean); 
    }
    

    You just have to put not ! to the value of a function so it will filter the value of a DK which does not have any value.

      var data_clean  = data_data.filter(function (r) {return !r[0]});
    

    Sample output:

    enter image description here

    NOTE: The range that I have used on this sample output is only for testing purposes to demonstrate how the code works.

    Reference: Logical NOT(!)