Originally published at: 15 useful JavaScript macros to try in 2024 | ONLYOFFICE Blog
While Microsoft Office has VBA-based macros, ONLYOFFICE goes a step further with JavaScript-based macros, providing more flexibility. In this blog post, we’ll showcase some of the numerous ONLYOFFICE macros that provide a compelling alternative to traditional VBA-based ones.
What is a JavaScript macro?
Macros are powerful tools that automate tasks. ONLYOFFICE employs JavaScript for creating macros covering document, spreadsheet, presentation and OFORM editing. These JavaScript macros allow users to automate tasks, enhance document functionality, and customize ONLYOFFICE applications according to their needs.
Why might you need an alternative to VBA macros?
With the ability to write custom JavaScript macros, you can achieve automation, interactivity, and seamless integration with external systems:
- Automation: JavaScript macros enable users to automate tasks such as formatting, data manipulation, and calculations within documents.
- Interactivity: Macros can make documents more interactive by responding to user actions or events.
- Customization: Users can customize the behavior of ONLYOFFICE applications according to their specific needs.
- Integration: JavaScript macros allow integration with external systems and services, enabling data exchange and communication between ONLYOFFICE and other platforms.
Given that ONLYOFFICE macros utilize JavaScript, and beyond its flexibility, it is an extremely popular language with a vibrant community. There is an abundance of tutorials available, providing comprehensive guidance on how to tackle various tasks with JavaScript. Furthermore, our detailed documentation provides clear explanations of the methods you can use in your JavaScript macros.
Comparing Excel VBA macros to ONLYOFFICE JavaScript alternatives
Highlight duplicates from selection
VBA Excel macro is the following:
Sub HighlightDuplicateValues() Dim myRange As Range Dim myCell As Range Set myRange = Selection For Each myCell In myRange If WorksheetFunction.CountIf(myRange, myCell.Value) > 1 Then myCell.Interior.ColorIndex = 36 End If Next myCell End Sub
Unlike the VBA counterpart, our alternative provides more advanced functionality by highlighting duplicate values with unique colors:
(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);
}
}
})();
Unhide all rows and columns
This VBA script unhides all rows and columns on the spreadsheet:
Sub UnhideRowsColumns() Columns.EntireColumn.Hidden = False Rows.EntireRow.Hidden = False End Sub
And the same can be done with the JavaScript macro:
(function()
{
var activeSheet = Api.ActiveSheet;
var indexRowMax = 1048576;
var n = 1;
for (let i = 0; i < indexRowMax; i++) {
activeSheet.GetRows(n).SetHidden(false);
n++;
}
var newRange = activeSheet.GetRange("A1");
newRange.SetValue("All the rows and columns are unhidden now");
})();
You can also hide all the rows and columns by setting the SetHidden parameter to true in this macro:
(function()
{
var activeSheet = Api.ActiveSheet;
var indexRowMax = 1048576;
var n = 1;
for (let i = 0; i < indexRowMax; i++) {
activeSheet.GetRows(n).SetHidden(true);
n++;
}
var newRange = activeSheet.GetRange("A1");
newRange.SetValue("All the rows and columns are hidden now");
})();
If you need more advanced features, we’ve detailed a macro in another blog post that allows you to hide/unhide specific rows and columns.
Highlight greater than values
Here’s a VBA version that highlights greater than value:
Sub HighlightGreaterThanValues()
Dim i As Integer
i = InputBox("Enter Greater Than Value", "Enter Value")
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlGreater, Formula1:=i
Selection.FormatConditions(Selection.FormatConditions.Count).S
tFirstPriority
With Selection.FormatConditions(1)
.Font.Color = RGB(0, 0, 0)
.Interior.Color = RGB(31, 218, 154)
End With
End Sub
The same task can be tackled with an ONLYOFFICE JavaScript macro, even using fewer lines of code:
(function ()
{
var activeSheet = Api.ActiveSheet;
var selection = activeSheet.Selection;
selection.ForEach(function (range) {
var value = range.GetValue();
if (value > 5) {
range.SetFillColor(Api.CreateColorFromRGB(255, 255, 0));
}
});
})();
Despite having fewer lines of code, our alternative offers better versatility. For instance, by changing the condition of the if statement, we can:
- Highlight lower than values:
(function ()
{
var activeSheet = Api.ActiveSheet;
var selection = activeSheet.Selection;
selection.ForEach(function (range) {
var value = range.GetValue();
if (value < 5) {
range.SetFillColor(Api.CreateColorFromRGB(255, 255, 0));
}
});
- Highlight negative numbers:
(function ()
{
var activeSheet = Api.ActiveSheet;
var selection = activeSheet.Selection;
selection.ForEach(function (range) {
var value = range.GetValue();
if (value < 0) {
range.SetFillColor(Api.CreateColorFromRGB(255, 255, 0));
}
});
})();
- Highlight cells with specific text:
(function ()
{
var activeSheet = Api.ActiveSheet;
var selection = activeSheet.Selection;
selection.ForEach(function (range) {
var value = range.GetValue();
if (value == 'text') {
range.SetFillColor(Api.CreateColorFromRGB(255, 255, 0));
}
});
})();
More useful JavaScript macros to try
- A JavaScript macro to add indexes to a selection in ONLYOFFICE spreadsheets
Sometimes the default indexing just won’t do. That’s where our time-saving JavaScript macro comes in. It’ll neatly index all the rows in your selection, making your spreadsheet workflow a whole lot simpler. - A JavaScript macro to copy spreadsheets
Editing spreadsheets can be tricky, especially when handling multiple spreadsheets with similar data. To ease this process, let’s create a JavaScript macro that copies data from one spreadsheet to another, and helps you handle large spreadsheets easily. - A JavaScript macro to insert multiple Baidu Search results into a spreadsheet
The Baidu Macro is a versatile tool designed to boost efficiency in managing single and multiple search queries. Regardless of your experience level, its user-friendly design and adaptability make it a beneficial addition to your toolkit. - A JavaScript macro to import hyperlinks into a spreadsheet
Hyperlinks can greatly enhance the aesthetics and functionality of your spreadsheets, making it effortless to access crucial resources within your documents. This JavaScript macro imports hyperlinks by extracting link data from another spreadsheet. - A JavaScript macro to find company logos
Given the many logos out there, discovering logo references can be a bit tricky these days, potentially leading to confusion. Yet, with ONLYOFFICE macros, you can make this process automatic. This JavaScript macro can simultaneously retrieve several logotypes from an external API and insert them into your spreadsheet. - A JavaScript macro to generate personalized gender-based greetings
Addressing people correctly is crucial in communication as it shows respect, inclusivity, and professionalism. This JavaScript macro utilizes the Genderize.io API to generate proper personalized greetings.
- A JavaScript macro to macro to track orders on Shopify
In this day and age, E-commerce has become a valuable tool to break down geographical barriers and facilitate the constant stream of income. Therefore managing and tracking order information is a vital part of a successful business strategy. This JavaScript macro retrieves order data from Shopify and inserts it into a spreadsheet. - A JavaScript macro to macro to import CSV and TXT data into your spreadsheet
Storing tabular data in a CSV format is very practical in many ways, which makes this type of file extremely popular. ONLYOFFICE Docs allow importing local CSV and TXT files. This JavaScript macro will help you tackle importing remote CSV and TXT files.
- A JavaScript macro to monitor air quality data
ONLYOFFICE macros make our lives easier. They help us automate our daily routine tasks and operate with huge chunks of data. This JavaScript macro allows you to display the current air quality data live.
- A JavaScript macro to populate cells with OpenAI data
AI technology has made significant advancements and become a valuable tool that can automate our workflow. This JavaScript macro populates a spreadsheet with data obtained from OpenAI.
- A JavaScript macro to analyze spreadsheet data
While ChatGPT 4 now provides enhanced features, some users still prefer ChatGPT 3.5 due to its lower token cost. However, ChatGPT 3.5 lacks file uploading capabilities, preventing users from analyzing data sheets. This JavaScript macro overcomes this limitation, enabling you to analyze spreadsheets. - A JavaScript macro to remove duplicates in the selection
This JavaScript macro removes duplicates from the spreadsheet selection. It helps you to get rid of duplicate entries, making your data clean and accurate with a simple and efficient solution that enhances your overall spreadsheet experience.
ONLYOFFICE macros are flexible and powerful tools, capable of not only mimicking the functionality of VBA macros but also surpassing them.
We also encourage everyone to leverage our ONLYOFFICE API and create your own fascinating macros. If you have any questions or ideas, don’t hesitate to reach out to us. We are open to discussion and collaboration.