Remove specific data regardless of letter case from the row

Question

Remove specific data regardless of letter case from the row

I've developed a Google Apps Script that scans specific columns for certain keywords and deletes rows that contain them:

function filterRows() { 
  var sheet = SpreadsheetApp.getActiveSheet(); 
  var rows = sheet.getDataRange(); 
  var values = rows.getValues(); 
  var rowsDeleted = 0; 
  var searchWords = ['furniture takeover','can be taken over','caravan','trailer'] 

  for (var i = values.length - 1; i >= 0; i--) {  
    var row = values[i]; 
    for (var j = 0; j < searchWords.length; j++) {    
      if (row['21','17'].indexOf(searchWords[j]) > -1) {         
        sheet.deleteRow(i+1);     
        rowsDeleted++;   
        break;         
      }
    } 
  } 
};

Is there a way to make the keyword check not case-sensitive? For example, if I include "apartment rental", it should remove all matches of: APARTMENT RENTAL, apartment rental, Apartment Rental?

javascript arrays google-apps-script google-sheets

Answer 1

Answer №1

Explanation:

To achieve the conversion of every element in the row array to lowercase, utilize the map() function along with toLowerCase(). This approach ensures that toLowerCase is applied to each element within the row array. Implement this modification as shown below:
```
if (row.map(r=>r.toLowerCase()).indexOf(arrayOfWords[j]) > -1)
```
The syntax row['21','17'] does not conform to valid JavaScript object standards since you cannot slice an array in this manner. If your intention is to specifically target columns 21 and 17, then adjust the if statement as follows:
```
if ([row[21],row[17]].map(r=>r.toLowerCase()).indexOf(arrayOfWords[j]) > -1)
```
Additionally, take note that row[21] corresponds to column V and row[17] corresponds to column
```
R</code within the sheet. Remember that JavaScript indexing starts at <code>0
```
, where 0 represents column A, 1 represents column B, and so on.

Solution:

function sort() { 
  var sheet = SpreadsheetApp.getActiveSheet(); 
  var rows = sheet.getDataRange(); 
  var values = rows.getValues(); 
  var rowsDeleted = 0; 
  var arrayOfWords = ['möbelübernahme','können übernommen werden','caravan','wohnwagen'] 

  for (var i = values.length - 1; i >= 0; i--) {  
    var row = values[i]; 
    for (var j = 0; j < arrayOfWords.length; j++) {    
      if (row.map(r=>r.toLowerCase()).indexOf(arrayOfWords[j]) > -1) {         
        sheet.deleteRow(i+1);     
        rowsDeleted++;   
        break;         
      }
    } 
  } 
};

Answer 2

Explanation:

To achieve the conversion of every element in the row array to lowercase, utilize the map() function along with toLowerCase(). This approach ensures that toLowerCase is applied to each element within the row array. Implement this modification as shown below:
```
if (row.map(r=>r.toLowerCase()).indexOf(arrayOfWords[j]) > -1)
```
The syntax row['21','17'] does not conform to valid JavaScript object standards since you cannot slice an array in this manner. If your intention is to specifically target columns 21 and 17, then adjust the if statement as follows:
```
if ([row[21],row[17]].map(r=>r.toLowerCase()).indexOf(arrayOfWords[j]) > -1)
```
Additionally, take note that row[21] corresponds to column V and row[17] corresponds to column
```
R</code within the sheet. Remember that JavaScript indexing starts at <code>0
```
, where 0 represents column A, 1 represents column B, and so on.

Solution:

function sort() { 
  var sheet = SpreadsheetApp.getActiveSheet(); 
  var rows = sheet.getDataRange(); 
  var values = rows.getValues(); 
  var rowsDeleted = 0; 
  var arrayOfWords = ['möbelübernahme','können übernommen werden','caravan','wohnwagen'] 

  for (var i = values.length - 1; i >= 0; i--) {  
    var row = values[i]; 
    for (var j = 0; j < arrayOfWords.length; j++) {    
      if (row.map(r=>r.toLowerCase()).indexOf(arrayOfWords[j]) > -1) {         
        sheet.deleteRow(i+1);     
        rowsDeleted++;   
        break;         
      }
    } 
  } 
};

Answer 3

Answer №2

A different approach to solve the issue

One way to tackle this is by utilizing the toLowerCase() method - for instance, transforming "Mietwohnung" into "mietwohnung", which allows for a seamless comparison.

To implement this change in your script, you can modify it as follows:

for (let i = values.length - 1; i >= 0; i--) {
   var row = values[i];
   var found = false;
   for (let k = 0; k < row.length; k++)
      for (let j = 0; j < arrayOfWords.length; j++)
         if (row[k].toString().toLowerCase().indexOf(arrayOfWords[j]) > -1)
            found = true;
   if (found == true) {
      sheet.deleteRow(i + 1);
      rowsDeleted++;
   }
}

The provided script iterates over all data values within the range and employs the additional loop to check each value against the words in the arrayOfWords array after converting them to lowercase using toLowerCase(). If a match is found, the corresponding row(s) are removed. The variable found acts as a marker for detecting whether the word was located or not.

Resource

JavaScript String.prototype.toLowerCase().

Answer 4

A different approach to solve the issue

One way to tackle this is by utilizing the toLowerCase() method - for instance, transforming "Mietwohnung" into "mietwohnung", which allows for a seamless comparison.

To implement this change in your script, you can modify it as follows:

for (let i = values.length - 1; i >= 0; i--) {
   var row = values[i];
   var found = false;
   for (let k = 0; k < row.length; k++)
      for (let j = 0; j < arrayOfWords.length; j++)
         if (row[k].toString().toLowerCase().indexOf(arrayOfWords[j]) > -1)
            found = true;
   if (found == true) {
      sheet.deleteRow(i + 1);
      rowsDeleted++;
   }
}

The provided script iterates over all data values within the range and employs the additional loop to check each value against the words in the arrayOfWords array after converting them to lowercase using toLowerCase(). If a match is found, the corresponding row(s) are removed. The variable found acts as a marker for detecting whether the word was located or not.

Resource

JavaScript String.prototype.toLowerCase().

Remove specific data regardless of letter case from the row

Answer №1

Explanation:

Solution:

Answer №2

Resource

Similar questions

The JavaScript indexOf method in an unordered list incorrectly returns the index of a specific event

The browser freezes after a short delay following an Ajax request

A guide on leveraging the each() method for looping through a JSON document

Updating ng-model with the values from a property in a collection in AngularJS

Load the values into the dropdown list based on the selection from the previous dropdown menu

No tests were found to run when Karma was executed using the ng test command

How should filtering be properly done on a data array within a Redux reducer function?

Python 3 Selenium struggles to run JavaScript code

While attempting to import modules in Visual Studio Code, an error message appears stating "Unexpected token {"

Retrieving a Promise's value for an HTML Element

Designing a button component with text

The code is functioning properly and executing without issues, yet I am puzzled as to why an error message is appearing in the console stating "Uncaught TypeError: Cannot read properties of null (reading 'style')"

Update $(img).offset() upon clicking and dragging the image

Issue with Axios in my React Application: Unidentified SyntaxError

Getting the text value from a table in JavaScript is a straightforward process. By using

What sorcery does Facebook use to alter the URL without triggering a page reload?

Creating an Array of Objects in JAVA - A Step by Step Guide

Passing data from parent AngularJS directive to child sub-directive

Deleting a precise key and value from a nested JSON array stored in a PostgreSQL column

Display a div using data from a Model in MVC