スプレッドシートで土日祝日を自動色分け！祝日リストもGASでかんたん自動作成

2024年9月26日2024年10月7日

スプレッドシートで日付入力の際、土日祝日のセルを自動的に色分けしたい時ありませんか？

セルを手動で色分けとなると、毎回の作業がとても面倒ですよね。

自動的に色分けするには条件付き書式を使いますが、土日は関数だけで対応できるものの、祝日はリストを用意する必要があります。

祝日リストがあれば、色分け設定もスムーズに進められます。

そこで、この記事では祝日リストを簡単に作成する方法と、土日祝日の色分け方法をご紹介します。
土日祝日のセルが自動で色付けされるようになれば、面倒な手作業がなくなります。

完成イメージ

条件付き書式を使って土、日、祝日の行をそれぞれ色分けします。

色分けの例

土曜日：青
日曜日：赤
祝日：緑

GASを使って祝日リストを作成する

Google Apps Script（GAS）を使って祝日リストを作成
参照データ：内閣府のホームページ　「国民の祝日」について

条件付き書式を設定する前段階として、祝日リストを作成します。

祝日データは内閣府のホームページから取得できます。

手動での作成は手間がかかるため、Google Apps Scriptを使って自動で取得します。

祝日リスト作成のスクリプト

function importHolidaysFromGov() {
  const url = 'https://www8.cao.go.jp/chosei/shukujitsu/syukujitsu.csv'; // 日本政府の祝日CSVファイルURL
  const response = UrlFetchApp.fetch(url);
  const csv = response.getContentText('Shift_JIS'); // 祝日CSVはShift_JISでエンコードされているので指定する
  
  // CSVをパース
  const csvData = Utilities.parseCsv(csv);
  
  // スプレッドシートのシートを取得（存在しない場合は作成）
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('祝日リスト');
  
  if (!sheet) {
    sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet('祝日リスト');
  }
  
  sheet.clear(); // 既存のデータをクリア
  
  // ヘッダーを追加
  sheet.getRange(1, 1).setValue('日付');
  sheet.getRange(1, 2).setValue('祝日名');
  
  // 祝日データを書き込み
  for (let i = 1; i < csvData.length; i++) {
    const row = csvData[i];
    const holidayDate = row[0]; // 日付
    const holidayName = row[1]; // 祝日名
    
    // 日付と祝日名をスプレッドシートに書き込む
    sheet.getRange(i + 1, 1).setValue(holidayDate);
    sheet.getRange(i + 1, 2).setValue(holidayName);
  }
}

スクリプト画面の右上にあるコピーボタンでコピーできます。

スクリプトの説明

UrlFetchApp.fetch(url):

この関数を使って、指定したURLからデータを取得します。
今回は、日本政府が提供する祝日CSVファイルを取得しています。

getContentText('Shift_JIS'):

日本の祝日CSVファイルはShift_JISエンコーディングで保存されているため、getContentTextでShift_JISを指定し、正しい文字コードでデータを取得します。
これにより、データが文字化けせずに利用できるようになります。

Utilities.parseCsv(csv):

取得したCSVデータを2次元配列に変換します。各行が配列の1つの要素になり、日付と祝日名がそれぞれの要素として格納されます。
たとえば、csvData[1][0]には1行目の「日付」、csvData[1][1]には「祝日名」が入ります。

getSheetByName('祝日リスト'):

既に「祝日リスト」という名前のシートが存在するかどうかを確認します。

insertSheet('祝日リスト'):

「祝日リスト」という名前のシートが存在しない場合、新しいシートを作成します。
このように、シートが存在しない場合でも自動的にシートを作成できるため、スクリプトを安全に実行できます。

sheet.clear():

シート内の既存データをすべてクリアします。
これにより、以前の祝日データが残っている場合でも上書きできます。

getRange(1, 1).setValue('日付') / getRange(1, 2).setValue('祝日名'):

シートの1行目に「日付」と「祝日名」というヘッダーを設定します。
これで、後にデータがどの列に入力されるかがわかりやすくなります。

for (let i = 1; i < csvData.length; i++):

CSVのデータをループで処理します。
最初の行（csvData[0]）はヘッダー行（列名）なので、1行目からデータの書き込みを開始します。

new Date(row[0]):

CSVの1列目にある日付データを**Dateオブジェクト**として取得します。これにより、日付として正しく扱えるようになります。

sheet.getRange(i + 1, 1).setValue(holidayDate) / sheet.getRange(i + 1, 2).setValue(holidayName):

取得した日付と祝日名をスプレッドシートの該当セルに書き込みます。
i + 1にすることで、1行目のヘッダーを飛ばし、データは2行目から書き込まれます。

手順：

STEP

ツールバーの『拡張機能 – Apps Script』を選択

Googleスプレッドシートを開き、メニューの「拡張機能」から「Apps Script」をクリックしてGASエディタを開きます。

STEP

スクリプトを貼付ける

画像に alt 属性が指定されていません。ファイル名: fc6b4ae7cb221643799268a803586860.jpg

function myFunction(){

}

が最初から入っているため、消去して先ほどのスクリプトを貼り付けます。

STEP

『プロジェクトを保存』を押す

スクリプトエディタの左上にある保存ボタンを押して、スクリプトを保存します。

STEP

『実行』を押す

「実行」ボタンを押してスクリプトを実行します。

初めてそのスクリプトを実行する場合は権限の確認が必要です。

そのため、『権限を確認』を押します。

許可の詳細手順

「詳細」をクリックします。

「無題のプロジェクト（安全ではないページ）に移動」をクリックします。

「許可」をクリックします。

このスクリプトで内閣府：「国民の祝日」についてのページ内にあるCSVデータを取得します。

（1955年からの祝日データをすべて取得）

スクリプトを実行すると「祝日リスト」というシートが作成されます。

条件付き書式で土日祝日を色分けする

表の作成例

A7:A37：日付入力（31日分）
B7:B37：祝日名の入力
色付けする行のA:J列までを対象

土曜日の色付け：条件付き書式

色付けの範囲を選択します（例: A7:J37）。
メニューから「表示形式」→「条件付き書式」を選択します。
「条件付き書式のルール」の下部にある「カスタム数式」を選びます。
下のカスタム数式を入力します。
セルの色設定をします。

=AND(ISNUMBER($A7), WEEKDAY($A7, 2) = 6)

数式の説明

ISNUMBER($A7):

A7セルの値が数値かどうかを確認する部分です。
Googleスプレッドシートでは、日付は内部的に数値として扱われます。（例えば、2024年1月1日は “45121” という数値で扱われます）。このため、A7セルに有効な日付が入っている場合にTRUEを返します。
A7セルが空白や無効なデータの場合はFALSEを返すため、条件付き書式が適用されません。

WEEKDAY($A7, 2) = 6:

WEEKDAY関数は、指定した日付が何曜日かを数値で返す関数です。
WEEKDAY($A7, 2)では、月曜日を1、日曜日を7とする方法で曜日を返します。
この場合、6は土曜日に対応します。**A7の日付が土曜日である場合にTRUE**を返します。

AND関数:

AND関数は、複数の条件がすべてTRUEである場合にTRUEを返します。
この場合、ISNUMBER($A7)とWEEKDAY($A7, 2) = 6の両方がTRUEの時に、数式全体がTRUEを返します。
A7に有効な日付があり、かつそれが土曜日である場合のみ、条件付き書式が適用されることになります。

日曜日の色付け：条件付き書式

色付けの範囲を選択します（例: A7:J37）。
メニューから「表示形式」→「条件付き書式」を選択します。
「条件付き書式のルール」の下部にある「カスタム数式」を選びます。
下のカスタム数式を入力します。
セルの色設定をします。

=AND(ISNUMBER($A7), WEEKDAY($A7, 2) = 7)

数式の説明

ISNUMBER($A7):

これは、A7セルの値が数値かどうかを確認します。
スプレッドシートでは、日付は内部的に数値として扱われます。（例えば、2024年1月1日は “45121” という数値として認識されます）ため、A7セルに有効な日付が入っている場合にTRUEを返します。
A7セルが空白であったり、日付以外のデータが入っている場合はFALSEを返すので、条件付き書式が適用されません。

WEEKDAY($A7, 2) = 7:

WEEKDAY関数は、指定した日付が何曜日かを数値で返す関数です。
WEEKDAY($A7, 2)では、月曜日を1、日曜日を7とする設定になっています。
この数式では= 7となっているため、A7セルの日付が**日曜日の場合にTRUE**を返します。

AND関数:

AND関数は、複数の条件がすべてTRUEの場合にのみTRUEを返します。
ISNUMBER($A7)とWEEKDAY($A7, 2) = 7の両方がTRUEの場合に数式全体がTRUEを返し、条件付き書式が適用されます。
A7に有効な日付があり、かつそれが日曜日である場合のみ、条件付き書式が適用されるようになります。

祝日リストから祝日名を抽出

条件付き書式のカスタム数式は別シートを参照できないため、日付入力するシートに祝日名の列を追加しています。（B列）

B7セルに以下の数式を入力します。
オートフィルでB37セル（31日分）までコピーします。

=IFERROR(INDEX('祝日リスト'!$B$1:$B, MATCH(A7, '祝日リスト'!$A$1:$A, 0)), "")

数式の説明

MATCH(A7, '祝日リスト'!$A$1:$A, 0):

MATCH関数は、A7セルにある日付を「祝日リスト」シートのA列（'祝日リスト'!$A$1:$A）で探し、その**一致する位置（行番号）**を返します。
A7は、現在のシートのA列にある日付（例えば2024年9月15日など）です。
'祝日リスト'!$A$1:$Aは、別のシート「祝日リスト」のA列にある日付の範囲で、ここから該当する日付を検索します。
0は完全一致を意味し、正確に同じ日付が見つかった場合の行番号を返します。

INDEX('祝日リスト'!$B$1:$B, 行番号):

INDEX関数は、「祝日リスト」シートのB列（'祝日リスト'!$B$1:$B）にある祝日名の範囲から、先ほどMATCH関数で取得した行番号を使って、該当する祝日名を返します。
例えば、MATCH関数が「3行目」と判定した場合、INDEX関数は「祝日リスト」のB列の3行目にある祝日名を取得します。

IFERROR(..., ""):

IFERROR関数は、数式の中でエラーが発生した場合に、エラーメッセージの代わりに指定した値を返します。この数式では、エラーが発生した場合（つまり、祝日リストに該当の日付がない場合）に、**空白（””）**を返すように設定されています。
例えば、A7の日付が祝日リストに存在しない場合、通常は#N/Aエラーが表示されますが、このIFERRORを使うことでエラー表示の代わりに空白を返します。

この数式では「日付」に基づいて「祝日名」を取得します。

別シート「祝日リスト」から対応する祝日名を検索し、その結果をセルに表示します。

該当する祝日が見つからない場合は、空白を返すように設定されています。

祝日の色付け：条件付き書式

色付けの範囲を選択します（例: A5:J37）。
メニューから「表示形式」→「条件付き書式」を選択します。
「条件付き書式のルール」の下部にある「カスタム数式」を選びます。
下のカスタム数式を入力します。
セルの色設定をします。

=AND(ISNUMBER($A7), $B7<>"")

数式の説明

ISNUMBER($A7):

A7セルに有効な日付（数値）が入力されているかどうかを確認します。
スプレッドシートでは、日付は内部的に数値として扱われます。（例えば、2024年1月1日は数値”45121″として認識されるため、ISNUMBER関数はこれをTRUEと判定します。）
もしA7セルが空白だったり、無効なデータが入っている場合はFALSEを返します。これにより、無効なセルには条件付き書式が適用されません。

$B7<>"":