Script to have Google Sheets jump to the next available row in column A automatically?

Question

I'm using Google Sheets to keep track of inventory. As the year goes on, the sheet gets quite long, so it is a pain to scroll all of the way to the bottom each time we start a new day, end the day or make a shift change.

Is there a way to add a script that will automatically scroll to the next available cell in column A when you open the sheet?

The closest thing I could find was this, but it goes to the bottom of the entire sheet, which is no good because we have extra rows on our sheet:

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{name:"MyFunction", functionName:"myFunction"}];
  sheet.addMenu("Scripts", entries);
  myFunction();
};

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var mysheet = ss.getActiveSheet();
  var lastrow = mysheet.getLastRow();
  mysheet.setActiveCell(mysheet.getDataRange().offset(lastrow-1, 0, 1, 1));
};

Answer 1

Here is the script that jumps to the cell in column A that is below the last non-empty cell in that column. You can call it from onOpen, or from menu, as you wish.

function jump() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var values = sheet.getRange("A:A").getValues();
  var maxIndex = values.reduce(function(maxIndex, row, index) {
    return row[0] === "" ? maxIndex : index;
  }, 0);
  sheet.setActiveRange(sheet.getRange(maxIndex + 2, 1));
}

Explanation: it gets all values in column A and finds the last row with nonempty cell. The addition of +2 on the last line is because (a) row numbers are 1-based while JavaScript index is 0-based, (b) we want to move one row down to get an empty cell.

Answer 2

You can also use this little piece of code:

Code

function jump() {
  var sheet = SpreadsheetApp.getActiveSheet();
  return sheet.setActiveRange(
    sheet.getRange(
      sheet.getDataRange().getHeight() + 1, 1)
  );
}

Explained

The script retrieves the height of the data range (without fetching the values) and uses that to set the active range.

Answer 3

Please try this code. It is working for me

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{name:"MyFunction", functionName:"myFunction"}];
  sheet.addMenu("Scripts", entries);
  myFunction();
};

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var mysheet = ss.getActiveSheet();
  var lastrow = mysheet.getLastRow();
  mysheet.setActiveCell(mysheet.getDataRange().offset(lastrow+0, 0, 1, 1));
};

Answer 4

I try this script, for your second function (myFunction), hope this helpful:

 function myFunction() {
     var spreadsheet = SpreadsheetApp.getActive();
     spreadsheet.setActiveSheet(spreadsheet.getSheetByName('blablabla_yourSheetName'), true);
     spreadsheet.getRange('A1').activate(); // this the which column you want to looking for

     var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();  
     var lastRow = sheet.getLastRow();

     var activeCell = sheet.getActiveRange();
     var currentColumn = activeCell.getColumn();
     var currentRow = activeCell.getRow(); 

     var columnData = sheet.getRange(1, currentColumn, lastRow, 1).getDisplayValues();
     var lookData = [].concat.apply([], columnData);
     var newRange;
     var lastCell = lookData.indexOf('', currentRow); 

     if (lastCell === -1) {
           newRange = sheet.getRange(lastRow, currentColumn);
         } else {
           newRange = sheet.getRange(lastCell + 1, currentColumn);
         }

         sheet.setActiveRange(newRange);

  };

Answer 5

Here is a small code you can add to your macro to resolve this issue.

Assuming you have a macro written, and your current cell selection is the topmost cell of your list, you can enter these lines:

ABC.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.DOWN).activate();
  ABC.getCurrentCell().offset(1, 0).activate();

Here, the name of your spreadsheet variable is 'ABC'.
In simple terms - this is like using CTRL+ENTER and then pressing the down arrow once.

Example:
Here macro name is ABC. I have a list in column A of sheet SECOND - let's say 100 items. Column B is incomplete - at around 95 items and I want to copy an item from cell B5 of sheet FIRST to cell B96 (which is empty and sits below B95).

function ABC() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('SECOND'), true);
  spreadsheet.getRange('B96').activate();
  spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.DOWN).activate();
  spreadsheet.getCurrentCell().offset(1, 0).activate();
  spreadsheet.getRange('FIRST!B5').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
};