Google apps - getCurrentCell() not working

Question

I'm trying to run this code on a brand new blank sheet:

function myFunction() {
  var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  var current = sheet1.getCurrentCell().getA1Notation();
  Browser.msgBox(sheet1.getSheetName()+'-'+current);
}

But no matter what cell I currently have selected, this always returns 'A1' as the current cell. Am I missing something or is this a bug?

I played around with this one day and found that you can set an active cell on each page manually and it will return to that cell when the user goes back to that page. But if you do the some thing with a script it always goes back to 'A1'. You can try it with the script below. — Cooper, Apr 22 '20 at 21:24

score 1 · Answer 1 · answered Feb 16 '21 at 01:35

This "always returning 'A1' as the current cell" irked me for an entire day, until I found the solution within a different answer here: Google App Script getActiveSheet returns leftmost sheet, not active sheet.

The key is here:

All previous calls to get objects must have used active. For eg, To get active range, You should have first got active spreadsheet => active sheet => active range(the chain of active objects). If any of the calls is not to the active object(say if you used getSheetByName() instead of getActiveSheet() in the middle step), the final call is likely to default to the default object(First sheet A1).

Hence the solution is to use this:

var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

NOT this:

var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];

You're welcome.

score 0 · Answer 2 · answered Apr 22 '20 at 21:25

function myFunction() {
  const ss=SpreadsheetApp.getActive();
  const shts=ss.getSheets();
  shts.forEach(function(s){
    s.activate();
    let msg=Utilities.formatString('Sheet: %s\nCell: %s',s.getName(),s.getActiveCell().getA1Notation());
    ss.toast(msg);
    SpreadsheetApp.flush();
    Utilities.sleep(5000);
  })
}

Google apps - getCurrentCell() not working

2 Answers2