3

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));
};
  • 2
    Uh...doesn't Ctrl-End or Ctrl-down-arrow get you to the bottom? – ale Dec 15 '16 at 23:37
  • 1
    yeah - command and the down arrow always jumps you to the next spot – Aurielle Perlmann Dec 17 '16 at 20:17
3

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.

1

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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.