org-mode spreadsheet formula, can one use the result of org-babel block?

Question

I'm trying to find a way to use the output of existing org-babel block in org-mode spreadsheet formulae without rewriting them in emacs-lisp. Is it possible?

TBLFM line is pseudo code, it is syntactically invalid.

#+NAME: currencyRateOnDate
#+BEGIN_SRC http :pretty :select .[0].rate
GET https://bank.gov.ua/NBUStatService/v1/statdirectory/exchange?valcode=USD&date=${date}&json
#+END_SRC


| Name |     Date | Currency rate |
|------+----------+---------------|
| Foo  | 20210125 |               |
#+TBLFM: $3=currencyRateOnDate(date=$2)


#+CALL: currencyRateOnDate(date="20210714")

#+RESULTS:
: 27.3216

or alternatively, is it possible to wrap existing org-babel blocks(e.g. in http, ruby "languages") as emacs-lisp wrapped blocks(which could be easily called in org-mode spreadsheet formula)?

Answer 1

After quite a bit of fighting trying to create a wrapper emacs-lisp function I found the org-sbe macro that solves this exact problem.

To make it work, I've changed TBLFM definition to the following:

#+TBLFM: $3='(org-sbe "currencyRateOnDate" (date $2))'

org-sbe macro docs:

org-sbe org-sbe is a Lisp macro in `ob-table.el'.

(org-sbe SOURCE-BLOCK &rest VARIABLES)

Return the results of calling SOURCE-BLOCK with VARIABLES. Each element of VARIABLES should be a two element list, whose first element is the name of the variable and second element is a string of its value. The following call to `org-sbe' would be equivalent to the following source code block.

(org-sbe 'source-block (n $2) (m 3))

#+begin_src emacs-lisp :var results=source-block(n=val_at_col_2, m=3) :results silent results #+end_src

NOTE: by default string variable names are interpreted as references to source-code blocks, to force interpretation of a cell's value as a string, prefix the identifier a "$" (e.g., "$$2" instead of "$2" or "$@2$2" instead of "@2$2").

NOTE: it is also possible to pass header arguments to the code block. In this case a table cell should hold the string value of the header argument which can then be passed before all variables as shown in the example below.

| 1 | 2 | :file nothing.png | nothing.png | #+TBLFM: @1$4='(org-sbe test-sbe $3 (x $1) (y $2))