2

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] == '[email protected]' || 
            row[1] == '[email protected]' || 
            row[1] == '[email protected]') {

            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!

Improve this question
0

2 Answers 2

Reset to default
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++;
      };
  };
}
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Won't be much of a problem...but,. (in .com)is considered as any character in regex.
you are right, we can change the words in the badDomains array to follow the pattern "vtldesign\\.com" to skip the period and match it, as you said it is not much of a problem :)
@helcode thank you for your help! I have two questions: 1) is this code standalone? I know I forgot to mention it, but it can't be bound to a sheet as it needs to be available across my workplace. 2) how do I update this code to use it in three columns, rather than just the two? I tried to make this adjustment myself, but I'm getting an error. See next comments for the code I've come up with.
@KatieRosenholm this function deletes the row if the criteria met in any of the three-row, you have one criterion for two columns (badWords) and one criterion for one column (badDomains).
@KatieRosenholm - Thanks, please delete the above not necessary comments to allow others to use the answer without going through a lot of troubleshooting. I'll delete this comment in two days.
|
0

You can use indexOf('what to find') to look for a partial string. Also, don't delete rows in the Sheet individually. That is inefficient. Delete elements (rows) from the array, clear the sheet tab, and then set all the new values.

function delVtlEm() {
  var i,row;

  var sheet = SpreadsheetApp.getActiveSheet(); 
  var rows = sheet.getDataRange();

  var numRows = rows.getNumRows(); 
  var values = rows.getValues(); 
  var rowsDeleted = 0; 

  var arrayOfStringsToFind = ["whatToLookFor","whatToLookFor2","whatToLookFor3"];

  for (i = 0; i <= numRows - 1; i++) {
    row = values[i];

    column1Value = row[0];//Get the value of column A for this row
    column2Value = row[1];
    column3Value = row[2];

    if (arrayOfStringsToFind.indexOf('column1Value') !== -1) { 
      values.splice(i,1);//
    }

    if (column2Value.indexOf('@vtldesign.com') !== -1) { 
      values.splice(i,1);//Remove one element in the data array at index i
    }
    if (column3Value.indexOf('whatToLoookFor') !== -1) { 
      values.splice(i,1);//
    }
  }

  sheet.clearContents();//clear the contents fo the sheet
  sheet.getRange(1, 1, values.length, values[0].length);//Set new values

}
Improve this answer

3 Comments

Also, I'm looking to remove rows the meet my criteria in a few different columns. How do I adjust this code to look at columns 0 (First Name), 1 (Email) and 3 (Company Name)?
One issue with a stand alone file, is that there is no "Active Document" available, unless the stand alone file is published as an add-on. So, if there is no active document, then the spreadsheet file ID needs to be passed to the code, so that the code can get the spreadsheet file. As far as checking the other columns, I edited my answer and made some changes to the code. See the new update.
This is incorrect: if (emailValue.indexOf('@vtldesign.com', '@parterreflooring.com') !== -1) You are entering two strings to search for. You can only enter one. If you want to test for multiple strings, you need to either use multiple statements, or maybe have an array of strings to find, then search the array. var arrayOfStringsToFind = ["@vtldesign.com","@parterreflooring.com",etc ]; if (arrayOfStringsToFind.indexOf(emailValue) !== -1) {//do something};

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.