I have a google sheet for my business that creates a prep list for events.

After tackling an automatic clearing issue for over 25 hours(serious);I got some help from your fantastic community, modified the code to work, see below: (If Fx is empty, clear the cell to the left):

function cgar() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('auto');
  var values = sheet.getRange("F11:F20").getValues();
  var ranges = values.reduce((ar, [f], i) => {
    if (f == "") ar.push("E" + (i + 11));
    return ar;
  }, []);
  sheet.getRangeList(ranges).clearContent();
}

1. Is there a way for me to skip a specific cell, either based on location or value = "Garnish"?

If I change the code to take the whole range of "F5:F24" instead of the individual groups "F11:F20" it clears the header rows of the merged cell E10:F10 & E21:F21.

2.

  var syr1 = ("D6")
  var syr2 = ("D7")

  var syr1isblank = SpreadsheetApp.getActiveSheet().getRange(syr1).isBlank()
  var syr2isblank = SpreadsheetApp.getActiveSheet().getRange(syr2).isBlank()


  if (syr1isblank == true) {
  SpreadsheetApp.getActiveSheet().getRange("C16:D23").moveTo(sheet.getRange(syr1))
    }
    else if (syr2isblank == true && syr1isblank == false ) {
  SpreadsheetApp.getActiveSheet().getRange("C16:D23").moveTo(sheet.getRange(syr2))
  }

I have been using if and else if to move the group ranges that are below the cleared ones to the newly created space above, but it's clunky:

Is there a way that I can also modify this to scan for empty range, I think the process would be like this:

• first empty cell spotted

• Mark the cell to the left to a variable?

• Scan the range f.x. E11:F20 for lastRow after it has been cleared by the function above

• Move the range to the variable marked cell above.

Image of the sheet in question: https://i.stack.imgur.com/ND2DC.png

I would greatly appreciate any assistance, thank you.