21

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.

3
  • 1
    Would a key combination suffice? Ctrl+End should jump you to the end of the file.
    – ale
    Commented May 25, 2016 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. Commented May 25, 2016 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
    Commented May 25, 2016 at 13:45

2 Answers 2

21

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

Go to Extensions → Apps Script 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);
}
4
  • Great answer @VidarS.Ramdal. How can I do the same when switching to an existing sheet?
    – Hamideh
    Commented Jan 14, 2020 at 17:13
  • 2
    @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. Commented Jan 14, 2020 at 17:17
  • Use this for the last line with text ``` function onOpen(e) { var spreadsheet = e.source; var sheet = spreadsheet.getActiveSheet(); var Avals = sheet.getRange("A1:A").getValues(); var lastRow = Avals.filter(String).length+1; var range = sheet.getRange("A" + lastRow + ":A" + lastRow); sheet.setActiveRange(range); } ```
    – laviex
    Commented Jul 7, 2020 at 6:25
  • 1
    @VidarS.Ramdal Google Sheets no longer has a Script Editor option under Tools. It now has the menu option Extension | Apps Scripts, so the answer needs updating. This function can be put in the resulting window opened. I then tried deploying it as a WebApp but the cursor remains at the top of the spreadsheet when it's opened. How should it be deployed to work?
    – boardtc
    Commented Jan 26, 2022 at 16:43
5

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.