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

Question

I like to write my own formulas inside Google Docs spreadsheets. But often what I want to do is very similar to a function that already exists . As an example, I could not find a function to include the date (August 31, 2010) on the lexical day of the week (Tuesday). I would like to write:

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

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

How can I access this function (or any function in general) that speadsheets already set from my user script?

Answer 1

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

I understand the need. I announced the same thing in the GAS 1 help forum very early, when GAZ became available but the reaction of the GAZ team was not very promising ... :( Basically, we need a GAS class that contains the spreadsheet functions that allow using them.

Please note that in the general table the functions can almost be used as functional programming language without the need to create scripts of them due to high-level functions like ArrayFormula, FILTER, SORT, UNIQUE, etc. Unfortunately, this is not possible to create, for example. replacing a macro that will allow us to quickly reuse formulas such as (in pseudo-macro format):

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

Answer 2

In your special apps app, you can use google built-in spreadsheet formulas as follows:

Suppose you want to use the function =WEEKDAY() on cell A1.

Then get your active table as in your custom appscript function:

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

set the formula as follows:

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

Also, if you want to convert 0.1, etc. 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 view the logs using ctrl + enter or by going to view-> Logs in script.