I had the need to do a little bit of automation in a LibreCalc spreadsheet based on an event trigger and have been struggling a bit. I installed APSO and have actually gotten my macro to work, but with some Band-Aids and/or ugly pieces of code. The first ugliness came when attempting to assign a formula to a cell:
sheet = XSCRIPTCONTEXT.getDocument().Sheets["Transaction Register"]
sheet[2,5].Formula = '=IF(ISBLANK($D$3),"",LOOKUP($D$3,\'Account Summary\'.$A$5:$A$65,\'Account Summary\'.$F$5:$F$65))'
This snippet results in the formula being assigned to the cell, but the cell reports “Err:508” even though the value appears to be completely valid. A simple manual edit of the placed formula (say, adding a space at the end of the formula) clears up the error and displays the corrected calculated result, so the formula is not actually wrong, even though I must be doing something wrong. I’d appreciate some advice.
I ended up working around this by manually recording the formula I wanted to assign elsewhere and then just copying it in the macro, like this:
sheet[2,5].Formula = sheet[2,6].Formula
This works, but I’d still like to know the correct way to do this.
The second piece of ugliness is that I originally wrote the assignment as above before I found an example that showed that I could use “A1 notation” to reference a cell or range, so I instead wrote:
sheet["F3"].Formula = sheet["G3"].Formula
which I think is unarguably easier to understand.
The final ugliness that I’ve had to code so far in my brief adventures in macroland is that, having discovered the above, I was sure that there must be a method to return an A1 notation string for a cell or a cell range object, but I couldn’t find it. So I wrote my own.
Which leads me to my second question. Could someone give me some pointers on how to discover all of the methods that these objects possess? I’m not completely fluent in Python yet; maybe this would be obvious if I were; but I’d appreciate some quick pointers.
Thanks in advance for the help.
- John