Googleスプレッドシートに集めた情報をGoogle apps Script（GAS）を使ってPDFにしてメールで自動配信する

2021年3月15日仕事効率化4DX,GAS,Google Apps Script,Googleスプレッドシート,PDCA,先行指標,自動化

素人だからこその単純なスクリプトですw

会社でもそうだと思いますが、KPIなどを一箇所に集めて定期的に確認している方が多いのではないでしょうか。

かくいう私もそうなのですが、ただ、データがまとまって報告書だったり、PPTになってからKPIを見ると「こんなに悪かったのか」とその時になって気づくということが多いのです。

その時になっていろいろと分析しようとしてもその期間分振り返ることになり、覚えていないことも多く、記録を遡るのに時間がかかります。

できる限りリアルタイムでこれらの情報を確認した方が分析の手間がなくなるわけですが、そのために自分からそのデータを毎日見るようにするというのも日々の業務に追われてなかなかめんどくさい。

ということで、できる限りリアルタイムで状況を把握するためにKPIを自動配信するという対策を考えてみました。

過去記事にありますようにプライベートでも数字を集計していますが、まずはこれを自動化をしました。Googleスプレッドシートの一つの機能「Google apps Script」を使うと簡単に実現できました。

フロー

今回やろうとしていることの流れはこんな感じです。

レポート用スプレッドシートの作成　→ レポート用スプレッドシートのpdf化とGmailで自分に送信

レポートをpdfで自動で配信できるようにすると言っても情報を1枚に収められた方が良いという考えからレポート用スプレッドシートを作成しています。

GASでPDFを作成する方法を知りたい方はレポート用スプレッドシート作成の部分を飛ばしてご覧ください。

レポート用スプレッドシートの作成

これまでIFTTTやWorkflowを使って色々なデータをGoogleスプレッドシートに集約する方法を紹介しました 。

わたしはこの方法で3種類のデータを集計しているのですが、それぞれのデータを見にいくのはさすがにめんどくさいので1枚にまとめました。

それぞれの別々のスプレッドシートの中で記録している生データを加工して見たい情報を作っているのですが、1枚にまとめるためにレポート用に新しいスプレッドシートを作成し、そこに「importrange」という関数を使ってそれぞれのスプレッドシートから必要な表を表示させます。

「importrange」を使うためには参照したいスプレッドシートのリンクが必要ですが、これも以下の共有ボタンから簡単にコピーできます。

「importrange」に入力する値は以下のようになっています。
IMPORTRANGE(“参照したいスプレッドシートのURL", そのシートで参照したい範囲)

例えば、こういう感じ。

= importrange("https://〇〇〇〇", "シート1!A1:D5")

Google Apps ScriptでレポートをPDF化してメールで送る

複数のスプレッドシートから情報を集めたひとつのスプレッドシート＝レポートをこれで作成することができました。

レポートはそこに置いておくだけでは見に行かなければならず、経験上、あまり見ません。

これだとレポートの意味がないので自動でメールで送られてくるようにします。
（自動で報告しされるようにします。）

自動で報告させるためにいろいろと調べて見ましたが、1番簡単な方法はGoogle Apps Scriptを使用する方法でした。

エクセルでいうところのマクロ、Visual Basic です。
今はPC版からしか編集できないようですが、これを利用するとそのシートで様々なことができます。

まず、スクリプトを設定したいスプレッドシートで「ツール」の中の「スクリプトエディタ」をクリックしてスクリプト編集画面を開きます。

そして以下のようにスクリプトを組みます。

function scoreboard() {
   //日付を作成する
  var date = new Date();
  var today = date.getFullYear() +"/"+ (date.getMonth()+1) +"/"+ date.getDate();
 //スプレッドシートを指定
  var mySheet = SpreadsheetApp.getActiveSpreadsheet();
 //PDFを作成
  var pdfname = "スコアボード（"+today+"）.pdf";
  var mypdf = mySheet.getAs('application/pdf').setName(pdfname);
  var strBody = "今週のスコアボードです。";
 //メールを送信
  GmailApp.sendEmail("メールアドレス","今週(" + today + ")のスコアボード",strBody,{attachments:mypdf});
}

このスクリプトだとこのスプレッドシート全体をPDF化しています。

ちなみに私が組んだスクリプトは、

1. date.getFullYear()、date.getMonth()、date.getDate()で送信する日を取得
2. getAs('application/pdf’)で指定したスプレッドシートをPDF化
3. GmailApp.sendEmailで指定したところにメールを送る

となっています。

1、送信する日を取得

これはpdf化とは関係ありません。

メールで自動配信する際に配信された日を表示させたいがために行っています。

2、getAs('application/pdf’)で指定したスプレッドシートをPDF化

//スプレッドシートを指定
var mySheet = SpreadsheetApp.getActiveSpreadsheet();
//PDFを作成
var pdfname = "スコアボード（"+today+"）.pdf";
var mypdf = mySheet.getAs('application/pdf').setName(pdfname);

この部分がPDFを作成している部分です。

まず、PDFにしたいスプレッドシートを指定します。現在スクリプトを組んでいるスプレッドシートを指定するのでここはこのまま使用できます。

getAs('application/pdf’)というのがスプレッドシートをPDFするコードで、”指定したスプレッドシートをPDFとして取得する“という意味になります。

var mypdf = mySheet.getAs('application/pdf').setName(pdfname);

mySheetというのは前述のようにPDFにしたいスプレッドシートのことで、それをPDFとして取得し、そのPDFの名前はpdfname（ここではスコアボードとしています。）とします。という意味になりますね。

本当はグラフだけをPDFにするというような操作もしたかったのですが、getAs('application/pdf’)はそういう指定ができないらしく、スプレッドシート全体をPDF化しています。

そのためはじめに作ったレポート用スプレッドシートでも1枚に収まるようにレイアウトしておく必要があります。（複数枚に分かれてもいい場合は気にしなくても良いです）

3、GmailApp.sendEmailで指定したところにメールを送る

GmailApp.sendEmail("メールアドレス","今週(" + today + ")のスコアボード",strBody,{attachments:mypdf})

GmailApp.sendEmailがGmailでメールを送るというコードになっていて、かっこの中に“送付先メールアドレス”、“件名”、“本文”と指定してあげればそのようにメールを送付できます。

GmailApp.sendEmailの中に{attachments:mypdf}を追加すればファイルを添付できます。（mypdfはスクリプト内で作成したPDFの名前。“var mypdf”と記述した部分）

以上のように組むと、メールの件名は「今週（年/月/日）のスコアボード」、本文は「今週のスコアボードです。」、PDFのタイトルは「スコアボード（年/月/日）.pdf」となります。

GASでメールを送ることに関しては以下のサイトが参考になりました。
GASでのメール送信についてまとめてみる – Qiita

todayがずれる

ちなみにGASのみならずGoogleスプレッドシートでtoday関数が実際の日付とずれるという問題も発生するようです。

その原因はGoogleスプレッドシートのタイムゾーンの設定が違っていることが原因のようです。

毎週の結果を確認して次の週の行動を改善する

このレポートは今の所、週間で発信されるようにしています。

これらの数字を見ながら達成するにはどうすればいいのかを考えているところです。

やってみてやはり日々の行動が目に見えるようになると「やらないとなぁ」「どうすれば達成できるのか」を考えるようになりますね。

ここまでの体制を作るのは少し大変かもしれませんが、やってみて損はないなぁと我ながら思いました。