Web Applications Stack Exchange is a question and answer site for power users of web applications. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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--) {};
share|improve this question
Do you need some more help, in getting the script to work? – Jacob Jan Tuinstra Jan 2 '13 at 8:21

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
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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