Google-sheets – Jump to bottom of spreadsheet, blank (non-formula) row

google sheetsgoogle-apps-script

I had a tall spreadsheet that I need to add data at every time it opens. Answers like Go to Bottom Cell have been helpful, but my quirk is that the first column contains formulas that return blank in most situations; therefore getLastRow() returns the last row in the entire sheet, not the last blank row.

Here's my solution, that works well except for one quirk. 

function onOpen() {
  var mysheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var dataRange = mysheet.getRange("A:A");
  var lastrow = dataRange.getLastRow();
  var dVal="";

  for (var i=lastrow; dVal==""; i--) {
    dVal=dataRange.getCell(i,1).getDisplayValue();
  }  
  mysheet.setActiveCell(dataRange.offset(i,0,1,1));
};

When the spreadsheet opens, the active cell is in the right spot (cell A4678) but the screen is displaying cells A4533-A4562. If I hit the down key, the screen refreshes and I am exactly where I need to be.

So how do I get the screen to refresh and show me the cursor in the active cell?

Edit: Further details, as requested (thx @Rubén):

  • First sheet has 4728 rows. First 4679 rows have data, therefore 1st blank row is 4680
  • 6 sheets in spreadsheet. Sheet 1 is the largest one, the rest use data from this sheet and analyse it in different ways
  • Click-to-ready time is about 8 seconds using the optimized formula suggested by @if…

Best Answer

Try Utilities.sleep(milliseconds) before setActiveCell

Regarding the optimal value of milliseconds, since Google Apps Script hasn't a way to know that the opening/recalculation of the spreadsheet is finished you we should find the value to use by trial and error and perhaps by using some statistics as the time that this operations take aren't deterministic.

Related Topic