Two ONLYOFFICE macros: highlight duplicates and go to the next empty row

Our users asked us how to recreate some VBA macros they were using in Excel. In this post, we’ll show how to do it on two simple examples.

ONLYOFFICE macros use JavaScript language syntax and ONLYOFFICE Document Builder API scripting notation. This means that you won’t be able to use VBA macros until you recreate them using JavaScript.

We chose to use JavaScript for our macros because:
 

• It is cross-platform,
• it is really easy to use,
• you can use the code you wrote as a macro or as a plugin.

Also, our macros are safe because they do not have any access to the system. They are just JS code that runs in the same window with the editors.

1. In spreadsheet editor, open the Plugins tab and click Macros.
2. Click “New” in the window that appeared.
3. Consult API documentation to write the script.

Let’s write a macro to highlight duplicates in the selected area with different colors to recognize duplicated values quickly. We’ll use this simple VBA macro as a base.

Here is the resulting script with comments for you to understand how it’s done. The colors are determined in macro code.

(function () {
    // Background color of cells with non-repeating values
    var whiteFill = Api.CreateColorFromRGB(255, 255, 255);
    // The current index of the color range
    var uniqueColorIndex = 0;
    // Color range to highlight duplicate values
    var uniqueColors = [Api.CreateColorFromRGB(255, 255, 0),
        Api.CreateColorFromRGB(204, 204, 255),
        Api.CreateColorFromRGB(0, 255, 0),
        Api.CreateColorFromRGB(0, 128, 128),
        Api.CreateColorFromRGB(192, 192, 192),
        Api.CreateColorFromRGB(255, 204, 0)];
 
    // Function to get color for duplicates
    function getColor() {
        // If you have chosen all the unique colors, then let's go from the beginning
        if (uniqueColorIndex === uniqueColors.length) {
            uniqueColorIndex = 0;
        }
        return uniqueColors[uniqueColorIndex++];
    }
 
    // Getting an active sheet
    var activeSheet = Api.ActiveSheet;
    // Getting selection on the active sheet
    var selection = activeSheet.Selection;
    // Map of values in cells with the duplicates number
    var mapValues = {};
    // All cells range
    var arrRanges = [];
    // Going through the selection
    selection.ForEach(function (range) {
        // Getting value from cell
        var value = range.GetValue();
        if (!mapValues.hasOwnProperty(value)) {
            mapValues[value] = 0;
        }
        mapValues[value] += 1;
        arrRanges.push(range);
    });
    var value;
    var mapColors = {};
    // We go through all the cells of the selection and setting the highlighting if this value is repeated more than 1 time
    for (var i = 0; i < arrRanges.length; ++i) {
        value = arrRanges[i].GetValue();
        if (mapValues[value] > 1) {
            if (!mapColors.hasOwnProperty(value)) {
                mapColors[value] = getColor();
            }
            arrRanges[i].SetFillColor(mapColors[value]);
        } else {
            arrRanges[i].SetFillColor(whiteFill);
        }
    }
})();

Here’s what we get with this macro:

This one finds the next available blank row in a worksheet. We took this VBA macro as an example and created this script:

(function()
{
    // Getting the active sheet
    var activeSheet = Api.ActiveSheet;
    // Minimum row index
    var indexRowMin = 0;
    // Maximum row index
    var indexRowMax = 1048576;
    // Column 'A'
    var indexCol = 0;
    // Row index for empty cell search
    var indexRow = indexRowMax;
    for (; indexRow >= indexRowMin; --indexRow) {
        // Getting the cell
        var range = activeSheet.GetRangeByNumber(indexRow, indexCol);
        // Checking the value
        if (range.GetValue() && indexRow !== indexRowMax) {
            range = activeSheet.GetRangeByNumber(indexRow + 1, indexCol);
            range.Select();
            break;
        }
    }
})();

This code will help you select the next empty row (we’ll bring scrolling in one of the next versions).

Note that the macro will skip any blanks that are between your data but not at the very end of it.

If you need help with writing macros or the method you need is absent from our API, post an issue on GitHub.

ONLYOFFICE on GitHub

Article on starting writing plugins. Read now>>

Plugin example: fill the cells with color and turn your worksheet into an image. Check it out>>