Google Sheet script for deleting entire row after a cell in a specific column becomes empty-CodePudding

I am trying to figure out a script with the following process:

If a certain cell in column C becomes empty by removing its value (starting from C4 until the last cell) -> the entire row corresponding to the position of that cell should be deleted (i.e. either row 4,5, etc.) I need this to happen to 6 different sheets. The sheet names are:

-FASHION NL 2023

-FASHION BE 2023

-HD&E BNL 2023

-KIDS & UNDERWEAR BNL 2023

-NEW BUSINESS BNL 2023

-SHOES & SPORTS BNL 2023

Can someone please help? I tried nothing yet. I am new to this.

CodePudding user response：

You probably want to check the "onEdit()" trigger. It will detect changes in the spreadsheet and provide you with an event that describes the change.

From there, you can check whether the change that was made is indeed the removal of the value in a cell that matches your conditions (third column, fourth row or below), and execute actions (like the deletion of the row) in consequence.

A simple script to do that would be as follow:

function onEdit(e) {
  /* When the spreadsheet is edited, check whether 
    - the event was the removal of the value 
    - of a cell in third column
    - and in a row lower than 4 (inclusive)
  */
  if (e.range.getColumn() == 3 && e.range.getRow() >= 4 && (!e.value ||e.value == "")) { // Verify both empty value or the absence of value, for deletions)
    var sheet = e.range.getSheet(); // Sheet in which the change was made
    sheet.deleteRow(e.range.getRow());
  }
}

If you need other conditions or other triggers (e.g. if there are more sheets than the 6 you mentioned and you want to add a check on the sheet's name as well, etc), check out the docs for that event object, that might give you an idea of other capabilities that might be useful. For actions on the sheets, ranges, etc, check the available methods on their respective documentation pages, if you're really new to this.

CodePudding user response：

Deletes row when C4 or greater is blank by user edit only

function onEdit(e) {
  //e.source.toast("Entry");
  const sh = e.range.getSheet();
  //sh.getRange("A1").setValue(JSON.stringify(e));
  const shts = ["Sheet1","Sheet2"];
  const idx = shts.indexOf(sh.getName());
  if(~idx && e.range.columnStart == 3 && e.range.rowStart > 3 && !e.value) {
    //e.source.toast('Gate1')
    sh.deleteRow(e.range.rowStart);
  }
}