Use an ONLYOFFICE macro to calculate the sum of the highlighted cells

When working with spreadsheets, there are often occasions when we need to calculate the sum of specific values. In this blog post, we will detail the process of creating a macro that calculates the sum of cells highlighted with a specific background color.

Building the macro

• Access the active worksheet
  This line fetches the active worksheet where the macro will run:

   const oWorksheet = Api.GetActiveSheet();

• Set the reference cell and color
  To help the editor identify the target color, we first need to create a reference:

      const range1 = oWorksheet.GetRange("B1"); // Set your range for the color reference
      const colorReference = range1.SetFillColor(Api.CreateColorFromRGB(91, 155, 213)); // Set targeted background color
      const targetedColor = range1.GetFillColor();

  Here, we set the reference cell (B1) and its background color. The color is set to RGB(91, 155, 213). To use colors from the existing range, comment out this line:

  const colorReference = range1.SetFillColor(Api.CreateColorFromRGB(91, 155, 213));

• Define the targeted range and result cell
  Here we set the targeted range from A1 to A16. The result will be displayed in cell A17:

  const range2 = oWorksheet.GetRange("A1:A16"); // Set the targeted range on the spreadsheet
      const result = oWorksheet.GetRange("A17"); // Set the cell where the result will be displayed

• Initialize the sum variable
  We initialize the sum variable to 0, which will hold the total sum of the values:

      let sum = 0;
      let cellColorCode;

• Iterate through each cell in the targeted range
  This block iterates through each cell in the range A1, checks if the cell’s background color matches the reference color, and if so, adds the cell’s value to the sum:

    range2.ForEach(function (range) {
          const cellColor = range.GetFillColor();
         
          if (cellColor!== "No Fill"){
           cellColorCode = cellColor["color"]["ey"]
          } else {
              cellColorCode = null;
          }
          
          if (cellColorCode && cellColorCode === targetedColor["color"]["ey"]) {
              const value = range.GetValue();
              if (!isNaN(parseFloat(value))) {
                  sum += parseFloat(value); 
              }
          }
      });

• Display the result
  Finally, we set the value of cell A17 to display the calculated sum:

  result.SetValue(`The sum: ${sum}`)

The entire macro code is the following:

/*About the script:
This script will calculate the sum of the values in the range A1:A16 that have the same background color as the cell B1.
The result will be displayed in the cell A17.
Order of operations:
1) Set the cell for the color reference in the variable 'range1' 
2) Set the targeted fill color in the variable 'colorReference'
3) Set the targeted range in the variable 'range2'
3) Set the cell for dispalying the result in the variable 'result'
4) Before runing the macro, make sure that none of the cells in the range A1:A16 are in the active selection 
*/

(function () {
    const oWorksheet = Api.GetActiveSheet();
    const range1 = oWorksheet.GetRange("B1"); // Set your range for the color reference
    const colorReference = range1.SetFillColor(Api.CreateColorFromRGB(91, 155, 213)); /* Set targeted background color. 
To use colors from the existing range, comment out this line.*/
    const targetedColor = range1.GetFillColor();
    const range2 = oWorksheet.GetRange("A1:A16"); // Set the targeted range on the spreadsheet
    const result = oWorksheet.GetRange("A17"); // Set the cell where the result will be displayed
    let sum = 0;
    let cellColorCode;

    range2.ForEach(function (range) {
        const cellColor = range.GetFillColor();
       
        if (cellColor!== "No Fill"){
         cellColorCode = cellColor["color"]["ey"]
        } else {
            cellColorCode = null;
        }
        
        if (cellColorCode && cellColorCode === targetedColor["color"]["ey"]) {
            const value = range.GetValue();
            if (!isNaN(parseFloat(value))) {
                sum += parseFloat(value); 
            }
        }
    });
    result.SetValue(`The sum: ${sum}`)
})();

Let us run our macro and see how it works!

This little quirky macro is a powerful way to automate tasks and enhance your productivity. We hope it will become a useful addition to your toolkit.

Seize the opportunity to embrace the potential of the ONLYOFFICE API. Our vast collection of API methods can bring your ideas to fruition. Your feedback is highly valued. We welcome any questions or innovative concepts you might have and look forward to the possibility of collaboration. Best of luck in your exploratory endeavors!