Get first data row/column in the active Google Sheet using Apps Script.

Get_First_Data_Position.md

First row

Get first row contain value in the active Sheet (1-Index). Return 1 for empty sheet.

function getFirstRow() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var lastColumn = sheet.getLastColumn();
  var lastRow = sheet.getLastRow();


  var firstRow = 1;
  while (firstRow<=lastRow) {
    var rowData = sheet.getRange(firstRow, 1, 1, lastColumn).getValues()[0];
    if (isEmptyRow_(rowData)) {firstRow++}  else break;
  };

  function isEmptyRow_(arr) {
    for (j=0; j< arr.length; j++) {
      if (arr[j]!= '') {return false};
    }
    return true;
  }

  Logger.log("First data row: " + firstRow);
  return firstRow
}

First column

Get first column contain value in the active Sheet (1-Index). Return 1 for empty sheet.

function getFirstColumn() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var lastRow = sheet.getLastRow();
  var lastColumn = sheet.getLastColumn();

  var firstColumn = 1;
  while (firstColumn<=lastColumn) {
    var columnData = sheet.getRange(1, firstColumn, lastRow, 1).getValues();
    if (isEmptyCol_(columnData)) {firstColumn++} else break;
  }

  function isEmptyCol_(arr) {
    for (l=0; l<arr.length; l++) {
      if (arr[l][0]!='') {return false};
    }
    return true;
  }

  Logger.log("First data column: " + firstColumn);
  return firstColumn
}

This function only get firt row/column of the active sheet. If you need to get first data position of another sheet, please input sheetname as a parameter and refactor the code.