【GAS】自動受付＆仕分け＆台帳作成システム（最終）～シート＆フォーム＆フォルダの作成とGASコードの実装する～
いつ来るか分からない、誰が送ってくるか分からないものについて、投稿先を掲示して何らかの提出物を受け付けるシステムのご紹介です。
Googleフォームを利用するのが妥当ですが、これにはファイル添付機能がないために、最初のエントリーのみGooglフォームから申し込んでもらい、自動発信されるGメールにファイルを添付して返信してもらうシステムです。
その後は受信したメールから自動で添付ファイルを抽出し、Googleドライブ上の所定のフォルダ下に保存します。
以上の一連の流れを列記すると以下になります。
＜☆事前に投稿ファイルの保管フォルダをGドライブ上に用意＞
→投稿者が、Googleフォームからエントリーする
→受け付け番号を自動生成して、投稿者にメール返信する
＜☆専用サブフォルダを自動作成して待ち受ける＞
→投稿者が、作品ファイルを添付してメール返信する
＜☆受信メールからファイルを自動抽出して専用フォルダに保存※＞
＜☆保存フォルダへのリンクを含む受け付け台帳を自動作成＞
※投稿作品が追加で送信された場合も対応できます。
＜☆＞は投稿者側には見えない作業です。返信作業を含み、通常なら受け付け担当がこうした裏方業務を手作業で毎日行うところ、google Apps Script を使う事で定期的に自動処理されます。
今回の記事は、これら一連の処理を実装する作業およびコードをご紹介します。このテーマの最終記事になります。
本記事では実装コードをご紹介しています。様々な理由により、この説明通りにいかない場合がしばしばあります。申し訳ありませんが、自己責任・自己解決でお進めくださるよう、お願いいたします。
（ご紹介の内容は、身内のささやかなイベントを想定しており、規模の大きなイベントや商用での利用は想定しておりません）
今回のテーマは、複数のアプリが介在しますので、予期しない不具合も起こりやすいと思います。ご注意ください。
なお、提起処理（トリガー設定）は、頻度を高め過ぎるとGoogle
Apps Script の処理時間制限をオーバーして停止してしまうので、投稿が集中する期間を覗き、なるべく粗い頻度が良いと思います。
なお、Gメールで投稿しきれないサイズの大きなファイルは、保存フォルダを共有して個別にアップしてもらう等の対応をとると良いでしょう。
実装作業1～事前に投稿ファイルの保管フォルダをGドライブ上に用意～
それでは早速、実装手順をご説明します。
最初に、投稿ファイルを保管するフォルダをGドライブ上に用意します。投稿作品は、この直下に自動作成されるサブフォルダに保管されます。
フォルダ名は何でもいいのですが、ここでは「投稿作品」フォルダとします。
さらに、後でフォームで設定する投稿カテゴリー別のサブフォルダをその下に作成します。
投稿カテゴリーは、ここでは以下の3つとし、頭に①～など連番をふっておきます。
①イラスト、②小説、③音楽
これは、カテゴリーが増えた場合でもソートすれば見やすくなる効果を狙っています。（GASでも利用しますので、1文字目はこの様にしてください）
また、カテゴリーが明記されなかった場合の「その他」も作成しておきます。
投稿作品は、該当するカテゴリーのフォルダ下に、さらに専用サブフォルダを自動で作って保存されます。
実装作業2～Googleフォームと台帳となるスプレッドシートを用意～
次は、Googleフォームと台帳となるスプレッドシートを用意します。
まずGoogleドライブ上の適当な場所に、スプレッドシートを新規作成します。ここでは、ファイル名を「投稿作品の台帳」としました。
新規作成したスプレッドシートの「ツール」メニューから「新しいフォームを作成」します。
フォームが作成されるので、適当な名称（「投稿作品フォーム」としました）にして、以下の4つの記入項目を追加します。
お名前（記述式）
連絡先メールアドレス（記述式）
参加部門（プルダウン）→①イラスト、②小説、③音楽
コメント（記述式）
なお、投稿台帳となるシートは、フォームを作成したタイミングで、「フォームの回答」などの名称で自動作成されます。
このシートの行や列はフォームの回答内容とリンクしていますので、行や列の削除・追加・入れ替えなどは厳禁です。ダミー投稿した場合も消さないこと！列を入れ替えたくなっても変えないこと！
実装作業3～Googleフォームに自動返信するGoogle Apps Scriptを用意～
フォームにはGASプログラムを設定することができます。
右上の「・・・」アイコンをクリックし、「スクリプトエディタ」を選択します。
GASのプロジェクトが自動作成され、GASのエディタが開きますので、ここからコードを記載していきます。
記載するコードは以下です。（★自分のID★）部分は、各自の台帳スプレッドシートのIDに打ち替えてください。
//＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
// フォームを受信すると確認メールを返信
//＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
function onSubmit(e) {
// フォーム入力内容
var itemResponses = e.response.getItemResponses();
var userName = itemResponses[0].getResponse(); // 回答 01 名前
var userEmail = itemResponses[1].getResponse(); // 回答 02 メールアドレス
var userGroup = itemResponses[2].getResponse(); // 回答 03 参加部門
var userComment = itemResponses[3].getResponse(); // 回答 04 コメント
// 受付作品の情報
var regCont = '';
regCont += "お名前 ：" + userName + "\n";
regCont += "連絡先アドレス：" + userEmail + "\n";
regCont += "参加部門 ：" + userGroup + "\n";
regCont += "\n";
regCont += "コメント：" + "\n";
regCont += " " + userComment + "\n";
regCont += "\n";
//スプレッドシートのID（★自分のID★）
var MyApp = SpreadsheetApp.openById('★自分のID★');
var MySheet = MyApp.getSheetByName('フォームの回答 1');
var regNum = MySheet.getLastRow(); // この入力が記入された行＝受付番号
//受付番号を記録する列
var C_regNun = 6; // フォームの項目数 + 2とする
//受付番号
var newCont = ("C"+(100000+regNum)).replace("C1", "C");
MySheet.getRange(regNum,C_regNun).setValue(newCont);
//メールの件名
subjectReply = "作品投稿用メール："; // 整理番号を付加
subjectReply += "受付番号【" + newCont + "】"; // 整理番号を付加
//メールの本文
var msgBody =
userName + " 様\n\n" +
"下記内容にて受付けました。\n" +
"\n" +
"受付番号：【" + newCont + "】\n" +
"----------------------------------\n" +
"応募いただいた内容\n" +
regCont +
"----------------------------------\n" +
"\n" +
"このメールは自動応答メールです。\n" +
"投稿作品はこのメールへの返信に添付して送信してください。\n" +
"（件名は編集しないで下さい。）\n" +
"";
//Ｇメールの送信
try {
GmailApp.sendEmail(userEmail, subjectReply, msgBody) ;
MySheet.getRange(regNum,C_regNun+1).setValue("自動返信成功");
} catch(e) { // 自動返信失敗時
MySheet.getRange(regNum,C_regNun+1).setValue("自動返信失敗");
}
}
ここでいくつか注意点です。
注意点1
フォームの回答内容が入っている、itemResponses配列は、必ずしも・・・
フォームの回答項目の順序＝itemResponses[0]、[1]、[2]の順序
・・・ではありません。
回答項目を追加削除したり、並べ変えたりした場合は、フォームの見た目とは全く異なる配列番号になっているので、注意ください。
確認するには、試しにすべての項目に入力してダミー投稿し、スプレッドシートにどういう順で記録されるか見ると良いでしょう。スプレッドシート上の記録順序が、上記配列の順序になります。
注意点2
このプログラムでは、受け付け番号を、記録行の行番号で発行しています。その為、初期値は2からですのでご注意ください。
注意点3
回答項目を増やした場合は、受け付け番号を記録する列もずらしてください。（現在は以下の設定ですが、数字を6より増やしてください）
//受付番号を記録する列
var C_regNun = 6; // フォームの項目数 + 2とする デプロイとトリガーの設定
回答の並びを確認し、コードを保存したら、プログラムをデプロイします。（デプロイはスプレッドシートのプログラム（以下参照）と同じです）
デプロイが済んだら、トリガーを設定します。スクリプトエディタの左にある時計の様なアイコンをクリックします。
実行させるモジュールを選び、「フォームの送信時」を選びます。
以上で、フォーム投稿されたら、受け付け番号を生成して自動返信メールを送る部分を実装できました。
実装作業4～保存用サブフォルダを作成し、Gメール添付の投稿作品を保存するGoogle Apps Scriptを用意する～
ここからは、単独のGASプロジェクトを作成して、そこに記述します。
Googleドライブ上にGASプロジェクトを新規作成します。
一番下の「その他」を選んで・・・
・・・Google Apps Scriptをクリックし、適当な名前のプロジェクトにします。ここでは「投稿作品の保存」としました。
スクリプトエディタ上で、スクリプトのファイルを新規作成し、「サブフォルダの作成」と「作品の抽出と保存」を用意します。
保存用サブフォルダの作成プログラム
保存用サブフォルダを自動作成するプログラムは以下です。上記の「サブフォルダ作成」ファイルに記述します。
以下のスプレッドシートのIDやフォルダのIDは、各自のものを調べて（右クリックでリンクを調べます）書き換えておきましょう。
★台帳シートのID★
★①イラストのフォルダID★
★②小説のフォルダID★
★③音楽のフォルダID★
★その他のフォルダID★
//--------受付番号のサブフォルダを作成してそのURLを収集します---------
function mkSFolder(){
//アプリケーションを取得｜スプレッドシートのIDは各自のものを’★台帳シートのID★’部に記入
var myApp = SpreadsheetApp.openById('★台帳シートのID★');
//対象シートをシートの名前を指定して取得
var mySheet = myApp.getSheetByName('フォームの回答 1');
//フォーム回答シートの最終行を取得する
var nRow = mySheet.getLastRow();
//受付番号を記録する列
var C_regNun = 6; // フォームの項目数 + 2とする
//投稿グループを記録する列
var C_group = 4; // 投稿グループ
//フォーム回答シートの２行目以降をループする
for(var iRow=2; iRow < nRow+1; iRow++){
//受付番号が空白でなければ実施する
if(!mySheet.getRange(iRow,C_regNun).isBlank()){
var FolderID='';
// switch文を使ってルートフォルダを条件分岐する
switch(mySheet.getRange(iRow,C_group).getValue().substring(0,1)){
case "①"://イラスト
FolderID='★①イラストのフォルダID★';
break;
case "②"://小説
FolderID='★②小説のフォルダID★';
break;
case "③"://音楽
FolderID='★③音楽のフォルダID★';
break;
default://その他
FolderID='★その他のフォルダID★';
}
//ルートフォルダを取得｜親フォルダのIDは該当のものを’～’部に記入
var myRootF=DriveApp.getFolderById(FolderID);
//フォーム回答シートの(C_regNun+2)列目が空白なら受付番号のサブフォルダを作成する
if(mySheet.getRange(iRow,C_regNun+2).isBlank()){
//受付番号を取得
var newC=mySheet.getRange(iRow,C_regNun).getValue();
//受付番号名のサブフォルダを追加
myRootF.createFolder(newC);
//フラグを記載
mySheet.getRange(iRow,C_regNun+2).setValue('作成済');
}
//フォーム回答シートの(C_regNun+3)列目が空白ならサブフォルダのURLを追記する
if(mySheet.getRange(iRow,C_regNun+3).isBlank()){
// ルートフォルダ内のフォルダを一括取得(Foldersオブジェクト)
const folders = myRootF.getFolders();
// ループを回してフォルダの属性を取得
while (folders.hasNext()) {
const folder1 = folders.next();
const foldername = folder1.getName();
const folderURL = folder1.getUrl();
if(foldername==newC){
//該当行の受付番号と一致する名前があれば、そのフォルダのリンクを記入
mySheet.getRange(iRow,C_regNun+3).setValue(folderURL);
}
}
}
}
}
}注意点
フォームの回答項目を増やした場合は、（何度も項目を追加・削除した場合も同様）受け付け番号の記載列を変えるこをを前述しましたが、その場合、本プログラムで受け付け番号を読み取っている以下部分も手直ししておきます。
//受付番号を記録する列
var C_regNun = 6; // フォームの項目数 + 2とするGメール添付の投稿作品を保存するプログラム
Gメール添付の投稿作品を自動保存するプログラムは以下です。「作品の抽出と保存」ファイルに記述します。
以下のスプレッドシートのIDは、各自のものを調べて（右クリックでリンクを調べます）書き換えておきましょう。
//--------Gメールで、件名に受付番号があるものを検索し、添付ファイルをサブフォルダに保存する---------
function fetchFile(){
//アプリケーションを取得｜スプレッドシートのIDは各自のものを’★台帳シートのID★’部に記入
var myApp = SpreadsheetApp.openById('★台帳シートのID★');
//対象シートをシートの名前を指定して取得
var mySheet = myApp.getSheetByName('フォームの回答 1');
//受付番号を記録する列
var C_regNun = 6; // フォームの項目数 + 2とする
//フォーム回答シートの最終行を取得する
var nRow = mySheet.getLastRow();
//フォーム回答シートの２行目以降をループする
for(var iRow=2; iRow < nRow+1; iRow++){
//フォーム回答シートの(C_regNun)列,(C_regNun＋3)列目が空白でないなら実施する
if((!mySheet.getRange(iRow,C_regNun).isBlank())&&(!mySheet.getRange(iRow,C_regNun+3).isBlank())){
//受付番号を取得する
var C = mySheet.getRange(iRow,C_regNun).getValue();
//検索条件を設定する |表題に受付番号を含む
const SEARCH_TERM = 'subject:' + C;
//メールを検索条件で検索する |念のため最大値を30としておく
const threads = GmailApp.search(SEARCH_TERM, 0, 30);
//検索結果からメールを抽出する
const messages = GmailApp.getMessagesForThreads(threads);
//保存フォルダを取得する
var FID = mySheet.getRange(iRow,C_regNun+3).getValue().substring(39);
const myFolder = DriveApp.getFolderById(FID);
//検索結果（２次元配列）からメッセージを抽出する
for(const thread of messages){
for(const message of thread){
//スターがないメッセージのみ処理
if(!message.isStarred()){
//メッセージから添付ファイルを抽出する
const attachments = message.getAttachments();
//処理済みのメッセージにスターをつける
message.star();
for(const attachment of attachments){
myFolder.createFile(attachment);
}
}
}
}
}
}
}
注意点
フォームの回答項目を増やした場合は、本プログラムで受け付け番号を読み取っている以下部分も手直ししておきます。
//受付番号を記録する列
var C_regNun = 6; // フォームの項目数 + 2とするデプロイとトリガーの設定
ここまで出来たら、このプロジェクトをデプロイします。
次いで、トリガーを設定します。
スクリプトエディタの時計アイコンをクリックして、設定します。
今度は2つとも時間ベースで設定します。
投稿頻度にもよりますが、3時間程度の間隔ではどうでしょうか。
実際には、フォルダの生成やGメールの調査などは、タイミングよく実行されるわけではありません。まだ保存フォルダがない場合はファイルの抽出作業はスキップされますが、次のタイミングで処理される筈です。
（なお、実行順は、確実に保存先フォルダが存在するもののみ抽出される様に、ファイル抽出を先にしていますが、逆でも良いかもしれません）
以上、少し長めの実装作業でした。
イベントの受け付け係りさんの労力が減ることを祈念します！
