G Suiteの主要なアプリケーションはOffice365とは違い、Google Apps Scriptで開発をし、機能を強化する事が可能です。このGoogle Formも同じです。オリジナルにはない機能を付け加える事で、更にG Suiteが活用出来るばかりではなく、全自動で人間が行っていた作業をやらせる事が出来るので、利用者が多ければ多いほど、削減出来る労力や時間・コストは大きいです。

今回は、前回の活用するその①に引き続き、さらにGoogle Apps Scriptで強化し、色々な作業をやらせてみたいと思います。このテクニックはFormだけでなく、自作のフォーム類や他のスクリプトでも応用の出来るものです。

今回使用するファイル

本フォームは自動応答機能付きGoogle Formとしてサイトでリリースされているものと同じものです。フォーム自体の使い方や、細かな設定関係や事前準備に関してはそちらのページを参照してください。

送信時スクリプトを使う為には

今回紹介しているスクリプト類を使う為には、必ずトリガーの設置が必要になっています。トリガー自体に関してはまた別のエントリーで詳しく紹介する予定ですが、ここでは必要最低限これだけはというトリガーの設置に関する解説をしてみます。

今回自動作業させる関数はsendForms()という関数で作成しています。以下の手順でスクリプト作成後にトリガーを設置してください。

ここで注意しなければならないのは、このトリガー設置はこのフォームの管理者だけが行って下さい。他の方もやってしまうと二重にトリガーが設置されてしまいます。フォームは他の人からは他の人のトリガーが見えない仕様なので、トリガー削除もその人でなければ出来ません。気を付けましょう。

  1. フォームの右上の「︙」をクリックし、「スクリプトエディタ」を開きます。
  2. メニューより、「編集」⇒「現在のプロジェクトのトリガー」を開きます。
  3. トリガーが空なので、リンクをクリックし、実行に「sendforms」、イベントに「フォームから送信時」にてセットします。

図:スクリプトトリガーがなければ動作しません

搭載している機能

自動応答メール機能

現在のGoogle Formには、アンケート送信時に「回答のコピーを送信」する機能が備わっています。以前はなかった機能ですが、これで自動応答メール的な役割は簡易的には可能です。しかし、あくまで簡易機能であり、その中のメッセージであったり、デザイン等をカスタマイズ出来るわけでもないので、便利なものではありません。

そこで装備したいのが「しっかりした自動応答メール送信機能」です。尚、回答のコピーを送信で行わせる場合には以下の手順でスクリプト無しで装備が可能です。

  1. 右上の歯車をクリックする
  2. 全般にて「メールアドレスを収集する」をチェックする
  3. 「回答のコピーを送信する」をチェックする

図:ただこの機能はちょっと不便なんですよね・・・

自動応答メールは、HTMLメールの形式で作成をするわけですが、その為にはHTMLタグで改行を加えつつメール本文を構築したり、またテーブルタグで綺麗に整形したり(またこの場合、レスポンシブメールにすると非常に綺麗になります)して、申請者や管理者宛に自動でメールを送信します。メール本文に署名や挨拶文などを加える事が可能になるので、業務やお問い合わせフォームなどでは必須の機能になります。

メールのSubjectやName,メッセージ内容はスクリプト内に直接書くのも良いのですが、メンテナンスフリーにする為に、このあたりもUIを自分で実装して誰でも変更出来るようにすると尚良いでしょうね(担当者が変わった程度でソースを直さないと行けないのは不便ですから)。

書類自動生成機能

例えば申請フォームなどの場合、このままではスプレッドシートに1行申請データが追加されるだけで、ペーパーレスならばこれでも良いのですが、会社内で印刷物で判子が必要といった場合には、データから別に申請書を作成しなければなりません。この作業を人間が行うのは非生産的以外の何者でもないので、予め用意して於いたテンプレートをコピーして、そこにデータを書き込ませる事で書類も自動生成させてしまうのが良いでしょう。

この後の項目のPDF化をするにも必須です。Googleドキュメントでも良いのですが、Googleスプレッドシートのほうがきめ細かなレイアウトを作成し易いので、ここではスプレッドシートにて諸類を自動生成させてみます。

予め用意しておいたGoogle Driveのフォルダにファイルを生成します。データはコピーした申請書テンプレートの1つずつのセルに書き込むのではなく、arrayで受け取ったデータを別のシートにappendRowで一気に書き込み、申請書シートからは数式でそれを参照する形にしています。この方が高速に書き込みが可能です。

ファイルの移動はちょっとややこしいですが、一度生成したファイルの親フォルダからはremoveFileし、格納先フォルダにaddFileする事が必要です。生成したファイルの親フォルダを知る為に、driveman()という関数を別に用意してあります。最期にfilesIdを本ルーチンに返して上げています。

自動PDF作成機能

自動生成された書類はスプレッドシートなどのデータです。しかし改ざんの可能性もあったり、即時に印刷をしたい場合には少々不便です。この書類をPDF化してしまいましょう。PDF化させる為のルーチンの詳しい解説はまた別のエントリーで紹介します。ここではPDF化する為にはこういうルーチンを組むという事だけ紹介します。

PDF生成は、特別なURLに対して、UrlFetchAppクラスを使ってアクセスさせ、Blob形式でPDFとして取得します。その際にsetNameでファイルに名前を付けています。また、この受け取ったPDFデータをDriveAppクラスを使ってドライブ内にPDFファイルも生成させています。

途中のrangechkですが、時々データ書き込み完了前にPDF生成にルーチンが移動してしまって、空のままPDFが作成されてしまうことがあるので、ここで書き込みチェックをする無限ループを設けてあります。チェックするセル番地は一番最期に書き込まれる所を指定すると良いでしょう。

メールに添付する時には、以下のように受け取ったpdfをattachmentに加えれば良いです。複数のファイルを添付する時には配列で渡してあげればOKです。

自前でスプレッドシートに書き込む

Google Formは自動でスプレッドシートへ書き込む機能が搭載されているのですが、書き込まれる情報はフォーム上の情報に限られ、また書き込まれる列の並び順なども固定です。これではスプレッドシートの二次利用をする上では少々困るケースがあります(ワークフロー的なシステムや、お問い合わせ番号で作業をするようなケース)

そこで、書き込み自体Google Formにやらせるのではなく、自前でスプレッドシートに書き込む機能を装備する事で、さらに高度なGoogle Formの活用をする事が可能です。但し、送信日付に関してはフォーム送信内容からは取得が出来ないので、new Date()で自分で生成してあげる必要があります。

スプレッドシート書き込み

自前で書き込みするといっても、さして難しいものではありません。取得したフォームのデータにユニークIDや自動生成した書類のURL、また処理ステータスの初期値などを自分の場合書き込ませてますが、基本は配列を用意して流し込むだけです。

今回はこの次の項目で紹介するユニークIDを配列の一番最初に、申請日を配列の2番目に、最期に初期ステータスとして「未処理」を追加するようにしています。こうする事で、自前で自由自在にデータを書き込みさせる事が可能になります。

ユニークIDを割り振ってあげる

ユニークなIDとは、絶対にかぶらない連番の事で、例えばお問い合わせ番号を発行してこの番号を元に、双方で仕事をしたり、またワークフロー的な仕組みを構築する時に、そのレコードを特定するにはユニークなIDが必要になります。このユニークIDは通常は1列目に書き込みを行わせます(vlookup関数でその後参照させたりする事もありますから)。

このユニークIDを発行する仕組みには、プロジェクトプロパティを使用しますが、同時に送信されるケースも考慮してLock Serviceを使い排他処理も装備する必要があります。排他処理自体はまた別の項目で紹介しています。ここではユニークID発行の仕組みだけを紹介します。

原理は簡単で

  1. LockServiceで排他処理を開始
  2. プロジェクトプロパティのuidの値を取得し、1を加算する
  3. プロジェクトプロパティにuidの値を書き戻す
  4. LockSercviceを開放する

後はスプレッドシートに書き込む為の配列の一番最初にこのuidの値をpushもしくはunshiftしてあげれば良いです。

自動でCC追加機能

業務で使用する場合、自動で上司や関連部署の人にCCで通知も送りたい事があります。その場合別のシートにその一覧を用意しておき、それを参照し、MailAppのccにはめ込めば良いでしょう。別のシートには、申請者のメアド、CC送信先の2列で用意すればOKです。シート名は「CC送信先」と付けています。データは2行目から入っています。

この時、ユーザのメアドは自動で取得させるほうが楽ですが、今回はFormのメールアドレス欄で入力されたものを使ってCCの送信先を探索させる方法をあえて取ってみます。CC送信先はカンマ区切りでセルに入力しておけば、複数送れます。

この処理では、メールアドレス入力欄が何番目であっても、メールアドレスという名称の入力欄の値を取得してくれます。また、そのメアドを元にCC送信先シートから一致するメアド、それに対応するCC送信先の値を変数ccに格納しています。cc送信先がみつかったら、break;で抜けて、後は、MailAppにて、ccにこの値を加えれば良いです。

フォームの回答を削除する

自前で書き込みをさせる時に、フォームに回答データを残しておきたくない場合があります。その時には、ルーチンの最期でフォームレスポンスデータを全て削除するコードを入れて入れておけば良いでしょう。

関連リンク