GASでスプレッドシート条件付き書式を自動設定

GASでスプレッドシート条件付き書式を自動設定

こんにちは、GASおじです。今回は中小企業の経営者やフリーランスの皆さん向けに、Google Apps Script（以下GAS）を使ってスプレッドシートの条件付き書式を自動で設定する方法をお伝えします。スプレッドシートの見やすさをグッと上げて、日々の業務効率化に役立ててくださいね。

■ 条件付き書式の自動設定で何が変わる？

スプレッドシートって、見た目がゴチャゴチャしていると使いづらいですよね。条件付き書式を上手く使うと、例えば「売上が目標未達なら赤色で強調」「期限切れのタスクは黄色に」など、一目で重要な情報がわかるようになります。

でも、手作業で設定するのは面倒だし、誤って既存データを消したり、書式が崩れるリスクもあります。そこでGASで自動化することで、

・既存データはそのまま残しつつ書式だけ追加

・バッチ処理で効率的に設定

・もしものためにバックアップも自動で作成

といった安心・安全な運用が可能です。

■ GASで条件付き書式を設定するポイント

おじさんが気をつけているのは以下の点です。

・既存シートは変更しない条件付き書式の設定は新規シートや新規ファイルに書き出す形で行い、元データは絶対に直接上書きしません。

・入力検証を入れる範囲指定や条件の妥当性をチェックして、誤動作を防止。

・バッチで書き込みシート操作はまとめて行い、実行時間を短縮。

・バックアップを作成処理開始時に日時付きのコピーを作成し、復元も可能に。

・Googleアカウントの利用枠内で運用追加の専用SaaS料金なし。ただしApps Scriptのquota・実行上限あり。ここは経費削減のポイントです。

■ 具体的なコード例

下記コードは、元データが入った「元データ」シートの内容を複製し、新しい日時付きシートに条件付き書式を設定します。既存データは一切触りません。

function setConditionalFormatting() {

const ss = SpreadsheetApp.getActiveSpreadsheet();

const sourceSheet = ss.getSheetByName('元データ');

if (!sourceSheet) {

throw new Error('元データシートが見つかりません');

}

// バックアップシート名作成

const timestamp = Utilities.formatDate(new Date(), ss.getSpreadsheetTimeZone(), 'yyyyMMdd_HHmmss');

const backupName = '元データ_backup_' + timestamp;

// バックアップ作成（元データのコピー）

sourceSheet.copyTo(ss).setName(backupName);

// 新規シート作成（日時付き）

const newSheetName = '条件付き書式_' + timestamp;

const newSheet = ss.insertSheet(newSheetName);

// 元データの値を取得（入力検証）

const dataRange = sourceSheet.getDataRange();

const values = dataRange.getValues();

if(values.length === 0){

throw new Error('元データシートにデータがありません');

}

// 新しいシートに値を一括セット（バッチ書き込み）

newSheet.getRange(1, 1, values.length, values[0].length).setValues(values);

// 条件付き書式ルールを作成

// 例: 2列目（B列）が100未満なら赤背景

const range = newSheet.getRange(2, 2, values.length - 1, 1); // ヘッダー除く2列目

const rule = SpreadsheetApp.newConditionalFormatRule()

.whenNumberLessThan(100)

.setBackground('#FFCCCC')

.setRanges([range])

.build();

// 既存の条件付き書式取得（もしあれば）

const rules = newSheet.getConditionalFormatRules();

rules.push(rule);

newSheet.setConditionalFormatRules(rules);

Logger.log('条件付き書式を設定した新シート「' + newSheetName + '」を作成しました。');

}

■ 初期設定と必要権限

このスクリプトを動かすには、Googleスプレッドシートの編集権限が必要です。また、初回実行時にGASから以下の権限を求められます。

・スプレッドシートの閲覧・編集権限

・スプレッドシートのコピー・作成権限

これを許可することで、バックアップ作成や条件付き書式の設定が可能になります。

■ 個人情報の取扱いとセキュリティ

スクリプトはGoogleアカウントの利用枠内で動作し、クラウド上で処理されます。個人情報や顧客データを含むスプレッドシートを扱う場合は、アカウントの権限管理に注意してください。また、外部サービスへのデータ送信は一切していませんので、社内運用でも安心して使えます。

□ installable triggerと復元手順

この処理は手動実行が基本ですが、定期的に条件付き書式を更新したい場合はinstallable trigger（時間主導型トリガー）を設定しましょう。

・スクリプトエディタの「編集」→「現在のプロジェクトのトリガー」から新規作成

・「時間主導型」→「日付ベースのタイマー」や「分ベースのタイマー」を選択し実行頻度を設定

復元手順はバックアップシートから元の名前にリネームするだけ。バックアップは日時付きで残るので、複数回の復元も安心です。

□ コスト削減効果（仮定）

例えば、手作業で月に2時間かかっていた条件付き書式の設定をGASで自動化し、月1回の更新に削減できたとしましょう。時間単価を3,000円として、2時間 × 12ヶ月 × 3,000円 = 72,000円／年 の人件費を削減可能。追加のSaaS料金は不要で、Googleアカウントの利用枠内で動くため、月額費用は増えません。

■ GASの相談や導入サポートを見たい方へ

この記事と近い作業をGoogleシートやGASで整えたい方は、Gasおじのココナラ出品一覧もご覧ください。

■ 関連サービス

GASおじのココナラ出品一覧

https://coconala.com/users/2259778

日々の記録、確認、通知、投稿準備などを、GoogleスプレッドシートとGASで無理なく自動化する相談ができます。