Creating a function that runs when a Google spreadsheet is opened

Question

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

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

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
  sh.setActiveCell('E'+indRowWithout);
}