Moving Row to another sheet and clear content/ ArrayFormula-CodePudding

Short story: How do I modify the script below to clear contents in multiple cells in the row? For example Rows 7,13,24,25,34

Long story (in case there is a better solution):

I have been using this script to move rows between sheets based on a value without carrying over the formatting so the next sheet has its own and the conditional formatting isn't stacking up.

  function onEditComplete(e) {
 //e.source.toast('Entry');
 //Logger.log(JSON.stringify(e));
 const sh=e.range.getSheet();
 if(sh.getName()=="Sheet1a" && e.range.columnStart==5 && e.value=="Completed") {
  //e.source.toast('Conditional');
 var tsh=e.source.getSheetByName("Sheet2a");
 tsh.getRange(tsh.getLastRow() 1,1,1,sh.getLastColumn()).setValues(sh.getRange(e.range.rowStart,1,1,sh.getLastColumn()). 
getValues());
sh.deleteRow(e.range.rowStart);}
  }

The problem I am encountering now is I am using Formulas. I have 4 sheets. Sheet1a and Sheet1b have vlookup formulas between them. Sheet2a and Sheet2b will also have the same Vlookup formula between them.

=ArrayFormula(IFERROR(VLOOKUP(PARTS!$B1:$B,{PARTS!$B1:$B,PARTS!$AF1:$BD&","&PARTS!$AJ1:$AJ&","&PARTS!$AN1:$AN&","&PARTS!$AR1:$AR&","&PARTS!$AV1:$AV&","&PARTS!$AR1:$AR&","&PARTS!$AZ1:$AZ},2,0)))

Since it's an ArrayFormula, as long as Sheet2a and Sheet2b are set it should only be a matter of clearing the content in the specific cells before it moves over since it transfers as text value and breaks the formula in Sheet2a and Sheet2b.

CodePudding user response：

Adding the ability to clear some cells in rows 7,13,24,25,34

function onEdit(e) {
  e.source.toast('Entry');
  Logger.log(JSON.stringify(e));
  const sh = e.range.getSheet();
  if (sh.getName() == "Sheet0" && e.range.columnStart == 5 && e.value == "Completed") {
    e.source.toast('Conditional');
    let cols = [7, 13, 24, 25, 34];
    let row = sh.getRange(e.range.rowStart, 1, 1, sh.getLastColumn()).getValues().flat().map((e, i) => {
      if (~cols.indexOf(i   1)) {
        return null;
      } else {
        return e;
      }
    })
    var tsh = e.source.getSheetByName("Sheet1");
    tsh.getRange(tsh.getLastRow()   1, 1, 1, sh.getLastColumn()).setValues([row]);
    sh.deleteRow(e.range.rowStart);
  }
}