3

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 spreadsheet, 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...

bumped to the homepage by Community 2 days ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

  • Not sure if it's related, but your loop would probably run faster if it used values = dataRange.getDisplayValues() and iterated over that double array, comparing values[i][0] to "". – user135384 Jan 21 '18 at 5:20
  • Thanks for this suggestion. While it did speed up the script, it did not address the screen refresh issue – Nick Jan 21 '18 at 16:28
  • Add more details in order to make your issue reproducible (How many rows have the first sheet? How many sheets has your spreadsheet? How long it takes to open the spreadsheet?) – Rubén Jan 21 '18 at 16:59
0

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.

  • Thanks @Rubén! Unfortunately, adding a 10-second delay (the maximum I would consider acceptable) did not solve the problem. – Nick Jan 21 '18 at 16:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.