Macro Maker

All great software programs, especially free and open source software, share one common feature: You can easily customize and extend the software as you wish. The LibreOffice productivity suite is no exception, thanks to its support for those "saved sequence[s] of commands or keystrokes that are stored for later use" [1], otherwise known as macros.

I like to think of macros as the LibreOffice equivalent of Unix scripts: Whether they are keyboard sequences or code written in a programming language, these simple programs may be created quickly, possibly with very little programming skill, to automate all sorts of tasks.

ScriptForge [2], a LibreOffice library for building scripts, along with the APSO extension needed to run ScriptForge, provides a great tool to learn how to automate LibreOffice because it solves a general, but very important, problem with the LibreOffice macro environment.

In this article, I will provide a brief background of LibreOffice macros, talk about using Python in LibreOffice, and then show you how to use ScriptForge to create portable macros to automate LibreOffice.

LibreOffice Macros

Regardless of the programming language used to write a macro, there are three types of LibreOffice macros: system-wide, user-specific, and document-specific.

A system-wide LibreOffice macro consists of code shipped and installed with LibreOffice. You will find system-wide macros in a system folder (usually /usr/lib/libreoffice/share/Scripts/ on Linux systems), which makes these macros accessible to everyone using that installation. When you select Tools | Macros | Run Macros in LibreOffice, the contents of that system folder show up in the LibreOffice Macros section as shown in Figure 1. As of mid-2022, a standard LibreOffice installation on Ubuntu includes a large number of system-wide macros in four different languages: BeanShell, Java, JavaScript, and Python.

Figure 1: LibreOffice comes with many ready-to-use macros, in several programming languages.

If you install additional macros, depending on their configuration, these macros may be placed in some other section of the /usr/lib/libreoffice hierarchy or even as user-specific macros. For example, the ScriptForge files end up inside several directories under /usr/lib/libreoffice/share/basic/.

In addition to system-wide macros, you can also create user-specific macros, which LibreOffice will display under My Macros (Figure 2). On Linux, the corresponding source files will be placed inside $HOME/.config/libreoffice/4/user/Scripts, which you should remember to add to your backups as soon as you start creating user-specfic macros!

Figure 2: User-specific macros and scripts will be saved in your LibreOffice configuration folder and available under My Macros.

System-wide and user-specific macros can be run on any document that a user opens with LibreOffice using their account on that specific computer.

If you want to make a macro available to any LibreOffice user (who has the right system libraries), you can embed the macro in the document. If a document-specific macro is available for a given document, it will appear in the menus shown in Figures 1 and 2 inside a separate folder named after that file, only when that document is open.

A final note on LibreOffice macros: By default, your LibreOffice installation may refuse to run all the macros you attempt to install (either ones you create or find inside a document). You can change this setting (but think twice before doing it) by going to Tools | Options | LibreOffice | Security, clicking on Macro Security, and then adjusting Security Levels and/or Trusted Sources to meet your requirements.

Macros vs. Scripts

In addition to macros, LibreOffice also uses the term "scripts" for third-party code run from inside LibreOffice to automate a task. You may wonder (as do I) what the real difference between the two categories is, but a single, clear, and simple answer seems hard to find. From personal experience, there seems to be little or no difference between the two concepts for all practical purposes, at least for end users and beginner programmers. The main difference seems to be that a "script" (as opposed to a "macro") might need an extra configuration step or package in order to run it.

Python in LibreOffice

Of the four languages that are "natively" supported by LibreOffice, I prefer to use Python because that is the language I use most frequently, even outside of LibreOffice. As far as LibreOffice automation with Python is concerned, it seems that the only way to easily embed Python code inside a document is to install an extension called Alternative Script Organizer for Python (APSO) [3]. From my understanding, APSO is necessary, at least on Ubuntu and (I assume) most other Linux distributions, because the glue code to run Python scripts is only available as a separate package. In general, with APSO you get an integrated Python interpreter and debugger, which are really useful if you want to do serious LibreOffice programming with Python, with or without ScriptForge.

Even if you just want to run some ScriptForge-based Python script you found online, you will need APSO (or equivalent extensions), at least to embed or extract scripts in the files you manage with LibreOffice (if their format allows it, of course). I will show how to actually use APSO for this purpose later in this article.

To install APSO, just visit the website, download the latest version, select Yes when asked if you would like to open that file with LibreOffice, and follow the instructions. Once APSO is installed, you will find an extra entry in the LibreOffice Macros submenu as shown in Figure 3: a macro organizer, whose default shortcut is Alt+Shift+F11, dedicated to Python scripts.

Figure 3: The APSO extension for LibreOffice adds another interface to execute Python code while running LibreOffice.

The ScriptForge Solution

Judging from an online search, LibreOffice macros and scripts are very popular. Besides countless third-party tutorials, you will find plenty of official documentation, as well as a collection of the most popular, ready-to-use macros in the Document Foundation's wiki [4]. Despite all this documentation, most users will find LibreOffice's macro/scripting subsystem and its corresponding API overwhelming enough that they never try to write their own scripts or macros. Indeed, writing your own code for LibreOffice can be pretty difficult and time consuming.

ScriptForge aims to make writing macros and scripts easier. With ScriptForge, you can quickly and easily find, recognize, and use the functions that are most frequently needed when writing code. These functions include – primarily, but not exclusively – user- and document-specific macros. These functions are packaged as reusable services (as seen in Figure 4) that can be loaded from code written in LibreOffice Basic or Python. Most ScriptForge services are deliberately written to work exactly the same in both Python and Basic. The main difference is the way you load ScriptForge. In LibreOffice Basic, you must insert the following command at the beginning of your macro:

GlobalScope.BasicLibraries.LoadLibrary("ScriptForge")

Figure 4: ScriptForge offers services for a variety of tasks: spreadsheet processing, string substitutions, database queries, and more.

With Python, you import the ScriptForge service as follows:

from scriptforge import CreateScriptService

The ScriptForge Library [2] provides several examples of ScriptForge services that process strings, process arrays of generic elements (e.g., cell ranges in spreadsheets or lists in text documents), or read and write full files. Using these functions, you can sort data, read or write CVS tables or databases, search and replace text with regular expressions, or browse folders.

ScriptForge organizes these features into three main sections, two of which are shown in Figure 5, which you should compare with Figure 4: Besides a Core library and Associated libraries, which are both developed by ScriptForge developers, you will find Guest libraries and extensions developed by third parties.

Figure 5: The internal organization of the ScriptForge library [5].

Among other things, the Core library holds code to process all the low-level data structures, manage files and folders, and handle localization issues. The Associated libraries are divided in three groups, which handle the contents of LibreOffice files (SFDocuments), user dialogs (SFDialogs), and databases (SFDatabases). SFDatabases can access databases inside LibreOffice Base files or external ones, reading and writing records with standard SQL queries.

You may write complete, useful macros using only these ScriptForge libraries. Once you have become familiar with them, you can use these macros as connectors to move raw data back and forth between a document (e.g., a spreadsheet) and Basic or Python data structures for more sophisticated processing.

Getting Started

In this article, I focus on how to get started using ScriptForge by showing how to load and run, first as a user-specific macro and then as an embedded one (document-specific), some elementary ScriptForge-based code written in Python, because that is the most complicated case to set up.

Consider the Python code in Listing 1, which is taken straight from the ScriptForge documentation. The first things Listing 1 does are declare (line 1) that the script must use the ScriptForge libraries and load from these libraries the specific methods needed to process cells inside LibreOffice Calc spreadsheets (line 2). Lines 4 to 7 define a function (increment_cell) that copies the current contents of Cell A1 in a Python variable called value, increments that variable, and then copies the result back into the same spreadsheet cell. Line 9 shows how to actually call that function. The last part of the script imports the ScriptForge service that handles dialog boxes and creates one with a "Hello" message.

01 from scriptforge import CreateScriptService
02 doc = CreateScriptService("Calc")
03
04 def increment_cell(args=None):
05   value = doc.GetValue("A1")
06   value += 1
07   doc.SetValue("A1", value)
08
09 g_exportedScripts = (increment_cell, )
10 from scriptforge import CreateScriptService
11 bas = CreateScriptService("Basic")
12 bas.MsgBox("Hello!")

The simple code in Listing 1 should be enough to highlight ScriptForge's real potential and the roads ScriptForge opens up for its users. Listing 1 essentially shows a direct, simple-to-use bridge between two very powerful programming environments: LibreOffice Calc's number processing and charting capabilities and Python's countless modules and features. Sure, fetching the content of a cell just to increment it is not a big deal, but it just shows how simple reading and writing spreadsheet cells is with ScriptForge (lines 5 and 7). Instead of an increment operator in line 6, you could use Python code (as shown in an earlier article [6]) that assigns to value some number fetched from the web in real time every time you call that macro. With similar techniques, you may download headlines from the Internet [7] (or any other content) and insert them inside a LibreOffice text document. Due to space constraints, I cannot show full examples of such applications here, but by using my earlier articles, it should not be too difficult an exercise.

Running ScriptForge

I will now show how to make LibreOffice actually recognize and run the code in Listing 1, first as a user-specific script and then as an embedded one. For a user-specific script, you should open your preferred text editor, copy the code from Listing 1 to the new file, and save the file as test.py in your local LibreOffice scripts folder, which on Linux will be $HOME/.config/libreoffice/4/user/Scripts/python/.

When you now go to Tools | Macros | Run Macro, you will see the contents of that script under My Macros of your LibreOffice Macros manager and can click on increment_cell to execute the script (Figure 6).

Figure 6: Once installed properly, user-specific Python scripts can be run just like ordinary macros.

This script will be a local, user-specific macro, usable on every spreadsheet you open with LibreOffice using your account on your computer. To make this script a document-specific macro, you need to embed the macro into the spreadsheet. However, for other LibreOffice users to immediately find and run the macro (if they have ScriptForge, of course) in that same spreadsheet, a bit more work is needed.

This is where the APSO script organizer enters the picture. You can use APSO both to embed already existing macros or create new macros inside a document. To embed an existing macro, open the spreadsheet in which you want to embed the macro and select Tools | Macros | Organize Python scripts, which will open the simple interface shown in Figure 7. From here, you can select the macro and then choose Menu | Embed in document (you can also execute the macro from this interface). You may use the same interface to export an embedded macro from a file.

Figure 7: With APSO, you can embed macros inside LibreOffice documents with a few clicks.

You can easily see where and how the macros were embedded, because the OpenDocument file format that LibreOffice uses by default is really just a ZIP archive. If you embed the script from Listing 1 inside a spreadsheet called test.ods, save that file as test.zip, and unzip it, you will obtain (among other things) a Scripts folder containing a python subfolder inside of which you'll find a file called test.pys, whose contents will be the code from Listing 1!

Once you embed the script and save the spreadsheet (named testmacro-2.ods in my example), anyone who opens the spreadsheet on a computer with LibreOffice and ScriptForge will see that macro under testmacro-2.ods and be able to run it, both in APSO (Figure 8) and in LibreOffice's standard macro manager (Figure 9).

Figure 8: Thanks to APSO, the user-specific macro first seen in Figure 6 is now embedded inside the current spreadsheet.

Figure 9: Macros embedded with APSO are usable also through LibreOffice's standard macro manager.

If you want to create new Python scripts with APSO, open APSO, select (for an embedded script) the current file and then go to Menu | Create module. After naming the module, you can select it and click on Menu | Edit to open a text editor and code directly from there, using APSO's Python shell to test your work.

Conclusion

Automating LibreOffice is less complicated than it first seems thanks to ScriptForge and Python. However, this article just touches on the basics. If you want to learn more about the power of Python and ScriptForge in creating LibreOffice macros and scripts, visit LibreOffice.org [5]. If you are interested in ScriptForge's internal architecture, check out the ScriptForge presentation given at the 2020 LibreOffice conference [8].

The Author

Marco Fioretti (https://mfioretti.com) is a freelance author, trainer, and researcher based in Rome, Italy, who has been working with free and open source software since 1995 and on open digital standards since 2005. Marco also blogs about digital rights at https://stop.zona-m.net.