How to make a google sheets function reference the cell it was called from

Question

background

I followed this tutorial which basically makes a button call a google sheets script like this

function jumpToDetail() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("details");
  ss.setActiveSheet(sheet).setActiveSelection("A2");
}

that makes you jump to a specific cell. I looked at the google sheets docs but couldn't figure out how to make this function reference the cell it was called from

question

How do I make a function in google sheets reference the function it was called from. For example I would like the final code to look something like this:

function jumpToDetail(clickedCell) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("details");
  // get the value of "id" of the same row the cell was clicked in
  ss.setActiveSheet(sheet).setActiveSelection(function_of(id));
}

Answer 1

The active range is obtained with getActiveRange method of SpreadsheetApp. This can be useful when a script is called from a custom function: the active range is then the cell at which the custom function is located. Example:

function currentCell() {
  return SpreadsheetApp.getActiveRange().getA1Notation();
}

Entering =currentcell() anywhere in the sheet will return the A1 notation of that cell.

But clicking a button (an inserted drawing) has no effect on active range, because drawings are not associated with any cell in a sheet. They are in their own layer, floating over the sheet. In a script called with a button, the active range is wherever the selected cell(s) happen to be, and has nothing to do with the location of the button.

In order to communicate some location to a script, the user would have to select a cell first and then call a script (via a button or a menu).

Here is a function that returns the content of the cell in the current row with column labeled "id". (The label is assumed to be in the 1st row of the sheet.)

function getId() {
  var row = SpreadsheetApp.getActiveRange().getRow();
  var sheet = SpreadsheetApp.getActiveSheet();
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
  var column = headers[0].indexOf('id') + 1;
  if (column > 0) {
    return sheet.getRange(row, column).getValue();
  }
  else {
    throw new Error('No column labeled id');
  }
}