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()
    if (c[1] !== '' && c[2] !== '' && c[4] !== '' && c[5] !== '' || c[5] !== 0)
      p.push(!!delRow(c[1], c[2], c[4], c[5]));
    return p;
  }, []);
  //} catch (e) {
  //  Logger.log(e.toString());
  • 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()
  .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")

  • I apologize for my poor English skill. I couldn't understand what you want to do from your question and script. Can I ask you about to skip files that have no blank rows? And can you provide the sample input and output you want? Of course, please remove your personal information.
    – Tanaike
    Jun 19, 2019 at 2:52
  • I also apologize for my unclear question. I will update the question again Jun 19, 2019 at 6:22
  • I have updated my question, hopefully my explanation is quite clear. Sorry, I'm still not good at speaking English. Jun 19, 2019 at 6:42
  • Thank you for replying and adding the information. Unfortunately, I cannot still understand what you want to do from your question and script. I apologize for my poor English skill. If I could correctly understand, I would like to think of the solution. I deeply apologize I cannot resolve your issue soon.
    – Tanaike
    Jun 19, 2019 at 9:09
  • the title question has updated again.. Jun 19, 2019 at 13:17

1 Answer 1


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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.