13

In VBA for Excel, I can get the column of the cell making the call to a custom function like this:

ThisCol = Application.Caller.Column

What is the equivalent in Google Scripts?
I've been trying to find my way through the API docs, but coming up empty....

I call this function in a cell in each row of a range. The function sums contents of a range of cells across the row. Here's the VB:

Public Function SumDays(row As Integer)

  Dim credit As Double

  NumCols = Range("D8", Range("D8").End(xlToRight)).Columns.Count
  ThisRow = row
  ThisCol = Application.Caller.Column  ' column containing the cell calling the fn

  credit = 0

  For i = (ThisCol + 3) To NumCols
     'do stuff across the range of cols in ThisRow
     ' If Cells(ThisRow, i) = ....
  Next i

  SumDays = credit
End Function     
3
  • Perhaps you can show us what you want to do, then we can show you how to do it? Sharing a doc is also very helpful.
    – Jacob Jan
    Jan 14, 2014 at 9:51
  • 1
    Added VB script and some more explanation.
    – Number8
    Jan 14, 2014 at 12:35
  • If you share a doc, and show us your expected result, then I will edit my post and give you the full code.
    – Jacob Jan
    Jan 19, 2014 at 18:20

1 Answer 1

9

The answer to your first question is:

Code

function test() {
  return SpreadsheetApp.getActiveSheet().getActiveCell().getColumn(); 
}

Screenshot

enter image description here

Remark

Add the code under Tools>Script editor and press the save button.

Example

I've prepared an example file for you: sample GAS code

8
  • Should this still work? If I run getColumn from within a cell it always returns 1, regardless of which cell. Mar 25, 2014 at 13:02
  • 1
    @JacobJanTuinstra - It seams to be an issue with the New Google Spreadsheets. It's working when I check your example file, and if create a new file with the new features turned off. Still not my files created in New Google Spreadsheet. Mar 25, 2014 at 21:52
  • 2
    This worked fine for me I have used it with SpreadsheetApp.getActiveSheet().getActiveCell().getValue()
    – Jacobvdb
    Aug 10, 2015 at 17:56
  • 7
    This is wrong answer, because when saved document starts again active cell is A1, when calling custom function can be anywhere else. Dec 30, 2017 at 15:24
  • 4
    This code gets the active cell (i.e. the user selected cell) - it does not answer the question "how do I get the column of the cell making a call to a custom function?".
    – GrayedFox
    Feb 24, 2018 at 7:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.