google-apps-scriptgoogle-sheetstriggersdelete-row

Delete a row in Sheet2 in Google Spreadsheets when a row in Sheet1 in deleted


When I manually delete certain row in Sheet1 I'd like to be able to automatically delete the next row in Sheet2.

I have a spreadsheet in my workplace where I use some data from Sheet1 in Sheet2 and add some extra values in other columns. The thing is, when I delete a row in the middle in Sheet1, all data gets pushed up - including refered cells in Sheet2. While this is desired outcome, the problem is the values in those extra columns do not go up with the rest of their row, shuffling the data.

The solution I came up to would be deleting corresponding row in Sheet2 when I delete row in Sheet1

I tried writing a simple script:

function deleteRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh1 = ss.getSheetByName("Sheet1");
  var sh2 = ss.getSheetByName("Sheet2");
  var range = sh1.getActiveRange().getRowIndex(); //Number of a row we are deleting manually
  
  if(sh1.deleteRow(range)==true) // I want to check if the row in sheet1 was removed manually, if yes then delete a row in sheet2
  {
  sh2.deleteRow(range+1) // increment since corresponding data is one row lower in Sheet2
  }
}

The problem is when i run the script it automatically deletes both rows, and I want it to wait for me deleting the first one. Basically me deleting the row has to be the trigger for the function to run.


Solution

  • You could insert a column of checkboxes in last column of Sheet1 and use this script

    function onEdit(e) {
      e.source.toast("Entry")
      const sh = e.range.getSheet();
      if(sh.getName() == "Sheet1" && e.range.rowStart > 1 && e.range.columnStart == "put last column number here and remove the quotes" && e.value == "TRUE") {
        sh.deleteRow(e.range.rowStart);
        e.source.getSheetByName("Sheet2").deleteRow(e.range.rowStart)
      }
    }
    

    Sheet1 showing checkboxes:

    enter image description here