Write custom formulae in Libreoffice Calc with Python
Gokulakrishnan Shankar
Posted on July 21, 2024
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.
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.
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. ```python
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.
- 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 :sweat_smile:.
- 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.
```visualbasic
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. ```visualbasic
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")`
## References
- [Installing the Libreoffice python script provider](https://wiki.documentfoundation.org/Macros/Python_Guide/Introduction#Installation)
- [Calling Python scripts from Basic](https://help.libreoffice.org/7.3/en-US/text/sbasic/guide/basic_2_python.html?DbPAR=BASIC)
- [Sample JSON data](https://opensource.adobe.com/Spry/samples/data_region/JSONDataSetSample.html)
Posted on July 21, 2024
Join Our Newsletter. No Spam, Only the good stuff.
Sign up to receive the latest update from our blog.