【Googleスプレッドシート】Apps Scriptで条件付き書式を一括設定!プログラム的な書式制御

条件付き書式を大量のセル範囲に適用するとき、一つひとつ設定するのは手間がかかります。特に数十個のルールを適用する場合、手作業ではミスが発生しやすくなります。この記事では、GoogleスプレッドシートのApps Scriptを使って条件付き書式を一括で設定する方法を解説します。プログラムでルールを制御することで、作業効率が大幅に向上します。

Apps Scriptで条件付き書式を操作する基本

条件付き書式ルールは、スプレッドシートの各シートに配列として保存されています。Apps Scriptでは、getConditionalFormatRules()で現在のルール一覧を取得し、setConditionalFormatRules()で新しいルールの配列に置き換えます。ルール一つひとつはConditionalFormatRuleBuilderクラスを使って作成します。このビルダーは、条件(いつ書式を適用するか)と書式スタイル(背景色や文字色など)を設定し、最後にbuild()メソッドでルールオブジェクトを生成します。スクリプト実行時には、スプレッドシートの承認が必要となる場合があります。

一括設定の手順

事前準備

1. スプレッドシートを開く
   条件付き書式を設定するスプレッドシートを開きます。スクリプトから操作するため、そのシートにデータが存在することを確認します。
2. スクリプトエディタを起動する
   メニューの「拡張機能」から「Apps Script」を選択します。新しいスクリプトプロジェクトが開きます。

コーディング

1. 関数を作成する
   スクリプトエディタに以下のコードを記述します。関数名は任意ですが、ここでは「setConditionalFormat」とします。

   function setConditionalFormat() {
     var sheet = SpreadsheetApp.getActiveSheet();
     var range = sheet.getRange('A1:A10');
     var rule = SpreadsheetApp.newConditionalFormatRule()
       .whenNumberGreaterThan(100)
       .setBackground('red')
       .setRanges([range])
       .build();
     var rules = sheet.getConditionalFormatRules();
     rules.push(rule);
     sheet.setConditionalFormatRules(rules);
   }

2. コードの解説
   getActiveSheet()で現在のシートを取得し、getRange()で対象範囲を指定します。newConditionalFormatRule()でビルダーを作成し、whenNumberGreaterThan(100)で数値が100より大きいセルに絞り込みます。setBackground(‘red’)で背景色を赤に設定し、setRanges()で適用範囲を指定します。build()でルールオブジェクトを生成し、現在のルール配列に追加してからsetConditionalFormatRules()で更新します。

スクリプトの実行と確認

1. 関数を実行する
   スクリプトエディタの実行ボタン(▷)をクリックします。初回は承認画面が表示されるため、内容を確認して許可します。
2. 条件付き書式を確認する
   スプレッドシートに戻り、A1:A10のセルに100より大きい数値を入力します。該当セルが赤色になっていれば成功です。

応用:複数ルールの一括設定

1. ルール配列を直接作成する
   複数のルールをまとめて設定するには、ルールの配列を構築してから一度に設定します。次の例では、数値が50未満のセルを緑色、100以上のセルを赤色にする2つのルールを同時に追加します。

   function setMultipleRules() {
     var sheet = SpreadsheetApp.getActiveSheet();
     var range = sheet.getRange('A1:A20');
     var rule1 = SpreadsheetApp.newConditionalFormatRule()
       .whenNumberLessThan(50)
       .setBackground('green')
       .setRanges([range])
       .build();
     var rule2 = SpreadsheetApp.newConditionalFormatRule()
       .whenNumberGreaterThanOrEqualTo(100)
       .setBackground('red')
       .setRanges([range])
       .build();
     sheet.setConditionalFormatRules([rule1, rule2]);
   }

   既存のルールをクリアせずに追加したい場合は、一旦getConditionalFormatRules()で取得してからpushします。

2. 全シートに一括適用する
   すべてのシートに同じ条件付き書式を設定するには、Spreadsheetオブジェクトから各シートをループします。

   function applyToAllSheets() {
     var ss = SpreadsheetApp.getActiveSpreadsheet();
     var sheets = ss.getSheets();
     sheets.forEach(function(sheet) {
       var range = sheet.getRange('A:A');
       var rule = SpreadsheetApp.newConditionalFormatRule()
         .whenTextContains('エラー')
         .setBackground('yellow')
         .setRanges([range])
         .build();
       sheet.setConditionalFormatRules([rule]);
     });
   }

   この例では、各シートのA列で「エラー」を含むセルを黄色にします。

注意点とよくあるトラブル

ルールが上書きされる

setConditionalFormatRules()は、現在のルールを新しい配列で完全に置き換えます。既存のルールを残したい場合は、必ずgetConditionalFormatRules()で取得し、配列に追加した上で設定します。

承認が必要な場合がある

スクリプトを初めて実行するとき、スプレッドシートの変更権限を求めるダイアログが表示されます。承認しないと実行できません。組織のアカウントでは管理者の承認が必要な場合もあります。

シート名を変更すると範囲がずれる

スクリプト内でシート名を直接指定している場合、シート名を変更するとスクリプトが正しく動作しなくなります。シート名を変更するときはスクリプトも更新する必要があります。

UI設定との比較

まとめ

この記事では、Apps Scriptを使って条件付き書式をプログラムで一括設定する方法を解説しました。newConditionalFormatRule()ビルダーとsetConditionalFormatRules()メソッドを組み合わせることで、大量のルールも効率的に管理できます。特に「全シートに同じルールを適用する」「複数の条件を同時に設定する」といった作業で威力を発揮します。次に試す応用としては、セルの値に応じてアイコンセットやカラースケールを設定することも可能です。スクリプトエディタで他のメソッドを調べて、より高度な書式制御に挑戦してみてください。