この記事はなにか

日々の業務でGmailを使っている方の中で、
日報や作業報告をカレンダーや前回報告内容を確認しながら作っている方は多いはず。

スプレッドシート上に設置したボタンをクリックすると日報の下書きを作れるだけではなく
カレンダーや前回送付内容も下書きメールに記載してくれるスクリプトを作成しました。

できること / イメージ

スプレッドシート上のボタン一つで下書きが作成できる！
前回送付したメールと当日のカレンダーの内容が記載されている！
記事通りやればかんたんに作れる！

目次

スクリプトを作成しよう
スプレッドシートを設定しよう
ボタンを作成しよう
完成！実行してみよう！

1.スクリプトを作成しよう

まずはスクリプトエディタを開きます。

開いたら下記ソースをコピペし、必要部分を編集します。
重要: ★★の部分は変更してください！
★★自分のカレンダーID(アドレス)★★
★★検索条件（メール検索する時のと一緒）★★
★★切り取り開始位置★★
★★切り取り終了位置★★


//ボタンを押したときのスクリプト
function pressButton() {
  createDraft();
}

//下書きを作成する
function createDraft() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const createDraftSheet = ss.getSheetByName('メール用');
  const values = createDraftSheet.getDataRange().getValues();
  const to = values[1][1]; //TO(「メール用」シート、セルB２)
  const mailArgs = { cc: values[2][1] }; //CC(「メール用」シート、セルB3)
  const subject = values[3][1]; //タイトル(「メール用」シート、セルB4)
  let body = '';
  body += values[4][1] + '\n\n'; //本文作成(「メール用」シート、セルB5)

  const latestMailBody = searchLatestMail(); //直近に送ったメールを取得
  const eachSchedules = getCalendar(); //本日のスケジュールを取得

  body += '---------参考情報---------' + '\n';
  body += '■スケジュールより' + '\n' + eachSchedules + '\n'; //スケジュールを本文に追記
  body += '■前回内容より' + '\n' + latestMailBody + '\n'; //前回送付内容を本文に追記
  body += '-------------------------------' + '\n';

  GmailApp.createDraft(to, subject, body, mailArgs); //下書き作成

}

//カレンダー情報を取得する
function getCalendar() {

  let i;
  const memberId = 'XXXXXX@gmail.com'; //★★自分のカレンダーID(アドレス)★★
  const today = new Date();
  let myCalendar;
  let title, sumTime; //メール本文に使う部品
  let myEvents = [];
  let eachSchedules = '';

  myCalendar = CalendarApp.getCalendarById(memberId)
  myEvents = myCalendar.getEventsForDay(today);

  if (myEvents.length == 0) {
    eachSchedules += ('・記載なし');
  } else {
    for (let i = 0; i < myEvents.length; i++) {
      title = myEvents[i].getTitle();
      sumTime = (myEvents[i].getEndTime() - myEvents[i].getStartTime()) / 3600000 ;
      eachSchedules += ('・' + title + ' …' + sumTime + '時間\n');
    }
  }
  return eachSchedules;
}

//直近のメール内容を探して切り抜く
function searchLatestMail() {

  const query = '"in:sent 作業報告"'; //★★検索条件（メール検索する時のと一緒）★★
  const start = 0;
  const max = 1; //条件にあったのを直近１つだけ取ってくる
  let startPosition;
  let endPosition;
  let latestMailBody = '';

  const threads = GmailApp.search(query, start, max);
  const messagesForThreads = GmailApp.getMessagesForThreads(threads);

  if (messagesForThreads == null) {
    latestMailBody += ('・記載なし');
  } else {

    for (const messages of messagesForThreads) {
      //メール本文切り取り位置指定
      startPosition = messages[0].getPlainBody().indexOf('≪作業報告≫'); //★★切り取り開始位置★★
      endPosition = messages[0].getPlainBody().indexOf('合計'); //★★切り取り終了位置★★
      latestMailBody = messages[0].getPlainBody().slice(startPosition, endPosition - 1); //文字数があるので足したり引いたりして調整
    }
  }
  return latestMailBody;
}

//参考
//メール情報取得
//https://tonari-it.com/gas-gmail-spreadsheet/
//メール本文切り取り位置取得
//https://untrace.co.jp/blog/js-cutout/
//【スプレッドシートで給与計算】打刻時間を指定の分単位で切り上げ、切り捨てをする方法
//https://tonari-it.com/spreadsheet-time-ceiling-floor/

2.スプレッドシートを設定しよう

メールの宛先や本文を設定するため、こんな感じのスプレッドシートを作ります。
重要！シート名は「メール用」としてください。

件名や本文がサンプル通りで良ければ以下のようにしてください。

タイトル（セルB4）

="【作業報告】"&text(today(),"yyyy/MM/dd(aaa)")&" プロモーション部 山田太郎"

本文（セルB5）

="〇〇さん、XXさん
お疲れ様です。山田です。

≪作業報告≫
・
・
・
・
合計 "& text(FLOOR(NOW()-TIME(9,0,0)-TIME(1,0,0),time(0,30,0)), "H時間mm分")&"

・連絡事項
特にありません

・次回出社日
"
&text(if(WEEKDAY(today())=5,today()+2,today()+1),"M/dd(aaa)")&"／本社"

※9時始業・休憩1時間・30分切り下げで業務時間を計算。
→text(FLOOR(NOW()-TIME(9,0,0)-TIME(1,0,0),time(0,30,0)), "H時間mm分")
※日報作成が金曜日の場合、次回出社日を月曜日に設定（祝日がかぶらない限り大丈夫なはず）。
→text(if(WEEKDAY(today())=6,today()+3,today()+1),"M/dd(aaa)")

3.ボタンを作成しよう

「ボタン」シートにボタンを作成します。

図形描写をクリックします。

ボタンを作って…

スクリプトのpressBttonを割り当てます。

以上で、ボタンの設定は完了です。

4.完成！実行してみよう！

さて、ボタンを押してみましょう！権限を許可し、実行できたら成功です！
毎日使うものですから、ブックマークにでも追加して、サッとボタンを押せるようにしておきましょう！

できなかったり質問等ある場合はコメント欄にお願いします。

所感

Outlookを使用していた頃はExcel上で下書きを作っており、ほぼリアルタイムに更新されていました。
ただ、Googleのスプレッドシート上でToday()やnow()などの関数を使うと
若干ではありますがタイムラグがあるように感じました。

今回は本文など変更が生じやすそうなパーツはスプレットシート上に記載し、
日付や時間もその中で連動させるようにしましたが、
厳密にしたい場合、日付や時間に関するところはスクリプトで記載したほうが良さそうです。