Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.javascript > #35580 > unrolled thread

Script in a Spreadsheet - how?

Back to article view | Back to comp.lang.javascript

Contents

  Script in a Spreadsheet - how? Dr J R Stockton <J.R.Stockton@physics.org> - 2018-09-11 01:31 -0700
    Re: Script in a Spreadsheet - how? "Evertjan." <exxjxw.hannivoort@inter.nl.net> - 2018-09-11 11:03 +0200
      Re: Script in a Spreadsheet - how? Dr J R Stockton <J.R.Stockton@physics.org> - 2018-09-11 03:19 -0700
        Re: Script in a Spreadsheet - how? "Evertjan." <exxjxw.hannivoort@inter.nl.net> - 2018-09-11 14:17 +0200
          Re: Script in a Spreadsheet - how? Dr J R Stockton <J.R.Stockton@physics.org> - 2018-09-11 05:54 -0700
    Re: Script in a Spreadsheet - how? Julio Di Egidio <julio@diegidio.name> - 2018-09-11 03:41 -0700
      Re: Script in a Spreadsheet - how? "Evertjan." <exxjxw.hannivoort@inter.nl.net> - 2018-09-11 14:22 +0200
        Re: Script in a Spreadsheet - how? Dr J R Stockton <J.R.Stockton@physics.org> - 2018-09-11 06:31 -0700
      Re: Script in a Spreadsheet - how? Ben Bacarisse <ben.usenet@bsb.me.uk> - 2018-09-11 15:46 +0100
        Re: Script in a Spreadsheet - how? Dr J R Stockton <J.R.Stockton@physics.org> - 2018-09-11 11:06 -0700

#35580 — Script in a Spreadsheet - how?

Script in a Spreadsheet - how?

I want to enter numbers manually in columns B and D,
with column C row N then being calculated as
          C[N] = C[N-1] + B[N] - D[N]
until line N is empty or otherwise marks the end -
and I have no idea how to code that in a
spreadsheet.

I can use Basic, but prefer JavaScript.

There will be roughly one line per week, starting
in 2013, so the calculation should be fast enough
and the sheet should have plenty of lines.

I have LibreOffice 6.0.

        I do recall that, many years ago, I tested
        Microsoft DatePart in Excel to see whether
        it would give ISO8601 week numbers.  It did,
        but with three errors per 28 years and another
        one every 400 years - no surprise there.

-- 
  (c) John Stockton, near London, UK.  Using Google Groups.           |
 Mail: J.R.""""""""@physics.org - or as Reply-To, if any.             |

[toc] | [next] | [standalone]

#35581

Dr J R Stockton <J.R.Stockton@physics.org> wrote on 11 Sep 2018 in 
comp.lang.javascript:

> Script in a Spreadsheet - how?
> 
> I want to enter numbers manually in columns B and D,
> with column C row N then being calculated as
>           C[N] = C[N-1] + B[N] - D[N]
> until line N is empty or otherwise marks the end -
> and I have no idea how to code that in a
> spreadsheet.
> 
> I can use Basic, but prefer JavaScript.
> 
> There will be roughly one line per week, starting
> in 2013, so the calculation should be fast enough
> and the sheet should have plenty of lines.
> 
> I have LibreOffice 6.0.
> 
>         I do recall that, many years ago, I tested
>         Microsoft DatePart in Excel to see whether
>         it would give ISO8601 week numbers.  It did,
>         but with three errors per 28 years and another
>         one every 400 years - no surprise there.

I would manipulate a .csv file,
as that can easily be scripted by FileSystemObject coding in JS.

[Or, more standard, use VBA in Excel.]

-- 
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

[toc] | [prev] | [next] | [standalone]

#35582

On Tuesday, September 11, 2018 at 10:03:27 AM UTC+1, Evertjan. wrote:
> Dr J R Stockton wrote on 11 Sep 2018 in comp.lang.javascript:
> 
> > Script in a Spreadsheet - how?
> > ...

> I would manipulate a .csv file,
> as that can easily be scripted by FileSystemObject coding in JS.
> 
> [Or, more standard, use VBA in Excel.]

But that does not answer the question - I want to know how
to insert or connect the code, and how to address an entry.
I don't want to address the File System, other than by
opening and saving the sheet.  I want to edit in the sheet.

It seems likely that an example in any combination of sheet
and language would help sufficiently.

-- 
  (c) John Stockton, near London, UK.  Using Google Groups.           |
 Mail: J.R.""""""""@physics.org - or as Reply-To, if any.             |

[toc] | [prev] | [next] | [standalone]

#35590

Dr J R Stockton <J.R.Stockton@physics.org> wrote on 11 Sep 2018 in 
comp.lang.javascript:

> On Tuesday, September 11, 2018 at 10:03:27 AM UTC+1, Evertjan. wrote:
>> Dr J R Stockton wrote on 11 Sep 2018 in comp.lang.javascript:
>> 
>> > Script in a Spreadsheet - how?
>> > ...
> 
>> I would manipulate a .csv file,
>> as that can easily be scripted by FileSystemObject coding in JS.
>> 
>> [Or, more standard, use VBA in Excel.]
> 
> But that does not answer the question - I want to know how
> to insert or connect the code, and how to address an entry.
> I don't want to address the File System, other than by
> opening and saving the sheet.  I want to edit in the sheet.

Nonsense, John.

A [closed] spreadsheet is a file.

You can edit a closed [by your spreadsheet viewer] spreadsheet by 
manipulating the file, and that can be done with JS using FileSystemObject,
run the js in cscript or wscript.

Storing the spreadsheet as an .csv makes that much easier than storing it as 
an .xlsx, as you do not need to know the specs of excel files.   

> It seems likely that an example in any combination of sheet
> and language would help sufficiently.

Not so, a spreadsheet is just a concept, they need to be available in the 
form of a file or other memory-form to be manipulated.

Just like a language needs an execution engine to be useful,
and such engine need to have access to the factual spreadsheet.
What engine did you have in mind for your JS-code?

-- 
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

[toc] | [prev] | [next] | [standalone]

#35593

On Tuesday, September 11, 2018 at 1:17:29 PM UTC+1, Evertjan. wrote:
> Dr J R Stockton wrote on 11 Sep 2018 in comp.lang.javascript: 
> > On Tuesday, September 11, 2018 at 10:03:27 AM UTC+1, Evertjan. wrote:

> Just like a language needs an execution engine to be useful,
> and such engine need to have access to the factual spreadsheet.
> What engine did you have in mind for your JS-code?

Whatever engine LibreOffice likes to connect to, or contains.  JScript and VBScript engines should be within reach.  For my own use, an Algol 60 engine would be nice (I have the Algol 60 Report, and used to know one of those who had written it); but I want something that is in Windows 7 and later, for compatibility.

-- 
  (c) John Stockton, near London, UK.  Using Google Groups.           |
 Mail: J.R.""""""""@physics.org - or as Reply-To, if any.             |

[toc] | [prev] | [next] | [standalone]

#35584

On Tuesday, 11 September 2018 10:31:22 UTC+2, Dr J R Stockton  wrote:
> Script in a Spreadsheet - how?
> 
> I want to enter numbers manually in columns B and D,
> with column C row N then being calculated as
>           C[N] = C[N-1] + B[N] - D[N]
> until line N is empty or otherwise marks the end -
> and I have no idea how to code that in a
> spreadsheet.

Are you kidding??  You don't need any scripting for that simple formula.

Anyway, LibreOffice does not support JS scripting, only some BASIC:

<https://help.libreoffice.org/6.0/en-GB/text/sbasic/shared/main0601.html>

Hit F1 for a change...

Julio

[toc] | [prev] | [next] | [standalone]

#35591

Julio Di Egidio <julio@diegidio.name> wrote on 11 Sep 2018 in 
comp.lang.javascript:

> On Tuesday, 11 September 2018 10:31:22 UTC+2, Dr J R Stockton  wrote:
>> Script in a Spreadsheet - how?
>> 
>> I want to enter numbers manually in columns B and D,
>> with column C row N then being calculated as
>>           C[N] = C[N-1] + B[N] - D[N]
>> until line N is empty or otherwise marks the end -
>> and I have no idea how to code that in a
>> spreadsheet.
> 
> Are you kidding??  You don't need any scripting for that simple formula.

Ofcourse you need scripting, even if your spreadsheet programme does such 
scripting for you with some clicks or stores it as a macro.

> Anyway, LibreOffice does not support JS scripting, only some BASIC:
> 
> <https://help.libreoffice.org/6.0/en-GB/text/sbasic/shared/main0601.html>




-- 
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

[toc] | [prev] | [next] | [standalone]

#35594

On Tuesday, September 11, 2018 at 1:23:18 PM UTC+1, Evertjan. wrote:
> Julio Di Egidio wrote on 11 Sep 2018 in comp.lang.javascript:
> > On Tuesday, 11 September 2018 10:31:22 UTC+2, Dr J R Stockton  wrote:
> >> Script in a Spreadsheet - how?
> >> 
> >> I want to enter numbers manually in columns B and D,
> >> with column C row N then being calculated as
> >>           C[N] = C[N-1] + B[N] - D[N]
> >> until line N is empty or otherwise marks the end -
> >> and I have no idea how to code that in a
> >> spreadsheet.
> > 
> > Are you kidding??  You don't need any scripting for that simple formula.
> 
> Of course you need scripting, even if your spreadsheet programme does such 
> scripting for you with some clicks or stores it as a macro.

You have been underestimating my ignorance of spreadsheet
 programming, and so it is probably without realising it
 that you have been useful !!  Perhaps a macro covering
 the whole sheet could do it.

> > Anyway, LibreOffice does not support JS scripting, only some BASIC:
> > 
> > <https://help.libreoffice.org/6.0/en-GB/text/sbasic/shared/main0601.html>


Basic is acceptable; you may have seen my VBScript Maths & Date
 pages, which would remind me sufficiently of the grammar.

I want the C column to be updated, from Row N onwards, after
 any change in fields B and D of Row N.

I can, of course, do that with JavaScript in a *local* HTML page.
 I can read any text data, including CSV, from a drive into that
 page, provided that the browser is as current Firefox - but I
 do not know how to write that back to a drive onWindowClose.

-- 
  (c) John Stockton, near London, UK.  Using Google Groups.           |
 Mail: J.R.""""""""@physics.org - or as Reply-To, if any.             |

[toc] | [prev] | [next] | [standalone]

#35595

Julio Di Egidio <julio@diegidio.name> writes:

> On Tuesday, 11 September 2018 10:31:22 UTC+2, Dr J R Stockton  wrote:
>> Script in a Spreadsheet - how?
>> 
>> I want to enter numbers manually in columns B and D,
>> with column C row N then being calculated as
>>           C[N] = C[N-1] + B[N] - D[N]
>> until line N is empty or otherwise marks the end -
>> and I have no idea how to code that in a
>> spreadsheet.
>
> Are you kidding??  You don't need any scripting for that simple formula.
>
> Anyway, LibreOffice does not support JS scripting, only some BASIC:
>
> <https://help.libreoffice.org/6.0/en-GB/text/sbasic/shared/main0601.html>

<https://help.libreoffice.org/6.0/en-GB/text/shared/guide/scripting.html>

says otherwise.

To the OP: You probably don't need scripting.  This short of thing is
usually done by copying a formula, though scripting it can make it
automatic.  And I've never had much luck with JavaScript and LibreOffice
calc.  I've not tried hard, but even the examples seem to go wrong.  I
suspect the API is not maintained in the same way that Basic one is.

-- 
Ben.

[toc] | [prev] | [next] | [standalone]

#35598

On Tuesday, September 11, 2018 at 3:46:45 PM UTC+1, Ben Bacarisse wrote:
> Julio Di Egidio writes:
> 
> > On Tuesday, 11 September 2018 10:31:22 UTC+2, Dr J R Stockton  wrote:
> >> Script in a Spreadsheet - how?
> >> 
> >> I want to enter numbers manually in columns B and D,
> >> with column C row N then being calculated as
> >>           C[N] = C[N-1] + B[N] - D[N]
> >> until line N is empty or otherwise marks the end -
> >> and I have no idea how to code that in a
> >> spreadsheet.
> >
> > Are you kidding??  You don't need any scripting for that simple formula.
> >
> > Anyway, LibreOffice does not support JS scripting, only some BASIC:
> >
> > <https://help.libreoffice.org/6.0/en-GB/text/sbasic/shared/main0601.html>
> 
> <https://help.libreoffice.org/6.0/en-GB/text/shared/guide/scripting.html>
> 
> says otherwise.
> 
> To the OP: You probably don't need scripting.  This short of thing is
> usually done by copying a formula, though scripting it can make it
> automatic.  And I've never had much luck with JavaScript and LibreOffice
> calc.  I've not tried hard, but even the examples seem to go wrong.  I
> suspect the API is not maintained in the same way that Basic one is.

Well, what I really wanted was a script in a programming
language so that I can *view* it all at once in an editor
... it would be overkill for this job, but with potential
for others.

But, as I've now more-or-less understood relative addressing
 and copying handles, it seems that all I need is to put in
 cell C1 a zero, then in cell C2 the formula =C1+B2-D2, pull
the handle of C2 far enough down, and it seems to work.  To
explain, column A is the date (as a *string* YYYY-MM-DD), B
is what she spent for me, C is what I owe her, D is what I've
given her, in cash or goods, and E is a descriptive string.

It is working !

I now want to be able to lock cell C1 and all of the formulae,
 and to import the whole of the data, somehow ....

-- 
  (c) John Stockton, near London, UK.  Using Google Groups.           |
 Mail: J.R.""""""""@physics.org - or as Reply-To, if any.             |

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.javascript

csiph-web