CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-api
Will H.
Will H.

Reputation: 15

deleteRow based off cell edit on another sheet of same spreadsheet

WHAT I HAVE

One google spreadsheet named "Script Test" with two sheets named "delete" and "non delete".

WHAT I NEED

If a row in Col B on "non delete" is changed to 'DELETE' via the drop down menu, the row with the same Buy Number on "delete" will be deleted.

WHAT I HAVE TRIED

What has worked = By researching on stack I found an onEdit function that deletes a row on "delete" based on if a cell has a specific value. In this case, that value is 'DELETE'. The problem with this is, I can only get it to work if that cell is on the sheet "delete" rather than the sheet "non delete". If I'm working off of "non delete" and need to go back to "delete" to delete a row of information, I can just right click on the row number and manually delete it. So, this script isn't necessarily saving me time.
This script is as follows:

    function onEdit(e) {
  try {
    var ss = e.source; 
    var s = ss.getActiveSheet();

    if (s.getName() == 'delete' && 
        e.range.columnStart == 1 && e.range.columnEnd == 1 &&  // only look at edits happening in col A which is 1
        e.range.rowStart == e.range.rowEnd ) {  // only look at single row edits which will equal a single cell
      checkCellValue(e); 
    }
  } catch (error) { Logger.log(error); }
};

function checkCellValue(e) {
  if (e.value == 'DELETE') { 
    e.source.getActiveSheet().deleteRow(e.range.rowStart);
  }
}



What has not worked = I fiddled with the script a bit to have it read Col F on "delete" and in Col F I have an Index Match of Col B in "non delete". However, this does not delete the row on "delete" when Col F changes to 'DELETE'. Now I'm not 100% on this but I can pretty much deduce that this is happening because Col F isn't being "edited", rather the formula inside of it is "updating". I've also tried fiddling with other scripts that I found on stack but none seem to have gotten me as close as the script above.

THINGS TO THINK ABOUT

First of all, thanks for any help you guys can give me. Just before posting this question, I came across a filter function that I think may be a direction to head in if I'm right about the Index Match. I found one function that hides rows based on a filter but I would need the row to be deleted so I'm assuming that is as simple as switching hideRows with deleteRows. I've tried adding screenshots of what I need done but I don't have enough reputation. I can and will add a link to a copy of the spreadsheet if that helps. Once again, thanks for any tips or guidance.
Copy of Script Test

Upvotes: 1

Views: 392

Answers (2)

Will H.
Will H.

Reputation: 15

After testing out the filter function for a couple minutes, I've pretty much got it to do what I needed. Thanks anyways!

Upvotes: 0

Alan Wells
Alan Wells

Reputation: 31310

Use the getSheetByName() method to get the delete sheet.

function checkCellValue(argRowToDelete) {
  if (e.value == 'DELETE') {
    var toDeltSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("delete");
    toDeltSheet.deleteRow(argRowToDelete);
  }
}

If you want a separate function just for deleting the row, maybe do the check for the 'DELETE' text in the main function, and pass the row number to the delete function:

I've tested the following code, and it works. When the drop down list is used to select 'DELETE' in the 'non delete' sheet, it deletes the corresponding row in the 'delete' sheet.

I made multiple changes to the code. Even though this code deletes a row in a sheet different from where the edit is taking place, there is still a potential problem. Once a row in the 'delete' sheet is deleted, the rows will shift. If you start deleting rows at the top or middle, then every row below the deleted row is no longer synced with the rows in the 'delete' sheet.

So, this answers your question, but now you have yet another problem.

function onEdit(e) {
  try {
    var ss = e.source;
    var s = ss.getActiveSheet();
    var colStart = e.range.columnStart;
    var colEnd = e.range.columnEnd;
    Logger.log('colStart: ' + colStart);
    Logger.log('colEnd: ' + colEnd);

    var thisRow = e.range.getRow();

    Logger.log('s: ' + s.getName());
    //Avoid looking at multi column edits.  If column start and column end is same column,
    //then not a multi column edit
    var editedFromNonDelete = (s.getName() === 'non delete');
    Logger.log('editedFromNonDelete: ' + editedFromNonDelete);

    var editedFromColB = (colEnd === 2)  && (colStart === colEnd);

    // only look at edits happening in col B
    if (editedFromNonDelete && editedFromColB) {
      Logger.log('e.value: ' + e.value);
      if (e.value === 'DELETE') {
        fncDeleteRow(thisRow);
      };
    }
  } catch (error) { 
    Logger.log(error); 
  }
};

function fncDeleteRow(argRowToDelete) {
  var toDeltSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("delete");
  toDeltSheet.deleteRow(argRowToDelete);
};

Upvotes: 0

Related Questions