validation - Google script pushing data from one tab to another while retaining data validation

Question

I have the following google apps script. It functions to look in my PENDING-IP tab and check the status (column G). If the status is "Screened - Ready for Review," then it moves the entire row of data to the SCREENED - READY FOR REVIEW tab.

My problem is this - the data in columns L, M, and N of the PENDING tab is a checkbox, but when it pushes it to the SCREENED tab, it changes it to TRUE or FALSE. Is there a way to modify my script to push the data with validation to retain the checkboxes? Thank you!

function screened() { 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('PENDING-IP'); //source sheet
  var testrange = sheet.getRange('G:G'); //range to check
  var testvalue = (testrange.getValues());
  var csh = ss.getSheetByName('SCREENED - READY FOR REVIEW'); //destination sheet
  var data = [];
  var j =[];
  //Condition check in G:G; If true copy the same row to data array
  for (i=0; i<testvalue.length;i++) {
    if ( testvalue[i] == 'Screened - Ready for Review') {
      data.push.apply(data,sheet.getRange(i+1,1,1,25).getValues());
      //Copy matched ROW numbers to j
      j.push(i);
    }
  }
  //Copy data array to destination sheet
  csh.getRange(csh.getLastRow()+1,1,data.length,data[0].length).setValues(data);
  //Delete matched rows in the source sheet
  for (i=0;i<j.length;i++){
    var k = j[i]+1;
    sheet.deleteRow(k);
    //Alter j to account for deleted rows
    if (!(i == j.length-1)) {
      j[i+1] = j[i+1]-i-1;
    }
  }
}

标签： validationgoogle-apps-scriptgoogle-sheets

Answer 1

尝试这个

除了在更简单的情况下，我还没有实际测试过

function screened() { 
  var ss=SpreadsheetApp.getActive();
  var sheet=ss.getSheetByName('PENDING-IP'); 
  var testrange=sheet.getRange(1,7,sheet.getLastRow(),1); 
  var testvalue=testrange.getValues();
  var valids=testrange.getDataValidations();
  var csh = ss.getSheetByName('SCREENED - READY FOR REVIEW'); //destination sheet
  var data = [];
  var valid = [];
  var j =[];
  var d=0;
  for (var i=0;i<testvalue.length;i++) {
    if (testvalue[i][0] == 'Screened - Ready for Review') {
      data.push(sheet.getRange(i+1-d,1,1,25).getValues());//I am not sure but I could see having to put [0] at the end here
      valid.push(sheet.getRange(i+1-d,1,1,sh.getLastColumn()).getDataValidations());//I am not sure but I could see having to put [0] at the end here
      sheet.deleteRow(i+1-d++);//this should delete matching rows
    }
  }
  csh.getRange(csh.getLastRow()+1,1,data.length,data[0].length).setValues(data);
  csh.getRange(csh.getLastRow()+1,1,data.length,data[0].length).setValues(valid);

}

我发现您可以像对待数据一样对待验证，将 getValues() 替换为 getDataValidations() 并将 setValues() 替换为 setValidations();