CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Rob Popolizio
Rob Popolizio

Reputation: 9

Google Sheets – Remove Row when cell in column A contains a string

Scripts newbie here. I have a list of URLs in column A

I want tot create a button that helps me delete a row whenever the cell in column A contains one of a list of keywords. I was trying the code below to delete all rows where A contains "blogspot".

It is still not working. I get this error: "TypeError: thisVal.indexOf is not a function"

However, I'm not even sure I can add multiple strings in addition to "blogspot". For example, I would like to delete all rows containing blogspot, wordpress and wix.

    function cleanRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName('Master');

  var colA_Vals = sh.getRange(1, 1, sh.getLastRow(), 1).getValues();

  var i = 0,
      j = 0,
      thisVal,
      chkVal,
      arrayValsToChk = ['blogspot'];

  for (i=colA_Vals.length;i>0;i-=1) {
    thisVal = colA_Vals[i-1][0];
    //Logger.log('i: ' + i);

    for (j=0;j<arrayValsToChk.length;j+=1) {
      chkVal = arrayValsToChk[j].toLowerCase() //Change to all lower case;

      if (thisVal.indexOf(chkVal) !== -1) {
        ss.deleteRow(i);
      };
    };
  };
};

Upvotes: 0

Views: 631

Answers (1)

Marios
Marios

Reputation: 27350

Explanation:

Your goal is to delete the rows for which the cells in column A contain a string in the arrayValsToChk list.

  • You need to get the position of the rows which satisfy the condition, namely string in cell A is included in arrayValsToChk. You can use map and filter to get the indexes.

  • Delete the rows backwards as it is also explained why in this thread.

Solution:

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh = ss.getSheetByName("Master");
  const colA_Vals = sh.getRange(1, 1, sh.getLastRow(), 1).getValues();
  const arrayValsToChk = ['blogspot','wordpress','wix']; // list of strings
  const indexToDlt= colA_Vals.flat()
                    .map((v,i)=>arrayValsToChk.includes(v.toString().toLowerCase())?i:'')
                    .filter(v=>v!='');
  for (let i = indexToDlt.length - 1; i>=0; i--) {
   sh.deleteRow(indexToDlt[i]+1); 
  }
}

Upvotes: 1

Related Questions