Inserting rows and adding a cell reference

Question

I'm trying to automate adding new rows if I'm on the last few rows of my sheet. It works, but since I'm also adding a cell reference to it, and checking the amount of free rows left using getMaxRows-getLastRow, it is always assuming that I have no free rows, so it adds another row.

edit : My last row and last column have that reference number, so I'm simply incrementing that last number.

Here is my function that is executed once a cell is modified :

function autoAddRows()
{
  var s = SpreadsheetApp.getActiveSheet();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var lastRow = s.getLastRow();
  var maxRow = s.getMaxRows();
  var lastColumn = s.getLastColumn();
  var lastVkey = s.getRange(lastRow,lastColumn,1,1).getValue();

  if (maxRow-lastRow < 2) {
    s.insertRowAfter(maxRow);
    s.getRange(lastRow+1,lastColumn,1,1).setValue(lastVkey+1);
  }
}

There has to be a better way?

Answer 1

You were pretty close. Here's a piece of code of which I think works:

Code

function autoAddRows() {
  var sh = SpreadsheetApp.getActiveSheet();
  var cRow = sh.getActiveRange().getRowIndex(), mRow = sh.getMaxRows();

  if((mRow-cRow) < 1) {
    var lCol = sh.getLastColumn(), key = sh.getRange(mRow,lCol).getValue();
    sh.insertRowAfter(mRow), sh.getRange(mRow+1,lCol).setValue(key+1);
  }
}

Remarks

• var ss isn't necessary, since you don't use it in the script.
• you need the row of the active cell, compared to the last row (getMaxRows)
• add all the goodies into the IF statement that need to be executed, when mRow-cRow=0

Example

I've prepared a file for you to experiment with: Inserting Rows + Reference