Code to delete unnecessary items not working correctly-CodePudding

I am working on a sheet that creates 4 separate lists of what's needed to be prepared for catering orders. The below code is supposed to create a duplicate of my Temp tab and then use the duplicate tab to trim out the unnecessary information.

I've got most of it working correctly, but when I press the macro button on the Order Input tab (paper and pencil), it just deletes every possible item on the duplicate tab instead of any item that has a 0 for quantity needed.

If anyone is willing to take a look at both the code and the sheet, I'd appreciate it.

function print() {
  var source = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = source.getSheetByName('Temp');

  sheet.copyTo(source).setName('Gathering')
  activateSheetByName('Gathering') //Activates the created sheet
  clearNotOrdered() //deletes any item that wasn't ordered
  
  Browser.msgBox('Ready to Print!') //just for kicks and giggles
}

function clearNotOrdered(){
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  
  //var limit = ss.getLastRow(); //number of rows in the sheet
  //var current = ss.getCurrentCell().getValues();

  ss.getRange('a7').activate();
  for(var i = 7;i <= 38;i  ){
    //loop for each value to be inserted in each row of the target sheet
    var prep = ss.getRange(i,1).getValue();
    if(prep==0){
    delAdjacentShiftUp() }
    else i 1}

  ss.getRange('d7').activate();
  for(var i = 7;i <= 38;i  ){
    var kitchen = ss.getRange(i,4).getValue();
    if(kitchen==0){
    delAdjacentShiftUp() }
    else i 1}

  ss.getRange('g7').activate();
  for(var i = 7;i <= 21;i  ){
    var breader = ss.getRange(i,7).getValue();
    if(breader==0){
    delAdjacentShiftUp() }
    else i 1}

  ss.getRange('j7').activate();
  for(var i = 7;i <= 55; i   ){
    var gather = ss.getRange(i,10).getValue();
    if(gather==0){
    delAdjacentShiftUp() }
    else i 1}
}

function activateSheetByName(sheetName) {
  var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  sheet.activate();
}

function delAdjacentShiftUp() {
  SpreadsheetApp.getActiveSheet().getActiveCell().offset(0,0,1,2).deleteCells(SpreadsheetApp.Dimension.ROWS);
}

CodePudding user response：

Use Range.getValues(), Array.filter(), Range.clearContent(), Range.offset() and Range.setValues(), like this:

function print() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Temp');
  const newSheet = sheet.copyTo(ss).setName('Gathering');
  const ranges = newSheet.getRangeList(['A7:B', 'D7:E', 'G7:H', 'J7:K',]).getRanges();
  clearNotOrdered(ranges);
  ss.toast('Ready to Print!');
}


function clearNotOrdered(ranges) {
  ranges.forEach(range => {
    const newValues = range.getValues()
      .filter(row => row[0] !== 0);
    range.clearContent();
    range.offset(0, 0, newValues.length, newValues[0].length)
      .setValues(newValues);
  });
}