DEV Community

Gokulakrishnan Shankar
Gokulakrishnan Shankar

Posted on

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 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


Enter fullscreen mode Exit fullscreen mode

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.

![LibreofficePython_CheckingFunction](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/f4f2lfgqvtrwl3aryspm.png)
- 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


Enter fullscreen mode Exit fullscreen mode
  • 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")`

![LibreofficePython_ExecutingFormula](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/y3do7bsapde615btwt62.png)
## 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)
Enter fullscreen mode Exit fullscreen mode

Top comments (0)