Zenn
🐣

ExcelAPIを利用して祝日を1列で取得する方法

2023/05/12に公開
Google Sheets
tech

背景・概要

Spreadsheetで、日付の一覧を利用することがあります。
条件付き書式で、土日に色を簡単につけられるのですが、祝日は別途取得する必要があります。

毎年の祝日一覧を取得する方法として、GASを利用するのも1つですが、"ExcelAPI"もできるようです。

ExcelAPIを使ってみたところ、Spreadsheetで祝日を取得する場合に、再取得を出来ないようにする方法がわかりませんでした。
少しセルの値を変更しただけでも、値を再取得するので、すぐに 「ERROR: アクセス制限中です。1日お待ちください。」 という表示が出てしまいます。
しかしながら、せっかくなので、わからなかった部分はGASを利用して、試行錯誤してみました。

まずはExcelAPIで祝日を出す方法

ゴールは、取得した祝日一覧を利用して、条件付き書式で祝日に色をつけることです。
ExcelAPIを利用し、スプレッドシートのA列に祝日一覧を値として表示することです。

Spreadsheetの説明

列の説明です

  • A列：B列を値で貼り付けた祝日一覧（GASを利用）
  • B列：ExcelAPIで取得した祝日一覧
  • C列：連番（1〜23を入力）
  • D列：ExcelAPIで取得する日付、独自の休日があれば追加する
  • E列：D列の祝日名を表示
  • G2：エラー判断（TRUEでGASでB列をA列に値のみコピーする）

ExcelAPIで取得する

  • C3からC25まで、1〜23の番号を入力します ※23でなくても祝日数の最大数を超えていれば構いません
  • E2には、"=YEAR(TODAY())"と入れて今年を取得します

D3に下記を入力して、C列の連番に合わせて連続コピーをします

=IF(IMPORTXML("https://api.ExcelAPI.org/datetime/holiday-list?year="&E$2&"&line="&$C3,".")=0, "", IMPORTXML("https://api.ExcelAPI.org/datetime/holiday-list?year="&E$2&"&line="&$C3,"."))

E3に下記を入力して、C列の連番に合わせて連続コピーをします

=IF(D3="", "", IFERROR(IMPORTXML("https://api.ExcelAPI.org/datetime/holiday?date="&D3,"."), ""))

取得した祝日をリストする

B1には

=QUERY({D:D;F:F;H:H},"select * where year(Col1) >= 2022 order by Col1 asc")

エラーではない場合にTRUEを表示して、GASにつなげる

G2にはエラーではない場合に'TRUE'が返ります。
1つ以上のセルでエラーが発生している場合はFalseが返ります。

=IF(COUNTIF(C2:E25,"*ERROR*")+COUNTIF(C2:E25,"#N/A")>0,FALSE,TRUE)

祝日がすべて取得できたタイミングで、コピーするGAS

trueになった時にのみ、コピーを行うGASです。

トリガーは毎月1日の午前1時から2時に設定しました。
とはいえ、1月の初旬は、エラーが無くなるまで毎日トリガーを実行することになると思います。

function overwriteColumn() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const l2Value = sheet.getRange("G2").getValue();
  
  if (l2Value == true) {
    const range = sheet.getRange("B2:B");
    const values = range.getValues();
    
    for (let i = 0; i < values.length; i++) {
      const row = i + 2;
      const value = values[i][0];
      sheet.getRange(row, 1).setValue(value);
    }
  } else {
    return;
  }
}

条件付き書式で土日祝日に色をつける

それぞれの条件のカスタム変数のサンプルです。

  • 土曜
=ISNUMBER(FIND("土", $A7))
  • 日曜
=ISNUMBER(FIND("日", $A7))
  • 祝日
    ExcelAPIやgasで取得した祝日一覧（ここでは、holidaysという名前のシート）
=COUNTIF(IMPORTRANGE("https://docs.google.com/spreadsheets/d/xxxx", "holidays!A2:A"), "= "&$A7)>0

おわりに

**「ERROR: アクセス制限中です。1日お待ちください。」**というエラーが表示されたセルでも、関数を貼り直すと正常に表示される場合があります。
しかし手作業の方が早い場合もあり、その場合はGASは必要ありません。
手作業になると、祝日一覧のサイトから手動でコピー＆ペーストするだけなので、ExcelAPIも必要ありません。

ただし、自動化が目的であるため、Googleカレンダーから定期的に祝日を取得するGASを選択するのが良さそうです。
今回の記事は、Excel APIの学習を目的としたものでした。

ExcelAPIは使い方次第では、定常業務を楽にすることができそうです。
詳しくは公式サイトを御覧ください。

GitHubで編集を提案

Discussion