Googleスプレッドシートの管理表のスケジュールを、担当者にメール通知＆担当者のGoogleカレンダーに登録したい

Googleスプレッドシートの管理表に入力した内容を、「担当」（プルダウンメニュー）を入力したタイミングで、担当者にメール通知し、さらに担当者のGoogleカレンダーに登録したいです。（登録する内容は日、時間、内容、場所）

※すでに入力された担当者に変更があった場合（たとえばAさん→Bさん）
・新担当者のBさんには通常通り、メール通知＆Googleカレンダー登録。
・旧担当者のAさんには変更があった旨（「AさんからBさんに担当が変わりました」）をメール通知。
・旧担当者のAさんのGoogleカレンダーに登録されたスケジュールは削除し、新担当者のBさんのGoogleカレンダーに登録する。

【追記】
下記のサイトに掲載されているコードが近いと思ったので、必要な部分のみを取り出して、自分の管理表に合わせて一部改変して試してみたのですが、私自身が全体を理解できていないため、正常に動かず（担当を入れると、同じ行のI列に「通知予定」と表示されるだけ）、混乱を避けるため、一旦破棄しました。

Googleスプレッドシートで課題管理表を作成し、メール通知などの処理を自動化しよう | デザインってオモシロイ -MdN Design Interactive-

Google
1// メールアドレスの設定
2var MAILADD = {
3　'阿部':'abe@example.com',
4　'佐藤':'sato@example.com'
5};
6
7
8// スプレットシートのアクティブを取得
9var SS = SpreadsheetApp.getActive();
10// アクティブなシートを取得
11var SHEET = SS.getActiveSheet();
12
13// 編集時
14function onEdit(e) {
15
16　// 変更行
17　e.range.getLastColumn();
18　// 変更列
19　e.range.getLastRow();
20
21　// 依頼者が入力されたら
22　if(e.range.getLastColumn() === 3){
23　　// 何か入力があれば
24　　if(e.range.getValue()){
25　　　 addEditDate(e.range.getLastColumn()+1,e.range.getLastRow());
26　　}
27　}
28　
29　// 対応内容が入力されたら
30　if(e.range.getLastColumn() === 8){
31　　// 何か入力があれば
32　　if(e.range.getValue()){
33　　　 addEditDate(e.range.getLastColumn()+1,e.range.getLastRow());
34　　}
35　}
36　
37　// 担当者が入力されたら
38　if(e.range.getLastColumn() === 6){
39　　range = SHEET.getRange(e.range.getLastRow(),e.range.getLastColumn()+1);
40　　range.setValue('通知予定');
41　}
42　
43　// ステータスが変更されたら
44　if(e.range.getLastColumn() === 2){
45　　// 完了の場合
46　　if(e.range.getValue()=='完了'){
47
48　　　var changeRnages = "A" + e.range.getLastRow() + ":" + "I" + e.range.getLastRow();
49　　　var range = SHEET.getRange(changeRnages);
50　　　range.setBackground("#cccccc");
51　　　
52　　}
53　}
54　
55　// 日付の差分
56　// dateDiff(new Date('2014/1/1'), new Date('2012/1/2')); // 1
57
58　// コメントを付与
59　//e.range.setComment("Edited by: " + e.user + new Date());
60}
61
62
63// 現在の日付を指定のセルに代入する
64function addEditDate(c,r){
65　range = SHEET.getRange(r,c);
66　// 日付の代入
67　range.setValue(formatDate(new Date()));
68　
69}
70
71function checkNotification(){
72
73　// 複数行のセルを取得　（列, 行, 何列, 何行)
74　var ranges = SHEET.getRange(2, 7, 30 , 1).getValues();
75
76　for (var i = 0; i < ranges.length; i++) {
77
78　　if(ranges[i] == '通知予定'){
79　　　var editRow = i + 2;
80　　　var editorName = SHEET.getRange(editRow,6).getValue();
81　　　Logger.log(editRow);
82　　　Logger.log(editorName);
83　　　
84　　　if(MAILADD[editorName]){
85　　　　notificationSendEmail(MAILADD[editorName],editorName,'タスクが追加されました',7,editRow);
86　　　}else{
87　　　　　range = SHEET.getRange(editRow,7);
88　　　　　range.setValue('通知失敗');
89　　　}
90　　}
91　}
92}
93
94// Emailの送信
95// メールの送信は規約により1か、2を実行する必要がある
96// 1.OAuth Developer Verification Formで承認を受ける
97// https://support.google.com/code/contact/oauth_app_verification
98// 2.Allow Risky Access Permissions By Unreviewed Appsのフォーラムに参加する
99// https://groups.google.com/forum/#!forum/risky-access-by-unreviewed-apps
100function notificationSendEmail(address,name,countData,col,row){
101　var mailBody = name + '様\n\n' + countData;
102　GmailApp.sendEmail(address,"タスクが追加されました" ,mailBody);
103
104　// ステータスの変更
105　range = SHEET.getRange(row,col);
106　range.setValue('通知済');
107}
108
109
110// 現在の日付を返す
111function formatDate(date) {
112　var returnDate;
113　returnDate = date.getFullYear() + '/';
114　returnDate += date.getMonth() + 1 + '/';
115　returnDate += date.getDate();
116　return returnDate;
117};

よろしくお願いいたします。m(_ _)m

papinianus

2019/06/06 10:14

現状のコードをください。レベル感と要件の後出しによるちゃぶ台返しをさけるためです

Junkak

2019/06/07 02:09

申し訳ございません。現状のコードはありません。下記のサイトに掲載されているコードが近いと思ったので、必要な部分のみを取り出して、自分の環境に合わせて一部改変して試してみたのですが、私自身が全体を理解できないため、正常に動かず（担当を入れると、同じ行のI列に「通知予定」と表示されるだけ）、混乱を避けるため、一旦破棄しました。 // メールアドレスの設定 var MAILADD = { 　'阿部':'abe@example.com', 　'佐藤':'sato@example.com' }; // スプレットシートのアクティブを取得 var SS = SpreadsheetApp.getActive(); // アクティブなシートを取得 var SHEET = SS.getActiveSheet(); // 編集時 function onEdit(e) { 　// 変更行　e.range.getLastColumn(); 　// 変更列　e.range.getLastRow(); 　// 依頼者が入力されたら　if(e.range.getLastColumn() === 3){ 　　// 何か入力があれば　　if(e.range.getValue()){ 　　　 addEditDate(e.range.getLastColumn()+1,e.range.getLastRow()); 　　} 　} 　　// 対応内容が入力されたら　if(e.range.getLastColumn() === 8){ 　　// 何か入力があれば　　if(e.range.getValue()){ 　　　 addEditDate(e.range.getLastColumn()+1,e.range.getLastRow()); 　　} 　} 　　// 担当者が入力されたら　if(e.range.getLastColumn() === 6){ 　　range = SHEET.getRange(e.range.getLastRow(),e.range.getLastColumn()+1); 　　range.setValue('通知予定'); 　} 　　// ステータスが変更されたら　if(e.range.getLastColumn() === 2){ 　　// 完了の場合　　if(e.range.getValue()=='完了'){ 　　　var changeRnages = "A" + e.range.getLastRow() + ":" + "I" + e.range.getLastRow(); 　　　var range = SHEET.getRange(changeRnages); 　　　range.setBackground("#cccccc"); 　　　　　} 　} 　　// 日付の差分　// dateDiff(new Date('2014/1/1'), new Date('2012/1/2')); // 1 　// コメントを付与　//e.range.setComment("Edited by: " + e.user + new Date()); } // 現在の日付を指定のセルに代入する function addEditDate(c,r){ 　range = SHEET.getRange(r,c); 　// 日付の代入　range.setValue(formatDate(new Date())); 　 } function checkNotification(){ 　// 複数行のセルを取得　（列, 行, 何列, 何行) 　var ranges = SHEET.getRange(2, 7, 30 , 1).getValues(); 　for (var i = 0; i < ranges.length; i++) { 　　if(ranges[i] == '通知予定'){ 　　　var editRow = i + 2; 　　　var editorName = SHEET.getRange(editRow,6).getValue(); 　　　Logger.log(editRow); 　　　Logger.log(editorName); 　　　　　　if(MAILADD[editorName]){ 　　　　notificationSendEmail(MAILADD[editorName],editorName,'タスクが追加されました',7,editRow); 　　　}else{ 　　　　　range = SHEET.getRange(editRow,7); 　　　　　range.setValue('通知失敗'); 　　　} 　　} 　} } // Emailの送信 // メールの送信は規約により1か、2を実行する必要がある // 1.OAuth Developer Verification Formで承認を受ける // https://support.google.com/code/contact/oauth_app_verification // 2.Allow Risky Access Permissions By Unreviewed Appsのフォーラムに参加する // https://groups.google.com/forum/#!forum/risky-access-by-unreviewed-apps function notificationSendEmail(address,name,countData,col,row){ 　var mailBody = name + '様\n\n' + countData; 　GmailApp.sendEmail(address,"タスクが追加されました" ,mailBody); 　// ステータスの変更　range = SHEET.getRange(row,col); 　range.setValue('通知済'); } // 現在の日付を返す function formatDate(date) { 　var returnDate; 　returnDate = date.getFullYear() + '/'; 　returnDate += date.getMonth() + 1 + '/'; 　returnDate += date.getDate(); 　return returnDate; }; https://www.mdn.co.jp/di/contents/4047/54812/

行動規範の内容に同意します

回答2件

質問回答に「全体を理解できないため」とありましたので、全体の処理の流れを回答いたします。まずは理解に努めてください。そのうえで、どの処理のどの部分がわからないのかを明確にして質問につなげてください。

参考：実装されたい処理全体の流れ
①onEdit(e)のeパラメータから編集箇所を取得し、該当箇所かどうかを判断して後続処理に渡す。
②編集箇所から担当者名を取得し、担当者リストから当該担当者のメールアドレスを取得する
③編集箇所と同じ行の情報からタイトル、日、内容、開始時間、終了時間、場所の情報を取得する。
④②で取得したメールアドレス宛にメールを送信する。
⑤②で取得したメールアドレスをゲストに追加したイベントを③で取得した情報を利用して管理用カレンダーに登録する。

+変更時の対応
onEdit(e)のeパラメータからoldValueを取得し、取得した担当者名がnullでは無いときに
その担当者名で、担当者リストから当該担当者のメールアドレスを取得して、後続処理に渡す
後続処理：③情報を利用して管理用カレンダーから予定を取得し、削除し、メールを送信する。
これを上記の③と④の間に入れればOK

投稿2019/06/12 08:28

hiroshi0240

総合スコア640

papinianus

2019/06/18 15:51

①onEdit(e)のeパラメータから編集箇所を取得し、該当箇所かどうかを判断して後続処理に渡す。 →違ってたら中断にしたい。好みだが。 ③編集箇所と同じ行の情報からタイトル、日、内容、開始時間、終了時間、場所の情報を取得する。 →終了時間はなさそう ⑤②で取得したメールアドレスをゲストに追加したイベントを③で取得した情報を利用して管理用カレンダーに登録する。 →その人のカレンダーに入れる要件。管理用カレンダーはないはず onEdit(e)のeパラメータからoldValueを取得し、取得した担当者名がnullでは無いとき →nullだろうか。undefinedではないだろうか ③情報を利用して管理用カレンダーから予定を取得し、削除し →回答者様は理解しておられるのだと思うがこの操作だとおそらくイベントの配列が取得できてしまう。

Junkak

2019/10/14 05:02

ありがとうございます！

行動規範の内容に同意します

ベストアンサー

javascript
1const MAILADD = {
2    '阿部': 'abe@example.com',
3    '佐藤': 'sato@example.com'
4};
5
6function onEdit(e) {
7    const sheetName = "管理表";
8    const colWhoInCharge = 8;
9    const curRange = e.range;
10    if(!e.value) { return; }
11    if (curRange.getColumn() !== colWhoInCharge) {
12        return;
13    }
14    const sheet = curRange.getSheet();
15    if (sheet.getName() !== sheetName) {
16        return;
17    }
18    const rowVal = sheet.getRange(curRange.getRow(), 1, 1, 9).getValues()[0];
19    if(rowVal[8]) {
20        CalendarApp.getEventById(rowVal[8]).deleteEvent();
21    }
22    const title = rowVal[4];
23    const createOption = {"description": rowVal[4], "location": rowVal[6]};
24    const start = new Date(rowVal[1].getFullYear(), rowVal[1].getMonth(), rowVal[1].getDate(), Number(rowVal[5].split(":")[0]), Number(rowVal[5].split(":")[1]));
25    const end = new Date(start.getFullYear(), start.getMonth(), start.getDate(), start.getHours() + 1, start.getMinutes());
26    const event = CalendarApp.getCalendarById(MAILADD[e.value]).createEvent(title, start, end, createOption);
27    sheet.getRange(curRange.getRow(), 9).setValue(event.getId());
28    MailApp.sendEmail(MAILADD[e.value], "You Assigned", "check calendar");
29    if(e.oldValue) {
30        MailApp.sendEmail(MAILADD[e.oldValue], "You Unassigned", "now assigned : " + e.value);
31    }
32}