How to pass cell coordinates to a custom function?

One limitation of custom functions in Google Sheets is that you cannot find out where in the spreadsheet the function was invoked. This is because custom functions, unlike other Apps Script functions, do not need authorization to run. You can use them just like you would use any other function in Google Sheets. However, because these functions can be invoked without the user's authorization, Google limits what you can do with them.

Since you can't find out where (i.e., which cell) the custom function is being invoked from, one way to make the function know from where it is being invoked is by explicitly passing cell coordinates to it.

Prerequisites

• Basic coding concepts in Google Apps Script

• Custom Functions in Google Sheets

• The Array method join() in Apps Script

How to pass a cell's coordinates to a custom function?

In Google Sheets, you can get the row coordinate of a cell by using the ROW() function and the column coordinate using the COLUMN() function. You can then pass these values to your custom function just like you would pass any other parameter.

For e.g., consider the custom function COORDINATES() that accepts the row and column number as inputs and returns a string obtained by concatenating them with a hyphen in between.

function COORDINATES(row, col) {
  return [row, col].join("-");
}

To pass it the coordinates of the cell where it is invoked, use the ROW() and COLUMN() function in Google Sheets.

Since the function is being invoked in cell B3, the cell's row number is 3 and its column number is 2. Therefore, the custom function COORDINATES() will return the string value "3-2".

Conclusion

In this tutorial I showed you how to pass the coordinates of a cell to a custom function using the ROW() and COLUMN() functions in Google Sheets. This way, the function can know where (i.e., from which cell) it is being run from.

Hope this post was helpful! Thank you for reading.