Google Apps Script for Removing Rows Containing Part of a Keyword

Question

I want to delete the rows on Google Sheets that contain employees, tests, irrelevant submissions, and duplicate entries. My code needs to be standalone so that it can be used across my workplace.

Specifically, I want to:

• Remove any rows that contain an email address belonging to a certain organization (ex: any email address that ends in @domainname.com). I've been using a piece of code to delete rows containing three specific email addresses belonging to my coworkers, but I was hoping to find a way to delete all employees in one sweep without coding in each individual email. Here's the code I've been using:

  function delVtlEm() {
      var sheet = SpreadsheetApp.getActiveSheet(); 
      var rows = sheet.getDataRange();
      var numRows = rows.getNumRows(); 
      var values = rows.getValues(); 
      var rowsDeleted = 0;
  
      for (var i = 0; i <= numRows - 1; i++) {
          var row = values[i];
          if (row[1] == 'isaac@domainname.com' || 
              row[1] == 'danni@domainname.com' || 
              row[1] == 'georgia@domainname.com') {
  
              sheet.deleteRow((parseInt(i) + 1) - rowsDeleted); 
              rowsDeleted++;
          }
      }
  }
  

• Remove any rows that contain the word "login" from a comment section where "login" might be only part of the copy in that column. For example, someone might fill out a Contact Us form and ask in the comment section for help with their login info - but this isn't a qualified lead for my purposes. Their message may be "Hey, can you help me with my login?" or some other similar phrasing, which is why I want to delete any row containing "login" in any capacity.

Please let me know if you have any ideas or suggested code!

Answer 1

I have implemented the following smartDelete() function based on your code.

This function allows you to achieve the following,

• Identify any number of domains (in badDomains array) to delete its corresponding rows.
• Identify any number of words (in badWords array) to delete its corresponding rows.
• Both of the two search criteria above are case-insensitive; you can change that by changing the regular expression modifier (stored in regExpModifiers) to "" or Null.
• Actions above can be taken on three different columns (stored in fnameColumnNumber, emailColumnNumber and companyColumnNumber)

Let me know if you face any issues or have any feedback.

function smartDelete() {
  // smartDelete settings goes here, 
  var badDomains = ["vtldesign\\.com", "parterreflooring\\.com"];
  var badWords = ["Vital", "Parterre", "test"];

  var fnameColumnNumber = 0;
  var emailColumnNumber = 1;
  var companyColumnNumber = 3;

  var regExpModifiers = "i";

  // Gain access data in the sheet
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();
  var rowsDeleted = 0;
  var deleteAction = false;

  // delete procedure
  for (var i = 0; i <= numRows - 1; i++) {
      var row = values[i];
      deleteAction = false;

      // check bad words
      for (var j = 0; j <= badWords.length - 1; j++) {
          var myPattern = new RegExp(badWords[j], regExpModifiers);
          var status = row[fnameColumnNumber].toString().match(myPattern);
          if (status) {
              // match found, mark this row for delete
              deleteAction = true;
              break;
          };
      };

      // check bad domains
      for (var j = 0; j <= badDomains.length - 1; j++) {
          var myPattern = new RegExp(badDomains[j], regExpModifiers);
          var status = row[emailColumnNumber].toString().match(myPattern);
          if (status) {
              // match found, mark this row for delete
              deleteAction = true;
              break;
          };
      };

      // check bad words
      for (var j = 0; j <= badWords.length - 1; j++) {
          var myPattern = new RegExp(badWords[j], regExpModifiers);
          var status = row[companyColumnNumber].toString().match(myPattern);
          Logger.log(status)
          if (status) {
              // match found, mark this row for delete
              deleteAction = true;
              break;
          };
      };
      // execute delete.
      if (deleteAction) {
            sheet.deleteRow((parseInt(i) + 1) - rowsDeleted);
            rowsDeleted++;
      };
  };
}