Could TYPEIN() compliment the FORMULA() function? ["TYPEIN()" was "TRANSFER()" in the original question.]

Bruce2 · September 28, 2022, 8:11pm

You know a lot more about functions in LibreOffice than I do. So, I’ll ask whether it might be worthwhile to add another function to the more than 500 that we already have. Could we add a function, TYPEIN(), to compliment the action of FORMULA()?

What is a complimentary function? It may be defined as one which reverses the action of another function. The compliment of addition is subtraction and division of multiplication. People use this type of operator to return to a previous state. Remember the 80286 processor protected mode to real-mode switching problems?

We recognize the plethora of functions in current spreadsheets. Could our Office Suite use one more complimentary function?

This question was raised in “[Solved] Reverse “FORMULA” function” at Apache OpenOffice Community Forum - [Solved] Reverse "FORMULA" function - (View topic).

Some people, including me, wish to have a complimentary function to FORMULA(). I would like, from a Donor Document, to update and run a formula in a Receiving Document without calling a macro and without user intervention (ex. “Copy-Paste as unformatted text”) on the Receiving Spreadsheet. Please see DocDonor.ods and DocRecipient_Variable_A_.ods.

Let us say we wanted to add such a function. What would we ask a programmer to do for us?

Form: TYPEIN(Donor_cell, Recipient_cell) to effect TYPEIN(Value_in_one_cell, Formula_in_another_cell)

Donor_cell and Recipient_cell need to be addressed either by Name or by coordinates (A1 notation).

TYPEIN() should follow the OpenFormula standard.

TYPEIN() should support nesting.

TYPEIN() should support standard Error Codes.

TYPEIN() should support the Function Wizard, including the Array checkbox.

TYPEIN() should be a Volatile function.

TYPEIN() should empty Recipient_cell before entering data to it.

DocDonor.ods (32.0 KB)
DocRecipient_Variable_A.ods (44.4 KB)

Villeroy · September 28, 2022, 8:40pm

Accounting on sheets is a highly unprofessional sin.

Every database program has a feature named “TRANSACTION”.

Professional accounting always makes use of some database engine.

Lupp · September 28, 2022, 9:30pm

[edit]
The OriginalQuestioner edited the question and changed the name of the proposed function.
I wouldn’t adapt all my posts every time somebody makes such a mistake.
[/edit]

A Formula returns its result to a cell or (in case of an array-result) To a cell range. Your function can’t comply with this rule.
If the rule is relaxed specifically for you, the (working!) formula in the Recipient_cell would be there only as a part of the result of the formula having executed the TRANSFER() function.Shall it be removed/changed or shall it persist as soon as the mentioned formula gives a different result or sends it to a different Recipient_cell due to changes in its arguments - or simply is removed itself?
You can experiment with a function similar to what you described if you code it as a UserDefinedFunction, accept the persistence of the second action mostly, and restrict the second part of its output (Recipient_cell) to a different sheet. Report your experioences.
You then surely can also give a proposed specification concerning the question what shall happen to the previous content of a cell your TRANFER() pushes a result to.

Bruce2 · September 29, 2022, 3:34pm

Villeroy, I agree, database programs have the feature “TRANSACTION,” and it is useful. Your graphic illustrates your point in a most grace filled way. Thank you. Your GeeksforGeeks reference was unknown to me. Thank you for alerting me to it. I am interested in SQL and have joined the site. I have downloaded material and plan to continue using this site. Again, I appreciate your help.
Lupp, Thank you for your response. A Formula does return its result to a cell (or range, if array). This proposed function does push data onto a cell in the document. Thank you for helping me to better understand the implications and restrictions of our spreadsheet functions. I expect no rule to be relaxed for me. You are helping me work within the rules. Thank you.

This new function should take, for example, the value in A1 and enter the characters of the value into A2 just as though a user had read the string appearing in A1 and typed those characters into A2. Maybe we should name the function TYPEIN() to emphasize that we are pushing data into a cell and to help imply that the data can go into any cell, since a user can type a string of characters into any given cell. In our example in DocRecipient_Variable_A_.ods we would effectively type what we see in cell E76 into E81.

As far as I know, the data a user enters by the keyboard persists in the cell until it is replaced, for example, by re-invoking TYPEIN() for the same Recipient_cell. If the Recipient_cell argument were changed to a Different_Cell, then TYPEIN() would enter the data into the Different_Cell, and the data in the First_Recipient_Cell would remain unchanged. It would operate just like a user entered one set of data into a cell (First_Recipient_Cell) and then entered the same or different data into a second cell (Different_Cell).

We agree that, at the moment, our only option is to code it, with the currently available LibreOffice functions, as a UserDefinedFunction and accept the limitation that the output be placed on a different sheet. How do we get TYPEIN() to avoid these limitations? If I knew how to program in C++, I might be able to program TYPEIN(), but I don’t. So, what can we do to specify this function for a C++ programmer? Maybe we should empty Recipient_cell before entering the characters from Donor_cell into it.

Bruce2 · October 27, 2022, 4:34pm

You say, “A Formula returns its result to a cell or (in case of an array-result) To a cell range.”

How may a programmer set the result of a formula to a variable?

Villeroy · October 27, 2022, 9:36pm

If you can’t find out how to do this, you’ll never write any macro.

Lupp · October 27, 2022, 11:21pm

So I said.

On the surface a formula is something starting with an equal sign. Somebody enters it into a spreadsheet cell (not refusing formula recognition). As soon as this was done the formula got parsed and tokenized in the background to allow for efficient recalculation (roughly) everytime a precedent changed. You see: Working formulas only exist in spreadsheet cells. And many references only have a definite meaning (what’s referenced) based on the position of the formula in a specific sheet and a specific cells. This vital environment is missing in a program, say a Basic macro. Therefore a formula can only exist as a dead string there. Constructs written where calculations shall be performed in programs mostly are called “expressions”. They aren’t recalculated like formulas due to changes in precedents, but when it is their turn during the sequential execution of the program.

In short: Since there is no formula, there is no result, and you can’t assign it to a variable.
What I showed you using the FormulaParser service may be useful in rare cases. You obviously used it. There is no FormulaEvaluator service in the API to Calc - and there are many reasons for the fact.

If there is a living formula in a cell, and you know the cell, and you are sure it isn’t “dirty” (not yet recalculated though precedents changed) , and … you can access that cell and ask it for the resulttype and for the .Value or the .String (probably better read from te .DataArray), and assign what you get to your program variable.
For actual macro programming: Forget it.

Villeroy · October 28, 2022, 12:53pm

By any standard any spreadsheet function must not write anything anywhere.

Bruce2 · January 12, 2023, 4:09pm

You say, “you can access that cell and ask it for the resulttype…"
.
You can see what I have looked at. What should I use to access that cell?
.
FormulaParser_service_details.odt (42.3 KB)

Lupp · January 12, 2023, 5:36pm

Are you sure you are not just chasing a pet idea (may also be a pipe dream) that has no value in the practice of programming for spreadsheet tasks?

Anyway. For whatever reason you want to access a cell object, you will use a variable representing the cell. Explicitly excluding what only is available under some kind of support for alien Basic (VBA) I know three methods capable of returning such an object: getCellByPosition(), getCellRangeByPosition(), and getCellRangeByName(). Each of these is usable if you already have an object supporting the service com.sun.star.sheet.SheetCellRange, and actually containing the respective cell. Such objects can be complete sheets or rectangular parts of any sheet. As a typical example you can find

theDoc = ThisComponent REM If running for a spreadsheet document
theSheet = theDoc.Sheets(2) REM For the third contained sheet
theCell = theSheet.getCellByPosition(3, 5) REM for the cell in the 4th columnn, 6th row of that sheet (D6).

You should actually study the famous texts by Andrew Pitonyak and consult LibreOffice: Main Page with its support for searches. No contributor here will write a new introduction to programming specifically assigned only to you like the gate to the law in Kafka’s parable to the requesting man.

Bruce2 · January 12, 2023, 8:47pm

Lupp, thank you for your response. In FormulaParser_service_details, section 1.1.2.3 XCellRangeReferrer, there is a reference to ::com::sun:: star::[table]::[XCellRange]. interface XCellRange has the three methods you so kindly mentioned – getCellByPosition(), getCellRangeByPosition(), and getCellRangeByName(). Thank you for finding those for us.
.
Could it be that this seemingly delusional, quixotic quest is a novel, simple, incremental improvement to LibreOffice calc and that the difficulties we are having are due to my lack of skill in LibreOffice? Could it be that we are actually not looking, not to run macros, but to change the base code?
.
Thanks for the Kafka reference.

Lupp · September 30, 2022, 10:40am

I don’t intend to use the “solution” contained in the attached example file myself. There are only six lines of working Basic code, but also commemts.
I made the example for purely theoretical interest remembering a recent case of using the FormulaParser service. No assurances of any kind!
relocateFormula.ods (15.3 KB)

Bruce2 · September 30, 2022, 9:28pm

Thank you, Lupp. I did not think this was possible using such a short and generalized macro. Very insightful solution. Of course, we are dependent on the FormulaParser service. Aren’t we all dependent on something. Today, for example, I depend on my house to shield my wife and I from hurricane Ian.

I appreciate well commented code. Also, I enjoyed the disclaimer. Your solution is one I never would have found. Grateful am I for your gracious help. Bruce

Lupp · September 30, 2022, 9:50pm

Hope at least your lives are safe. I was terrified by the hurricane news.
And I’m terrified again and again by “political” news which actually are about criminal tragedies.
Humans should add less to overall horror. How to achieve that?

Bruce2 · October 1, 2022, 9:14am

Dear Wolfgang,
Did my enthusiasm run away with me? I fear it did. I apologize to you.

Only after I wrote my post did I realize, did I realize that I had not looked carefully enough to understand everything you so kindly wrote. So, here is a graphic showing what I do not yet understand.

Am I to understand that the formulae with the magenta font color in column AA correspond to “…DocRecipient_Variable_A_.ods’#$DonorSheet.E81?”
The cells corresponding to “…DocRecipient_Variable_A_.ods’#$DonorSheet.E76?” are not obvious to me. What am I missing?

I am distressed more than I can say to have to write this. You have been so very gracious to be kind enough to help me understand this problem. Right now I do not know whether I am looking at the solution and just do not understand what I should see or whether we are so close to solving this, but not quite there yet.

In either case, I feel I have failed as your assistant in this effort. You have been wonderful. Thank you so much.

Warmest regards,
Bruce

Lupp · October 1, 2022, 5:33pm

I won’t comment on sheets I don’t know (have at hand). Please don’t attach images, but always the “real thing” (an .odf file giving the example) If not there is an issue specifically concerning a view.

In this case I completed my example with some explanations:
relocateFormulaPlusEx.ods (25.6 KB)

Bruce2 · October 3, 2022, 5:18pm

Lupp, here are the Documents I did not send earlier. I also include relocateFormulaPlusEx.ods with comments.
.
BRAVO.
.
relocateFormula_modified.ods (31.2 KB)
relocateFormulaPlusEx_annotated.ods (30.1 KB)
DocRecipient_Variable_A_.ods (43.4 KB)

Bruce2 · December 7, 2022, 10:21pm

Lupp, you say, “If there is a living formula in a cell … you can access that cell and ask it for the resulttype and for the .Value or the .String (probably better read from the .DataArray), and assign what you get to your program variable.” And I believe you.

In LibraOffice Help searching on .Value, .String and .DataArray did not result in anything I recognozed as useful. I also looked at the following four things.

Using Calc Functions in Macros

http s://help.libreoffice.org/7.4/en-US/text/sbasic/shared/calc_functions.html?&DbPAR=SHARED&System=WIN
com::sun::star Module Reference

the module com::sun::star is the root module of the UNO API

http s://api.libreoffice.org/docs/idl/ref/namespacecom_1_1sun_1_1star.html

CreateUnoService Function

Instantiates a Uno service with the ProcessServiceManager

http s://help.libreoffice.org/6.4/en-US/text/sbasic/shared/03131600.html?DbPAR=BASIC

FunctionAccess Service Referencepublished

allows generic access to all spreadsheet functions.

http s://api.libreoffice.org/docs/idl/ref/servicecom_1_1sun_1_1star_1_1sheet_1_1FunctionAccess.html

A Google search on .DataArray or DataArray yields at least two results.

DataArray provides a wrapper around numpy ndarrays that uses labeled dimensions and coordinates to support metadata aware operations

http s://docs.xarray.dev/en/stable/generated/xarray.DataArray.html

Xarray is a python library that lets us create N-dimensional arrays just like numpy but it let us name the dimension of the N-dimensional array as well.

http s://coderzcolumn.com/tutorials/python/xarray-guide-to-labeled-array-dataarray

At https://www.openoffice.org, “Your search - .DataArray site:https://www.openoffice.org - did not match any documents.”

However, a search on DataArray yields more than these four results

On the module sheet, we see these possible prospects.

module sheet: Spreadsheet specific interfaces.

Services

AccessibleCell The accessible view of a spreadsheet document;

Cells represents a collection of used cells in a spreadsheet document;

FormulaParser , (Generic. converts between text and token representations of formulas ) (https://www.openoffice.org/api/docs/common/ref/com/sun/star/sheet/FormulaParser.html)

SheetCell represents a single addressable cell in a spreadsheet document.

Included Services - Summary

:com::sun::table::Cell provides basic cell handling and contributes common cell formatting properties

Exported Interfaces - Summary

::com::sun::document::XActionLockable provides methods to lock and unlock the update between ::com::sun::table::XCell and ::com::sun::text::XText.

::com::sun::text:XTextFieldsSupplier: provides access to the text fields used in this cell. (getTextFields )

Properties’ Summary

string FormulaLocal [ OPTIONAL ] contains the formula string with localized function names. (https://www.openoffice.org/api/docs/common/ref/com/sun/star/sheet/SheetCell.html#FormulaLocal)

[ readonly ] long FormulaResultType contains the result type of a formula (VALUE, STRING, ERROR).

(https://www.openoffice.org/api/docs/common/ref/com/sun/star/sheet/SheetCell.html#FormulaLocal)

[ readonly ] string AbsoluteName [ OPTIONAL ] Returns the absolute adress of the range as string, e.g. “$Sheet1.$B$2”.

.

service SheetCell https://www.openoffice.org/api/docs/common/ref/com/sun/star/sheet/SheetCell.html

Interfaces

XArrayFormulaRange provides handling of array formulas in a cell range.

XArrayFormulaTokens gives access to an array formula as token sequence.

XCalculatable represents something that can recalculate.

XCellAddressable represents a cell which can be addressed with a ::com::sun::table::CellAddress Sheet, Column, Row

XCellRangeData allows to get and set an array of data from a cell range. getDataArray gets an array from the contents of the cell range. setDataArray fills the cell range with values from an array. (https://www.openoffice.org/api/docs/common/ref/com/sun/star/sheet/XCellRangeData.html) (uses of interface XCellRangeData: General. https://www.openoffice.org/api/docs/common/ref/com/sun/star/sheet/XCellRangeData-xref.html)

XCellRangeFormula allows to get and set cell contents (values, text or formulas) for a cell range.

XCellRangeReferrer allows direct access to the cells in a named range or to the cells which are visible in a view, without the need to get the document object first.

XCellRangesAccess provides access to the cells or to sub-ranges of all sheets.

XFormulaParser converts between text and token representations of formulas. parses a formula into tokens. converts a formula into a string.

XFormulaQuery provides methods to query cells for dependencies in formulas.

XFormulaTokens gives access to a formula as token sequence.

XSheetCellRange provides access to the spreadsheet that contains a cell range.

XSheetOperation provides methods to execute operations on a cell range or ranges.

XSolverDescription gives access to user-visible strings for a solver. Returns a short description for a bound, constrained, or free property.

XSpreadsheetDocument provides access to a collection of spreadsheets.

XSpreadsheetView is the main interface of a SpreadsheetView. It manages the active sheet within this view.

Structs

ComplexReference contains a reference to a cell range.

FormulaToken contains a single token within a formula. (OpCode of the token. Additional data in the token

https://www.openoffice.org/api/docs/common/ref/com/sun/star/sheet/FormulaToken.html)

FunctionArgument contains the description of a single argument within a spreadsheet function.

SingleReference contains a reference to a single cell (Column, RelativeColumn, Row, RelativeRow, Sheet, RelativeSheet, Flags).

FillDirection used to specify the direction of filling cells, for example, with a series

GeneralFunction used to specify a function to be calculated from values.

PasteOperation used to specify which operations are carried out when pasting cell values into a cell range.

SheetLinkMode used to specify how a sheet is linked to another sheet.

ValidationAlertStyle used to specify how invalid cell contents are treated.

Constant Groups

AddressConvention These constants specify which address convention to use in the formula parser. Each variation specifies a different cell and cell range address syntax.

CellFlags These constants select different types of cell contents.

DataResultFlags: specify the result type of one element in the data pilot data array – HAS DATA,

SUBTOTAL, ERROR. (https://www.openoffice.org/api/docs/common/ref/com/sun/star/sheet/DataResultFlags.html)

FormulaMapGroup Constants of bit masks used with ::XFormurmulaOpCodeMapper::getAvailableMappings to specify for which group of symbols the mappings are to be obtained. If no bit is set, a sequence of special mappings is returned in the order that is defined by FormulaMapGroupSpecialOffset. Note that the special group and other groups are mutual exclusive.

FormulaResult used to select different result types of cell formulas

ReferenceFlags defines flags for references.

[TableValidationVisibility](http s://www.openoffice.org/api/docs/common/ref/com/sun/star/sheet/TableValidationVisibility.html) These constants specify whether and how a list of possible values of a cell should be shown.

.

http s://www.openoffice.org/api/docs/common/ref/com/sun/star/sheet/module-ix.html

A search on resulttype yields at least three results.

MyBatis - lists of primitive types

http s://stackoverflow.com/questions/9179890/mybatis-lists-of-primitive-types

ResultType.UUID_LIST

http s://programtalk.com/java-more-examples/ResultType.UUID_LIST/

XPathResult.resultType

http s://developer.mozilla.org/en-US/docs/Web/API/XPathResult/resultType

Lupp, you have given me excelllent advice. I appreiate your help. To follow your excellent, technical references, I need something a little more detailed.