Do you have to work with Word documents, Excel spreadsheets, or PowerPoint presentations and need to repeat complex tasks over and over again? For example, you need to highlight duplicate values in a sheet or remove shapes from the slides of a presentation.
If this is the case, it might be challenging for you as a Linux user. Performing such tasks manually is meaningless. A lot of different operations can be easily done automatically with VBA macros in Microsoft Office. However, there is a serious problem – they don’t natively run on Linux machines.
However, there is an ingenious solution to this problem. You can write and run JavaScript macros in ONLYOFFICE Docs to do monotonous operations.
Read this article to find out how.
Table of Contents
What is ONLYOFFICE Docs?
ONLYOFFICE Docs is an open-source and self-hosted office suite that is designed for creating a local collaborative environment on a Linux or Windows server.
It brings the benefits of real-time document editing and co-authoring to a large number of file-sharing solutions, e-learning platforms, and document management systems.
Currently, it’s compatible with Nextcloud, ownCloud, Seafile, Plone, Confluence, Alfresco, Redmine, WordPress, Chamilo, Moodle, Drupal, and other software. Integration is possible through ready-to-use connectors that are available on GitHub.
Using ONLYOFFICE Docs, you can work with all popular types of office files, including text documents, spreadsheets, fillable forms, slides, and PDFs.
ONLYOFFICE Docs’ native format is OOXML (DOCX, XLSX, PPTX), so it can easily replace Microsoft Office on Linux machines. Other popular formats, like ODF, are supported through automatic conversion to OOXML.
In addition to its self-hosted version, ONLYOFFICE Docs has a free cloud version for personal use. Also, there are free apps for all popular desktop and mobile platforms, including Windows, macOS, Linux, Android, and iOS.
When it comes to task automation, ONLYOFFICE Docs is not compatible with Microsoft’s VBA macros. It uses JavaScript macros that are capable of doing the same operations in a much safer manner.
Getting Started with Macros for ONLYOFFICE Docs
A macro is a small script that makes it easier to work with different types of documents by automating a certain task. In other words, it’s a piece of programming code that contains a set of actions that you can run as often as you want.
For example, you can run the corresponding macro to highlight duplicates in the selected area in your spreadsheet with different colors.
In ONLYOFFICE Docs, it looks like this:
What is the practical utility of this macro? When working with data, you can run the saved macro within seconds by clicking the corresponding button to highlight duplicate values as many times as you need, which will save you hours of time.
Main Reasons Why ONLYOFFICE Macros are Good
In ONLYOFFICE Docs, macros are based on the JavaScript syntax and the API methods of ONLYOFFICE Document Builder. There are some obvious reasons why they are better than VBA macros:
- ONLYOFFICE macros are cross-platform – Unlike Visual Basic, JavaScript is a more common programming language. Therefore, JavaScript macros can easily run on Windows, Linux, and macOS platforms.
- ONLYOFFICE macros are easy-to-use – JavaScript is often recommended for beginners and has a fast-growing community. Many people choose JavaScript as their first programming language because there are plenty of detailed tutorials and guides on the Internet.
- ONLYOFFICE macros are safe – The JavaScript code of ONLYOFFICE macros runs in the same window as the editors. Therefore, they can’t be used to compromise your system by attackers because they have no access to it.
How ONLYOFFICE Macros Work
An ONLYOFFICE macro is a combination of the JavaScript syntax and API methods of ONLYOFFICE Document Builder.
ONLYOFFICE Document Builder is a C++ library used to create and edit Office Open XML files as well as save non-OOXML files to DOCX, XSLX, PPTX, and PDF formats. It operates with the JavaScript API.
There are a lot of ONLYOFFICE API methods, and you are not likely to use most of them. These are some practical examples:
- The SetFillColor method is designed to change the background color of the current cell range to the color of a previously created color object.
- The GetValue method returns the value of the specified range.
- The GetSlideByIndex method returns a slide to its position in the presentation.
- And much more.
A detailed description of all ONLYOFFICE API methods and what they do can be found in the official documentation.
In ONLYOFFICE Docs, macros can be created and deleted via a special plugin on the top toolbar. To access it, open the Plugins tab and click the Macros button. You will see a window where you will be able to write JavaScript code for your macros.
Using the corresponding buttons, you can create new macros and delete the existing ones. You are also allowed to rename macros and even activate the Autostart feature.
How to Write an ONLYOFFICE Macro
Now that you understand how ONLYOFFICE macros work, let’s write one. Before we start, it’s necessary to decide what task it going to be done with our macro.
Let’s start with something simple and imagine that we need to change the background color of a cell in our Excel worksheet. This task is piece of cake but the macro that we are going to create will make it much simpler.
To get started, go to the Plugins tab and click Macros. If you open this window for the first time, you will find an already-created macro there. However, there will be no JavaScript code in this macro. What you will see is a simple function wrapper:
(function()
{
// ... your code here ...
})();
Rename your macro if necessary.
Now you can start writing your code. After consulting the ONLYOFFICE Document Builder API documentation, which has been already mentioned in the previous chapter, you will know that the following methods should be used for your macro:
- The Api.GetActiveSheet method to get the active sheet.
- The GetRange method to set the required range.
- The SetFillColor method to set the background colour of the required cell.
- The Api.CreateColorFromRGB to set the desired colour.
First, you need to get the active sheet and set the required range by typing the corresponding methods Let’s target cell A2. Start typing the names of the methods in the function wrapper, and you will see all available tooltips, which is very convenient.
Then, you need to set the background colour of the current cell. To do so, use the SetFillColor method. The Api.CreateColorFromRGB method will allow you to set the colour you like. Your code should be as follows:
(function()
{
// Api.GetActiveSheet().GetRange("A2").SetFillColor(Api.CreateColorFromRGB(0, 0, 210));
})();
Now it’s time to run the macro and see the results. After clicking the Run button, you will see that cell A2 is coloured. You can play with different colours by changing the value in the Api.CreateColorFromRGB method.
If you don’t want to learn the basics of JavaScript coding to create ONLYOFFICE macros, you can always use ready macro examples from the official website and modify them according to your needs.
Conclusion
As you can see, creating macros in ONLYOFFICE Docs is not as difficult as it seems at first sight. Of course, you need to know the fundamentals of JavaScript coding to make complex macros with dozens of code lines.
However, creating simple macros for day-to-day routine activities doesn’t require you to be a JavaScript guru. You can learn the basic principles while trying to create your own plugins.
Importantly, the ONLYOFFICE office suite comes with detailed API documentation and ready-to-use examples to make the process of creating macros as simple as possible.
