Write custom formulae in Libreoffice Calc with Python

Context

What if you wanted to generate values in Column B using values from Column A (on your Libreoffice spreadsheet), based on some complex logic? For instance, let’s say that my Column A contains values in JSON format (why not 😅?) and I w…


This content originally appeared on DEV Community and was authored by Gokulakrishnan Shankar

Context

What if you wanted to generate values in Column B using values from Column A (on your Libreoffice spreadsheet), based on some complex logic? For instance, let's say that my Column A contains values in JSON format (why not 😅?) and I want to extract only values of a particular field into Column B.

LibreofficePython_Task
This can be done in 3 ways:

  • Use existing functions (In this case, that would be REGEX, but what if want more control in deciding the logic?)
  • Write custom functions
    • Define the function in Libreoffice Basic (You can think of Basic as Libreoffice's own programming-suite, but is it really worth learning a new language for such a simple use-case?)
    • Define the function in Python and integrate it with Libreoffice (Yep, this is much more easier)

Pre-requisites

To use Python functions in Libreoffice, you need to have the python script-provider for Libreoffice installed. To check if your version of Libreoffice already has this feature, go to Tools → Macros → Organize Macros - you should see the option, Python.

LibreofficePython_CheckingMacro
If not, (i.e.), if you only see the option Basic, you will need to install this script provider as follows:

sudo apt install libreoffice-script-provider-python

You should now be able to see Python alongside Basic under the Organize Macros option.

Let's get to it!

Deciding the type of Macro

Roughly put, a macro is any script that you use for automating tasks (via GUIs, functions, etc.) in Libreoffice. There are 3 types of Macros based on where the scripts reside.

Type Location (Linux) Accessibility
User Macros /home/USER/.config/libreoffice/4/user/Scripts/python Only current user
System Macros /usr/lib/libreoffice/share/Scripts/python/ All users
Document Macros Inside the spreadsheet-file (Yes, you can extract spreadsheet files 🤯) Only this document

For this post, I'm going to choose to create a user macro. If the path for the user script does not exist, you can go ahead and create it with mkdir

Note: To create document macros, some extra steps are needed. Have a look at the "Installing the Libreoffice python script provider" link in the References section.

Writing the Python function

  • Create the file Custom.py (it can be any name), inside /home/USER/.config/libreoffice/4/user/Scripts/python.
  • Define your function - in this case, I would supply the stringified JSON and the field name as input and return the value of the specified field. 
import json

def extract_from_json(stringified_json, key):
    mapping = json.loads(stringified_json)
    return mapping.get(key, "")

Setting up Libreoffice to use the Python function

Check if your function is visible

  • In Libreoffice Calc, go to Tools → Macros → Python.
  • In the now-opened "Python Macros" window, click on My Macros → [your python script filename]. You should see the list of functions present inside that script.

LibreofficePython_CheckingFunction

  • If the options on the right side are greyed-out, don't worry about them. You wouldn't need to use them much anyway.

Setting up a formula to use the function

Remember when we chucked out using Libreoffice Basic to write custom formulae? Turns out we still need to use a small part of it 😅.

  • First we need a name for the formula that we are actually going to use in our spreadsheet (this can be different from our python function name). Let's say I choose the name EXTRACTFROMJSON.
  • The definition for this formula (which will be written in Basic) should
    • Fetch the necessary function from the python script (this in itself will be a separate Basic function - let's call it getPythonScript)
    • Execute the function and return the results
  • Go to Tools → Macros → Edit Macros and paste the below code to fetch the python script. 
Option Explicit
Option Compatible

Private scr As Object ' com.sun.star.script.provider.XScript

Public Function GetPythonScript(macro As String, _
        Optional location As String) As com.sun.star.script.provider.Xscript
    ''' Grab Python script object before execution
    ' Arguments:
    '    macro   : as "library/module.py$macro" or "module.py$macro"
    '    location: as "document", "share", "user" or ENUM(eration)
    ' Result:
    '    located com.sun.star.script.provider.XScript UNO service'''
    If IsMissing(location) Then location = "user"
    Dim mspf As Object ' com.sun.star.script.provider.MasterScriptProviderFactory
    Dim sp As Object ' com.sun.star.script.provider.XScriptProvider compatible
    Dim uri As String
    If location="document" Then
        sp = ThisComponent.getScriptProvider()
    Else
        mspf = CreateUNOService("com.sun.star.script.provider.MasterScriptProviderFactory")
        sp = mspf.createScriptProvider("")
    End If
    uri = "vnd.sun.star.script:"& macro &"?language=Python&location="& location
    GetPythonScript = sp.getScript(uri)
End Function ' GetPythonScript
  • Next, we define the actual formula. 
Public Function EXTRACTFROMJSON(stringifiedJson As String, key As String) As String
    scr = GetPythonScript("Custom.py$extract_from_json", "user")
    EXTRACTFROMJSON = scr.invoke(Array(stringifiedJson, key), Array(), Array())
End Function
  • The first argument to GetPythonScript should be of the form PythonFileName.py$PythonFunctionName; the second argument is the type of macro ("user" in this case)
  • We use invoke to execute the python function - the first argument of this function is an array of arguments to be passed to our actual python function.
  • Finally, according to the rules of Libreoffice Basic, for a formula to return a value, the syntax is to be specified as FORMULANAME = ReturnValue

Calling the function from the spreadsheet

This is a simple as doing =EXTRACTFROMJSON(A2, "type")

LibreofficePython_ExecutingFormula

References


This content originally appeared on DEV Community and was authored by Gokulakrishnan Shankar


Print Share Comment Cite Upload Translate Updates
APA

Gokulakrishnan Shankar | Sciencx (2024-07-21T13:12:45+00:00) Write custom formulae in Libreoffice Calc with Python. Retrieved from https://www.scien.cx/2024/07/21/write-custom-formulae-in-libreoffice-calc-with-python/

MLA
" » Write custom formulae in Libreoffice Calc with Python." Gokulakrishnan Shankar | Sciencx - Sunday July 21, 2024, https://www.scien.cx/2024/07/21/write-custom-formulae-in-libreoffice-calc-with-python/
HARVARD
Gokulakrishnan Shankar | Sciencx Sunday July 21, 2024 » Write custom formulae in Libreoffice Calc with Python., viewed ,<https://www.scien.cx/2024/07/21/write-custom-formulae-in-libreoffice-calc-with-python/>
VANCOUVER
Gokulakrishnan Shankar | Sciencx - » Write custom formulae in Libreoffice Calc with Python. [Internet]. [Accessed ]. Available from: https://www.scien.cx/2024/07/21/write-custom-formulae-in-libreoffice-calc-with-python/
CHICAGO
" » Write custom formulae in Libreoffice Calc with Python." Gokulakrishnan Shankar | Sciencx - Accessed . https://www.scien.cx/2024/07/21/write-custom-formulae-in-libreoffice-calc-with-python/
IEEE
" » Write custom formulae in Libreoffice Calc with Python." Gokulakrishnan Shankar | Sciencx [Online]. Available: https://www.scien.cx/2024/07/21/write-custom-formulae-in-libreoffice-calc-with-python/. [Accessed: ]
rf:citation
» Write custom formulae in Libreoffice Calc with Python | Gokulakrishnan Shankar | Sciencx | https://www.scien.cx/2024/07/21/write-custom-formulae-in-libreoffice-calc-with-python/ |

Please log in to upload a file.



There are no updates yet.
Click the Upload button above to add an update.

You must be logged in to translate posts. Please log in or register.

Related Posts