自作カレンダーにGoogleカレンダーのデータを一瞬で反映させる方法 (Googleカレンダー→GAS→スプレッドシート→EXCEL)

はじめに

Googleカレンダーのデータを一気にオリジナル様式のカレンダーや予定表に流し込みたい事案が発生したため、Googleスプレッドシートにデータを自動でインポートする仕組みを作ってみました。
予定表やシステム手帳の様式等自由な様式に予定データを表示できて、Excelに変換もできるので、色々使い道があると思います。

GAS（GoogleAppsScript）でGoogleスプレッドシートにデータを頂き、スプレッドシートの関数を利用してカレンダーを作りました。

GoogleカレンダーからGoogleスプレッドシートで作成した複数の様式のカレンダーにデータを出力している写真

今回のゴール

Googleカレンダーの予定が入ったGoogleスプレッドシートのカレンダーを作る。

頑張れば、システム手帳の予定表や日記帳、イベントの出欠調整表の自作も可能です。

必要なもの

Googleアカウント

持っていない人は作成してください。

　https://support.google.com/accounts/answer/27441?hl=ja

Googleスプレッドシート作成

https://docs.google.com/spreadsheets/u/0/

↑URLから作成できます。

Googleスプレッドシートが開きます。

シート作成

スプレッドシート名は任意で大丈夫です。
まず、設定シートを作成します。
A1にカレンダーID、A2に設定　年、A3に設定　月、A4に取得開始日、A5に取得終了日、A7に今日、A8に設定日を入力して表題を作成します。
設定　年と設定　月は、オリジナルカレンダーの年月を指定します。
取得開始日と取得終了日は、Googleカレンダーからデータを取得したい日にちの開始日と終了日を入力します。後の設定で、祝日も自動で取得したいと思います。
今日は、B7に=now()と入力し、今日の日付を設定します。
設定日は、B8に=DATEVALUE(B2&"/"&B3&"/1")と入力し、カレンダーの初めの日を設定します。

B7の表示形式を数字、日付の順にクリックし、yyyy/mm/ddの形式に変更します。
B8も同様に表示形式を日付に変更します。

シート名を設定に変更しておきます。

GoogleカレンダーID取得

https://calendar.google.com/calendar/

Googleカレンダーにアクセスし、右上の歯車のマーク、設定の順にクリックします。

左側でマイカレンダーの設定から、カレンダーを選択し、右側を下にスライドすると、カレンダーIDと表記された箇所があります。
デフォルトはGoogleアカウントが入っていると思います。複数カレンダーを作成されている方は、ユニークIDが表示されます。これをコピーします。

Googleスプレッドシートに戻って貼り付けます。

シート名を設定にします。

シート名「シート1」をダブルクリックすると編集できます。

シートを追加していきます。
左にある「+」をクリックすると新たなシートが作成されます。
先ほどと同じ方法でシート名を予定取得に変更します。

同じようにシートを追加していき、予定設定、祝日、記念日、月間、年間、予定表の順に作成します。
※今回は、月間のカレンダーの作成方法を紹介します。

GASでGoogleカレンダーの予定を取得する

さっそくカレンダーの予定を取得します。
と、その前に
・・・GASって何？
簡単に言うと、Googleが開発した軽量アプリケーション開発用のスクリプト言語です。
Google Apps Script 入門

GASを作成

シートのツール、スクリプトエディタの順にクリックします。

すると、GASのスクリプトエディタが起動します。

次のソースコードを入力します。

ソースコード

/* 指定月のカレンダーからイベントを取得する */
function getCalendar() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet(); 
  var msheet= sheet.getSheetByName('設定'); //設定シートを取得
  var range = msheet.getRange("B1");//メインシート（カレンダーID）
  var value = range.getValue();//valueが取得したいカレンダーID
  var myCal=CalendarApp.getCalendarById(value); //特定のIDのカレンダーを取得

/* 予定取得シートデータクリアからの流し込み準備 */
  var ysheet=sheet.getSheetByName('予定取得'); //予定取得シートを取得
  ysheet.getRange('A:C').clear(); //予定取得シート内をクリア
  var maxRow=ysheet.getDataRange().getLastRow(); //最終行を取得 

/* 設定 */
  var rng1 = msheet.getRange("B4");//メインシート（取得開始日）
  var startDate = new Date(rng1.getValue());//取得開始日
  var rng2 = msheet.getRange("B5");//メインシート（取得終了日）
  var endDate = new Date(rng2.getValue());//取得終了日

/* カレンダーのイベントを取得 */ 
  var myEvents=myCal.getEvents(startDate,endDate); //いつからいつまで

/* イベントの数だけ繰り返してシートに記録 */
  for each(var evt in myEvents){

    ysheet.getRange(maxRow+1,1).setValue(evt.getStartTime()); //イベントの開始時刻
    ysheet.getRange(maxRow+1,2).setValue(evt.getTitle()); //イベントのタイトル
    maxRow++;
  }
}

よくわかんないや！ってかたは、とにかくソースコードを貼り付けます。
貼り付ける時は最初入っているコードごと上書きしてください。

こんな感じになると思います。

任意の名前を付けます。

ちなみに今回はカレンダーにしてみました。

GASを実行

GoogleカレンダーのデータをGoogleスプレッドシートに流し込む準備が整いました。
今、画面にスプレッドシートを開いている場合はスクリプトエディタを再度開いてください
エディタの上の方に三角の再生ボタンがありますよね。
いきなり実行してみてください

承認

すると、承認が必要です画面がポップアップされるので、許可を確認をクリックします。

アカウントを選択します。

このアプリは確認されていません
当然今作っているので確認されてたらおかしいのですが、とりあえず詳細を選択。

つづいてカレンダー（安全ではないページ）に移動を選択。

アカウントへのアクセスを許可
許可を選択します。

するといきなりエラーが発生します。

これはデフォルトが「myFunction」になっているためです。

getCalendarを選択していることを確認し、保存、実行の順にクリックします。

これで実行されたかと思います。
確認してみましょう。

スプレッドシートを確認

カレンダーの内容が入っていれば成功です。

予定設定

予定取得シートのデータを編集します。
オリジナルカレンダーに表示できるようにします。
GASで書式からデータ編集までしちゃえばいいのだけれでも、生データの方が加工の幅が広がる（本当はめんどくさい）ので、あえてGoogleスプレッドシート側の関数を利用します。

まず、予定設定シートに移動し、表題を年月日、予定編集、予定数、予定の順に入力します。

A2に=ARRAYFORMULA('予定取得'!A2:A)と入力します。
このARRAYFORMULA。発想がクレイジーで、一発目に使用すれば、数式を配列形式で表示します。Excelに無い概念ですね。

A列に数値が入ります。
17行目からは元のデータが無いためエラーになっています。

A列をクリック、表示形式、数字、日付の順でクリックします。

西暦表示に変わります。

続いて、
B2に=ARRAYFORMULA(if(C2:C=1,D2:D,if(C2:C=2,D2:D&CHAR(10)&D3:D,if(C2:C=3,D2:D&CHAR(10)&D3:D&CHAR(10)&D4:D,""))))
C2に=ARRAYFORMULA(COUNTIF(A2:A,A2:A))
D2に=ARRAYFORMULA('予定取得'!B2:B)
と入力します。
まず、予定取得からA列とD列に年月日と予定を持ってきます。この時、年月日については、後でカレンダーに表示するために日付形式に変更し、セルの書式設定も変更。
C列は同じ日にある予定の数を計算し、2件又は3件なら改行（CHAR(10)を利用）して予定を表示できるように関数でトッピングします。
多すぎるとカレンダーに表示しきれなくなるので3件までにしました。
また、カレンダー側に表示するときにVlookupを利用しますが、Vlookupの性質上、重複した場合、一番上のデータを引っ張るといった特性を利用した作りにしました。
なので、同じ日にちの予定が3つ並んだ時、予定編集の一番上のデータのみカレンダーに表示するといった感じです。

祝日

自作カレンダーに祝日を表示したいと思います。
せっかくなので。GASを使って自動で取得してみたいと思います。
祝日シートを選択して、上部ツール、スクリプトエディタの順にクリックします。

すると、先ほどのGASのスクリプトエディタが起動します。
左側にコード.gsといった文字があります。先ほど作成したスクリプトファイルです。
右側の▼をクリックし、予定取得に名前を変更します。

祝日を取得するスクリプトファイルを作成したいので、上部ファイル、新規作成、スクリプトファイルの順でクリックします。

ファイルを作成のポップアップが表示されるので、予定取得と入力してOKボタンをクリックします。

新たなスクリプトファイルが作成されます。

次のソースコードを入力します。

ソースコード

/* 設定シートの開始日と終了日を取得 */
function holiday_main() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet(); 
  var msheet=sheet.getSheetByName('設定'); //設定シートを取得
  var rng1 = msheet.getRange("B4");//設定シート（取得開始日）
  var startDate = new Date(rng1.getValue());//取得開始日
  var rng2 = msheet.getRange("B5");//設定シート（取得終了日）
  var endDate = new Date(rng2.getValue());//取得終了日

/* 祝日を取得 */
  var holidaysheet = getholidaysheet();
  var holidays = getHoliday(startDate, endDate);

  var lastRow = holidaysheet.getLastRow();
  var startRow = 1;

  // シートが空白で無いとき、取得した祝日配列の先頭の日付と一致するカラムの位置を探索
  if (lastRow > 1) {
    var values = holidaysheet.getRange(1, 1, lastRow, 1).getValues();
    for(var i = 0; i < lastRow; i++) {
      if(values[i][0].getTime() == holidays[0][0].getTime()) {
        break;
      }
      startRow++;
    }
  }

  holidaysheet.getRange(startRow, 1, holidays.length, holidays[0].length).setValues(holidays);
}

/**
 * SHEET_URLで指定したドキュメント内のSHEET_NAMEのシートを取得する
 * SHEET_NAMEのシートが存在しない時は、シートを作成する
 */
function getholidaysheet(){
  try{
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var holidaysheet = sheet.getSheetByName('祝日');
  holidaysheet.clear();
  }catch(e){
    Browser.msgBox(e);
  }

  return holidaysheet;
}

/**
 * startDate〜endDateまでの祝日をgoogleカレンダーの「日本の祝日」から取得
 * [日付,祝日名]の多次元配列にした上で返す
 */
function getHoliday(startDate, endDate) {
  var cal = CalendarApp.getCalendarById("ja.japanese#[e-mail]");

  var holidays = cal.getEvents(startDate, endDate);
  var values = [];

  for(var i = 0; i < holidays.length; i++) {
    values[i] = [holidays[i].getStartTime(), holidays[i].getTitle()];
  }

  return values;
}

いきなり実行

エラーきました。
これは、関数をきちんと選択していないエラーです。

関数を選択でholiday_mainを選択します。

保存して、再度実行します。

特にエラーメッセージが無いようでしたら、ブラウザのタブから、Googleスプレッドシートのカレンダーを選択します。

祝日シートを確認してみましょう。
設定シートで指定した間の祝日が入力されています。

記念日

続いて、記念日の設定をします。
記念日シートを選択し、A1、B1、C1、D1、E1の順に年、記念日、月、日と入力します。

適当にC2からE5までに記念日やイベントを入力します。

A2に=ARRAYFORMULA('設定'!$B$2&"/"&D2:D&"/"&E2:E)と入力します。
続いて、B2に=ARRAYFORMULA(TEXT(DATE((YEAR(A2:A)),(MONTH(A2:A)),(DAY(A2:A))),"mmdd"))と入力します。
すると、記念日の表記が月日になります。これを後で記念日として利用します。

月間(カレンダー)

いよいよカレンダーを作っていきます。
月間シートを選択します。
A1、A2を選択し、表示形式→セルを結合→すべて結合、の順にクリックすると、セルが結合されます。

結合したセルA1に='設定'!B8と入力します。

書式を設定して、カレンダーの月を表示したいと思います。
表示形式→数字→表示形式の詳細設定→その他の日付や時刻の形式
の順に選択します。

年（1930）の横に開くマークがあるので、クリックして選択しを展開します。

削除を選択します。

同じように日にちも削除します。

すると、月の両端に-が残っているので、こちらも削除します。

こんな感じです。
適用をクリックします。

セルA1が月表示に変わりました。
続いてフォントサイズを変えたいと思います。
A1を選択した状態で、メニュー下にあるフォントサイズを選択し、直接50と入力します。

カレンダーの月を作成した方法と同じように、年を表示させたいと思います。
セルG2に=A1と入力し、先ほどと同じ方法（表示形式→数字→表示形式の詳細設定→その他の日付や時刻の形式）で、次は月、日、- を削除して年を表示させます。
フォントサイズは24にしてみました。

セル内のデータをセル中央（上下）に配置したいと思います。
左上の写真部分をクリックして全てのセルを選択した状態で、上下位置調整のメニューから中央を選択します。

A3からG3に日から土までの曜日を入力します。
カレンダーの曜日部分を目立たせるために、塗りつぶしの色から、セルと文字に着色していきます。
文字を白、セルを黒にしてみました。

文字を中央寄せします。

日にちを表示

A5に=IF(WEEKDAY($A$1, 1)=1,A1,"")と数式を作成。
B5に=IF(A5<>"",A5+1,IF(WEEKDAY($A$1, 1)=2,$A$1,""))と数式を作成。
C5に=IF(B5<>"",B5+1,IF(WEEKDAY($A$1, 1)=3,$A$1,""))と数式を作成。
D5に=IF(C5<>"",C5+1,IF(WEEKDAY($A$1, 1)=4,$A$1,""))と数式を作成。
E5に=IF(D5<>"",D5+1,IF(WEEKDAY($A$1, 1)=5,$A$1,""))と数式を作成。
F5に=IF(E5<>"",E5+1,IF(WEEKDAY($A$1, 1)=6,$A$1,""))と数式を作成。
G5に=IF(F5<>"",F5+1,IF(WEEKDAY($A$1, 1)=7,$A$1,""))と数式を作成。
セルの書式がそのままだと、月が表示されるので、
表示形式→数字→表示形式の詳細設定→その他の日付や時刻の形式
の順に選択し、さきほど書式を月にしたのと同じ方法で日にします。

※重要なのは、IF文とTEXT関数などで、曜日をddd指定しないことです。後で、Excel形式でダウンロードしたときに、関数が働きません。Googleスプレッドシートの曜日関数はdddなのですが、Excelはaaaだからです。

次にA9に=G5+1、B9に=A9+1と式を入力し、
B9のセルを選択すると、右下に小さな四角の点が出てくるので、カーソルを上に合わせて、右方向G9セルまでドラッグ&ドロップします。
すると、数式がコピーされます。

13行目、17行目も同じ方法で数式を入力します。

A21に=IF(MONTH(G17)=MONTH(G17+1),G17+1,"")
B21に=IF(A21="","",IF(MONTH(A21)=MONTH(A21+1),A21+1,""))
を入力します。
先ほどと同じ方法で、B21の数式をG21までドラッグ&ドロップでコピーします。

A25に=IF(G21="","",IF(MONTH(G21)=MONTH(G21+1),G21+1,""))
B25に=IF(A25="","",IF(MONTH(A25)=MONTH(A25+1),A25+1,""))
B25の数式をC25にコピーします。

祝日を表示

A6に=IFERROR(VLOOKUP(A5,'祝日'!$A:$B,2,FALSE),"")を入力します。
※=IF(ISERROR(VLOOKUP(A5,'祝日設定'!A:B,2,FALSE)),"",VLOOKUP(A5,'祝日設定'!A:B,2,FALSE))でも同じなのだけど、IFERROR関数がExcel 2007から追加されてからは数式を短くできるので、こちらがおススメです。
この数式をG6までコピー(ドラッグ&ドロップ)します。

同じように、A10からG10、A14からG14、A18からG18、A22からG22、A26からC26までコピーします。
これで祝日のデータを表示できるようになりました。

予定データを表示

続いて、Googleカレンダーから取得した予定データを表示するセルの設定をします。
A7に=IF(IFERROR(VLOOKUP(TEXT(A5,"mmdd"),'記念日'!$B:$C,2,FALSE),"")="",IFERROR(VLOOKUP(A5,'予定設定'!$A:$B,2,FALSE),""),IF(IFERROR(VLOOKUP(A5,'予定設定'!$A:$B,2,FALSE),"")="",IFERROR(VLOOKUP(TEXT(A5,"mmdd"),'記念日'!$B:$C,2,FALSE),""),IFERROR(VLOOKUP(TEXT(A5,"mmdd"),'記念日'!$B:$C,2,FALSE),"")&"
"&IFERROR(VLOOKUP(A5,'予定設定'!$A:$B,2,FALSE),"")))
と数式を入力します。
記念日のデータがあればもってきて、なおかつ予定を下に表示。なければ予定を表示的な感じです。

A7とA8を選択して表示形式→セルを結合→すべて結合の順にセルを結合します。

A7（A7,A8結合セル）をコピーしてB7からG7に貼り付けます。
同じようにA11からG11、A15からG15、A19からG19、A23からG23、A27からC27まで貼り付けます。

A5からA8を選択して、枠線で囲みます。

A6からA8を選択して、表示形式→テキストの折り返し→折り返すの順位選択します。
※フォントサイズ等については、好みに合わせて設定してください。

書式を整える

祝日の日にちを赤色にしたいので、セルA5で右クリックして、条件付き書式を選択します。

書式ルールの条件でカスタム数式
式に=A6<>""と入力します。
スタイルで、フォントを赤色、塗りつぶしを透明にして完了をクリックします。

A5からA8をコピーして、A5からG28を選択（A5を選択し、Shiftを押したままG28）
右クリックから特殊貼り付け→書式のみ貼り付けを選択します。

枠線、書式がもろともコピーされるので、続いて、土日の日にちと祝日の文字を赤色のフォントに変更します。

月間カレンダーができあがりました！

印刷する

印刷で、印刷形式→グリッド線の表示のチェックを外せばきれいに印刷できます。

エクセルでダウンロードする

Excel形式でダウンロードして、Excelとしても利用できます。

まとめ

今回は月間カレンダーの作成例を紹介させていただきました。
個人的にシステム手帳（A5）の様式を作ってパンチで穴を開けて利用したり、予定表を共有したり、年間カレンダーなんかもつかっています。
忘年会の日程調整にも利用したりできます。
多くの友人や知人から、作り方を教えてほしいとの声をいただきましたので、Qiitaで公開してみました。
便利なデジタルコンテンツがたくさんできても、「オイラ、手帳と共に歩み続ける！」って方もいらっしゃいますよね。

IMAGE関数を利用して、毎月自動で写真が切り替わる家族カレンダーを作ってもいいかもですね。
もしよかったら、自分好みに色々カスタマイズして使ってみてください。
長々と、お付き合いありがとうございました！

参考

• google spread sheetに自動更新される日本の祝日シートを作る
• Google Apps Scriptで特定月のカレンダーのイベント情報を取得する