Reputation: 2777

How to get the current active sheet in my script?

I'm using this code to use data in a Google Apps Script:

function getCurrentRow() {
      var currentRow = SpreadsheetApp.getActiveSheet().getActiveSelection().getRowIndex();
      return currentRow;
    }

But when I use other sheet than the first one (number "gid=0"), my function remains getting data from that first sheet and not from my current active sheet. Since I'm using the method .getActiveSheet() how can I fix that?

PS - I'm not calling the code from a cell. I'm using this code: http://opensourcehacker.com/2013/01/21/script-for-generating-google-documents-from-google-spreadsheet-data-source/

Upvotes: 10

Answers (5)

Benjamin Warren

Reputation: 539

I'm having the same problem while developing in the Script Editor -- the Script Editor instance/window becomes 'disconnected' from the Sheets instance/window and just has the first sheet / A1 etc as the 'actives'.

What worked for me:

Closing the Script Editor window and re-opening from Sheet > Tools > Script editor. Voila, .getActive...()s are working again.

Also:

As implied by some of the other answers, triggering the execution from the Sheets window/instance (probably always) also works. One of the answers calls a function from a cell, which means it's going to be triggered by the Sheet. Another option would be to add a .UI menu and menu-option and trigger it there.

Upvotes: 3

David Tew

Reputation: 1471

I created from scratch a brand new spreadsheet, within which, I created a few tabs/sheets with some data within each; and then in google apps script in that spreadsheet I installed the following code:

function getCurrentRow() {
  var currentSelection = SpreadsheetApp.getActiveSheet().getActiveSelection()
  var currentValue = currentSelection.getValue();
  var currentRow = currentSelection.getRowIndex();
  Logger.log(currentValue);
  Logger.log(currentRow);

  return currentRow;
}

I ran the script, and it gave me the correct results for the sheet that was open/which cell was selected. So I would say that this code pretty much works as you expect.

In your case, may I suggest that the quickest way to get more info about the error, or to see if the error persists, is for you start from scratch too, with a new spreadsheet, and paste in the code above, and then test to prove that at least that much works for you too. Then, only after this, paste in the greater code (that you have linked to), and see if it still/stops working.

Upvotes: 17

smarty

Reputation: 1

create a spreadsheet in google drive and save it. In the spreadsheet go to tools menu and script editor and type the function.

Upvotes: -3

Zig Mandel

Reputation: 19864

Its because you are calling it from a custom function in a cell. You cant do that because custom functions must be deterministic. The answer from wchiquito works because he is using a different cell to try the 2nd case. If you use the same cell it will show the old cached result. Search s.o. for the issues about dedeterministic functions. Ive provided workarrounds in those (basically pass a second param =now()

Upvotes: 0