Help us understand the problem. What is going on with this article?

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

はじめに

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

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

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

今回のゴール

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

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

必要なもの

Googleアカウント

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

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

Googleスプレッドシート作成

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

↑URLから作成できます。
kankyoukanshiimg30.JPG

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

シート作成

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

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

GoogleカレンダーID取得

https://calendar.google.com/calendar/
1.JPG
4.JPG

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

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

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

シート名を設定にします。
1.JPG
シート名「シート1」をダブルクリックすると編集できます。

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

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

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

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

GASを作成

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

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

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

ソースコード

/* 指定月のカレンダーからイベントを取得する */
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++;
  }
}

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

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

任意の名前を付けます。
1.JPG
ちなみに今回はカレンダーにしてみました。

GASを実行

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

承認

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

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

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

つづいてカレンダー(安全ではないページ)に移動を選択。
1.JPG

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

するといきなりエラーが発生します。
6.JPG
これはデフォルトが「myFunction」になっているためです。

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

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

スプレッドシートを確認

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

予定設定

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

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

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

A列に数値が入ります。
17行目からは元のデータが無いためエラーになっています。
1.JPG
A列をクリック、表示形式、数字、日付の順でクリックします。
4.JPG
西暦表示に変わります。

続いて、
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つ並んだ時、予定編集の一番上のデータのみカレンダーに表示するといった感じです。
1.JPG

祝日

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

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

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

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

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

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

ソースコード

/* 設定シートの開始日と終了日を取得 */
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;
}

いきなり実行
1.JPG

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

関数を選択でholiday_mainを選択します。
3.JPG

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

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

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

記念日

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

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

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

月間(カレンダー)

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

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

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

年(1930)の横に開くマークがあるので、クリックして選択しを展開します。
1.JPG

削除を選択します。
2.JPG

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

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

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

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

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

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

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

2.JPG
3.JPG

文字を中央寄せします。
4.JPG

日にちを表示

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,""))と数式を作成。
セルの書式がそのままだと、月が表示されるので、
表示形式→数字→表示形式の詳細設定→その他の日付や時刻の形式
の順に選択し、さきほど書式をにしたのと同じ方法でにします。
image.png

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

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

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

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にコピーします。
1.JPG

祝日を表示

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までコピー(ドラッグ&ドロップ)します。
1.JPG

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

予定データを表示

続いて、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),"")))

と数式を入力します。
記念日のデータがあればもってきて、なおかつ予定を下に表示。なければ予定を表示的な感じです。
Screenshot 2019-12-01 at 17.37.57.png

A7とA8を選択して表示形式→セルを結合→すべて結合の順にセルを結合します。
Screenshot 2019-12-01 at 17.52.06.png

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

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

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

書式を整える

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

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

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

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

月間カレンダーができあがりました!
image.png

印刷する

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

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

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

まとめ

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

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

参考

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away