11

Is it possible to define a function in Google Spreadsheets that can be used in any cell?

It would be helpful if I could define and use functions that refer to other cells in the same way that I can use native functions, e.g. by entering =myfunction(C1, C2, C3)

Improve this question
2
  • These custom functions are slow. Be careful.
    – fiatjaf
    Commented Nov 17, 2012 at 13:30
  • Looks like functions available in the spreadsheet are not all available to the script. For example, trying to use the JOIN function in a script results in error "JOIN not defined", even though using JOIN in the spreadsheet itself works fine.
    – Triynko
    Commented Mar 31, 2015 at 17:05

2 Answers 2

Reset to default
8

Yes - there's a tutorial. Just use javascript functions by name in your spreadsheet. Define them using Tools > Script editor.

Watch out for name restrictions; I was confused by the behavior of functions that I created with names like "function x10() {}" not being found. Renaming to a longer name fixed it. There are probably documented rules for what isn't allowed, but I don't know where they are.

Improve this answer
1
  • 5
    x10 is not a valid function name because it is also the native name of a cell (row '10', column 'X'). Besides that there are no special rules. The other important fact to remember is that custom functions should ONLY operate with the parameters being passed, ie should not use any external values because the function must be deterministic (same input should always return same value, as spreadsheets will cache that value).
    – Zig Mandel
    Commented Nov 29, 2013 at 11:21
-2

I am a "newbee". But is is my experience that you can only access a "cell" via the "range" object. You must define the range as a single cell. For example "A1:A1", will give you access the the cell at "A1".

A RANGE is an object associated to a "SHEET". A SHEET is an object associated to a "SPREADSHEET".

Here is some sample code to access cell A1 in the current active sheet:

var cell_A1 = SpreadsheetApp.getActiveSheet().getRange("A1:A1");

From here you can pass the object like any other parameter.

myFunction(cell_A1);

The receiving function must "know" that it is dealing with a "range". It can only access its values by calling "methods" associated to the "range" object.

Be careful! A "range" can consist of more than one cell. Your called function should test to see that it is working with a single cell. If you pass a range of more than one cell, your function might not act in the way you expect.

The two methods of a range object: "getNumRows()" and "getNumColumns()" returns the numbers of Rows and Columns in a range object.

In general, if you use methods that are limited to changing or accessing a single cell, and operate on a larger range set, the function will only be performed on the upper-left cell member. But be careful. While you might assume a method will only change a single cell, it may actually affect all cells in the range. Read the documentation closely.

There is another method to obtain a range of a single cell. Its instruction looks like this:

var cell_B2 = SpreadsheetApp.getActiveSheet().getRange(2, 2, 1, 1).

The first two parameters tell the "getRange" function the location of the cell (in row, column format). The second two parameters define the number of "rows" and "columns" to associated with the range. By setting them both to "1", you access a single cell.

Hope this helps.

Improve this answer
1
  • 1
    THis is not what he is asking. Hes asking about using custon function just like built-in spreadsheet functions in cells. In those cases you do NOT get cell values like you said. Your explanation only applies to "regular" functions withing a script and not custom functions.
    – Zig Mandel
    Commented Nov 29, 2013 at 11:19

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