CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Tom Sawkins
Tom Sawkins

Reputation: 361

Modify Code - Delete a row of data referenced from a cell. Google Apps Script / Google Sheets

I would like to modify the below code, so that instead of it being fixed inside the code - it would delete a row based on a certain cell. So if H2 had "expected" it would look at the datarange and delete any row that contained expected. then if you change H2 to "completed" it would then run the script and delete all rows that contain completed etc etc. I will run it from a custom menu.

 function DeleteText() {
 // This Code will delete any "Donation Sub-Task" in Column A.
 var sheet = SpreadsheetApp.getActive();
 sheet.setActiveSheet(sheet.getSheetByName('JIRA JQL QUERY - READ ONLY'), true);
 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[0] == 'expected') {
 sheet.deleteRow((parseInt(i)+1) - rowsDeleted);
 rowsDeleted++;
     }
   }
 };

As Always Thanks in Advance

Upvotes: 0

Views: 125

Answers (1)

ross
ross

Reputation: 2774

Requirement:

Script to delete rows depending on value in cell "H2".

Script:

function findAndDeleteRows() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('JIRA JQL QUERY - READ ONLY');
  var range = sheet.getDataRange();
  var value = sheet.getRange('H2').getValue();
  var find = range.createTextFinder(value).findAll().reverse();
  if (find.length > 0) {
    for (i = 0; i < find.length; i++) {
      sheet.deleteRow(find[i].getRow());
    }
  }
}

Explanation:

We can use the TextFinder to achieve this pretty easily, it returns all of the rows that have your specific text. I use Array.reverse() so that the rows are deleted from the bottom upwards, otherwise you'll get rows deleted that you wanted to keep. Once we've got all of the rows we need, we can loop through them and use .deleteRow() to get rid of them.

References:

Upvotes: 2

Related Questions