Use existing spreadsheet formulas in a custom formula in google docs/spreadsheets

Question

I like writing my own formulas inside of Google Docs Spreadsheets. But often what I want to do is very similar to a function that already exists. As an example, I couldn't find a function to turn a date (31-Aug-2010) into the lexical day of the week (Tuesday). I'd like to write:

=LexWeekDay('31-Aug-2010')
'Tuesday'

Clearly I can write all of this logic using core javascript, but there already exists a normal spreadsheet function called WEEKDAY() which takes a date and converts into into a number representing the day of the week [0 => Sunday, 1=> Monday, etc].

How can I access this function (or generally any function), that speadsheets already define, from my custom script?

Answer 1

I asked the same question at Google help, but did not get a solution. According to user Ahab:

I understand the need. I voiced the same in the GAS help forum 1 very early one when GAS became available but the reaction from the GAS team was not very promising... :( In essence we'd need a GAS class that contains the spreadsheet functions do allow using them.

Note that in general spreadsheet functions already virtually can be used as a functional programming language without the need of scripting them because of high-level functions like ArrayFormula, FILTER, SORT, UNIQUE, etc.. Unfortunately it is not possible to create e.g. substitution macro's that would allow us to quickly re-use formulas like (in pseudo-macro format):

Name: INVERSE Description: Inverse a columnar array Syntax: #INVERSE( array ) Call: #INVERSE( #1 ) Execute: =ARRAYFORMULA(SORT( #1 ; ROW( #1 ); FALSE))