質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

88.19%

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

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 2
  • VIEW 1,282

Junkak

score 16

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

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

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

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

// メールアドレスの設定
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;
};

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

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • papinianus

    2019/06/06 19:14

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

    キャンセル

  • 退会済みユーザー

    2019/06/07 10:21

    複数のユーザーから「やってほしいことだけを記載した丸投げの質問」という意見がありました
    「質問を編集する」ボタンから編集を行い、調査したこと・試したことを記入していただくと、回答が得られやすくなります。

  • Junkak

    2019/06/07 11: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

+2

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

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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/06/19 00:51

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

    キャンセル

  • 2019/10/14 14:02

    ありがとうございます!

    キャンセル

checkベストアンサー

+1

const MAILADD = {
    '阿部': 'abe@example.com',
    '佐藤': 'sato@example.com'
};

function onEdit(e) {
    const sheetName = "管理表";
    const colWhoInCharge = 8;
    const curRange = e.range;
    if(!e.value) { return; }
    if (curRange.getColumn() !== colWhoInCharge) {
        return;
    }
    const sheet = curRange.getSheet();
    if (sheet.getName() !== sheetName) {
        return;
    }
    const rowVal = sheet.getRange(curRange.getRow(), 1, 1, 9).getValues()[0];
    if(rowVal[8]) {
        CalendarApp.getEventById(rowVal[8]).deleteEvent();
    }
    const title = rowVal[4];
    const createOption = {"description": rowVal[4], "location": rowVal[6]};
    const start = new Date(rowVal[1].getFullYear(), rowVal[1].getMonth(), rowVal[1].getDate(), Number(rowVal[5].split(":")[0]), Number(rowVal[5].split(":")[1]));
    const end = new Date(start.getFullYear(), start.getMonth(), start.getDate(), start.getHours() + 1, start.getMinutes());
    const event = CalendarApp.getCalendarById(MAILADD[e.value]).createEvent(title, start, end, createOption);
    sheet.getRange(curRange.getRow(), 9).setValue(event.getId());
    MailApp.sendEmail(MAILADD[e.value], "You Assigned", "check calendar");
    if(e.oldValue) {
        MailApp.sendEmail(MAILADD[e.oldValue], "You Unassigned", "now assigned : " + e.value);
    }
}

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/06/19 00:51

    I列にイベントIDを入れるべき

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 88.19%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

同じタグがついた質問を見る