Move data using Button But only True Entries

Question

I have data in "Payment Approval Sheet" where I want to move data from "Payment Approval Sheet" to "Final Approval Sheet". The condition is that the entries which are True as per Column J should be moved only. Rest entries should not move.

The link of Spreadsheet is as below:

https://docs.google.com/spreadsheets/d/1cUVBBMR8TaPvvz5SwG1SJiu0KCJXpqF_Rr6WcsV9QP8/edit?usp=sharing

The code I am using is as below:

function copyInfo() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var copySheet = ss.getSheetByName("Payment Approval Sheet");
  var pasteSheet = ss.getSheetByName("Final Approval Sheet");
  
  var pasteSheet_size=pasteSheet.getRange('A:A').getValues().filter(String).length;
  var source_size=copySheet.getRange('A8:A').getValues().filter(String).length;
   
  // get source range
  var source = copySheet.getRange(8,1,source_size,10);
  // get destination range
  var destination = pasteSheet.getRange(pasteSheet_size+1,1,source_size,10);

  // copy values to destination range with copyTo
  source.copyTo(destination,{contentsOnly:true}); //copy only the values

  // copy values to destination range with setValues()
  // destination.setValues(source.getValues());

  // clear source values
 //source.clearContent();

}

soMario soMario · Accepted Answer · 2020-10-06T10:40:05

Explanation:

You can use filter() to consider only the entries which are true as per Column J:

var source_data = copySheet.getRange(8,1,source_size,copySheet.getLastColumn()).getValues().filter(row=>row[9]==true);

Solution:

function copyInfo() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var copySheet = ss.getSheetByName("Payment Approval Sheet");
  var pasteSheet = ss.getSheetByName("Final Approval Sheet");
  
  var pasteSheet_size=pasteSheet.getRange('A:A').getValues().filter(String).length;
  var source_size=copySheet.getRange('A8:A').getValues().filter(String).length;
  var source_range = copySheet.getRange(8,1,source_size,copySheet.getLastColumn());

  var move_data = source_range.getValues().filter(row=>row[9]==true);
  var source_data = source_range.getValues().filter(row=>row[9]==false);
  pasteSheet.getRange(pasteSheet_size+1,1,move_data.length,move_data[0].length).setValues(move_data);
  
  source_range.clearContent();
  copySheet.getRange(8,1,source_data.length,source_data[0].length).setValues(source_data);
      
}

Also insert checkboxes in column J in the Final Approval Sheet so when the return value is TRUE you will get a ticked checkbox.

Move data using Button But only True Entries

1 Answers