How to write an onOpen triggered script without using Activate() methods

Question

Good morning,

I am having an issue with a piece of script which will open the spreadsheet at the first available row after a data table. I am fairly new to this, but as I understand it from research, the onOpen trigger will not work for scripts featuring Activate() methods (which I assume are things like getActiveRange() and setActiveRange() etc.).

I'd really like for this to work, but I can only do it currently by placing a button at the top of the sheet which the user has to click to run the script. I'm at a loss as to how to write in a different way, and have spent ages googling but only get things which don't help me much at all. My code is as below, any help you can give in writing it without using Activate() methods would be very much appreciated. Thanks, MB

function onOpen() {   
var ss = SpreadsheetApp.getActiveSpreadsheet();   
var range = ss.getRange("A2");   
var rowCount = range.getValue() + 3;   
var newRange = ss.getRange("A" + rowCount);  
SpreadsheetApp.setActiveRange(newRange); };

The value on line 4 refers to a value in a cell which is counting the number of rows in the dataset (I had issues trying to count this using script because there are other columns in the dataset which throw functions like getLastRow()).

Answer 1

As mentioned in issue 3928 there is no way (as of now, september 2014) to set a selection on a cell/range/sheet from a triggered function.

This issue was closed as "wont fix" but recently reopened - probably because a lot of people were very disappointed (to use a polite word) - so I suggest you star this issue to increase its visibility and its priority.

In the mean time I'm afraid there is not much we can do except continue to use old version spreadsheet in which this works without problem.

About what you said about counting the last row in a single column, this has been the subject of one of the most popular thread on this forum, look at the suggested solutions there (and also in other similar threads).