4

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...
2
  • 1
    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
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.

1
  • 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
0

The sheet.setActiveCell(cell) method is undocumented and therefore unsupported, which may explain the observed behavior. It would probably be better to use cell.activate() or sheet.setCurrentCell(cell).

The two proposed solutions only iterate column A, so they will activate the last row that has visible content in column A but will ignore content in other columns.

The following code will look at all columns, and can easily be configured to look at a specific column only.

function onOpen(e) {
  const sheet = SpreadsheetApp.getActiveSheet();
  const lastRow = getLastRow_(sheet);
  sheet.getRange(lastRow, 1).activate();
}

/**
* Gets the position of the last row that has visible content in a column of the sheet.
* When column is undefined, returns the last row that has visible content in any column.
*
* @param {Sheet} sheet A sheet in a spreadsheet.
* @param {Number} columnNumber Optional. The 1-indexed position of a column in the sheet.
* @return {Number} The 1-indexed row number of the last row that has visible content.
*/
function getLastRow_(sheet, columnNumber) {
  const values = (
    columnNumber
      ? sheet.getRange(1, columnNumber, sheet.getLastRow() || 1, 1)
      : sheet.getDataRange()
  ).getDisplayValues();
  let row = values.length - 1;
  while (row && !values[row].join('')) row--;
  return row + 1;
}

The getLastRow_() function deals with visible content. It is only required when the sheet contains formulas that use "" to return "blank" values. These zero-length text strings are not truly blank, i.e., null. To get truly blank values, use iferror(1/0).

When the sheet employs array formulas that use iferror(1/0) to return truly blank values, you can just use sheet.getLastRow().

2
  • Thank you for extending the prior solution. Switching to sheet.setCurrentCell() did not solve the question: "So how do I get the screen to refresh and show me the cursor in the active cell?" - I still have to hit the down key before the active cell is displayed on the screen.
    – Nick
    Apr 13 at 23:45
  • Please share a publicly editable copy of a spreadsheet where the issue can be reproduced. Apr 14 at 6:09

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.