How to fix this error in programmatically (with trigger) active() the last occupied row in a Google sheet?

Question

Supose you have a sheet with several hundred rows long. You could to this to go to the last row and start your work:

1) after opening the spreadsheet, do Ctrl End; Ctrl leftArrow; Ctrl upArrow this will take you to the last occupied row cell in column A 2) before closing the spreadsheet, select the last occupied cell in column A and assign it range name endRow then when you open the spreadsheet do Edit > Named ranges then click on endRow this will take you to the last occupied row cell in column A;

I just find a way to programmatically implement this, it's really simple, but worked only in the oldspreadsheet. I just put this line inside onOpen() trigger and just works fine: activeSheet.getRange(activeSheet.getLastRow(), 1).activate();. But it didn't work in the new Google Sheets (a new one with some thousands of rows). So, I tried the Zig advice, write this function:

function goToLastRow() {
  var s = SpreadsheetApp.getActiveSpreadsheet();
  var range = s.getRange(s.getLastRow()+1, 1);
  range.activate();
 } 

And set the trigger manually (Script Editor>>Resources>>Triggers from this project>>Ad new trigger). But it still didn't work.

Any suggestion?

Answer 1

I found this way to accomplish what you need:

function onOpen() { var sheet = SpreadsheetApp.getActiveSheet(); sheet.hideRows(1,sheet.getLastRow()); }

This hides all rows with data from the active sheet, places the cursor on the next (empty) row, so you can start typing away.
when done with the row, simply click the little triangle to the left of the hidden rows, and they will reappear if you need so.
Might cause problems if more than one person enters new rows at the same time.

★ Here is another cool way to accomplish this without any scripting, plus works even with simultaneous users (the script version above could hide a row being entered if another person opens the spreadsheet with edit permissions.)

This 2nd no-script solution will only work if your data has a column with a fixed set of values (¹), for example a column with only the possible values 'pending', 'doing' and 'done'. So find such column, which I assume its on column 'A' in this example.

➮ Lets use "Filter Views" in the "Data" menu (only in the new sheets versions.)
This feature allows for personalized filters that only the user applying them sees. Create a filtered view called "Enter new rows mode" with no values checked for column 'A' (click filter:"clear" when you drop-down the column filter menu).

➮ Now, when you enter the spreadsheet, the filtered view is applied automatically and all rows with data will go away.
Start typing away. If other users start entering new rows, your row only goes away for them, not for you until you refresh the browser window.

➮ If other users remove the filtered view, its removed only for them, not for you.

You can also do something similar with the "old" filters but it has the same dissadvantage as the script solution regarding multiple users.

*¹ A fixed set of values is needed because the spreadsheet filters automatically add new items as checked in the view. You cant use a date column because you will always get new future values.
To configure the filter you need to already have at least one row per possible value, so that you can uncheck them in the column filter.
If new values are later entered, you need to edit the view to uncheck it.

Answer 2

I just find the way, it's really very simple, just put this line inside onOpen() trigger and just works fine: activeSheet.getRange(activeSheet.getLastRow(), 1).activate();. But it didn't work in the new Google Sheets. So, I tried the Zig advice, write this function:

function goToLastRow() {
  var s = SpreadsheetApp.getActiveSpreadsheet();
  var range = s.getRange(s.getLastRow()+1, 1);
  range.activate();
 } 

And set the trigger manually (Script Editor>>Resources>>Triggers from this project>>Ad new trigger).

Any suggestion?

Answer 3

Change getActiveSpreadsheet to getActiveSheet. The spreadsheet is all the file, a sheet is just a sheet :P. But you will find that you cant go to any part of a spreadsheet in an onOpen trigger.

The best way is to create a menu for this.

At the end the code goes like this:

function onOpen() //the same to create a onOpen trigger
{
  SpreadsheetApp.getUi().createMenu("Go to").addItem("Last Row", "goToLastRow").addToUi();
}

function goToLastRow() {
  var s = SpreadsheetApp.getActiveSheet();
  Logger.log(s.getLastRow());
  var range = s.getRange(s.getLastRow()+1, 1);
  range.activate();
}