CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Bossch
Bossch

Reputation: 1

Removing empty rows from spreadsheet

I have a spreadsheet where I want to delete the content of the rows where a checkbox is marked as TRUE. If I delete these rows, they stay empty in between the other ones. Now I want to move all rows in the same order up without empty rows in between.

I tried with a sort because of the ease, but this shuffles the remaining data. 

function cleanRange() {
var sheet = SpreadsheetApp.getActive().getSheetByName('Open');
var data = sheet.getRange('E:E').getDisplayValues();
var range = [];
data.forEach(function(e, i){
  if (e[0] == "TRUE") range.push(("C" + (i + 1)),("D" + (i + 1)),("E" + (i + 1)) );
});
  sheet.getRangeList(range).clearContent();
  sheet.sort(3, false);}

Upvotes: 0

Views: 2061

Answers (1)

ross
ross

Reputation: 2774

Goal:

Script to delete rows where checkbox is set to 'TRUE'.

Code:

function deleteEmptyRows() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('blank');
  var data = sheet.getRange('E:E').getDisplayValues();
  for (i = data.length - 1; i >= 2; i--) {
    if (data[i][0] === 'TRUE') {
      sheet.deleteRow(i + 1);
    }
  }
}

Explanation:

Currently your script is clearing the contents of each row, then you're trying to sort the columns. This is causing your data to be rearranged due to the sorting step.

Instead you could just delete the row in question and cut out the sort step completely.

I've added a for statement to scan the array from bottom to top (if you do it top to bottom you'll delete valid data), then an if statement to check if the value is true. When the if statement is met, the corresponding row is deleted.

Reference:

Upvotes: 2

Related Questions