Re: [office-formula] Syntax loose ends: inline error names,

["David A. Wheeler" <dwheeler@dwheeler.com>]

> Andreas J Guelzow:
> > Maybe more first we should consider what is Sheet1.A1...
> > options:
> > 1) cell A1 in table Sheet1
> > 2) value of named range or expression called A1 in table Sheet1
> > 3) value of named range or expression called Sheet1.A1 in current table
> > oh boy

Right.  You see why in the exchange format we use [...] for cell refs!
We actually don't have any problems with "A1" style;
it's the named ranges/expressions with a sheet name that cause troubles.

I believe that all the ambiguity issues disappear if we
REQUIRE that sheetnames be surrounded by '...' when not
in a cell reference, and if a cell reference but not the initial
sheet. Which is a very trivial extension to the current syntax.
I also think that optional "." prefixing of named expressions in the
current sheet would be helpful; it'll be required for those who
can call named expressions as functions (otherwise we won't know
if FOO() is a call to a local named expression or a standard function FOO).

Below is my reasoning - anyone have a complaint about this?
I'd like to hear from Eike in particular, since he helped write the
current syntax material, but ANYONE is very welcome to pipe up!!

--- David A. Wheeler



==========================================

Let me walk through each requirement I postulated earlier,
and show that I think this tweak meets the requirements.

> Let's try to identify potential requirements for the syntax, that may help.
> We need to be able to disambiguate between:
> * Named expressions, which may be attached to a particular sheet (table) or not

NamedExpression                  # NamedExpression in current sheet
'Sheet1'.NamedExpression      # NamedExpression in Sheet1

If the other sheet has been deleted, so you now have an error like #REF.
That's not a problem:
#REF?.NamedExpression


> * Cell references (okay, we handle that already with [], but let's note that), current
>    sheet or a different sheet

Already handled:
[.A1]
[Sheet.A1] or ['Sheet'.A1]
[#REF.A1]

> * Both of the above MAY be in a different external spreadsheet.  Traditionally
>    you can only reference named expresssions, but the syntax shouldn't be
>    limited that way.

'http://stuff'#NamedExpression
'http://stuff'#'Sheet1'.NamedExpression
['http://stuff'#.A1]
['http://stuff'#Sheet.A1] or ['http://stuff'#'Sheet'.A1]
['http://stuff'##REF.A1]

Note that ## is how you refer to an external spreadsheet, whose
sheet you know has been removed.  Not exactly a common case,
but we can handle it.

> * In OpenDocument, tables may contain other tables.  Spreadsheet apps don't
>    necessarily implement spreadsheet tables in tables, but they CAN, and the
>    syntax should support it.

[.A1.B2]
[Sheet.A1.B2] or ['Sheet1'.A1.B2]

OpenDocument allows subtables, not entire sets of sheets in a cell...
but sheets in cells _is_ a plausible extension, and I'd like to be
able to expand to that case.  And we can; after the
first sheetname, you need to include the '...':
 [Sheet.'Sheet2'.B2] or [Sheet.'Sheet2'.B2]
That way, you know that 'Sheet2' is a sheet, not column SHEET row 2.

> * Function calls, which may have 'structure'. (ORG.OPENOFFICE.ARABIC()).

ORG.OPENOFFICE.ARABIC("III")

No prefix means it's a function call.

If people want more information when they read the first character,
we could permit or require an "@" character in front of function names.
Certainly that tradition has a long lineage!  But I don't see the necessity
for it.

> * Able to expand to allow named expressions to serve as function calls (future).

We probably ought to allow initial '.' followed by a named expression as
"named expression in current sheet". That would unambiguously mean
"this is a named expression".

That'd be particularly important in the future when we
allow named expressions to be used as (local) functions.  In fact, we may
as well allow that syntax now (presuming that NamedExpression cannot begin
with a digit... which it can't):
 .NamedExpression
We don't need this form unless we allow function calls on named parameters,
but we DO need them if we do.  Otherwise, if version 1.8 adds function "FOO",
then we wouldn't know if FOO() meant the built-in function or to call the
named expression named FOO.  The disambiguation rule should probably be
"always call the built-in function".  Then, to call a named expression
as a function, starting the search for the expression from the current sheet,
you'd do this:
 .NamedExpression(5)


> * It should be a clean and easy syntax, one that's easy to read and parse.
>    Preferably without a lot invention.  Often having an initial unique character
>    makes parsing very easy.

Hopefully this proposal meets this criteria too.

It's an incredibly minor tweak to the syntax: when adding a sheet prefix
to a named expression, the sheet name has to be in '...'.
I think that works!

--- David A. Wheeler