Google sheets custom function argument address for conditional formatting

Question

I'm writing a custom function in Google Sheets with intention of using it for conditional formatting:

function f(cellValue) {
}

How can i get the address of the cell whose value i get in the cellValue argument?

The function should return true if the three cells above the given one are empty.

Max Makhrov Max Makhrov · Accepted Answer · 2016-04-12T19:14:36

As far as I know, you can't do it directly.

But your function could take 2 more arguments:

function f(cellValue, rowNum, columnNum) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getRange(rowNum, columnNum);
  var address = range.getA1Notation();
  return address + " = " + cellValue;
}

Example of the usage of the formula:

 = f(A1, row(A1), column(A1))

So we use native functions (row & column) inside custom formula.

OK, let's talk about your task.

The function should return true if the three cells above the given one are empty.

You may use this formula (no script needed):

=join("",OFFSET(B63,-3,,3))=""

Google sheets custom function argument address for conditional formatting

1 Answers