6

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     

migrated from superuser.com Jan 14 '14 at 0:49

This question came from our site for computer enthusiasts and power users.

  • 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 Tuinstra Jan 14 '14 at 9:51
  • 1
    Added VB script and some more explanation. – Number8 Jan 14 '14 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 Tuinstra Jan 19 '14 at 18:20
7

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

  • Should this still work? If I run getColumn from within a cell it always returns 1, regardless of which cell. – Punchlinern Mar 25 '14 at 13:02
  • @Punchlinern tried naming the function into getColumn() and test() and both returned the desired result. Added an example file. – Jacob Jan Tuinstra Mar 25 '14 at 13:31
  • 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. – Punchlinern Mar 25 '14 at 21:52
  • 2
    This worked fine for me I have used it with SpreadsheetApp.getActiveSheet().getActiveCell().getValue() – Jacobvdb Aug 10 '15 at 17:56
  • 2
    This is wrong answer, because when saved document starts again active cell is A1, when calling custom function can be anywhere else. – Akim Kelar Dec 30 '17 at 15:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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