ONLYOFFICEマクロで重複を削除する

今回は、スプレッドシートの選択範囲から重複を削除する小さなマクロについてご説明します。機能的には、Excelで広く使われているRemove duplicatesマクロに似ています。しかし、JavaScriptベースのマクロであるため、全体のアプローチは若干異なるでしょう。

ONLYOFFICEマクロについて

Microsoft Excelを使いこなしている方なら、VBAマクロをご存じでしょう。それは、日常的なタスクを自動化するための小さなスクリプトです。それはデータの再構築であったり、セル範囲に複数の値を挿入することです。ONLYOFFICEマクロは、JavaScriptの構文とドキュメントビルダーAPIのメソッドに基づいています。JavaSriptベースのマクロは、使いやすく、クロスプラットフォームで、安全です。そのため、VBAベースのものよりも大きなアドバンテージがあります。

参照用マクロ

参考までに、Highlight Duplicatesマクロを使用します。このONLYOFFICEマクロは、選択された領域内の重複を異なる色でハイライトします。サンプルコードは、APIドキュメントページのマクロサンプルセクションで見つけることができます。もう少し詳しく見てみましょう。

まず、重複していない値を持つセルには白の塗りつぶしを設定します。重複するセルについては、固有の色を持つ配列を作成します。これは、CreateColorFromRGB APIメソッドを実装することで実現します。

(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++];}

次に、アクティブシート上の選択範囲をターゲットにして、ForEachメソッドを実行します。この選択範囲を通過して、重複するセルに追加の値を代入します。

 // 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);
    });

ここでもう一度、すべてのセルに目を通します。もしセルに上で割り当てた追加の値があれば、getColor関数を実行します。この関数は複製されたセルに固有の塗りつぶし色を選びます。そして、SetFillColorメソッドを実行してこの色を適用します。残りの重複しないセルは白の背景で塗りつぶされます。

  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);
        }
    }
 });

新しいマクロを作成する

重複削除マクロは、重複する値を強調表示するものではありません。選択範囲から取り除くだけです。この場合、重複する値を検出する部分のみが必要となります。

 (function ()
{
    // Getting an active sheet
    var activeSheet = Api.ActiveSheet;
    // Getting selection on the active sheet
    var selection = activeSheet.Selection;
    // Map of cell values
    var mapValues = {};
    // All cells range
    selection.ForEach(function (range) {  
        // Getting value from cell
        var value = range.GetValue();   
         // If the cell does not have a duplicate value
        if (!mapValues.hasOwnProperty(value)) {   
         // We set this value to 0 
            mapValues[value] = 0;  
       }
  });
})();

あとは、重複したセルの内容をクリアするメソッドが必要です。Clearメソッドを使用することをお勧めします。このメソッドは、スプレッドシートの現在の範囲をクリアします。このため、このタスクの完璧な候補となります。このメソッドを、セルに重複した値がある場合に実行する else 文に含めます。

 (function () 
{
    // Getting an active sheet
    var activeSheet = Api.ActiveSheet;
    // Getting selection on the active sheet
    var selection = activeSheet.Selection;
    // Map of cell values
    var mapValues = {};
    // All cells range
    selection.ForEach(function (range) { 
        // Getting value from cell
        var value = range.GetValue();  
         // If the cell does not have a duplicate value
        if (!mapValues.hasOwnProperty(value)) {   
        // We set this value to 0 
        mapValues[value] = 0;  
        }
        // If the cell has a duplicate value
        else {  
        // We clear the content of the cell
            range.Clear();       
    }
    });
})();

このマクロは、APIのメソッドを実装することでできることの多くの例の一つに過ぎません。このマクロは、APIのメソッドを実装することでできる多くの例のひとつです。あなたのアイデアやマクロを私たちと共有してください。私たちは、議論と協力にオープンです。探求的な努力に幸あれ!

ONLYOFFICEのGitHub