Delete all Rows after last non-NULL Row

Question

I have a large set of data and need to clean it up. Column B is the key column; if there is a null value in column B, then the rest of the row can be deleted.

I sort on column B and have all the null values at the bottom of the sheet. There is data in columns C - BZ, but none in B. I want to automatically identify the last non-null value in B (let's say it is cell B3841), and then delete rows 3842 to the bottom of the sheet. My datasets vary in size each day.

The below code sorts on column B and then deletes all the completely empty rows. I want to also delete the rows that have null in column B but may have data in C or D or any other row.

function cleanData(){

  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('data'), true);


  var columnToSortBy = 1;
  var tableRange = "A:AZ";

  if(1 == 1){   
    var range = spreadsheet.getRange(tableRange);
    range.sort( { column : columnToSortBy, ascending: false } );
  }

  var morecleaning = spreadsheet.getSheets();
  for (var s in morecleaning){
  var sheet = morecleaning[s]
  var maxRows = sheet.getMaxRows(); 
  var lastRow = sheet.getLastRow();
    if (maxRows-lastRow != 0){
      sheet.deleteRows(lastRow+1, maxRows-lastRow);
    }}


};

Answer 1

function cleanData(){

  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('data'), true);


  var columnToSortBy = 1;
  var tableRange = "A:AZ";

  if(1 == 1){   
    var range = spreadsheet.getRange(tableRange);
    range.sort( { column : columnToSortBy, ascending: false } );
  }

  var morecleaning = spreadsheet.getSheets();
  for (var s in morecleaning){
  var sheet = morecleaning[s]
  var maxRows = sheet.getMaxRows(); 
  var lastRow = sheet.getLastRow();
    if (maxRows-lastRow != 0){
      sheet.deleteRows(lastRow+1, maxRows-lastRow);
    }}

  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('data');



  var rg=sh.getRange(1,1,sh.getLastRow(),sh.getLastColumn());
  var vA=rg.getValues();
  var vB=[];
  for(var i=0;i<vA.length;i++) {
    if(vA[i][1]) {
        vB.push(vA[i]);
      }
    }
  sh.clearContents();
  sh.getRange(1,1,vB.length,vB[0].length).setValues(vB);

  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('data'), true);


var allsheets = spreadsheet.getSheets();
  for (var s in allsheets){
  var sheet=allsheets[s]
  var maxRows = sheet.getMaxRows(); 
  var lastRow = sheet.getLastRow(); if (maxRows-lastRow != 0){
      sheet.deleteRows(lastRow+1, maxRows-lastRow);
  }}};

Answer 2

Delete all the rows where B==''

function cleanData(){
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('data');
  var rg=sh.getRange(1,1,sh.getLastRow(),sh.getLastColumn());
  var vA=rg.getValues();
  var d=0;
  for(var i=0;i<vA.length;i++) {
    if(!vA[i][1] && vA[i][1].toString().length==0) {
      sh.deleteRow(i+1-d++);
    }
  }
}

Did you wish to do this for all sheets? If so, let me know.

You could do something like this for all sheets.

function cleanData(ss,sh){
  if(ss && sh) {
    var rg=sh.getRange(1,1,sh.getLastRow(),sh.getLastColumn());
    var vA=rg.getValues();
    var d=0;
    for(var i=0;i<vA.length;i++) {
      if(!vA[i][1] && vA[i][1].toString().length==0) {
        sh.deleteRow(i+1-d++);
      }
    }
  }else{
    Logger.log('Invalid Parameters'):
  }
}

function forAllSheets() {
  var ss=SpreadsheetApp.getActive();
  var shts=ss.getSheets();
  for(var i=0;i<shts.length;i++) {
    cleanData(ss,shts[i]);
  }
}

To speed it up a lot try changing cleanData() to this:

function cleanData(ss,sh){
  if(ss && sh) {
    var rg=sh.getRange(1,1,sh.getLastRow(),sh.getLastColumn());
    var vA=rg.getValues();
    var vB=[];
    for(var i=0;i<vA.length;i++) {
      if(vA[i][1]) {
        vB.push(vA[i]);
      }
    }
    sh.clearContents();
    sh.getRange(1,1,vB.length,vB[0].length).setValues(vB);
  }
}