GoogleスプレッドシートからGoogleカレンダーにインポート(重複防止)
以前、GoogleスプレッドシートからGoogleカレンダーにインポートという記事を書いたのですが、いくつか質問をいただいて、その中に「スプレッドシートとカレンダーを同期させたい」というのがありました。
前回、紹介した方法は同期でなくインポートなので、二度実行すると同じイベントが二回追加されてしまいます。
今回はこれを防ぐ方法を紹介します。詳細は前回の記事を参考にしてください。
既に前回のコードを使用している場合は、今回のコードに差し替えてください。
スプレッドシート
右端に「Calendar」という列を作りました。
イベントを追加するとここに「追加済み」という文字列がセットされます。
コード
var sheet, i, myevent, mystart, myend, mylocation,
added;
sheet = SpreadsheetApp.getActiveSheet();
for(i = 2; i <= sheet.getLastRow(); i++) {
myevent = sheet.getRange(i, 1)
.getValue();
mystart = sheet.getRange(i, 2)
.getValue();
myend = sheet.getRange(i, 3)
.getValue();
mylocation = sheet.getRange(i, 4)
.getValue();
added = sheet.getRange(i, 5).getValue();
if(added == "") {
thisevent = CalendarApp.getDefaultCalendar()
.createEvent(myevent, mystart, myend, {
location: mylocation
});
sheet.getRange(i, 5).setValue("追加済み");
}
}
}
5列目(Calendarの列)が空欄の場合、カレンダーに追加し、この列に「追加済み」という文字列を追加します。
次回以降は、この列に文字列がある行については処理しません。
場所によって登録先カレンダーを振り分ける
(2016年2月11日追記)
「会議のイベントを登録する際に会議室によって登録先のカレンダーを振り分けたい」というコメントをいただきました。
上のコードではデフォルトカレンダーに登録されますがIDを指定することで任意のカレンダーに登録することができます。
GoogleスプレッドシートからGoogleカレンダーにインポート | You Look Too Cool
これを踏まえてlocationが
「会議室1」ならば「xxxxx@group.calendar.google.com」、
「会議室2」ならば「yyyyy@group.calendar.google.com」
に登録する方法を考えました。
上のコードの後半を次のように書き換えます。
if(mylocation=="会議室1"){
id="xxxxx@group.calendar.google.com";
}else if(mylocation=="会議室2"){
id="yyyyy@group.calendar.google.com";
}
thisevent = CalendarApp.getCalendarById(id)
.createEvent(myevent, mystart, myend, {
location: mylocation
});
sheet.getRange(i, 5).setValue("追加済み");
}
実際には該当しない場所が指定された場合は後続をスキップしたりデフォルトカレンダーに登録したりするなどの処理が必要かもしれません。
[ 2015年5月14日 | カテゴリー: JavaScript | タグ: Google Apps Script , カレンダー ]
« 自動的に「改行」するマクロ | 下の階層を含めたファイル一覧を作成するマクロ »
コメント
-
＞keane234さん
今回のコードのみでOKです。
前回のコードは削除してください。 -
スプレッドシートとGカレンダーとの同期を実現させるスクリプト、大変便利に使える予感があります。
ご教授ありがとうございます。さて今回のスクリプト加えて
もうひとつできればいいなと思っていることがあります。mystartの日時だけを送信すると、
自動的に一律1時間なり2時間なりを
Gカレンダーに反映させることはできないでしょうか？よろしくお願いします。
-
＞藤田さん
次のように書き換えるとmyendにmystartの2時間後がセットされます。
myend = new Date(mystart.getTime() + 2 * 60 * 60 * 1000);
試してみてください。 -
[…] 「GoogleスプレッドシートからGoogleカレンダーにインポート」を書いたのですが、今回、こんな質問をいただきました。 「開始時刻を設定し、終了時刻は自動的にその1時間後にする方法はないか」 そこでGoogle Apps Scriptで日時がどのように扱われているか、調べてみました。 […]
-
1時間30分の場合
myend = new Date(mystart.getTime() + 1.5 * 60 * 60 * 1000);
として試してみたら、一応カレンダーに反映しましたが
問題ないでしょうか？よろしくお願いします。
-
＞藤田さん
ご認識のとおりです。
スクリプトはミリ秒(1000分の1秒)という単位で時間を管理しています。
詳細については次のページを確認ください。
http://stabucky.com/wp/archives/7442 -
netz より:
こちら、いつも便利に使わせていただいております。
googleフォームから記入し追加して自動的にカレンダーに登録される仕組みですが
どうしても自動で登録されてくれません。
1行上の「追加済み」を削除すると自動的にすべて登録してくれるのですが、、、現状はこれを社内で「つまり」と呼んで、定期的につまりを取ってます。w
つまりを解消する方法はございますか？ -
＞netzさん
1.GoogleフォームからGoogleスプレッドシートにイベントを保存。
2.GoogleスプレッドシートのイベントをまとめてGoogleカレンダーに登録。
という流れでしょうか。
もしこの流れならば1.の段階でイベントを保存する際の処理がずれているように思えます。
もし一件ずつ登録するのであればGoogleフォームからGoogleカレンダーに直に登録する方法があるようです。
「GoogleフォームからGoogleカレンダー」で検索するといくつかヒットします。
私自身は直に登録する方法は試したことがありません。
お役に立てず申し訳ありません。
その後の状況等を教えていただけると幸いです。 -
教えていただいた、フォームからカレンダーへの連携スクリプト
大変重宝させて頂いております。さて、このスクリプトで幾つかのカレンダーに振り分けることは可能でしょうか？
弊社には会議室が3箇所ほどありまして、
フォームのラジオボタンで
会議室1
会議室2
会議室3
を選んでフォーム送信するとそれぞれのカレンダーに登録されるようになれば
更に便利に使えるのではと考えています。
そのような機能を教えていただいたスクリプトに追加できますでしょうか？よろしくお願いします。
-
＞藤田さん
「フォーム」というのはアンケートを作ったりできるGoogleフォームでしょうか。
興味はあるのですがまだ使ったことがありません。
会議室毎にカレンダーがあるのでしょうか。となればカレンダー毎にIDがあるので振り分けることは可能です。
http://stabucky.com/wp/archives/5532 -
「フォーム」はGフォームのことです。
Gフォームは送信された予約内容などをGスプレッドシートに自動的に記録されるようになっています。
流れとしては、以下のようになります。
Gフォームに入力→Gスプレッドシートに自動記録→Gカレンダーに自動登録多人数の会議場予約を可能にしたいので、
スタッフ各人が出先のスマホなどからも予約できればいいなと思って
ご相談させていただきました。http://stabucky.com/wp/archives/5532も見させていただきました。
ありがとうございます。
「CalendarApp.getDefaultCalendar()」の部分を
「CalendarApp.getCalendarById(“xxxxx@group.calendar.google.com”)に変更
とのことですが
Gフォームで選択された3つの会議場を振り分けるには
どうなるでしょうか？Gフォームで選択された3つの会議場は
Gスプレッドシートの特定のセルに「会議室1」「会議室2」「会議室3」のいずれかが
入力されるものとします。よろしくお願いします。
-
訂正します。
Gフォームで選択された3つの会議場は
Gスプレッドシートの特定のセルに「会議室1」「会議室2」「会議室3」のいずれかが
入力されるものとします。上記の文章中、「特定のセル」ではなく「特定の列」でした。
-
＞藤田さん
カレンダーの振り分けについて追記しました。
Locationに会議室がセットされるという前提です。適宜、修正してください。 -
＞藤田さん
上手くいったようで安心です。
今回、Googleフォームについても試してみました。面白い仕組みですね。
また何かあれば教えてください。 -
moke より:
＞stabucky様
記事を参考にさせていただいております。ありがとうございます。
下記、質問させて頂いてもよろしいでしょうか？１）イベントタイトルに、／や：を用いて複数項目（複数セル）を登録させることは可能でしょうか？
２）createAllDayEvent を用いて終日イベントを登録するようにしているのですが、
END Date が入力した日付より１日前で登録されてしまいます。
回避方法はありますでしょうか？ご教授いただければ幸いです。
何卒よろしくお願いいたします。 -
＞mokeさん
（１）
シートの予定を「イベントＡ／イベントＢ」として、イベントＡとイベントＢの両方をカレンダーにセットするということでしょうか。
可能ですが難しいです。「／」を使って予定を二つに分割することは可能です(splitで分割して配列にする)。これをループさせてそれぞれをセットします。これが面倒だと思います。
シートの予定を「イベントＡ」と「イベントＢ」として同じ日ならば「イベントＡ／イベントＢ」としてカレンダーにセットするということであれば、さらに難しいです。
（２）
そのような仕様になっているようです。
公式サイト
https://developers.google.com/apps-script/reference/calendar/calendar#createAllDayEvent(String,Date,Date)
に
「August 15th to 17th」をセットする場合、
開始日「August 15, 1969」
終了日「August 18, 1969」
とする例が挙げられています。
仕様なのでどうしようもない気がします。 -
stabucky 様
ご返信ありがとうございます！
（１）は下記のように、イベントの補足情報を、説明欄ではなく
イベントタイトルに組み込みたいという感じです。
例）〇〇小学校運動会／佐藤（２）なるほど了解いたしました。
スクリプト上で、”+１日”というようなことができればと思ったのですが
難しいですね。。 -
＞mokeさん
（１）
上の例でF列に例えば「Note」という列を作ります。
コードを次のように書き換えます。
added = sheet.getRange(i, 5).getValue();
mynote = sheet.getRange(i, 6)
.getValue();
if(added == “”) {
thisevent = CalendarApp.getDefaultCalendar()
.createEvent(myevent+”/”+mynote, mystart, myend, {
location: mylocation
});
sheet.getRange(i, 5).setValue(“追加済み”);
}
}
A列に「〇〇小学校運動会」、F列に「佐藤」として実行すると「〇〇小学校運動会/佐藤」とセットされます。
（２）
コードを次のように書き換えると日付が翌日になります。24時間後という意味です。
myend = sheet.getRange(i, 3).getValue();
myend = new Date(myend.getTime() + 24 * 60 * 60 * 1000); -
＞stabucky様
ありがとうございます！！！！！
無事に動きました！
プログラミング（と言っていいのでしょうか？）自体、ド素人でしたが
こちらのサイトたどり着き、何とか思っていたものが出来上がりました。
助かりました。ありがとうございました！！！ -
意図通り動作したようで安心しました。
-
kei より:
こんにちは 非常に参考にさせていただきました。
ご相談をさせて頂きたいのですが、一度同期をしてGCalendarに追加した後に、時間やタイトルをスプレットシート上で修正した際に既に加わっているCalendar上の予定も自動で修正されるようには出来ないでしょうか？ -
keiさん
厳密に言えば同期ではないので、修正はできないです。追加になってしまいます。 -
Kana より:
はじめまして
検索よりこちらの記事にたどり着きました。さっそくこちらのコードを参考にさせていただき、フォームから予定を追加してみたのですが、
トリガーをフォーム送信時にしたところエラーはないもののカレンダーに反映されず、
スクリプトから直接実行すると、
thisevent = CalendarApp.getCalendarById(id) に対して『ReferenceError: 「id」が定義されていません。』
というエラーが出てしまいます。
こちらの場合はスプレッドシートやカレンダーは、きちんと「実行済み」になって予定も追加されています。
どのような問題が考えられるか、ご教示くださいませんでしょうか
よろしくお願いします。 -
上について
×「実行済み」→「追加済み」
でした。すみません -
＞Kanaさん
カレンダーが一つで振り分ける必要がない場合（デフォルトカレンダーのみ）は
thisevent = CalendarApp.getDefaultCalendar()〜
とします。※本文の前半
カレンダーが複数あって振り分ける場合は
id=”xxxxx@group.calendar.google.com”;
thisevent = CalendarApp.getCalendarById(id)〜
のようにidを指定します。※本文の後半
試していませんがidを指定しないとエラーになるもののデフォルトカレンダーに対して処理を行うためきちんと実行されるのかもしれません。 -
Hana より:
はじめまして。
初歩的な質問で申し訳ございませんが、上記を実施したところ、
「メソッド createEvent(string,string,string,object) が見つかりません。」と表示され、
カレンダーへの登録も行われませんでした。
解決策をご教授いただけないでしょうか。 -
＞Hanaさん
別のページですが同じような質問がありました。下のページで確認ください。
https://stabucky.com/wp/archives/5532#comment-83230 -
Shinjo より:
はじめまして。
こちらのページが大変参考になっております。
外部RSSから自動的にスプレッドシートに読み込んだものをGoogleカレンダーに書き出しており、一番最初の「Calendar」欄がまっさらな状態ではきちんとカレンダーに読み込まれて「追加済み」マークがつきますが、二度目以降RSSを読み込むと、新たに読み込まれたデータにも「追加済み」マークがついているため、Googleカレンダーに書き出されません。
こちらで公開されているコードは、手動で行を追加した場合のみ対応でしょうか？ -
＞Shinjoさん
RSSから読み込んでいるとのことですが、既にセットされている行に上書きするのでしょうか。であればCalendarの列に前の情報が残ってしまうかもしれません。
RSSからの読み込みを、既にセットされている行ではなく、その下の新しい行から始める仕組みにすればよいと思います。
あるいは一度、シート（の2行目以下）をクリアしてから、読み込むといいかもしれません。 -
YUTA HIRASAWA より:
はじめまして！
こちらのページが大変参考になっております。空欄があると、エラーが出ます。stabuckyさんのサンプルで言うとc2の欄が空欄でc3が埋まっている時、エラーが出てしまいます。空欄の時はその行を飛ばして次の行のデータをカレンダーに送りたいです。
以下コードです。エラーは１０行で起きます
function createEventFromSheet2() {
var sheet, i, myevent, mystart, myend,
added;
sheet = SpreadsheetApp.getActiveSheet();
for(i = 2; i <= sheet.getLastRow(); i++) {
myevent = sheet.getRange(i, 1)
.getValue();
mystart = sheet.getRange(i, 2)
.getValue();
myend = new Date(mystart.getTime() + 1.5 * 60 * 60 * 1000);
added = sheet.getRange(i, 4).getValue();
if(added == "") {
thisevent = CalendarApp.getDefaultCalendar()
.createEvent(myevent, mystart, myend, );
sheet.getRange(i, 4).setValue("追加済み");
}
}
}ご返信お待ちしています。
-
YUTA HIRASAWAさん
例えば開始時刻mystartのセルが空欄のとき処理をスキップするならば次のようにします。
if(mystart != “” && added == “”) {
確認ください。
なお
createEvent(myevent, mystart, myend, )
の部分ですが4番目の引数は省略できますので念のため
createEvent(myevent, mystart, myend)
としてください。 -
starbucky様
ありがとうございます！
どこに
if(mystart != “” && added == “”) {
を入れたらいいですか？
初歩的な質問で申し訳ありません。 -
野田 より:
はじめまして。
初心者ですがとても参考になりました。
正常にできたのですが、場所（location）についてこちらで入力するとGカレンダーには場所として入力されます。ですが、社内でリソースしている会議室カレンダーへも入力をしたい場合どういうコードになりますか？
要約すると、自身で管理しているカレンダーと同時にリソースで予約できる社内会議室カレンダーへも反映させたいということです。（２つのカレンダーを同時反映させたい）
例の下記コードを追加してみましたが、リソースしている会議室予約はできませんでした。（もちろんリソース会議室IDをあてこみ会議室名も同じにしました）
ご教授いただければ幸いです。if(added == “”) {
if(mylocation==”会議室1″){
id=”xxxxx@group.calendar.google.com”;
}else if(mylocation==”会議室2″){
id=”yyyyy@group.calendar.google.com”;
}
thisevent = CalendarApp.getCalendarById(id)
.createEvent(myevent, mystart, myend, {
location: mylocation
});
sheet.getRange(i, 5).setValue(“追加済み”);
} -
YUTA HIRASAWAさん
if(added == “”) {
の代わりに
if(mystart != “” && added == “”) {
をセットします。 -
野田さん
二つ考えられます。
一つ目は二つの処理をそれぞれ書く方法です。シンプルです。
if(added == “”) {
id=”xxxxx@group.calendar.google.com”;
thisevent = CalendarApp.getCalendarById(id)
.createEvent(myevent, mystart, myend, {
location: mylocation
});
id=”yyyyy@group.calendar.google.com”;
thisevent = CalendarApp.getCalendarById(id)
.createEvent(myevent, mystart, myend, {
location: mylocation
});
sheet.getRange(i, 5).setValue(“追加済み”);
}二つ目はidを配列idsに持たせてforで繰り返す方法です。idが増える可能性があればこちらの方がラクです。
if(added == “”) {
ids=[
“xxxxx@group.calendar.google.com”,
“yyyyy@group.calendar.google.com”
];
for(j=0;j<ids.length;j++){
thisevent = CalendarApp.getCalendarById(id)
.createEvent(myevent, mystart, myend, {
location: mylocation
});
}
sheet.getRange(i, 5).setValue(“追加済み”);
} -
お世話になっております
ご丁寧に教えていただき、大変助かっております。
どうしても１２行目でエラーが出てしまいます。
スプレッドシートは以下の通りです。A B
1 面談 2020/12/20 11:00:00
2 面談2
3 面談3 2020/12/22 11:00:00function createEventFromSheet2() {
var sheet, i, myevent, mystart, myend,
added;
sheet = SpreadsheetApp.getActiveSheet();
for(i = 2; i <= sheet.getLastRow(); i++) {
myevent = sheet.getRange(i, 1)
.getValue();
mystart = sheet.getRange(i, 2)
.getValue();
myend = new Date(mystart.getTime() + 1.5 * 60 * 60 * 1000);
added = sheet.getRange(i, 5).getValue();
if(mystart != “” && added == “”) {
thisevent = CalendarApp.getDefaultCalendar()
.createEvent(myevent, mystart, myend);
sheet.getRange(i, 5).setValue("追加済み");
}
}
}よろしくお願いいたします。
-
stabucky 様
お世話になっております。詳細ありがとうございます。
１点補足させてください。
会議室Ａを予約したいとき⇒自身のカレンダー＆会議室Ａのカレンダー
会議室Ｂを予約したいとき⇒自身のカレンダー＆会議室Ｂのカレンダー
会議室Ｃを予約したいとき⇒自身のカレンダー＆会議室Ｃのカレンダー
と、自身のカレンダーはすべていれたいですが場所によってカレンダー記入場所を変更したいです。
リソースの会議室であり、会社が制限をかけていて設定できないということもありますでしょうか？
会社の会議室Ａのカレンダーだけの反映としても反映できませんでした。
原因わからず。。 -
誤：どうしても１２行目でエラーが出てしまいます。
正：１０行目エラーコード：TypeError: mystart.getTime is not a function（行 10、ファイル「コード」）
-
stabucky 様
何度も申し訳ございません。
日程・開始時間・終了時間とわけて入力したい場合もご教授いただければ幸いです。
お忙しいところ恐縮ですが何卒よろしくお願い申し上げます。 -
YUTA HIRASAWAさん
mystartが空欄のためエラーが発生します。空欄のときはcontinueを使ってスキップするようにします。
myendの行の前に次のようにセットしてください。
if(mystart == “”){
continue;
}
myend = new Date(mystart.getTime() + 1.5 * 60 * 60 * 1000); -
野田さん
＞リソースの会議室であり、会社が制限をかけていて設定できないということもありますでしょうか？
＞会社の会議室Ａのカレンダーだけの反映としても反映できませんでした。
こうなるとさすがにわかりません。申し訳ありません。 -
できました！！
ありがとうございます！！
これからもお願いします！！ -
お世話になっております。
プログラミングが一番下のやつしかうごかなくなってしまいました。
このようなgoogleスプレッドシートをgoogleカレンダーに反映するにはどうしたらいいでしょうか？
A B C D
予定 １次面接 ２次面接 ３次面接
1 面談 12/20 11:00 12/25 12:00 12/24 16:00
2 面談2
3 面談3 12/22 11:00 12/23 13:00function createEventFromSheet2() {
var sheet, i, myevent, mystart, myend,
added;
sheet = SpreadsheetApp.getActiveSheet();
for(i = 2; i <= sheet.getLastRow(); i++) {
myevent = sheet.getRange(i, 1)
.getValue();
mystart = sheet.getRange(i, 2)
.getValue();
if(mystart == ""){
continue;
}
myend = new Date(mystart.getTime() + 0.5 * 60 * 60 * 1000);
added = sheet.getRange(i, 18).getValue();
if(mystart != "" && added == "") {
thisevent = CalendarApp.getDefaultCalendar()
.createEvent(myevent, mystart, myend);
sheet.getRange(i, 18).setValue("追加済み");
}
}
}function createEventFromSheet2() {
var sheet, i, myevent, mystart, myend,
added;
sheet = SpreadsheetApp.getActiveSheet();
for(i = 2; i <= sheet.getLastRow(); i++) {
myevent = sheet.getRange(i, 1)
.getValue();
mystart = sheet.getRange(i, 3)
.getValue();
if(mystart == ""){
continue;
}
myend = new Date(mystart.getTime() + 0.5 * 60 * 60 * 1000);
added = sheet.getRange(i, 19).getValue();
if(mystart != "" && added == "") {
thisevent = CalendarApp.getDefaultCalendar()
.createEvent(myevent, mystart, myend);
sheet.getRange(i, 19).setValue("追加済み");
}
}
}function createEventFromSheet2() {
var sheet, i, myevent, mystart, myend,
added;
sheet = SpreadsheetApp.getActiveSheet();
for(i = 2; i <= sheet.getLastRow(); i++) {
myevent = sheet.getRange(i, 1)
.getValue();
mystart = sheet.getRange(i, 4)
.getValue();
if(mystart == ""){
continue;
}
myend = new Date(mystart.getTime() + 0.5 * 60 * 60 * 1000);
added = sheet.getRange(i, 20).getValue();
if(mystart != "" && added == "") {
thisevent = CalendarApp.getDefaultCalendar()
.createEvent(myevent, mystart, myend);
sheet.getRange(i, 20).setValue("追加済み");
}
}
} -
YUTA HIRASAWAさん
＞プログラミングが一番下のやつしかうごかなくなってしまいました。
使わない関数は削除してください。
＞このようなgoogleスプレッドシートをgoogleカレンダーに反映するにはどうしたらいいでしょうか？
縦のループに加えて横のループが必要になります。結構面倒です。
記事大変参考になりました。
コードは上手く機能しましたが、まだ重複してしまいます。
コードは前回のインポートのコードの後に、今回のコードを貼り付ければよろしいでしょうか？ または、このコードのみでよろしいでしょうか？