Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 0 down vote accepted

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

share|improve this answer
    
thank you very much! –  user43365 Jul 8 '13 at 21:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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