CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Robert Kiss
Robert Kiss

Reputation: 13

How can I modify a script to delete the row in base of the first and second column?

As it is the script only looks at the given value on the second column, however, I would like to add another requirement where it looks at the first column as well.

function deleteRowsToday() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName('Staffing Data 0');
  var t = ss.getSheetByName('Data Collector');
  var r = s.getRange(1,2,s.getLastRow());
  var v = r.getValues();
  var x = t.getRange(2,2).getValue();
  for(var i=v.length-1;i>=0;i--)
    if(v[0,i]== x)
      s.deleteRow(i+1);

I would like to add the value y as well which is another criteria I'm giving in base to delete that row. And it should be deleting the row only if both x & y are present.

I tried modifying the script, but it still deletes the row only if x or y are present.

function deleteRowsToday() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName('Staffing Data 0');
  var t = ss.getSheetByName('Data Collector');
  var r = s.getRange(1,2,s.getLastRow());
  var v = r.getValues();
  var x = t.getRange(2,2).getValue();
  var y = t.getRange(2,1).getValue(); 
  for(var i=v.length-1;i>=0;i--)
    if(v[0,i]== x && y)
      s.deleteRow(i+1);

In the Sheet this is the "Data Collector" tab

X should be taking value B2 and y value A2

Then in the tab "Staffing Data 0" it should find the value x on the second column and y on the first column, and if both match the script should delte the row.

Upvotes: 1

Views: 81

Answers (1)

Marios
Marios

Reputation: 27350

Explanation:

  • Replace if(v[0,i]== x && y) with if(v[i][0] == y && v[i][1] == x)

  • Replace s.getRange(1,2,s.getLastRow()) with s.getRange(1,1,s.getLastRow(),s.getLastColumn()).getValues();

Solution:

 function deleteRowsToday() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const s = ss.getSheetByName('Staffing Data 0');
  const t = ss.getSheetByName('Data Collector');
  const v = s.getRange(1,1,s.getLastRow(),s.getLastColumn()).getValues(); // <- new code
  const x = t.getRange(2,2).getValue();
  const y = t.getRange(2,1).getValue(); 
   
      for(let i=v.length-1;i>=0;i--){
    if(v[i][0] == y && v[i][1] == x) // <- new code
      s.deleteRow(i+1);
    }    
 }

Upvotes: 1

Related Questions