Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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
add comment

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
  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);
}
share|improve this answer
add comment

Your Answer

 
discard

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.