Originally published at: Use the attachEvent method to handle events in your macro code | ONLYOFFICE Blog
As we continuously update our editors and enhance their functionality, we strive to provide our users with the ability to tailor them to their precise needs. One of the powerful customization tools available is ONLYOFFICE macros. In this blog post, we will introduce the attachEvent method and explain how it works with events in your macro code. The script we’ll use will run two separate macros in a single execution.
About the attachEvent method
This method listens for the specified event and calls the callback function when the event is triggered, allowing us to execute an additional script within this callback. Here is an example of the implementation using the onWorksheetChange event:
var oWorksheet = Api.GetActiveSheet();
var oRange = oWorksheet.GetRange("A1");
oRange.SetValue("1");
Api.attachEvent("onWorksheetChange", function(oRange){
console.log("onWorksheetChange");
console.log(oRange.GetAddress());
});
Using the attachEvent method to execute two macros in one run
In the first part of our script, we will use the Import CSV macro to import data from an external CSV file and insert it into a table. In the second part, executed within the callback, we will calculate all the values in a specific column and display the result.
So, first we import the external CSV and insert the data into the table:
const oWorksheet = Api.GetActiveSheet();
const oRange = oWorksheet.GetRange("B2:B16");// set the range for calculations
const result = Api.GetActiveSheet().GetRange("B17");// set the cell to display the result
let sum = 0;
function LoadFile() {
$.ajax({
url: 'pathToCsVfile',
dataType: 'text',
}).done(successFunction);
}
function successFunction(data) {
const arrAllRows = data.split(/\r?\n|\r/);
let i = 1;
let j = 1;
for (let singleRow = 0; singleRow < arrAllRows.length; singleRow++) {
const rowCells = arrAllRows[singleRow].split(',');
for (let rowCell = 0; rowCell < rowCells.length; rowCell++) {
oWorksheet.GetCells(i, j).SetValue(rowCells[rowCell]);
j += 1;
}
i += 1;
j = 1;
}
}
Then we introduce the calculateSum() function that will calculate the values in the selected range:
function calculateSum() {
sum = 0; // Reset the sum
oRange.ForEach(function(cell) { // Loop over all cells in the range
const value = cell.GetValue();
if (!isNaN(parseFloat(value))) {
sum += parseFloat(value); // Add the cell's numeric value to the sum
}
});
}
Next, we invoke the LoadFile function and execute the attachEvent method, which will listen for any changes on the worksheet. When a change occurs, the calculateSum() function will be executed within the callback:
LoadFile();// Attach event to recalculate sum when cells change Api.attachEvent("onWorksheetChange", function() { calculateSum(); });
At the end of our macro, we execute the asc_calculate function to reload the spreadsheet and display the calculation result in the selected cell:
// Update the cell B17 with the calculated sum
let reload = setInterval(function() {
Api.asc_calculate(Asc.c_oAscCalculateType.All);
result.SetValue(`The sum: ${sum}`);
});
The entire macro code is the following:
(function() {
const oWorksheet = Api.GetActiveSheet();
const oRange = oWorksheet.GetRange("B2:B16");// set the range for calculations
const result = Api.GetActiveSheet().GetRange("B17");// set the cell to display the result
let sum = 0;
function LoadFile() {
$.ajax({
url: 'pathToCsVfile',
dataType: 'text',
}).done(successFunction);
}
function successFunction(data) {
const arrAllRows = data.split(/\r?\n|\r/);
let i = 1;
let j = 1;
for (let singleRow = 0; singleRow < arrAllRows.length; singleRow++) {
const rowCells = arrAllRows[singleRow].split(',');
for (let rowCell = 0; rowCell < rowCells.length; rowCell++) {
oWorksheet.GetCells(i, j).SetValue(rowCells[rowCell]);
j += 1;
}
i += 1;
j = 1;
}
}
function calculateSum() {
sum = 0; // Reset the sum
oRange.ForEach(function(cell) { // Loop over all cells in the range
const value = cell.GetValue();
if (!isNaN(parseFloat(value))) {
sum += parseFloat(value); // Add the cell's numeric value to the sum
}
});
}
LoadFile();
// Attach event to recalculate sum when cells change
Api.attachEvent("onWorksheetChange", function() {
calculateSum();
});
// Update the cell B17 with the calculated sum
let reload = setInterval(function() {
Api.asc_calculate(Asc.c_oAscCalculateType.All);
result.SetValue(`The sum: ${sum}`);
});
})();
Now let’s run the macro and see how it works!
In the realm of ONLYOFFICE, we always strive to enhance the flexibility and versatility of our product. ONLYOFFICE macros are a superb tool that allows you to customize functionality and address your specific needs. Our extensive library of API methods, which is constantly updated, enables you to craft your own solutions.
We encourage you to take advantage of this resource and create your own macros. If you have any questions or macros to share, don’t hesitate to contact us. We welcome discussion and collaboration. Best of luck in your exploratory endeavors!