日報送信ツールをGoogleスプレッドシートで作ってみた

はじめに

みなさん「あ～、毎日日報送るのめんどくせぇ～」って思ったことはないでしょうか？
人間1度は、、、いや、10回くらいは思ったことあるでしょう！

ということで、日報を送るのが楽になるツールをスプレッドシートで作りたいと思います。

具体的には毎日TODOリストみたいなので、やってる作業と工数をスプレッドシートでメモしてたので、そこから日報メールを送信するものを作っていきます。

目次

• [ざっくりとどのようなものかを説明](## ざっくりとどのようなものかを説明)
• [実査に作ってみる](## 実査に作ってみる)
  • [まずTODOリストをスプレッドシートで作成しよう！](### まずTODOリストをスプレッドシートで作成しよう！)
  • [次にジャンルの一覧を用意しましょう](### 次にジャンルの一覧を用意しましょう)
  • [ジャンルマスタを選択できるようにする](### ジャンルマスタを選択できるようにする)
  • [メールに記載するタスクの文章を作る](### メールに記載するタスクの文章を作る)
  • [メールを送信するための設定を用意する](### メールを送信するための設定を用意する)
  • [メールを送信するためスクリプトを用意する](### メールを送信するためスクリプトを用意する)
  • [メールを送信するためのボタンを用意する](### メールを送信するためのボタンを用意する)
• [最後に](## 最後に)

ざっくりとどのようなものかを説明

スプレッドシートでTODOリストを用意して、そこにその日やることをいろいろ書いていきます。
それぞれの作業がどのくらい時間がかかったかも工数として残していきましょう。

その結果をスプレッドシートの関数を使ってメール本文を作成して、GASを使ってメール送信するというものになります。

実際に作ってみる

まずTODOリストをスプレッドシートで作成しよう！

まずはスプレッドシートを新規作成してTODOリストのシートを作成しましょう。
シート名は「todo」にします。
あとは以下の項目を用意しましょう。

• ジャンル
• タスク
• 工数
  • ここにはC列の合計値も表示したいので以下のような関数をタイトルに入れましょう。
  • ="工数："&SUM(C2:C)
• 備考

イメージとしてはこんな感じです。

次にジャンルの一覧を用意しましょう

TODOリストのジャンルにいれる一覧（ジャンルマスタ）を用意します。
このジャンルの番号によってメール本文の出す個所を分けるので名前は注意しましょう。
具体的にはジャンルが「1」から始まるものはメール本文の「開発」の項目に表示するといったことをやろうとしています。

ジャンルマスタを選択できるようにする

TODOリストのジャンル項目でさっき作成したジャンルマスタを選択できるようにします。
「todo」のA2のシートを選択して右クリックからデータの入力規則を選択する。
そして以下のような設定をしましょう。

• セル範囲：ジャンルを選択させるセルを指定する。
• 条件：ジャンルマスタの範囲を選択する。

設定を保存すると以下のようにジャンルでジャンルマスタを選択できるようになります。

メールに記載するタスクの文章を作る

まずはサンプルとして「todo」シートに以下のように入力してみます。

そのうえで「tasksummary」シートを用意していきます。

「tasksummary」シートではタスクをジャンル別に「タスク：工数」の形式で1つのセルに入れるための集計を行っています。

まずはB2セルにtodoのジャンルが「1」から始まる工数に入力がある一覧を取得します。
=QUERY(todo!$A:$E, "SELECT B,'：',C WHERE A LIKE '1%' AND C > 0")

次にA3セルに取得した一覧をすべて列結合するようにします。
=ARRAYFORMULA(B3:B & C3:C & D3:D)

さっきのQUERY関数で「：」を間に入れてるのはここで列結合するために入れてました。
ARRAYFORMULAに「：」を入れると入力がない行も「：」が入っちゃうのでQUERY側に入れてます。

最後にA1セルに結合した行をすべて改行で行結合していきます。
=IF(A3="","",CONCAT(TEXTJOIN(CHAR(10), TRUE, A3:A),CHAR(10)))

「CHAR(10)」は改行のことです。
あとは最後に改行を1つ足すためにCONCATにて改行を足してたりもします。

上記の作業を各ジャンル（1～5で始まるジャンル）分を横に用意していきましょう。
変更する箇所はQUERY関数の「1」から始まる部分のところ「2～5」に変更していきます。

メールを送信するための設定を用意する

GoogleAppsScriptでメールを送信するために「mail」シートを用意します。

関数で設定する箇所に関しては以下に記載していきます。

• subject：=TEXTJOIN(" ",FALSE,"作業日報",B$6,TEXT(B$8,"MM/dd"),TEXT(B$9,"hh:mm"),"～",TEXT(B$11,"hh:mm"))
  • タイトルに「名前」「日付」「開始時間」「終了時間」などをスペース区切りで結合しています。
• body：=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B$7,"@KAIHATSU",tasksummary!$A$1,1),"@HOSHU",tasksummary!$E$1,1),"@MTG",tasksummary!$I$1,1),"@RELEASE",tasksummary!$M$1,1),"@OTHER",tasksummary!$Q$1,1)
  • templateに対してtasksummaryシートで作成した作業内容を置換していきます。
• day：=TEXT(NOW(),"yyyy/MM/dd")
• endTime：=TIME(0,0,VALUE(B$10)*24*60*60+(B$11+B$13)*60*60)
  • 日付加算は一度秒に変換して加算させています。
• sumWorkload：=SUM(todo!C$2:C)

メールを送信するためスクリプトを用意する

ツール＞スクリプトエディタを開いて、以下のソースを用意しましょう。
内容としては「mail」シートからtoなどの情報を取得してMailApp.sendMailにてメールを送信します。

function sendDailyReport() {
  // シート取得
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadsheet.getSheetByName('mail');

  // メール内容を取得
  var values = sheet.getDataRange().getValues();
  var mailObject = getKeyValueMap(values);

  if(mailObject["to"]=="" || mailObject["subject"]=="" || mailObject["name"]=="" || mailObject["body"]==""){
    Logger.log("【ERROR】mailObject is fail.")
    Logger.log(mailObject)
  }

  doSendMail(mailObject["to"],mailObject["cc"],mailObject["bcc"],mailObject["subject"],mailObject["name"],mailObject["body"])
}

function doSendMail(to, cc, bcc, subject, name, body) {
  Logger.log("■Mail：" + to + "：" + cc + "：" + bcc + "：" + subject + "：" + name + "");
  Logger.log("■Body：" + body + "");
  MailApp.sendEmail({ to: to, cc: cc, bcc: bcc, subject: subject, name: name, body: body });
}

function getKeyValueMap(values) {
  var obj = {};
  for (var i = 1; i < values.length; i++) {
    var key = values[i][0];
    var value = values[i][1];
    obj[key] = value;
  }
  return obj;
}

メールを送信するためのボタンを用意する

挿入＞図形描画を選択して、ボタンとなる図形を作成しましょう。
該当の図形をクリックして設定から「スクリプトを割り当て」でsendDailyReportを指定しましょう。

その後該当の図形をクリックするとMailをクリックするとメールが送信されます。
※一番最初はGMailの権限承認が聞かれますので、以下を参考に承認しましょう。
https://qiita.com/onlooker_kata/items/1b57435c7a6d990f5fdb

これで完成！

これで毎日TODOリストを入力して、仕事が終わったら日報送信ボタンをクリックで日報を出せるってなりますね！

最後に

この日報ツールはメールのテンプレートとtasksummaryのシートをカスタマイズしてもらえれば、いろいろなことに使えるかと思いますので、試してみてください。

今回のツールはGASで作れる部分をあえて、スプレッドシートの関数で作っています。
プログラムをあまり知らないけどスプレッドシートはよく使うという人でも、スプレッドシートとちょっとしたプログラムで簡易ツールがいろいろ作れる参考に使ってもらえればと思います！