Google-sheets – Script to have Google Sheets jump to the next available row in column A automatically

google sheetsgoogle-apps-script

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));
};

Best Answer

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.

Related Topic