I want to create a function which runs automatically when the spreadsheet is opened. This function will make the selected cell the last cell without text in a certain column. Basically, I don't want to scroll down a lot, so I'm trying to write a script.

Anyway, my spreadsheet, in Columns A-D, will have an equal number of rows with text or more. I want to get the first row in Column E without text. Column F also has a few rows with text.

Here is my code thus far:

function onOpen() {
  var ss = SpreadsheetApp.getActive();
  var s = ss.getActiveSheet();
  var indLastCol = s.getLastColumn()-1;
  var indRowWithout = s.getLastRow();
  s.setActiveSelection(indRowWithout , indLastCol); // this line is broken.
  for (; s.getActiveSelection().getValue() != 'DONE'; indRowWithout--) {};
  • Do you need some more help, in getting the script to work?
    – Jacob Jan
    Jan 2, 2013 at 8:21

1 Answer 1


This little script will:

  • create an extra menu item, thus making it accessible throughout the workbook. T
  • the menu item will set the active range to column E, last row + 1.

See the example I've prepared: Creating a function...Google spreadsheet is opened (edit rights)

function onOpen() {
  // get active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // create menu
  var menu = [{name: "get last row", functionName: "getLastRow"}];

  // add to menu
  ss.addMenu("Rows", menu);  

  // execute function

function getLastRow() {
  // get active spreadsheet
  var ss = SpreadsheetApp.getActive();

  // get active sheet
  var sh = ss.getActiveSheet();

  // get column F
  var indLastCol = sh.getLastColumn()-1;

  // get last row + 1
  var indRowWithout = sh.getLastRow()+1;

  // set new active range

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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