CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Andrew Ramon Medina
Andrew Ramon Medina

Reputation: 109

Google Sheets: How to delete rows if cells in certain are blank?

I currently have a sheet of 13,000 rows. I have to create a script that will delete entire rows only if cells in column C & D are 0 or blank. The script I currently have only applies to cells in columns C. How do I add in column D. Here is my script: Thanks for your insight!

/*** Deletes rows in the active spreadsheet that contain 0 or
* a blank value in column "C". 
* For more information on using the Spreadsheet API, see
* https://developers.google.com/apps-script/service_spreadsheet
*/

function readRows() {
var sheet = SpreadsheetApp.getActiveSheet();
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[2] == 0 || row[2] == '') {
  sheet.deleteRow((parseInt(i)+1) - rowsDeleted);
  rowsDeleted++;
   }
  }
};

 /**
 * Adds a custom menu to the active spreadsheet, containing a single menu item
 * for invoking the readRows() function specified above.
 * The onOpen() function, when defined, is automatically invoked whenever the
 * spreadsheet is opened.
 * For more information on using the Spreadsheet API, see
 * https://developers.google.com/apps-script/service_spreadsheet
 */
 function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
  name : "Remove rows where column C and D is 0 or blank",
  functionName : "readRows"
  }];
 sheet.addMenu("Script Center Menu", entries);
};

Upvotes: 1

Views: 4561

Answers (1)

Dnaaz
Dnaaz

Reputation: 195

Change the 'if' so it also looks in element 3 for blanks and zeroes:

if (row[2] == 0 || row[2] == '' || row[3] == 0 || row[3] == '') {

Here's the Sheet I used to test it, consisting of a few 'normal' rows, then 4 test rows with blanks and zeroes. The change to your script removes all the test rows.

Unless you want it to remove rows where cells in C AND D are missing their values? In that case you'd use the && operator, and wrap the two || expressions in parentheses:

if ((row[2] == 0 || row[2] == '') && (row[3] == 0 || row[3] == '')) {

Here's a revised sheet with additional zero+blank combinations :)

Upvotes: 2

Related Questions