はじめに
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#holiday@group.v.calendar.google.com");
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関数を利用して、毎月自動で写真が切り替わる家族カレンダーを作ってもいいかもですね。
もしよかったら、自分好みに色々カスタマイズして使ってみてください。
長々と、お付き合いありがとうございました！
