Google Sheets code to move the active cell in the onOpen event?

2
votes

I've made a simple Google Sheet that has a single (frozen) header row, six columns (A:F), and many rows under the headings. The rows are added to regularly (by more than one editor), so it is getting increasingly annoying to have to scroll down to the next blank row at the bottom of the sheet when it is first opened. I'm aware that I can press ctrl-down to get to the first blank cell, but the users of the sheet generally aren't aware of that. To add slightly to the complexity, I'd like to position the active cell based on the first blank cell in column B (which may not be the same row as the first blank cell in column A). I have the onOpen() event hooked up and working.

I thought about trying to fire two key presses at the spreadsheet: right-arrow (to go to column B), followed by ctrl-down-arrow (to get to the first blank cell in column B), but I can't find any code to do that.

Are there any API calls to move (in the onOpen event) the active cell to the first blank cell in column B?

1
google-apps-scriptgoogle-sheets

1 Answers

3
votes

I am not sure what you have done already, but here is how I would do it

function onOpen(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  for (var i = 0;i < data.length;i++) {
    if (data[i][1] === "") {
      break;}
  }
  sheet.getRange(i+1, 2).activate();
}