[O] Using org-table-get-remote-range to sum cells or assign a range

[Christophe Schockaert]

Hi Org fellows,


I would like to make use of "org-table-get-remote-range()" to write a 
spreadsheet formula since I learned I could get more flexibility than 
with the standard "remote()" way. Actually, I need to use some variables 
to reach a set of cells.

However, I can't get to where I want to.

I will show you a simple example, in the hope you can guide me there.
For sake of simplicity, I will keep static references in the formula.
I have Org mode version 9.1.13 running in GNU Emacs 25.1.1.


Let's have 2 tables, named "MainData" and "ExternalData"

#+NAME: MainData
| Month  | x         |  y |
|--------+-----------+----|
| Set 1  | 1         | 10 |
| Set 2  |           |    |
| Set 3  |           |    |
|--------+-----------+----|
| Sum    | 2 20 3 30 |    |
#+TBLFM: @>$2='(mapconcat 'identity (org-table-get-remote-range 
"ExternalData" (concat "@" "I+" "2" "$" "1" ".." "@" "II-" "1" "$" "2")) 
" ")::@2$2='(org-table-get-remote-range "ExternalData" (concat "@" "I+" 
"1" "$" "1"))::@2$3='(org-table-get-remote-range "ExternalData" (concat 
"@" "I+" "1" "$" "2"))


#+NAME: ExternalData
| x |  y |
|---+----|
| 1 | 10 |
| 2 | 20 |
| 3 | 30 |
|---+----|


What I could achieve, is to refer to one cell at a time, like in :
@2$2='(org-table-get-remote-range "ExternalData" (concat "@" "I+" "1" 
"$" "1"))
@2$3='(org-table-get-remote-range "ExternalData" (concat "@" "I+" "1" 
"$" "2"))

I was also able to concat the data from range @address@hidden using the 
syntax shown in 
https://orgmode.org/worg/org-tutorials/org-spreadsheet-lisp-formulas.html#org4a215c8 
:
@>$2='(mapconcat 'identity (org-table-get-remote-range "ExternalData" 
(concat "@" "I+" "2" "$" "1" ".." "@" "II-" "1" "$" "2")) " ")

However, the two things I would like to do are :

1) assigning the range to @address@hidden in table "MainData"

@address@hidden = '(org-table-get-remote-range "ExternalData" (concat "@" 
"I+" "1" "$" "1" ".." "@II-" "1" "$" "2"))

I also tried '(identity (org-table-get-remote-range ...))


2) summing the range in a cell of "MainData"

@>$3 = '(+ (mapcar 'string-to-number (mapconcat 'identity 
(org-table-get-remote-range "ExternalData" (concat "@" "I+" "2" "$" "1" 
".." "@" "II-" "1" "$" "2")) " ")))

I first tried without the (mapconcat 'identity (...))

I entered the formula-debugger (for some reason, I can't C-c C-c in a 
specific cell to evaluate that cell only), and this is the result I get 
for evaluating (org-table-get-remote-range ...) :
(#("2" 0 1 (fontified t face org-table)) #("20" 0 2 (fontified t face 
org-table)) #("3" 0 1 (fontified t face org-table)) #("30" 0 2 
(fontified t face org-table)))

I don't know what kind of forms they are, or how I can get the data from 
them.
I tried to "cadr" them, without luck.

Also, I see that after being (mapconcat 'identity (...)), they become :
#("2 20 3 30" 0 1 (face org-table) 2 4 (face org-table) 5 6 (face 
org-table) 7 9 (face org-table))

Which shows, they are transformed in some way it is understandable in 
the spreadsheet, but I don't know how it get transformed to that, 
neither how the spreadsheet handles the extra arguments that seem 
different thant the individual form of each argument gathered.

I also tried using "org-no-properties" somehow, but in the overall I am 
just trying to guess what to do.
I don't understand much of the data I get, how to handle it, and what 
the spreadsheet expects to get in the end.


Can you give me a hint for that, please ?

I would be greatful :)


Christophe


--
--------------->
Once it's perfectly aimed, the flying arrow goes straight to its target.
Thus, don't worry when things go right.
There will be enough time to worry about if they go wrong.
Then, it's time to fire a new arrow towards another direction.
Don't sink.  Adapt yourself !  The archer has to shoot accurately and
quickly.
[Words of Erenthar, the bowman ranger] <---------------<<<<