再利用可能なONLYOFFICEマクロでスプレッドシートのテキストをクリーンアップする

スプレッドシートのクリーンアップを繰り返し行うには、スプレッドシートエディターで小さなJavaScriptマクロを作成し、変更したいテキスト範囲のみを対象として、まず複製したワークブックでテストしてください。以下の例では、メール列から非表示文字を削除し、余分な空白をトリミングしながら、すべての行の位置を維持します。

ONLYOFFICE マクロはエディター内で実行され、Office JavaScript API を使用します。公式ドキュメントでは、ワークフローは依然として[表示] > [マクロ]を開き、コードを記述または貼り付け、エディターで実行すると説明されています。現在のドキュメントで確認されている最近の変更では、この基本的なプロセスは変更されていません。マクロ自体は、ソフトウェアテストのレポートではなく、説明のための再利用可能なスクリプトです。ONLYOFFICEマクロのクイックスタートとスプレッドシートエディターの [表示] タブのリファレンスを参照してください。

安全なコピーを作成し、クリーンアップを定義します

ワークブックに、1行目にヘッダー、B列のB2からB500までメールアドレスが入力された連絡先リストがあるとします。インポートされた値の中には、改行、タブ、先頭と末尾の空白が含まれている場合があります。この例では、該当するテキスト列のみをクリーンアップします。コードを実行する前に、シートに合わせて範囲を調整してください。

• ワークブックのコピーを別途保存するか、ワークシートを複製してください。出力結果を確認するまでは、元のワークブックはそのままにしておいてください。
• 選択した範囲に、数式、スペースに依存するID、または正確な間隔を維持する必要のあるデータではなく、生のテキストが含まれていることを確認してください。
• ヘッダー行は範囲外に含めてください。このマクロはヘッダーを検出したり、変更しても安全な列を判断したりしません。

使用範囲全体ではなく、B2:B500などの列指定範囲を使用してください。すべてのセルを無差別に消去すると、名前、住所、コード、または数式の結果が意図しない形で変更される可能性があります。

マクロエディタを開いてスクリプトを追加する

ONLYOFFICE スプレッドシート エディターでワークブックを開きます。[表示] > [マクロ]を選択してマクロ エディターを開き、マクロを作成するか、エディター内のスターター コードを置き換えます。ONLYOFFICE マクロは JavaScript 構文と Office API を使用するため、VBA マクロとは異なります。エディターのマクロ ワークフローとサポートされている API メソッドについては、公式のマクロ入門ガイドを参照してください。

この例を貼り付けてから、B2:B500実際のテキストのみの範囲に変更してください。

(function () {
    var sheet = Api.GetActiveSheet();
    var range = sheet.GetRange("B2:B500");
    var rows = range.GetValue();
    var functions = Api.WorksheetFunction;

    for (var i = 0; i < rows.length; i++) {
        var value = rows[i][0];
        if (typeof value === "string") {
            value = functions.CLEAN(value);
            rows[i][0] = value.replace(/^\s+|\s+$/g, "")
                              .replace(/\s{2,}/g, " ");
        }
    }

    range.SetValue(rows);
})();

このマクロは、範囲を2次元配列に読み込み、文字列セルを処理し、配列を同じ範囲に書き戻します。CLEAN非表示文字を削除し、2つの正規表現置換によって、端の空白を削除し、文字列内の繰り返し空白を1つのスペースに減らします。APIリファレンスでは、GetValue範囲に対して2次元配列を返すことができ、SetValue配列を受け入れることが確認されています。GetValue 、SetValue、およびCLEANのリファレンスには、これらの呼び出しに関するドキュメントがあります。

このスクリプトは値を書き戻すため、数式を含むセルには指定しないでください。数式セルは、現在計算されている値で置き換えられる可能性があります。また、重複レコードの削除、大文字小文字の標準化、住所の配達可能性の検証、電話番号のフォーマット変更は行いません。これらは別のクリーンアップルールであり、個別に設計およびテストする必要があります。

管理された範囲で実行してください

1. 範囲指定をもう一度確認してください。例えば、リストが240行目で終了する場合は、数百行に及ぶ未使用の行を処理するのではなく、B2:B240を使用してください。
2. マクロエディタで「実行」をクリックします。エディタが構文エラーを報告した場合は、範囲を変更したり、ロジックを追加したりする前に、引用符、括弧、中括弧を確認してください。
3. ワークシートに戻り、修正が必要な値、既に正しい形式になっている値、および空白のセルなど、いくつかのセルを確認してください。

範囲を狭く設定することで、効果を理解しやすくなります。出力が正しければ、範囲を意図的に編集することで、別のシートでマクロを再利用できます。列文字や行境界が別のワークブックでも有効であるとは限らないので注意してください。

Macros コマンドが見つからない場合、またはコードが実行できない場合は、エディタでマクロが有効になっているか、ホスト アプリケーションの設定で無効になっているかを確認してください。オンライン エディタでは、管理者がマクロの有効化/無効化を制御できます。製品のスプレッドシート エディタの詳細設定には、ユーザー向けのマクロ オプションが記載されています。不明なソースからのファイルに対して自動実行を有効にしないでください。理解できるコードのみを実行してください。

マクロを再利用する前に結果を確認してください。

見た目だけで判断するのではなく、クリーニング後の列を元のコピーと比較してください。空白でないメールアドレスがすべて同じ行にあり、連絡先が消えておらず、正規化しようとした文字とスペース以外に違いがないことを確認してください。選択した範囲の上部、中央、および末尾付近のサンプルを確認してください。エディタで表示できる場合は、値の先頭または末尾のスペースを検索し、以前に改行や制御文字が含まれていたインポートされた値を調べてください。

また、行数と、その列を使用する依存する数式、フィルター、またはルックアップも比較してください。数式の結果が予期せず変更された場合は、編集を取り消すか、作業コピーを復元してください。マクロは、生のテキストセルに限定する必要があります。大規模なワークブックや業務上重要なワークブックの場合は、クリーンアップ済みの列全体をバックアップと比較するか、変更内容を確認できるように、まず別の出力列を使用してください。

清掃作業は繰り返し行えるようにする

最初の実行が正しく行われたら、エディターのマクロワークフローに従って、マクロがドキュメントとともに利用できるようにブックを保存してください。1つのブックに保存されたマクロが、すべての新しいファイルに自動的に表示されるとは限りません。繰り返しインポートする場合は、クリーンなテンプレートまたは保護されたソースコピーを保持し、マクロが想定する範囲を文書化し、シートレイアウトが変更されたときにそれを更新してください。ONLYOFFICE マクロは、決定論的なクリーンアップルールに役立ちますが、値が事実として正しいかどうか、または変更がデータセットに適しているかどうかを判断することはできません。

信頼できる手順はシンプルです。ソースのコピーを保存し、既知の範囲を対象とし、定義された変換を1回実行し、結果を比較してから、データを共有したり他の場所にインポートしたりします。