Googlesheet adding unwanted rows

Question

I have two sheets (Sheet1 and Mirror) in a single google spreadsheet. I have some columns in the Mirror sheet which are being mirrored from Sheet1 using an array formula (={"Firstname";ARRAYFORMULA(INDIRECT("Sheet1!B2:B"))}).

I have rows being copied automatically from a google form into Sheet1. For some reason, periodically, about 500 empty rows are entered into the Mirror sheet and I have to manually delete them.

I have these googlesheet functions below that trigger on change. I have only deployed the function ReadRows and it works like a charm. Rather than deploying the second function (DeleteBlankRows) which might slow down my spreadsheet, I do like to know how I can figure out what is causing the extra periodic 500 blank rows to appear and stop it or I'd like any professional advice you can give.

Please see the sample scripts below. Thanks in advance.

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

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

soMario soMario · Accepted Answer · 2021-02-23T17:16:22

Updated answer:

The issue is that you need to delete the rows backwards for Sheet1 and then you don't need to use DeleteBlankRows for Mirror anymore. Replace your code with this one:

function readRows() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  var rows = sheet.getDataRange();
  var values = rows.getValues();
  for (var i = values.length - 1; i>=0; i--) {
    var row = values[i];
    if ((row[1] == 0 || row[1] == '') && (row[2] == 0 || row[2] == '')){
      sheet.deleteRow(i+1); 
    }
  }
};

To get rid of empty rows, you can simply filter them out before you copy them to the target sheet.

Change:

var values = rows.getValues();

to:

var values = rows.getValues().filter(r=>r[0]!='');

Assuming for every empty row, the cell in column A is also empty.

You can now remove the DeleteBlankRows unless you want to delete also the rows.

Googlesheet adding unwanted rows

1 Answers