Originally published at: https://www.onlyoffice.com/blog/2022/06/remove-duplicates-with-onlyoffice-macro/
In this post, we will talk about a small macro that will remove duplicates from the Spreadsheet selection. In terms of functionality, it is similar to the Remove duplicates macro that is widely used in Excel. However, since it is a JavaScript-based macro, the entire approach will be slightly different.
About ONLYOFFICE macros
If you are a confident Microsoft Excel user, you are probably familiar with VBA macros. They are little scripts that help you automate routine tasks. Be it restructuring your data or inserting several values in a cell range. ONLYOFFICE macros are based on JavaScript syntax and Document Builder API methods. JavaSript-based macros are easy to use, cross-platform and secure. That gives them a significant advantage over the VBA-based ones.
Reference macro
For reference, we will use the Highlight Duplicates macro. This ONLYOFFICE macro highlights duplicates in the selected area with different colors. The example code can be found in the macro samples section of our API documentation page. Let`s take a closer look at it.
In the beginning, we set the white fill for the cells with non-duplicate values. For the duplicate cells, we create an array with unique colors. We achieve that by implementing the CreateColorFromRGB API method:
(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)];
After that we add a function that will pick the unique color from the array:
// 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++];}
Then we target the selection on the active sheet and execute the ForEach method. It will go through this selection and assign an additional value to the duplicate cells:
// 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);
});
Now we go through all the cells one more time. If a cell has the additional value that we assigned above, we execute the getColor function. It will pick the unique fill color for the duplicate cell. Then we apply this color by running the SetFillColor method. The remaining non-duplicate cells get the white background fill:
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);
}
}
});
Creating a new macro
The Remove duplicates macro will not be highlighting the duplicate values. It will be removing them from the selection. In that case, we only need the part of the code that will detect the duplicate values:
(function ()
{
// Getting an active sheet
var activeSheet = Api.ActiveSheet;
// Getting selection on the active sheet
var selection = activeSheet.Selection;
// Map of cell values
var mapValues = {};
// All cells range
selection.ForEach(function (range) {
// Getting value from cell
var value = range.GetValue();
// If the cell does not have a duplicate value
if (!mapValues.hasOwnProperty(value)) {
// We set this value to 0
mapValues[value] = 0;
}
});
})();
Now we need a method that will clear the content of the duplicate cell. I suggest using the Clear method. It clears the current range in the spreadsheet. That makes it a perfect candidate for this task. We include this method in the else statement that we run if the cell has a duplicate value:
(function ()
{
// Getting an active sheet
var activeSheet = Api.ActiveSheet;
// Getting selection on the active sheet
var selection = activeSheet.Selection;
// Map of cell values
var mapValues = {};
// All cells range
selection.ForEach(function (range) {
// Getting value from cell
var value = range.GetValue();
// If the cell does not have a duplicate value
if (!mapValues.hasOwnProperty(value)) {
// We set this value to 0
mapValues[value] = 0;
}
// If the cell has a duplicate value
else {
// We clear the content of the cell
range.Clear();
}
});
})();
This macro is just one of the many examples of what you can do by implementing our API methods. We kindly encourage you to experiment and create your own macros. Feel free to ask questions, and share your ideas or your macros with us. We are open to discussion and cooperation. Best of luck in your exploratory endeavors!