Googleスプレッドシートスケジュール表テンプレートの条件付き書式を更新するGASを作った

最近お絵かきもしてないしブログに書くことも特にないので、Webの仕事用に自作したGASについて書こうと思います。

長いスパンのプロジェクトではクライアント、チームとの進捗共有や自身のタスク管理のためにスケジュール表を作成する方が多いと思います。
Googleスプレッドシートはアプリのダウンロードも不要で無料でWeb上で共有できるのでスケジュール表を作成するツールとして大変オススメです✨

Googleスプレッドシートで作るスケジュール表テンプレート

仕事でスケジュール表を作る機会は多いと思いますが、スケジュール表を毎回作るのが面倒エクセルでいろいろな人が…

TORUBLOG

私は上記サイトで配布されているガントチャート式のスケジュール表テンプレートがわかりやすいので自分が使いやすいようにカスタマイズして利用させてもらっています😊

ただ、ガントバーの色分けをする条件付き書式を追加、カラー変更したい時に逐一条件付き書式を手動で追加、カラー設定をしないといけません。
1つ程度だったら問題ないのですが、5個10個条件付き書式の追加が必要だったり複数シートの更新が必要だと結構な手間でした🤔

そこでGoogle Apps Script（GAS）でシートに追加したカラー設定をワンクリックで条件付き書式に反映するスクリプトを作ってみました！
GAS、JavaScriptはまだまだ勉強中であまり詳しくないので綺麗なコードではないかもしれません…、スマートでない書き方をしていても見逃してください🤮

以下、スプレッドシートへのGAS追加方法とスクリプトの使い方の説明です。

カラー設定シートを追加する

まず、カラー設定用のシートを新規で追加します。シートの名前は「Color Settings」にしてください。
A列に条件付き書式のカテゴリー、B列のセルを設定したい色で塗りつぶします。

ついでに、スケジュールシートの「担当者」列のセルもデータの入力規則を設定して、「Color Settings」シートの値をプルダウンで選択できるようにしましょう。

='Color Settings'!$A:$A

入力規則の条件を「プルダウン（範囲）」にして上記範囲を指定します。

GASを追加する

メニューバーの「拡張機能」から「Apps Script」をクリックしスクリプト編集ページを開きます。

// ===================================================================
// ボタンを追加
// ===================================================================
function onOpen() {
  // UIの取得
  const ui = SpreadsheetApp.getUi()
  // メニューの表示名
  const menu = ui.createMenu('スクリプト実行');
  // メニューに追加するボタン
  menu.addItem('条件付き書式更新', 'setNewRules');
  // メニューを画面に追加
  menu.addToUi();
}

// ===================================================================
// スケジュール塗りつぶし処理
// ===================================================================

// 変数
//----------------------------------------------------------------- /
// シート名指定
const color_sheet = "Color Settings";


const TARGET = SpreadsheetApp.getActiveSpreadsheet(); // アクティブのスプレッドシート
const SHEET = SpreadsheetApp.getActiveSheet(); // アクティブのシート
const COLORSHEET = TARGET.getSheetByName(color_sheet);

// カラーセッティング取得
const COLORRANGE = COLORSHEET.getRange( 1, 2, COLORSHEET.getLastRow() ); // カラーコード一覧範囲
const color_data = COLORRANGE.getBackgrounds(); // セルの背景色カラーコード取得

// 新規条件付き書式追加
//----------------------------------------------------------------- /
function setNewRules() {
  let datas = getRules(); // 既存条件付き書式のデータ取得
  let range = SHEET.getRange(datas[1]); // 条件付き書式適用範囲を取得

  resetRules(); // 条件付き書式削除

  let rules = SHEET.getConditionalFormatRules(); // 条件付き書式

  // ガントチャートバー色分け条件式追加
  for ( let i=0; i < color_data.length; i++ ) {
    let formula = String(datas[0]);
    let regexp = /=AND\((.*?)=INDIRECT\("(.*?)"\)\)/;
    let v = i + 1;
    let bc = formula.replace(regexp, '=AND($1=INDIRECT("Color Settings!A' + v + '"))');

    let rule = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied(bc)
    .setBackground(color_data[i])
    .setRanges([range])
    .build();
    rules.push(rule);
  }
  // 偶数列塗りつぶし条件付き書式追加
  let rule = SpreadsheetApp.newConditionalFormatRule()
  .whenFormulaSatisfied('=AND(ROW()<>1,MOD(ROW(),2)=0)')
  .setBackground('#f8f8f8')
  .setRanges([range])
  .build();
  rules.push(rule);

  SHEET.setConditionalFormatRules(rules);
}

// スケジュールバーの条件付き書式ルール取得
//----------------------------------------------------------------- /
function getRules(rule) {
  let rules = SHEET.getConditionalFormatRules(); // 条件付き書式取得
  // let array = '';
  for ( let i=0; i < rules.length; i++ ) {
    let booleanCondition = rules[i].getBooleanCondition(); // ルール情報を取得
    if ( booleanCondition != null ) {
      let value = booleanCondition.getCriteriaValues(); // 条件式を取得

      // 一致する条件式のみ処理
      let pattern = /=AND\((.*?)=INDIRECT\("(.*?)"\)\)/;
      if ( pattern.test(value) === true ) { 
        // 範囲を取得
        let ranges = rules[i].getRanges();
        let range;
        for( let j=0; j < ranges.length; j++ ) {
          range = ranges[j].getA1Notation();
        }

        // [条件式, 範囲]で配列に格納
        let array = [value, range];

        return array;
      }
    }
  }
}

// 既存のスケジュールバー条件付き書式削除
//----------------------------------------------------------------- /
function resetRules() {
  let rules = SHEET.getConditionalFormatRules(); // 条件付き書式取得
  for ( let i=0; i < rules.length; i++ ) {
    let result = SHEET.getConditionalFormatRules(); // 条件付き書式再取得
    for ( let j=0; j < result.length; j++ ) {
      let booleanCondition = result[j].getBooleanCondition(); // ルール情報を取得
      if ( booleanCondition != null ) {
        let value = booleanCondition.getCriteriaValues(); // 条件式を取得
        // ガントチャートバー色分け条件式を削除
        let pattern = /=AND\((.*?)=INDIRECT\("(.*?)"\)\)/; // 
        if ( pattern.test(value) === true ) {
          result.splice(j, 1);
          SHEET.setConditionalFormatRules(result);
          break;
        }
        // 偶数列塗りつぶし条件を削除
        pattern = /=AND\(ROW\(\)<>1,MOD\(ROW\(\),2\)=0\)/;
        if ( pattern.test(value) === true ) {
          result.splice(j, 1);
          SHEET.setConditionalFormatRules(result);
          break;
        }
      }
    }
  }
}

上記が条件付き書式更新の関数+スクリプト実行ボタン追加の関数です。
既に設定されている色分け設定の条件付き書式の内容を取得後削除して「Color Settings」シートのデータから新規条件付き書式をアクティブシートに追加します。