How do I edit the calling cell of a custom function with google apps script from within the custom function?

Question

After searching through various questions and the documentation I still cannot edit the calling cell of a custom function. According to the docs:

Returns the range of cells that is currently considered active. This generally means the range that a user has selected in the active sheet, but in a custom function it refers to the cell being actively recalculated.

Although this description is for the getActiveRange() function one assumes the getActiveCell() functions in the same way.

I am trying to call a helper function from within my custom function which should return the 'active cell' which, to my knowledge, should be the calling cell of the custom function. Not working code:

// returns the current active (calling) cell for use within custom functions
function callingCell() {
  return SpreadsheetApp.getActiveSpreadsheet().getSheets()[0].getActiveRange()
}

// change background colour of calling cell
function getInfoFromPublicApi(type) {
      ... // get prices using UrlFetchApp
      callingCell().setBackground('green')
      return averagePrice(prices, type)
}

I have also tried using the SpreadsheetApp from within the custom function directly, as well as using:

SpreadsheetApp.getActiveSpreadsheet().getActiveCell()
SpreadsheetApp.getActiveSpeadsheet().getActiveRange()

Brian Brian · Accepted Answer · 2018-02-24T14:47:25

You can go about it two ways. First, set calling cell as a variable. You cannot chain methods together like you're trying to do in the larger function. Or, you can drop the callingCell() function because the information is handled in the event object of onEdit().

Method 1

// returns the current active (calling) cell for use within custom functions
function callingCell() {
  return SpreadsheetApp.getActiveSpreadsheet().getSheets()[0].getActiveRange();
}

// change background colour of calling cell
function getInfoFromPublicApi(type) {
  var active = callingCell();

  ... // get prices using UrlFetchApp
  active.setBackground('green')
  return averagePrice(prices, type)
}

Method 2

function onEdit(e) {
  // Not sure what your `type` param is, so you'd need to test for that somehow.
  ... // get prices using UrlFetchApp
  e.getRange().setBackground('green');
  ...
}

How do I edit the calling cell of a custom function with google apps script from within the custom function?

1 Answers