4

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
  • 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
4

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

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

  };
0

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

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.