Writing your own macros

Now that you know how macros work, let us try and write your own macro. Say, we have a table and need to color the alternate table rows (odd will be colored green, even will become red). The table contains 200 rows (would take quite some time to do that manually) and columns from A to S.

  1. Open ONLYOFFICE Desktop Editors and create a new spreadsheet.
  2. Now open the plugins and select Macros. The macros window will popup.
  3. Click New. You will be presented with the basic function wrapper which will allow you to enter the necessary code:
    (function()
    {
        // ... your code goes here ...
    })();
  4. Let's consult the ONLYOFFICE Document Builder API documentation to see what we need to complete our task:
    • First, get the current worksheet using the GetActiveSheet method:
      var oWorksheet = Api.GetActiveSheet();
    • Then create a loop to run from the first to the last row:
      for (var i = 1; i < 200; i += 2) {
      }
    • Now set two variables: one for odd rows, the second for even rows:
      var rowOdd = i, rowEven = i + 1;
    • Now that we can access both odd and even rows, let's color them in proper colors. Set the desired colors using the CreateColorFromRGB method. Get the cell range within the row using the GetRange method and set the color for the odd rows:
      oWorksheet.GetRange("A" + rowOdd + ":S" + rowOdd).SetFillColor(Api.CreateColorFromRGB(118, 190, 39));
      The same is for the even rows, but with a different color:
      oWorksheet.GetRange("A" + rowEven + ":S" + rowEven).SetFillColor(Api.CreateColorFromRGB(186, 56, 46));

Now let's sum it up with the complete script code:

(function()
{
    var oWorksheet = Api.GetActiveSheet();
    for (var i = 1; i < 200; i += 2) {
        var rowOdd = i, rowEven = i + 1;
        oWorksheet.GetRange("A" + rowOdd + ":S" + rowOdd).SetFillColor(Api.CreateColorFromRGB(118, 190, 39));
        oWorksheet.GetRange("A" + rowEven + ":S" + rowEven).SetFillColor(Api.CreateColorFromRGB(186, 56, 46));
    }
})();

Paste the code above to the macros window and click Run. The table rows from 1 to 200 will be colored alternately in less than a second.