LibreOffice Calc & Python Programming: Annex 1 – Assign a Python Macro to a key.


This episode of my LibreOffice Calc & Python Programming tutorial is an annex to the normal series. In this article I am going to show you a quick way to execute your python scripts.
The idea is simple: you assign a combination of keyboard keys to any of your Python scripts (I also called them programs, it sounds more pro!).

Here is the plan for this Annex 0 of the tutorial:

Write a simple python program.

Let’s make a quick python program that writes numbers from 1 to 10 in 10 consecutive cells in column B of any LibreOffice Calc Spreadsheet (yes a lot like in this tutorial!).

# get the doc from the scripting context 
# which is made available to all scripts
desktop = XSCRIPTCONTEXT.getDesktop()
model = desktop.getCurrentComponent()

# access the active sheet
active_sheet = model.CurrentController.ActiveSheet

def write_1_to_10(*args):
    """Write 1,2...10 in Column B"""
    for each_number in range(10):
        active_sheet.getCellByPosition(1,each_number).Value = each_number + 1

In this simple example we don’t use a main() program to call our function ‘write_1_to_10, so the function needs to have the *args (pointer to arguments) for passing information from the LibreOffice environment to Python.

Save the script at the right place.

I have named my file to make it easier for you to match it with this lesson on my gitHub repository dedicated to LibreOffice Tutorials.

Now that you have written your program, to have it available in LibreOffice you have to save it in the correct folder.
For Linux:
/home/<user name>/.config/libreoffice/4/user/Scripts/python
(to see folder ‘.config’ you need to ‘Show hidden files’ , the shortcut CTRL+h).
For Windows:
For Mac:
(I think it’s)/Users/<user name>/Library/Application Support/LibreOffice/4/user\Scripts\python
(Please let me know in a comment if you know for sure what the folder is for Mac OS).

Open a new LibreOffice Calc document if you haven’t doe it yet, to check that you can run your script. And also to debug it if needed…
The best is to use the APSO Add-on (how to install it here).
So if everything is ok you should find your script listed by looking in either:
Tool/Macros/Organize Macros/Python
Tool/Macros/Organize Python Scripts

Menu Tools>Macros>Organize Macros>Python
Menu Tools>Macros>Organize python scripts (if you have installed APSO)

Showing here the APSO organizer (the built-in LibreOffice one is self explanatory as well…):

My program is saved in my usual MyPythonLibrary Module.

(Check-out my helloworld tutorial if you need more details on how and where to save your python script)

Try to execute your program by selecting it from the APSO organizer and click on the execute button, or from the LibreOffice organizer on the ‘Run’ Button.
If everything goes write the program should write numbers 1 to 10 in column B. If not you have to debug it… or leave me a comment if you have any problem.

Assign your scrip to [CTRL]+0.

If like me you feel that executing a python script within LibreOffice as showing previously requires too many mouse clicks you will appreciate a much quicker way to run your pythonic creations.
And to debug more easily your programs on the go, it is much easier also.

To assign a key or a combination of keys to a python script (or to a LibreOffice Basic Macro as well) go directly in the menu Tools/Customize’

From Menu Tools>Customize

or right click anywhere on the toolbar (not the top menu bar) and nearly at the bottom of the contextual menu select ‘Customize Toolbar’:

From a right click on the toolbar

The next steps are:

  1. Select the tab ‘Keyboard’ .
  2. In the ‘Category’ section, find and select your python module and file (in the example MyPythonLibrary/Annex1).
  3. In the ‘Function’ section select the function within the file (in the example there is only one file: write_1_to_10).
  4. In the ‘Shortcut Keys’ section select the key or combination of keys from the list, better choose one that has nothing assigned to it already (in the example Ctrl+0 : so the combination of keys ‘Ctrl’ + ‘0’).
  5. Click on the ‘Modify’ button.
  6. You should see the chosen keys now in the ‘Keys’ section.
  7. You should see the function’s name assigned to the Keys combination entry in the ‘Shortcut Keys’ section.
  8. Click ‘OK’.
Assigning function write_1_to_10 to CTRL+0

And after emptying the cells in column B, just give a try of Ctrl+0, it should filled them again!

When I am testing python code, I sometimes keep that Ctrl+0 combination assigned to a function ‘test(*args)’, when I am happy with the function I copy/rename it to a better named one, then use the test function to code something else. You can also assign useful functions that you have built and that you use often for example to format cells in a particular way.

I hope you find this useful, please leave any comments to help me making this tutorial better.

Leave a Comment

Your email address will not be published. Required fields are marked *