How to open a Google Sheets file and go immediately to the end of the file

Question

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.

Answer 1

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

Answer 2

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.