こんにちわ、ミヤタカです。

僕のお仕事は営業事務支援ということで、営業マンが苦手とする受注後の社内システムへの売上報告などのシステム入力などもお手伝いしています。

営業マンからはメールで登録の依頼をいただくのですが、またこれが人によって情報の粒度も違うので登録しづらいんですね。めちゃめちゃ詳しく書いてくれる人もいれば、悟って！と言わんばかりのほとんど情報がないような依頼をしてくる人もいます。

また、メールでの依頼にしているので、宛先こそ固定になるものの、件名もバラバラ、その依頼が今着手中なのか、営業に打ち返したのかという進捗管理もできず、苦心していました。中には、依頼が埋もれてしまって対応漏れしていた、という案件もありました。

そこで、依頼の窓口をGoogleFormに統一して、依頼情報をなるべく均一化した状態で受け取れるようにしよう！という取り組みを行うことにしました。

作成したコード

以下のコードをフォームの回答が溜まるスプレッドシートにスクリプトとして記載しています。フォーム側はスクリプトなしです。

function　onFormSubmit(e)　{


//問い合わせ連番（A列）の取得

//シートの取得
 var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('「sheetname」');
 var lastRow1 = sheet.getRange(1, 2).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();

//B列の最終行を取得して、その行のA列の受付番号を拾う
 var range = sheet.getRange(lastRow1,1);
 var numbr = range.getValue();

 // フォーム項目の取得
 
 //送信者と同報メールアドレスを合体させて変数に格納
 var email = e.namedValues["メールアドレス"][0];
 var shienmail = '「同報するメールアドレス」';
 var sendto =　email　+ ',' + shienmail;
 
 var time = e.namedValues['タイムスタンプ'][0];
 var shikyu = e.namedValues['１.至急対応の有無'][0];
 var shikyu_date = e.namedValues['1-1．（至急対応の場合）対応希望日'][0];
 var kessainum = e.namedValues['2.受注申請　起案番号（D～）'][0];
 var estnum = e.namedValues['3.見積番号（なしの場合Nを記入）'][0];
 var kubun = e.namedValues['4.新規登録or内容変更'][0];
 var pjcd = e.namedValues['4-2.（変更の場合）案件番号'][0];
 var reason = e.namedValues['4-3.（変更の場合）変更の理由'][0];
 var t_kubun = e.namedValues['5.取引形態'][0];
 var syukan = e.namedValues['5-1.（生産部門が複数の場合）業務主管部門'][0];
 var evidence = e.namedValues['6.受注証憑（未提出の場合）、最新の案件情報シート'][0];
 var message = e.namedValues['7.その他伝達事項（登録完了連絡時のCc追加アドレス希望、単価ごとの社内売・原価指定等）'][0];
   
 // 自動返信メール件名
 var subject = '【登録支援】依頼を受け付けました。［受付No.'+numbr+'］';
     
 // 自動返信メール本文
 var body =
   email + 'さま \n'+
   '\n' +
   'お疲れさまです。\n\n'+
   '支援につき、承りました。' +
   '\n' +
   '登録完了次第、ご連絡いたします。\n' +
   '※申請事項に不明点などありましたらお問い合わせさせていただきます。\n' + 
   '\n' +
   'よろしくお願い申し上げます。\n' + 
   '\n' +
   '─────────────────────────\n' +
   'ご依頼内容の確認\n' +
   '─────────────────────────\n\n' +
   '【受付日時】\n' +
     time + '\n' + 
    '\n' +
   '【至急】\n' +
    shikyu + '\n' + 
    '\n' +
   '【希望対応期日】\n' + shikyu_date +
    '\n\n' +
    '【受注決裁番号】\n' + kessainum +
    '\n\n' +
    '【見積番号】\n' +estnum +
    '\n\n' + 
    '【新規・変更】\n' + kubun +
    '\n\n' + 
    '【（変更の場合）対象案件番号】\n' + pjcd +
    '\n\n' + 
    '【（変更の場合）変更の理由】\n' + reason +
    '\n\n' + 
    '【取引区分】\n' + t_kubun +
    '\n\n' +
    '【業務主管】\n' + syukan +
    '\n\n' +
    '【証憑類】\n' + evidence +
    '\n\n' +
   '【伝達事項】\n' +
   message　+ '\n';
 
 // メール送信
 MailApp.sendEmail(
   sendto,subject,body,{name:'事務支援担当'});
}

構想・要件整理

入り口はGoogleFormにするとして、標準の機能だけですと今の運用にマッチしないということがまず最初の壁でした。

その中で、必要な要件は以下と整理しました。

・依頼が来たら事務支援チームのML＋依頼した本人に受付完了メールが入る
・依頼内容は受付完了メールに転載されること
・スプレッドシートで対応者、ステータスが分かるようにすること
　（これは完了したか否かがわかるようにという意味合い）
・対応状況の進捗がわかるように依頼ごとに依頼ナンバーを振る
　（依頼した人も支援側も案件を特定しやすい）

自動送信をどうやって実現するか？

基本的な機能はGoogleFormの基本機能でカバーしたかったのですが、受付完了メールを送るという要件を満たすためにはどうしてもスクリプトが必要でした。

標準のGoogleFormを利用したことのある方であればご存知と思いますが、GoogleFormは投稿者へのサンキューメール機能の使い勝手が悪いんです。コピーを自分宛てに送信、という機能はありますが、これってほんとに受領されてるのかな？と不安になるようなレベルです。

これをなんとかスクリプトで解決するというのがまずはじめの課題でした。

この自動送信の機能は「GoogleForm　返信メール」などでググれば色々出てきます。ざっくりとした方針は、

フォームに情報が投稿される
↓
スプレッドシートに回答内容が記載される
↓
フォーム送信をトリガーとして、メール送信のスクリプトを走らせる

という流れです。

ソースも後半で公開していますが、（e）という引数？を使って、特定のイベント（今回はフォーム送信）が起こったら関数を実行するというようなスクリプトにしています。この（e）ですが、私もまだまだ勉強が足りず、深くは理解していないのですが、ひとまずはざっくりとイベントをトリガーとした関数という理解をしています。

メールにフォーム回答内容を転載する

スプレッドシートに回答内容が溜まっていくのでその内容をそのまま転記していこうという方針のもと、すべての回答内容を変数に突っ込んでいます。その変数を要所要所で引っ張ってきて反映させているというシンプルな作りです。

メールの本文もテキストと変数を組み合わせながら作成し、最終的には変数「body」に格納しています。

受付番号を付与したい！

これ、あまり他の人に理解されなかったのですが、もらった依頼に通し番号を付けて管理するというのはものすごーく大切なことと思っています。

依頼した側からすれば、この番号の処理ってどうなっていますか？と質問しやすいですし、処理する側もユニークな番号で案件を管理できるので対処漏れが少なくなります。宅配便や郵便、通販の仕組みももれなく受付番号を発行していることから、依頼受付に通し番号を振るというのはすごく大切なことなのだなとわかってもらえると思います。

では、どうやって実現したか？

まず、フォーム回答のスプレットシートの一番左に１列手動で追加します。この列のラベルは受付Noとでもしておくと良いと思います。

次に、通し番号を振ります。

これはよくエクセルの通し番号をrow関数を使って振ったりしますが、それを使って一律番号を振ってしまいます。関数が邪魔くさければ１０００行くらい数字を振ってしまっても良いと思います。

ここからが腕の見せ所ですね。

新規で追加された行の一番左端の番号を受付番号としてメールのタイトルに入れたく、変数にそのセルに入っている数字を格納するという動作になりますが、まず、B列（タイムスタンプ）の１行目にカーソルを合わせてCtrl+↓を押すような動作をさせます。これで一番下の行が何行目かを取得します。これがスクリプトでいう「lastRow1」の変数に格納されます。

そうしたら、（A, B列の最終行）という座標を「range」に格納、その座標の値を「numbr」に格納という手順で受付番号をゲットしてきます。

この変数「numbr」をメールタイトルに入れることで、受付番号も同時に依頼者へお知らせすることができます。

//問い合わせ連番（A列）の取得

//シートの取得
 var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('「sheetname」');
 var lastRow1 = sheet.getRange(1, 2).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();

//B列の最終行を取得して、その行のA列の受付番号を拾う
 var range = sheet.getRange(lastRow1,1);
 var numbr = range.getValue();

メールを送信する

Google Apps Scriptでは便利なライブラリがあり、MailApp.sendEmail();でGmailを利用したメール送信ができるようになっています。これはホント便利です。引数は順番に、宛先、件名、本文。ここまでが必須で、残りは任意項目となっています。

任意項目、最初はいらないかと思ったのですが、何回かテストすると自動送信アカウントが受付用のアカウントですよということがはっきりわかったほうが精神安定上いいなと思い、送り主の名前を「事務支援担当」と設定することにしました。

突然、rparobot@〜〜といったメールアドレスから返信が来たら僕だったらゴミ箱に捨てちゃいますね（笑）　ということで、見てもらうためにも送り主表示は変えたほうが良いかと思います。

MailApp.sendEmail(
   sendto,subject,body,{name:'事務支援担当'});

 

最後にトリガーをかならず設定

フォームが発射されるたびにこのスクリプトを実行するため、トリガー設定は必須です。これを僕は最初忘れてまして、いくら送ってもメールが飛ばないという事態に１時間近く陥っていました。素人丸出しですね（汗）

トリガーを追加、から、メール送信するアカウントでトリガーを設定しましょう！

このトリガーを自分のアカウントで設定してしまうと、自分のGmailから送信されることになるので、送信ボックスが大変なことになります。できれば自動返信用のアカウントを発行してもらうのが好ましいですね。

運用開始、その後

無事、実務者のテストを経て、実践稼働となり思ったより不具合なく動いています。メールで貰った依頼に対してメール返信で対応というもともとの運用を活かして要件を作ったので、スムーズな運用担ったのではないかと思っています。

まれに、スプレッドシートのラグがあるのか、受付番号がうまく取れないことがあります。メールだけを信用するのではなく、随時スプレッドシートを見て、抜け番がないかどうかを確認するという運用も必要です。

ただ、Gmailをスレッド表示にしている場合は、同じ件名で違う依頼が来た段階でおかしい！と気付けるのでその時点で確認してもよいかと思います。

最後に

言葉足らずなところもあるかと思いますが、興味が出た方、ここはどうしているの？など質問があればぜひお気軽にコンタクトいただけると幸いです。