14

I have a Google Spreadsheet with 100 lines.

Every time I open that file, I want to insert a row on the end of my table.

How can I jump to the last line of the table when I open the doc?

Because right now I have to scroll down in order to be able to insert a new line.

  • 1
    Would a key combination suffice? Ctrl+End should jump you to the end of the file. – ale May 25 '16 at 13:27
  • 1
    @AlE.: Does Ctrl+End move to the last column also? I don't have a Windows box to test with, but on Mac, the corresponding shortcut (⌘+Fn+➔) moves to last row, last column. – Vidar S. Ramdal May 25 '16 at 13:40
  • @Vidar: Yeah, probably. (I don't have the ability to test it at the moment.) Fairly easily rectified by then hitting the Home key. – ale May 25 '16 at 13:45
11

You can create a trigger that runs every time your spreadsheet is opened.

Go to Tools → Script editor and paste the following:

function onOpen(e) {
  var spreadsheet = e.source;
  var sheet = spreadsheet.getActiveSheet();
  var lastRow = spreadsheet.getLastRow();
  if (sheet.getMaxRows() == lastRow) {
    sheet.appendRow([""]);
  }
  lastRow = lastRow + 1;
  var range = sheet.getRange("A" + lastRow + ":A" + lastRow);
  sheet.setActiveRange(range);
}

Click the Save button, then close the script editor, and the spreadsheet.

Now, open your spreadsheet again. Give it a couple of seconds, and you should see that a new row is inserted at the end of your sheet, and that that row is selected.

I have created this spreadsheet to demonstrate - feel free to copy it (click File → Copy). You will need to run your own copy in order to see the script run successfully.

The script explained:

The onOpen function name has a special meaning. See documentation. It takes a single argument, an Event object. Its source property is a reference to the spreadsheet being opened. With the spreadsheet, we can do getLastRow() to find the index of the last row that has content. getMaxRows() gives us the max number of rows in the sheet, even empty ones. With that knowledge, we can see if the last row has content - if so, we append a new, empty row. Finally, we can create a range and call setActiveRange on it, to move to the last row.


If you just want to move to the last line, not inserting anything, the script can be simplified as this:

function onOpen(e) {
  var spreadsheet = e.source;
  var sheet = spreadsheet.getActiveSheet();
  var lastRow = spreadsheet.getLastRow();
  var range = sheet.getRange("A" + lastRow + ":A" + lastRow);
  sheet.setActiveRange(range);
}
  • thanks a lot for your help! I will try this later. Thanks for your time and help – UcanDoIt May 25 '16 at 16:46
  • 1
    this dont go for the last line with text, as i want. :( it goes for the last line that has formulas. – UcanDoIt Jun 3 '16 at 0:03
  • 1
    can you help me so it goes for the last line with text ? – UcanDoIt Jun 3 '16 at 0:03
  • 1
    ok I'm at work and don't have access to Google here, but will make that later.. Will try now with words... In my spreadsheet I have formulas, in columns, until line 1000. And I've inserted data until line 200 (only). So with your solution when I open the doc it goes to the line 1000, and i wanted it to go to line 200 so I could just insert new data in the next available line. – UcanDoIt Jun 7 '16 at 8:52
  • 1
    @Hamideh There's no event triggered when switching between sheets in the same document, as I'm aware of. So this approach would not work in that case. – Vidar S. Ramdal Jan 14 at 17:17
3

If you have the additional problem that certain columns are populated with formulas all the way down, and you want to scroll to the first row that has non-formula data, here is an approach. In this case column A has formulas all the way down, so I scroll to the first blank cell in column B in row 5 or below.

function onOpen(e) {
    var spreadsheet = e.source;
    var sheet = spreadsheet.getActiveSheet();
    var row = 5;
    while (true) {
        var range = sheet.getRange("B" + row);
        if (!range.getValue()) {
            sheet.setActiveRange(range);
            return;
        }
        row++;
    }
}

As the other answer indicates, you should open Tools → Script editor, paste in that function, name your "project", save, and reload the spreadsheet.

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