The documentation to add script buttons to Google Docs Spreadsheet is quite well advertised in the documentation. Surprisingly, it was more difficult to find how to make custom functions. Custom functions are such that are used as the normal spreadsheet functions "=func(A1:A9)"

My need was to add function "the last cell in the column with content". I have a spreadsheet where one line is added every day. Even more confusingly, some of the values are added to today and some to tomorrow - and some to yesterday if the values were missing. To use value "last" makes much more sense that trying to keep track of the last day when the data was added.

The basic documentation is in the Google Docs Help:

https://developers.google.com/apps-script/execution_custom_functions

If you create a GoogleScript (=JavaScript) function, it is callable from the spreadsheet as the normal functions:

=myFunction(argument)

As the argument, you can use a cell reference as in the normal functions.

So, the next question is what happens when the cell reference is an range. There is no mention of this in the basic documentation.

The next source of information is:

http://www.javascript-spreadsheet-programming.com/2011/01/user-defined-functions.html

This gives us a long list of things what we can or can not do in custom functions. You can use closures but you can not format cells. There is also an example "Producing a Concatenated List From a Range Of Cells", that demonstrates that the range argument turns into a JavaScript array.

This can be tested easily. We create the following function:

function myFunction(args) {
        return(args);
}

If we call this by writing to spreadsheet shell:

=myFunction(A1:A7)

The function copies the cells A1:A7 to a new cell range, so that the cell having the function is in the topmost cell.

The function got the cell range as a JavaScript table. The returned JavaScript array is spread into the cells after the formula cell.

It is actually great that we do not need to use cell access functions to access the cells. The spreadsheet cell access functions are a well-known performance problem:

http://stackoverflow.com/questions/6882104/faster-way-to-find-the-first-empty-row-google-apps-script

This discussion also confirms that functions like "last cell" or "first cell" are definitely missing in the long list of standard spreadsheet functions.

So, the final function is:

function getLastNonemptyCell(range) {
        position = range.length - 1;
        if (position <= 0) {
                return(range[0]);
        }
        while ( range[position][0] == "" ) {
                position--;
                if (position <=0) {
                        position = 0;
                        break;
                }
        }
        return(range[position]);
}

And it is used like:

=getLastNonemptyCell(A1:A22)

The first if statement checks the one-cell case "B1:B1". While loops from the end of the range until an non-empty cell is found. We don’t need to handle tricky off-by-one problems here because we only return the value in the cell (cells are numbered starting from 1 and JavaScript arrays are numbered starting from 0).

The function works with 2d ranges as well ("A1:B5") and fills all the cells that are mentioned in the range to the right of the formula cell. There is one catch: all the values are from the row that is non-empty in the first column. If the last cell is in the different row in the other columns, you have to repeat the function as normal, in the every column.

This function works only in columns. It is left as a trivial reader exercise to transform it to accept rows as well as columns…