Google Sheets – How to Delete Blank Rows from Multiple Files

google sheetsgoogle-apps-script

I created a script to delete blank rows from many spreadsheet files.

enter image description here

The script I created like this (Version 1) :

function delRow(id, name, startBlankRow, countBlankRow){
  SpreadsheetApp.openById(id).getSheetByName(name).deleteRows(startBlankRow, countBlankRow);
}

function bulkDelRow(){
  //try {
  var dataValue = [];
   dataValue = SpreadsheetApp.getActive()
  .getRange('List!A2:F')
  .getValues()
  .reduce(function(p,c){
    if (c[1] !== '' && c[2] !== '' && c[4] !== '' && c[5] !== '' || c[5] !== 0)
      //p.push(SpreadsheetApp.openById(c[1]).getSheetByName(c[2]).deleteRows(c[4],c[5]));
      p.push(!!delRow(c[1], c[2], c[4], c[5]));
    return p;
  }, []);
  //} catch (e) {
  //  Logger.log(e.toString());
  //}
  //Logger.log(spreadsheetID);
}

  • If all files have empty rows,

    the script to delete empty rows will succeed.

  • But if there is a file there is no row that is empty

    then an error.

    Those rows are out of bounds

Updated,
I try to use another script by using the getMaxRows function,

function deleteBlankRow() {
  var dataValue = [];
  dataValue = SpreadsheetApp.getActiveSpreadsheet()
  .getSheetByName('List')
  .getRange('A2:C')
  .getValues()
  .reduce(function (p, c) {
    var ssID = SpreadsheetApp.openById(c[1]);
    var sheet = ssID.getSheetByName(c[2]);
    var maxRow = sheet.getMaxRows();
    var startBlankRow = sheet.getRange(1, 3).getDataRegion(SpreadsheetApp.Dimension.ROWS).getLastRow() + 1;
    var countBlankRow = maxRow - startBlankRow;
    if (countBlankRow !== 0 && c[0] !== '' &&  c[1] !== '' && c[2] !== '') {

        p.push(ssID.getSheetByName(sheet).deleteRows(startBlankRow, countBlankRow));
               return p;
      } else {
        Logger.log('no blank row');
            }

}, []);

}

but after I run the result it still has an error.

TypeError: Cannot call method "deleteRows" of null. (line 15, file
"deleletBlankRow")

Best Answer

Add a conditional statement to check if the sheet rows are greater than the last row, if they are proceed otherwise do nothing (or something different to delete rows)

There are several ways to do this

  1. If your sheet (first image) is already reporte the number of black rows, if there aren't blank rows, just skip that file.
  2. Use getMaxRows() and getLastRow() to the the number of rows on sheet and the last row of the data range, if they are the same, the skip that file.

NOTE: As there isn't clear description of what the script does and the variable used I'm not sure how to make a simple fix to the script.

Related Topic