Remove duplicates with ONLYOFFICE macro

14 June 2022 By Serge 0 78

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.

Remove duplicate cells with ONLYOFFICE macro

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:

After that we add a function that will pick the unique color from the array:

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:

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:

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:

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:

Remove duplicate cells with ONLYOFFICE macro

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!