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) {

There has to be a better way?

1 Answer 1


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


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);


  • 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


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


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.