Script to search for value in whole sheet and delete based on it

Question

I wanted to make a script that searches 9 specified sheets in the whole spreadsheet for the value of four cells next to each other, and when it finds it, it deletes the value from only the last cell it found.

Example:

Input part:

data1 | data2 | data3 | data4 |
-------------------------------
 ABC  |  123  |  XYZ  |  MNO  |
-------------------------------
 [Search and destroy button]

The four data values would be searched in the sheets that look like this where is should be found and deleted:

       A      B      C       D     E    F       G       H      I    
1  |data1 | data2 |data3 | data4 |   |data1 | data2 |data3 | data4 |
2  |------|-------|------|-------|---|------|-------|------|-------|
3  | gfd  | nbv   | hgf  | hjg   |   | sdg  | uzt   | saf  | hdd   |
4  |------|-------|------|-------|---|------|-------|------|-------|
5  | ztr  | mvb   | ABC  | jgf   |   | uzt  | XYZ   | XYZ  | ABC   |
6  |------|-------|------|-------|---|------|-------|------|-------|
7  | ioj  | lkj   | gfd  | jgf   |   | ABC  | 123   | XYZ  | MNO   |
8  |------|-------|------|-------|---|------|-------|------|-------|
9  | mnb  | ztd   | jgf  | gfd   |   | nfs  | tez   | hfd  | xyv   |
10 |------|-------|------|-------|---|------|-------|------|-------|

In this case it would need to find the data thats from F7 to I7 and delete the value from I7.

Is this possible? Its a little more advanced then what im capable of figuring out unfortunately.

Edit: This is what i was able to come up with that works as i wanted it to. I have to specify the Sheet names, but its not a problem i can get that from the data anyway.

function SaF() {


var sh = SpreadsheetApp.getActiveSpreadsheet();
var ss = sh.getActiveSheet();
var cell = ss.getRange('A1:D1');
var value = cell.getValues();
var shname = ss.getRange('Q9').getValue();                        

var sheets = sh.getSheetByName(shname)

  var activeR = cell.getRow()-1;
  var activeC = cell.getColumn()-1;
  var data = sheets.getRange("A1:BS87").getValues()
  var step = 0

  for(var r=activeR;r<data.length;++r){
    for(var c=activeC;c<data[0].length;++c){
      step++
      Logger.log(step+' -- '+value[0]+'  =  '+data[r][c]);
      if(data[r][c]==''||step==1){ continue };
      if(value[0][0]==data[r][c] && value[0][1]==data[r][c+1] && value[0][2]==data[r][c+2] && value[0][3]==data[r][c+3]){
         sheets.getRange(r+1,c+4).clearContent();
        return;
      }        
    }
  }
 }

Its probably not very clean, and could be made better like:

I cant specify a source thats not from A1, i suspect this is cause of the way it looks up rows and columns, but dont have the knowhow to change it.

You can use an inner loop an outer loop in the Outerloop you can iterate through all of the sheets and access them by putting all of their names in an array and using getSheetsByName() and in the inner loop you can access the same range and iterate through it row by row getting data and deciding what to delete. Try to use getValues() rather than getValue() as much as possible — Cooper
Can it be the case that ABC | 123 | XYZ | MNO are located in other cells say A3:D3 or in the mentioned cells only? — user13338210
@Madhurjya Yes it could be anywhere, also the actual sheets that the search would look at would be multiple columns of 4 not just two. — Veress Attila
If it could anywhere then you will have search everywhere.? You could possibly use textFinder — Cooper

Cooper Cooper · Accepted Answer · 2020-05-19T18:49:56

Try this:

The parameter keyA is a flat array equal to the values of the adjacent cells that you are searching for. I assume adjacent cells are in same row.

function searchAndDestroy(keyA) {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getActiveSheet();
  const rg=sh.getDataRange();
  const vA=rg.getValues();
  for(var i=0;i<vA.length;i++) {
    for(var j=0;j<vA[i].length-keyA.length+1;j++) {
      if(vA[i][j]==keyA[0] && vA[i][j+1]==keyA[1] && vA[i][j+2]==keyA[2] && vA[i][j+3]==keyA[33]) {
        sh.getRange(i+1,j+4).setValue('');
      }
    }
  }
}

Script to search for value in whole sheet and delete based on it

1 Answers