-1

All, Imagine a google sheet with many columns, and every column has a different filled row, and imagine that we wanted the sheet load on the last filled row of a specific column.

I am using the following function to make google Sheets view the last row of column “C" upon loading. Yet, upon loading, I found the sheat focus on the last row of the first column rather than the last row of column “C". This function is working fine based on the first column.

function onOpen() {
  var column = "c"
  let ss = SpreadsheetApp.getActiveSheet();
  let lastRow = ss.getMaxRows();
  ss.setActiveRange(ss.getRange(column + lastRow));
}

I tried these steps in this post stackoverflow but without success.

———————

Based on the solution below, here is the updated onOpen function.

function onOpen() {
  let ss = SpreadsheetApp.getActiveSheet();
  let lastRow = ss.getLastRow();
  mycol=getColumnHeight(3)
  //Browser.msgBox(mycol)
  ss.setActiveRange(ss.getRange(mycol,1));

}
Kernel
  • 591
  • 12
  • 23
  • 1
    `getMaxRows()` ([Doc ref](https://developers.google.com/apps-script/reference/spreadsheet/sheet#getmaxrows)) returns the current number of **rows in the sheet**, _regardless of content_. Compare this to `getLastRow()` ([Doc ref](https://developers.google.com/apps-script/reference/spreadsheet/sheet#getlastrow)) which returns the position of the _last row that has content_. So use `getLastRow` instead of `getMaxRows` – Tedinoz Apr 01 '23 at 23:37
  • Both yield the same behavior, the sheet presents the last row upon loading. – Kernel Apr 01 '23 at 23:40
  • 1
    Does this answer your question? [Determining the last row in a single column](https://stackoverflow.com/questions/17632165/determining-the-last-row-in-a-single-column) – Tedinoz Apr 01 '23 at 23:45
  • I saw that post before, but I am not sure how it might be helpful to my question; I am a newbie to Google scripting. – Kernel Apr 02 '23 at 02:01
  • 1
    "Yet, it did not work out." This is not a good way to describe your problem. Would you please read [How do I ask a good question?](https://stackoverflow.com/help/how-to-ask) and then edit your question to describe your scenario, data and data layout, the problem with your script and the result you are trying to achieve. Also please include an example of successful outcome as part of creating a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example) . – Tedinoz Apr 02 '23 at 05:09

1 Answers1

2
function getColumnHeight(col, sh, ss) {
  var ss = ss || SpreadsheetApp.getActive();
  var sh = sh || ss.getActiveSheet();
  var col = col || sh.getActiveCell().getColumn();
  var rcA = [];
  if (sh.getLastRow()){ rcA = sh.getRange(1, col, sh.getLastRow(), 1).getValues().flat().reverse(); }
  let s = 0;
  for (let i = 0; i < rcA.length; i++) {
    if (rcA[i].toString().length == 0) {
      s++;
    } else {
      break;
    }
  }
  return rcA.length - s;
}
Cooper
  • 59,616
  • 6
  • 23
  • 54
  • Not sure how I can pass the value of the column that I am interested in into the function; I am interested in using the setActiveRange so that when I open the sheet, the focus goes directly to the specific row that I am interested. – Kernel Apr 02 '23 at 01:58
  • @Kernel "the focus goes directly to the specific row that I am interested." Therein lies the challenge since you haven't described what row you expect to show. You need to edit your question to supply sample data and an example of a successful outcome. – Tedinoz Apr 02 '23 at 05:13
  • I succeeded in calling the above function in the onOpen function function `ll=getColumnHeight(3)` `ss.setActiveRange(ss.getRange(ll,1));` – Kernel Apr 02 '23 at 17:13