What is the Google App Script to move to the first empty row?

Question

I have reviewed previously asked questions on this but still can't work out the code. I have a sheet with a significant number of rows of data in it. Rather than scroll down to the first empty row, I have created a button and on click of that button, I want the cursor to take me to column A of the first empty row. This is my script at this point but I keep getting errors:

Can someone please help me.

Answer 1

Try this:

function getLastRow() {
  var sss = SpreadsheetApp.getActiveSheet();
  sheet.getRange(sss.getLastRow()+1,1).activate();
}

Answer 2

If I understand your question correctly, you just want to go the first empty row in your sheet, not the first empty cell in column A. If that's the case, this small piece of code should do it:

function getLastRow() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
  sheet.getRange(lastRow + 1, 1).activate();
}

I hope this is what you wanted to accomplish.

Answer 3

Please try this:

function TestFindBlank() {
  //Jojo is rank that can be used for next 
  var Jojo=GetFirstBlank('Sheet1','F6');  
};

function GetFirstBlank(mysheetName, myrangeStr)
{
  var spreadsheet = SpreadsheetApp.getActive();
  var Mysheet=spreadsheet.getSheetByName(mysheetName);
  var Jojo=spreadsheet.getRange(myrangeStr).getNextDataCell(SpreadsheetApp.Direction.DOWN);   
  if (Jojo.offset(1,0).isBlank() ) {
    return  Jojo.offset(1,0);
  }

  var Jojo=spreadsheet.getRange(Jojo.getA1Notation()).getNextDataCell(SpreadsheetApp.Direction.DOWN);   
  return Jojo.offset(1,0);

}

Answer 4

Just run setupStartUpTrigger() one time to set it up. Then close and open again and give it a little time and you'll end up at the bottom.

function startUp() {//You can put your menu in here too
  SpreadsheetApp.getUi().createMenu('My Tools')
    .addItem('Item Name', 'functionName')
    .addToUi();  
  var sh=SpreadsheetApp.getActive().getSheetByName('Sheet1');
  sh.getRange(sh.getLastRow()+1,1).activate();
}
//setup an installable onOpen() trigger
//this will fire on opening the spreadsheet
function setupStartUpTrigger(){
  var ss=SpreadsheetApp.getActive();
  if(!isTrigger('startUp')){
    ScriptApp.newTrigger('startUp').forSpreadsheet(ss.getId()).onOpen().create();
  }
}

//this is nice to use to keep you from creating unnecessary triggers
function isTrigger(funcName){
  var r=false;
  if(funcName){
    var allTriggers=ScriptApp.getProjectTriggers();
    for(var i=0;i<allTriggers.length;i++){
      if(funcName==allTriggers[i].getHandlerFunction()){
        r=true;
        break;
      }
    }
  }
  return r;
}

Answer 5

Here next alternative to next blank or to next non blank cell:

function TestNextBlank_or_NotBlankUse() {
  var spreadsheet = SpreadsheetApp.getActive();
  var mysheet=spreadsheet.getSheetByName('Sheet1');
  var myrange=mysheet.getRange('Q1');

  var myrange=nextNonBlank(myrange); //First Non Blank
  Logger.log(myrange.getA1Notation());

  var myrange=nextNonBlank(myrange); //Second Non Blank
  Logger.log(myrange.getA1Notation());

  var myrange=nextNonBlank(myrange); //Third Non Blank
  Logger.log(myrange.getA1Notation());


  var myrange=mysheet.getRange('Q1');

  var myrange=nextBlank(myrange); //First Blank
  Logger.log(myrange.getA1Notation());

  var myrange=nextBlank(myrange); //Second Blank
  Logger.log(myrange.getA1Notation());

  var myrange=nextBlank(myrange); //Third Blank
  Logger.log(myrange.getA1Notation());

};


function nextBlank(theRange)
{
  if (theRange.offset(1, 0).isBlank()) return theRange.offset(1, 0);
  theRange=theRange.offset(1, 0);
  if (theRange.offset(1, 0).isBlank()) return theRange.offset(1, 0);
  theRange=theRange.getNextDataCell(SpreadsheetApp.Direction.DOWN).offset(1, 0);
  return theRange;
}

function nextNonBlank(theRange)
{
  if (theRange.offset(1, 0).isBlank()==false) return theRange.offset(1, 0);
  theRange=theRange.offset(1, 0);
  if (theRange.offset(1, 0).isBlank()==false) return theRange.offset(1, 0);
  theRange=theRange.getNextDataCell(SpreadsheetApp.Direction.DOWN);
  return theRange;
}

Answer 6

function TestFindBlankRow() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh=ss.getSheetByName('Sheet1');
  //Column A to T because you have formula in column U
  var Rslt=FirstBlankRange(sh,'A','T');  //0 : If Not Found
  Logger.log(Rslt);
  var Rslt=NextBlankRange(sh, Rslt, 'A','T');  //0 : If Not Found
  Logger.log(Rslt);
  var Rslt=LastBlankRange(sh,'A','T');  //0 : If Not Found
  Logger.log(Rslt);

};


function FirstBlankRange(Sheet, FirtColumn, LastColumn)
{
  for (a=1;a<Sheet.getLastRow()+1;a++)
  {
    var rr=Sheet.getRange(FirtColumn + a + ':' + LastColumn + a);
    var Rslt=rr.getValues().toString();
    if ((Rslt.length+1)==rr.getNumColumns()) 
    {
      return a;
    }
  }
  return 0;
}

function NextBlankRange(Sheet, CurPos, FirtColumn, LastColumn)
{
  for (a=CurPos+1;a<Sheet.getLastRow()+1;a++)
  {
    var rr=Sheet.getRange(FirtColumn + a + ':' + LastColumn + a);
    var Rslt=rr.getValues().toString();
    if ((Rslt.length+1)==rr.getNumColumns()) 
    {
      return a;
    }
  }
  return 0;
}

function LastBlankRange(Sheet, FirtColumn, LastColumn)
{
  for (a=Sheet.getLastRow();a>1;a--)
  {
    var rr=Sheet.getRange(FirtColumn + a + ':' + LastColumn + a);
    var Rslt=rr.getValues().toString();
    if ((Rslt.length+1)==rr.getNumColumns()) 
    {
      return a;
    }
  }
  return 0;
}