google-apps-script

Write value to data range that has been called previously

I have the following script:

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);  
  srcSheet.getRange(data_clean).setValues('SUBMITTED');
}

This line here:

srcSheet.getRange(data_clean).setValues('SUBMITTED');

I'm trying to call the data range set in this section:

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

Or, more specifically, column [0] as defined here:

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

So that it populates the word "SUBMITTED" to those cells.

Any constructive advice, greatly appreciated.

Solution

  • I believe your goal is as follows.

    Although I'm not sure whether I could correctly understand your expected result, how about the following modification?

    Modified script:

    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();


  // I modified the below script.
  if (data_clean.length == 0) return;
  dstSheet.getRange(lr + 1, 1, data_clean.length, 8).setValues(data_clean);
  const rangeList = data_data.reduce((ar, [a, ...b], i) => {
    if (!a && b.join("")) {
      ar.push(`DK${i + 4}`);
    }
    return ar;
  }, []);
  srcSheet.getRangeList(rangeList).setValue('SUBMITTED');
}

    References: