How to link to external data: not the formula but the cell contents. python code for formula to value

[parsely]

The 25 departments of a society report each year their financial needs for their maintenance program using a fixed template. The annual reports can be regarded as a snap-shot and frozen set of data.
At the headquarters an overview of the 25 datasets need to be generated. This can be arranged by links to the 25 files. However, almost all the source cells contain formulas: sum(…:…)

I have read post https://ask.libreoffice.org/t/calc-problem-linking-to-data-in-another-spreadsheet/7275.
In this non-dynamic case also opening each file and (data>calulate>) convert formula to value serves the desired result.

If other shortcuts are about as laboursome, suggestions for a python macro performing the ‘formula to value’ action are welcome.

[elmau]

Only sets the cell value as the value of the source cell, Value for numbers, String for text.

For example, if in A1 exists a formula ans result is a value.

doc = XSCRIPTCONTEXT.getDocument()
sheet = doc.CurrentController.ActiveSheet

source = sheet['A1']
target = sheet['B1']

target.Value = source.Value

[parsely]

It is possible to update an external link by value. The sheetproperty sheet.LinkMode controls this. It is equivalent to the formula to value for selected cells issued in the GUI.
This action is irreversible, once the document is stored. To keep the sourcefiles dynamic, the procedure is therefore to load the source document(s), set the linkmode to VALUE for all sheets, and store the source file(s) with a suffix.
In the target file, the right external links needs to be inserted, using the source filenames with suffix. (first run createUpdateSource to create the suffix files)

At any time, run linkByValue to obtain the most updated results from the source files and update all links in the target file. @GRMOTT , FYI

import uno

ctx = uno.getComponentContext()
smgr = ctx.ServiceManager
dispatcher = smgr.createInstanceWithContext(“com.sun.star.frame.DispatchHelper”, ctx)
StarDesktop = smgr.createInstanceWithContext(“com.sun.star.frame.Desktop”, ctx)

from com.sun.star.sheet.SheetLinkMode import VALUE as LINKVALUE, NORMAL as LINKNORMAL

def createOrUpdateSource():
sFiles = [ “~/source.ods”, “~/target.ods”]
for i, sFile in enumerate(sFiles[:-1]):

    sText = uno.systemPathToFileUrl( sFile)
    oDoc = StarDesktop.loadComponentFromURL(sText,"_blank",0,() )
    oSheets = oDoc.getSheets()
    for j in range(oSheets.Count):
        oSheet = oSheets.getByIndex(j)
        oSheet.LinkMode =LINKVALUE
    n = sText.rfind(".")
    sText = sText[:n] + "_tmp" + sText[n:]
    oDoc.storeAsURL( sText , () )
    oDoc.close( True )

return sFiles

def linkByValue():

sFiles = createOrUpdateSource()

sText = sFiles[len(sFiles)-1]
sText = uno.systemPathToFileUrl( sText)
oDoc = StarDesktop.loadComponentFromURL(sText,"_blank",0,() )

oAreaLinks = oDoc.AreaLinks
for i in range(oAreaLinks.Count):
    oLink = oAreaLinks.getByIndex(i)
    oLink.refresh()

msgbox("done")

[parsely]

Work in progress: the above works for documents with only one sheet. If more than one sheet is present, all sheets with an index>0 become a copy of sheet(0).
I regard the linkvalue as a sheet property that ‘enables the linking of the (source) sheet and controls whether formulas are copied.’ If the value is SheetLinkMode::NORMAL, formulas are copied. With SheetLinkMode::VALUE, only results of formulas are used.
(From the API setLinkMode )