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.
- In spreadsheet editor, open the Plugins tab and click Macros.
- Click “New” in the window that appeared.
- 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>>