Google Apps Script - Find Last Empty Row of a Column in Spreadsheet

Question

I have currently a script like this: (Which will run every minute and fetch any values)

  // function 01
  sheet2.getRange(sheet2.getLastRow() + 1, 1, 1, 6).setValues(values);

  // function 02
  sheet2.getRange(sheet2.getLastRow() + 1, 10, 1, 6).setValues(values);

It finds the last row and set the values in next row. Both are in separate functions. But currently it outputs something like this.

Current Output : NOT GOOD

// function 1 output here       // function 2 output here
------------------------------------------------------------
|   A    |     B    |   C     ||         |         |        |
------------------------------------------------------------
|        |          |         ||   D     |    E    |   F    |
------------------------------------------------------------
|        |          |         ||   G     |    H    |   I    |
------------------------------------------------------------
|   J    |    K     |   L     ||         |         |        |
------------------------------------------------------------

I want that to be displayed like this:

EXPECTED RESULT

------------------------------------------------------------
|   A    |     B    |   C     ||   D     |    E    |   F    |
------------------------------------------------------------
|   J    |     K    |    L    ||   G     |    H    |   I    |
------------------------------------------------------------
|        |          |         ||         |         |        |
------------------------------------------------------------

Hope I'm clear.

This should help: http://stackoverflow.com/questions/6882104/faster-way-to-find-the-first-empty-row — a-change, Oct 04 '16 at 19:12

score 2 · Accepted Answer · edited May 23 '17 at 12:15

Try the below function, very slightly modified to pass the column from the solution Mogsdad supplied on Nov 27, 2014 for the New Sheets in response to the thread Faster way to find the first empty row

// Don's array approach - checks first column only
// With added stopping condition & correct result.
// From answer https://stackoverflow.com/a/9102463/1677912
// Added the passing of myColumn which needs to be a column range
// example use: var emptyRow = getFirstEmptyRowByColumnArray('B:B');
function getFirstEmptyRowByColumnArray(myColumn) {
  var spr = SpreadsheetApp.getActiveSpreadsheet();
  var column = spr.getRange(myColumn);
  var values = column.getValues(); // get all data in one call
  var ct = 0;
  while ( values[ct] && values[ct][0] != "" ) {
    ct++;
  }
  return (ct+1);
}

It could, of course, be written instead to just pass the column and create the range if you like.

Google Apps Script - Find Last Empty Row of a Column in Spreadsheet

1 Answers1