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

In your custom appscript, you can use the in-built formulas of google spreadsheet in this way:

Lets say you want to use =WEEKDAY() function on cell A1.

Then, get your active spreadsheet like this in your custom appscript function:

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("YOUR_SHEET_NAME");

now, set the formula like this:

sheet.getRange("A1").setValue("=WEEKDAY()");

Also, if you want to convert 0,1 etc to Sunday,Monday...then define an array like this:

var days = ['Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday'];

And then use:

var dayIndex = sheet.getRange("A1").getValue(); 
Logger.log(days[dayIndex]);

You can see the logs using ctrl+enter or by going to View->Logs in script editor itself.

Answer 2

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))