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?
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))
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With