Delete Row If Two Corresponding Cells Match

Question

In my google sheet file, sheet2 is being used as a blacklist wherein colA = a name, colB = date they were blacklisted, colC = date they will be removed from the blacklist (3 months after value in colB). Sheet1 records the responses of a signup form.

I would like a script that will automatically remove the names after their colC date. Perhaps a script that can delete an entire row if two cells match. An example would be if B2 and C2 are equal then delete row 2. This would have to apply to every row.

Any help would be greatly appreciated. Thank you very much.

KitzyKitt KitzyKitt · Accepted Answer · 2019-05-15T22:48:40

It wasn't immediately clear which sheet you wanted to remove the name from. My answer assumes that you are removing the name from the blacklist sheet, sheet2.

var ss = SpreadsheetApp.getActiveSpreadsheet ();
var sheet2 = ss.getSheetByName ("sheet2");
var values = sheet2.getDataRange ().getValues ();

var day = 24*3600*1000;  
var today = parseInt ((new Date ().setHours (0,0,0,0))/day); 
var ssdate; // Date to check, spreadsheet date

// Start at the bottom and move up the file, since all rows will shit up when deleted
for (var i = values.length; i >= 0; i--)
{
    try
    {
      ssdate = values[i][2].getTime () / day; // 2 is for the C column (0 is for the A column)
    }
    catch (e) {ssdate = null;}

    // Testing that the date is today or later to remove from file
    if (ssdate && Math.floor (ssdate) >= today)
    {
      sheet2.deleteRow (i + 1); // Removes name from blacklist.
                                // i + 1, because it is the row number, which starts at 1
                                // where arrays start index at 0.
    }
}

Delete Row If Two Corresponding Cells Match

1 Answers