【Google Apps Script(GAS)】申請フォーム(ワークフロー)の作成例

スポンサーリンク

オンラインGAS講座 開講中!
オンライン無料体験あり!
最短4週間でGASをマスターできる
Google Apps Scriptコース』開講中!

今回はGoogle Apps Script(GAS)を使い、

・Googleフォーム

・スプレッドシート

・Googleドライブ(フォルダ)

・Google Sheets API

などを組み合わせた申請フォーム(ワークフロー)の作成例についてご紹介します。

いつもご覧いただきありがとうございます。

先日、日本国在住の男性(多分)の方から、

Googleフォームを使った簡単なワークフローが上手くいかない

とのお便りをいただきました。

(いつもご覧いただきありがとうございます!)

本来は基本的に個別対応は行わないつもりなのですが(というより対応するキャパや余力がないのですが・・・)、たまには徳を積まないとご先祖様に怒られそうです(笑)

ですので、今回は私にできる範囲で、ではありますが、ご紹介してみたいと思います。

もっと良い方法や、情報が不十分な面による認識違いがあるかもしれませんが、そこはご了承下さい。

今回作成する申請フローの流れ

今回実現したい流れとしては、

①申請者

→ ②上長(承認者)

→ ③作業者

→ ④申請者

のフローですが、今回は、

①申請者がGoogleフォームを使って上長に承認依頼を出す

②上長はメールを受け取り、スプレッドシートより承認完了メールを送信する

(HTML Serviceを使って簡単な管理画面を作成する手もありますが、今回は簡単なワークフローとの事ですし、HTML Serviceは使用しない方向にしたいと思います)

③作業者は上長よりメールを受け取り、スプレッドシートより作業完了メールを送信

④申請者は作業完了メールを受け取る

というイメージでご紹介したいと思います。

用意するもの

今回用意するGoogleフォーム、スプレッドシート、Googleドライブ(フォルダ)などは、下記のようになります。

申請用のGoogleフォーム

※内容は適当ですので、必要に応じて変更いただけますと幸いです。

申請内容を記載するためのスプレッドシート

※内容は適当ですので、必要に応じて変更いただけますと幸いです。

またシート名は「申請依頼管理シート」としています。

添付ファイル格納用のフォルダ(Googleドライブ)

Googleドライブ上のお好きな場所に、添付ファイル保存用のフォルダを作成してIDを取得してください。

今回の処理内容

今回の処理内容は、ざっくり下記のようになります。

・Googleフォーム送信後に、必要なアドレスへ申請内容をメール送信

・Googleフォーム送信後に、申請内容をスプレッドシートへ記載

・添付ファイルがある場合、Googleフォーム送信後に、Googleドライブ上にフォルダを作成し、ファイルを格納

・Googleフォーム送信の際、何らかの理由でエラーとなって送信できなかった場合、申請漏れにならないようにエラーを検知して申請者や管理者などへメール送信

・スプレッドシートに生成されたチェックボックスをクリックすると、承認メールや作業完了メールが自動送信される

サンプルスクリプト

以下が、今回使用するスクリプトになります。

メールの文面や申請IDなどは適当ですので、必要に応じて変更いただければと思います。

・サンプル1

var SP_ID = “[スプレッドシートのIDを記載]”;
var SH_NAME = “申請依頼管理シート”;
var sh = SpreadsheetApp.openById(SP_ID).getSheetByName(SH_NAME);
var last_row = sh.getLastRow();
var s_id = “S” + (100000 + last_row);
var sup_add = “[上長のアドレスを記載]” //上長のアドレス
var man_add = “[フォーム管理者のアドレスを記載]” //フォーム管理者のアドレス
var work_add = “[作業者のアドレスを記載]” //作業者のアドレス
var ali_add = “[GASオーナーがエイリアス設定するアドレスを記載]” //GASオーナーがエイリアス設定するアドレスvar list = “[スプレッドシートの共有URLを記載]”;function getformdata(e) {try {var form = FormApp.openById(“[フォーム編集画面のIDを記載”);
var formResponses = form.getResponses();
for (var i = 0; i < formResponses.length; i++) {
var formResponse = formResponses[i];
var address=formResponse.getRespondentEmail();
}

var data = [];
data[0] = e.namedValues[“タイムスタンプ”];
data[1] = e.namedValues[“申請者”];
data[2] = e.namedValues[“コメント”];
data[3] = e.namedValues[“添付ファイル”];
data[4] = address;

var timeStamp=data[0];
var name=data[1];
var com=data[2];
var file=data[3];
var app_add=data[4]; //申請者のアドレス

createfile(data);

} catch (e) {

result = “【エラー内容】” + e;
Logger.log(result);

GmailApp.sendEmail(
app_add,
“申請フォームからの送信に失敗しました”,
“申請フォームからの送信に失敗しました。\nお手数ですが、申請フォーム管理者までご連絡ください。\n\n【申請者名】”+name+”\n【申請者メールアドレス】”+app_add+”\n”+result,
{
from: ali_add,
cc: ali_add
}
);

} finally {

var ash = SpreadsheetApp.getActiveSpreadsheet();
var objsh = ash.getSheetByName(“申請依頼管理シート”);
ash.setActiveSheet(objsh);

}
}

//添付ファイル保存処理
function createfile(data) {

var timeStamp=data[0];
var name=data[1];
var com=data[2];
var file=data[3];
var app_add=data[4];

var str=String(file);

if (str != “”){

var formatDate = Utilities.formatDate(new Date(), “JST”,”yyyyMMdd”);

var arr = str.split(‘, ‘);
var count = arr.length;

var destfolderid = “[添付ファイル保存フォルダのIDを記載]”;
var destfolder = DriveApp.getFolderById(destfolderid);
var foldername = s_id + ‘(’ + formatDate + ‘)’;
var destfolder_childs = destfolder.getFolders();

var flag = 0;

if ( destfolder_childs.hasNext() ){

while ( destfolder_childs.hasNext() ){
var folder = destfolder_childs.next();
if ( folder.getName().indexOf(foldername) != -1 ){
flag = 1;
}
}

if (flag == 0){

var newfolder = destfolder.createFolder(foldername);
var newfolderid = newfolder.getId();

}

else if (flag == 1){

var newfolder = destfolder.getFoldersByName(foldername).next();
var newfolderid = newfolder.getId();

}

}else{

var newfolder = destfolder.createFolder(foldername);
var newfolderid = newfolder.getId();

flag = 2;
}

var fid=[];
var templateFile=[];
var OutputFolder=[];
var OutputFileName=[];

if (str != “”){

for (var j = 0; j < count; j++) {

fid[j]=arr[j].replace(“https://drive.google.com/open?id=”, “”) ;

templateFile[j] = DriveApp.getFileById(fid[j]);

OutputFolder[j] = DriveApp.getFolderById(newfolderid);

OutputFileName[j] = templateFile[j].getName();
templateFile[j].makeCopy(OutputFileName[j], OutputFolder[j]);

}
}
}

if (str != “”){

var newfolderurl = newfolder.getUrl();

} else if (str == “”) {

var newfolderurl = “添付ファイルなし”;

}

sendmail(data,newfolderurl);

}

//メール送信用の処理
function sendmail(data,newfolderurl) {

var timeStamp=data[0];
var name=data[1];
var com=data[2];
var file=data[3];
var app_add=data[4];

var body=”〇〇様\n\n”;
body+=”お疲れ様です。”+name+”です。\n\n以下、申請致します。\n\n”;
body+=”【依頼日時】”+timeStamp+”\n”;
body+=”【申請ID】”+s_id+”\n\n”;
body+=”【申請者コメント】”+com+”\n\n”;
body+=”【添付ファイル保存先】”+newfolderurl+”\n\n”;

body+=”【申請管理表】”+list+”\n\n”;
body+=”\n以上、宜しくお願い致します。”;

var title=”【申請承認依頼】申請ID:”+s_id+”(申請者名:”+name+”)”;
var cc = app_add + “,” + ali_add;

GmailApp.sendEmail(
sup_add,
title,
body,
{
from: ali_add,
cc: cc,
noReply: false,
replyTo: app_add
}
);

writesheet(data,newfolderurl);

}

//スプレッドシート記載用の処理
function writesheet(data,newfolderurl) {

var timeStamp=data[0];
var name=data[1];
var com=data[2];
var file=data[3];
var app_add=data[4];

sh.insertRowAfter(last_row);

sh.getRange(last_row+1, 1).setValue(s_id);
sh.getRange(last_row+1, 2).setValue(timeStamp);
sh.getRange(last_row+1, 3).setValue(name);
sh.getRange(last_row+1, 4).setValue(app_add);
sh.getRange(last_row+1, 5).setValue(com);
sh.getRange(last_row+1, 6).setValue(newfolderurl);

var sheetId = sh.getSheetId();
var resource1 = {“requests”: [
{
“repeatCell”: {
“cell”: {“dataValidation”: {“condition”: {“type”: “BOOLEAN”}}},
“range”: {“sheetId”: sheetId, “startRowIndex”:last_row, “endRowIndex”: last_row+1, “startColumnIndex”: 6, “endColumnIndex”: 7},
“fields”: “dataValidation”,
},
},
]};

Sheets.Spreadsheets.batchUpdate(resource1, SP_ID);

var resource2 = {“requests”: [
{
“repeatCell”: {
“cell”: {“dataValidation”: {“condition”: {“type”: “BOOLEAN”}}},
“range”: {“sheetId”: sheetId, “startRowIndex”:last_row, “endRowIndex”: last_row+1, “startColumnIndex”: 7, “endColumnIndex”: 8},
“fields”: “dataValidation”,
},
},
]};

Sheets.Spreadsheets.batchUpdate(resource2, SP_ID);

}

function checkbox(){

var Ash = SpreadsheetApp.getActiveSheet();
var rng = Ash.getActiveCell();
var row_num = rng.getRow();
var SHEET_NAME = Ash.getName();
var flag = rng.getValue();

if(SHEET_NAME == “申請依頼管理シート” && rng.getColumn() == 7 && flag == true){

var id = Ash.getRange(row_num, 1).getValue();

if(id == ”){

Browser.msgBox(“申請管理IDが空欄です”);
rng.setValue(“false”);

} else {

var yourSelections = Browser.msgBox(id + ” の承認メールを送信しますか?”,
Browser.Buttons.OK_CANCEL);

if(yourSelections == “ok” ){

var name = Ash.getRange(row_num, 3).getValue();
var app_add = Ash.getRange(row_num, 4).getValue();
var file = Ash.getRange(row_num, 6).getValue();

var title=”【承認のご連絡】” + id;

var body=”ご担当者様\n\n”;
body+=”お疲れ様です。\n\n”;
body+=”承認しましたので、何卒宜しくお願い致します。”;

GmailApp.sendEmail(
work_add,
title,
body,
{
from: ali_add,
cc: ali_add,
noReply: false,
replyTo: sup_add
}
);

Browser.msgBox(id + ” の承認完了メールを送信しました”);

} else if ( yourSelections == “cancel” ){

rng.setValue(“false”);
Browser.msgBox(id + ” の承認完了メール送信を中止しました”);

}
}

} else if ( SHEET_NAME == “申請依頼管理シート” && rng.getColumn() == 7 && flag == false) {

Browser.msgBox(“承認済です”);
rng.setValue(“true”);

}

if(SHEET_NAME == “申請依頼管理シート” && rng.getColumn() == 8 && flag == true){

var id = Ash.getRange(row_num, 1).getValue();

if(id == ”){

Browser.msgBox(“申請管理IDが空欄です”);
rng.setValue(“false”);

} else {

var yourSelections = Browser.msgBox(id + ” の作業完了メールを送信しますか?”,
Browser.Buttons.OK_CANCEL);

if(yourSelections == “ok” ){

var name = Ash.getRange(row_num, 3).getValue();
var app_add = Ash.getRange(row_num, 4).getValue();
var file = Ash.getRange(row_num, 6).getValue();

var title=”【作業完了のご連絡】” + id;

var body=”ご担当者様\n\n”;
body+=”お疲れ様です。\n\n”;
body+=”作業完了しましたので、何卒宜しくお願い致します。”;

GmailApp.sendEmail(
app_add,
title,
body,
{
from: ali_add,
cc: ali_add,
noReply: false,
replyTo: work_add
}
);

Browser.msgBox(id + ” の作業完了メールを送信しました”);

} else if ( yourSelections == “cancel” ){

rng.setValue(“false”);
Browser.msgBox(id + ” の作業完了メール送信を中止しました”);

}
}

} else if ( SHEET_NAME == “申請依頼管理シート” && rng.getColumn() == 8 && flag == false) {

Browser.msgBox(“作業完了報告済です”);
}
}

設定するもの

チェックボックス生成の準備(Google Sheets API)

次に、スプレッドシート上にチェックボックスを生成する準備をします。

まずスクリプトエディタを開き、エディタ上部の

「リソース」⇒「Googleの拡張サービス」

より

Google Sheets API

をオンにします。

さらにそのまま

Google Cloud Platform API ダッシュボード

のリンクをクリックします。

開いた画面上部の検索窓より、

Google Sheets API

を検索しクリックすると画面が変わりますので、

有効にする

をクリックします。

トリガーの設定

今回設定するトリガーは2つです。

①実行する関数「getformdata」
 ⇒イベントのソース「スプレッドシートから」
 ⇒イベントの種類「フォーム送信時」

②実行する関数「checkbox」
 ⇒イベントのソース「スプレッドシートから」
 ⇒イベントの種類「編集時」

注意点、問題点

ここからは、今回のスクリプトに関する注意点についてご紹介します。

from(送信元)のメールアドレス設定

この申請フォームの問題点の1つに、

GmailApp.sendEmailのfrom(送信元)アドレスは、

GASオーナーのアドレス

か、

GASオーナーがエイリアス設定したメールアドレスしか設定できない

という点があります。

※Gmailヘルプ「別のアドレスやエイリアスからメールを送信する

セキュリティ上、他者のGoogleアカウントのメールアドレスをfromに設定する事はできないようです。

(もしかしたら他の方法があるかもしれませんが・・・)

これをなんとかしないと、

申請時のfromアドレスは常にGASオーナーのメールアドレス

という事態になってしまいます。

対策方法としては、

・特定の個人に紐づいていない、マスターアカウント的な共有のGoogleアカウントを作成し、GASオーナーに設定する(社内セキュリティ的に問題なければ)

・GASオーナーのGoogleアカウントに、メーリングリストをエイリアス設定し、メーリングリストから申請依頼をメール送信できるようにする

などが考えられるかと思います。

また、

メールの返信先を指定したい場合には、GmailApp.sendEmailのreplyToに指定したいメールアドレスを設定

します。

添付ファイルの末尾にGASオーナーの名前(アカウント名)が表示される?

Googleフォームからフォルダを作成して保存するファイルですが、ファイル名の末尾にGASオーナーの名前(アカウント名)が付いてしまうようです。

一応、置換などを駆使して消すことは可能なのですが、予期せぬファイル名に出くわした時などに、ファイル名が変になってしまう事があります。

(身をもって経験済みです・・・)

ですので、今回のサンプルスクリプトでは保存するファイルの名前はいじらない事にしています。

GASオーナーの送信済メールにメールが溜まる

GASオーナーのGoogleアカウントにメーリングリストをエイリアス設定した場合、当然ですが他人が送信した申請フォームからの送信メールもGASオーナーのところに溜まり続けます・・・

サンプルのスクリプトについて

先ほどご紹介したサンプル1についてですが、今回は下記5つのスクリプト関数に分けて作成しました。

・Googleフォームのデータを取得すると共に、起点となるスクリプト関数「getformdata」

・フォルダを作成し、ファイルを保存するための「createfile」

・申請メールを送信するための「sendmail」

・スプレッドシートに記載するための「writesheet」

・チェックボックス操作時の処理を決める「checkbox」

それぞれのスクリプト関数についてここでご紹介すると、非常に長くなってしまいますので、各処理の詳細については下記の記事などをご覧いただけますと幸いです。

システムを作る際、エラーの度に動かなくなった挙句、必要以上に原因の究明に時間がかかってしまっては非常に問題です。 そこで今回はGoog...
今回は、Googleフォームの回答内容を、好みの形でスプレッドシートに集計する方法をご紹介します。 スプレッドシートからGoogleフォー...
今回は、Googleフォームの複数の添付ファイルを、Googleドライブ上の任意の場所にフォルダを自動作成して保存する方法についてご紹介しま...
今回は、Google Apps Script(GAS)を使ってGoogleフォーム回答者のGメールアドレスを取得し、スプレッドシートに反映す...
今回は、Google Apps Script(GAS)のスクリプト関数から別のスクリプト関数を呼び出し、同時に変数も受け渡していく方法につい...
Google Apps ScriptではGmailApp.sendEmailを使ってGメールを送信する事ができます。 このスクリプトには様...
今回は、Google Apps Script(GAS)を使ったチェックボックスの判定方法や、チェックボックスを元のしたスクリプトの実行方法に...

グローバル領域

また、以前の記事では触れていなかった点について補足をしますと、冒頭のグローバル領域に記述した変数、

var SP_ID = “[スプレッドシートのIDを記載]”;
var SH_NAME = “申請依頼管理シート”;
var sh = SpreadsheetApp.openById(SP_ID).getSheetByName(SH_NAME);
var last_row = sh.getLastRow();
var s_id = “S” + (100000 + last_row);
var sup_add = “[上長のアドレスを記載]” 
var man_add = “[フォーム管理者のアドレスを記載]”
var work_add = “[作業者のアドレスを記載]”
var ali_add = “[GASオーナーがエイリアス設定するアドレスを記載]”
var list = “[スプレッドシートの共有URLを記載]”;

は、全てのスクリプト関数で使用する事のできる変数です。

これらの変数は全てのスクリプト関数で共通して使用するため、今回はグローバル領域に記述してみました。

チェックボックスの生成

また、スクリプト関数「writesheet」内の下記スクリプトですが、

var sheetId = sh.getSheetId();
var resource1 = {“requests”: [
{
“repeatCell”: {
“cell”: {“dataValidation”: {“condition”: {“type”: “BOOLEAN”}}},
“range”: {“sheetId”: sheetId, “startRowIndex”:last_row, “endRowIndex”: last_row+1, “startColumnIndex”: 6, “endColumnIndex”: 7},
“fields”: “dataValidation”,
},
},
]};

Sheets.Spreadsheets.batchUpdate(resource1, SP_ID);

var resource2 = {“requests”: [
{
“repeatCell”: {
“cell”: {“dataValidation”: {“condition”: {“type”: “BOOLEAN”}}},
“range”: {“sheetId”: sheetId, “startRowIndex”:last_row, “endRowIndex”: last_row+1, “startColumnIndex”: 7, “endColumnIndex”: 8},
“fields”: “dataValidation”,
},
},
]};

Sheets.Spreadsheets.batchUpdate(resource2, SP_ID);

これは先ほどご紹介したGoogle Sheets APIにより、スプレッドシート上にチェックボックスを生成するためのスクリプトです。

フォーム送信後、スプレッドシートへの反映結果

下記は、フォームから送信後にスプレッドシートへ反映された結果となります。

チェックボックス操作時の処理

下記はチェックボックスにチェックを入れた時や、外したときにどうなるかです。

今回のスクリプトでは一度チェックを入れて承認、または作業完了報告した場合、チェックは外せないようになっています。

もし、これではガチガチ過ぎて困るという場合は、必要に応じて変更していただければと思います。

※「上長承認」のチェックボックスにチェックを入れた場合

※一度チェックを入れた「上長承認」のチェックボックスのチェックを外そうとした場合

最後に

今回はGoogle Apps Script(GAS)を使った申請フォーム(ワークフロー)の作成例についてご紹介してきました。

荒いところなどあるかと思いますが、何かのお役に立ちましたら幸いです。

スポンサーリンク
スポンサーリンク

シェアする

  • このエントリーをはてなブックマークに追加

フォローする