Auto-delete function mixed with for loop doesn't quite do its job

Viewed 18

I am working on a code to affect an inventory list. I'm trying to make it auto-remove any item that is not being ordered (thanks to Cooper for his piece of code that deletes the cells), but when I try to run it, it removes one item from the first column and then entirely deletes the other 3 columns of information. The information is paired up with the quantity needed in columns A, D, G, J and the item names in columns in B, E, H, K. The order lists don't start until row 7, and I was able to get the code to acknowledge that much, but I can't get it to only delete the items that aren't needed.

I'm wondering if I'm playing with too many variables and just need to make it one variable (the cell currently active) and just make sure it runs through all four columns that way.

Short version: the macro properly activates where I want it to, it deletes like I want it to, now I just want to make sure it only deletes when it encounters a 0.

Any advise would be appreciated.

Edited for the update I made. Now it also clears out the first column too.

function clearNotOrdered(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  //var limit = sheet.getLastRow(); //number of rows in the sheet

  var prep = sheet.getRange(7,1).getValues(); //Prep list
  var kitchen = sheet.getRange(7,4).getValues(); //Kitchen's list
  var breading = sheet.getRange(7,7).getValues(); //Breader's list
  var gatherer = sheet.getRange(7,10).getValues(); //Catering Person's list

  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A7:a35').activate();
  for(var i = 7;i <= 35;i++)
    //loop for each value to be inserted in each row of the target sheet
    if(prep==0){
      delAdjacentShiftUp() }
    else i+1

  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('d7:d37').activate();
  for(var i = 7;i <= 37;i++)
    if(kitchen==0){
      delAdjacentShiftUp() }

  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('g7:g22').activate();
  for(var i = 7;i <= 22;i++)
    if(breading==0){
      delAdjacentShiftUp() }

  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('j7:j49').activate();
  for(var i = 7;i <= 49;i++)  
    if(gatherer==0){
      delAdjacentShiftUp() }

}

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);
}
0 Answers
Related