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.