Create Google Sheets Macro to delete a cell and the cell to the right (and shift up) when the left cell's value is 0

Viewed 29

I'm a relative newbie to using Apps Script, though I'm decent with non-Macro automation in Google sheets. I am not familiar with Javascript, though I had a class on Python back in college. What I am currently aiming to do (but am unable to figure out) is how to write a macro that deletes a cell and the cell to the immediate right if the left cell has a value of 0. (Eg. a7 is 0; both a7 and b7 should be deleted with the cells below getting shifted up). Just to throw another wrench into the mix, I have 4 separate column pairs like this (a&b, d&e, g&h, j&k), where the left value is how much is needed and the right cell is for what item needs that quantity. I've shoe-strung together some others' code (including Cooper, who was nice enough to comment on the original question) and added a couple thoughts, but I think I'm missing something and am not catching it due to my unfamiliarity with Script. Here's the code in question:

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();
  var sheet = ss.getSheets()[0];
  var limit = sheet.getLastRow(); //number of rows in the sheet

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

for(var i = 7;i <= limit;i++){//loop for each value to be inserted in each row of the target sheet

  if(prep==0){
  delAdjacentShiftUp() }

  if(kitchen == 0){
  delAdjacentShiftUp() }

  if(breading==0){
  delAdjacentShiftUp() }

  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);
}

The code isn't currently giving me any error messages, but it's also not removing the data. Do I just need to run 4 separate "For" commands instead of trying to run them all together?

1 Answers

Try this:

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

Demo:

enter image description here

Related