Google Apps Scriptで複数データを送信出来るフォームを作る【GAS】
Google Formは基本的に複数のデータを送信することは出来ません。ですので、例えば、旅行申込書というフォームを作ったとしても、送れるデータは１名分（1レコード）のみです。擬似的に複数の人間分テキストボックスを用意するということは可能と言えば可能ですが、非常に煩雑なものになり、また、それを分解して複数レコードとして送り出す為に、余計なコードを書かなければなりません。HTML Serviceを利用したフォームでもそのままでは同様の事が言えます。
しかし、HTML Servicesの場合、JavaScriptとライブラリを駆使して、複数レコードを送信するようなフォームを作る事が可能です。投稿するレコード数に制限をつけず、また画面遷移が出来ないHTML Servicesでは、ちょっとしたテクニックが必要になります。今回は、それに挑戦してみたいと思います。
※今回のフォームを超絶発展させたものが、備品貸出管理フォームのようなものになります。
図：lotnum単位で複数レコード同時登録します
リンク
目次
使用するメソッド・クラス、準備するもの
- 今回使用するスプレッドシート
- 今回使用するちょっと特別なGoogle Form
- ちょっと特別なGoogle Form用の書き込み用スプレッドシート
- HTML ServiceのcreateHtmlOutputFromFileメソッド
ソースコード
GAS側コード
ウェブアプリケーションとして作成するので、doGet()でHTMLを表示させる。また、送信ボタンを押した時のコードを用意しておく。基本的には、HTML側からのデータを受け取って、スプレッドシートに一括書き込みをするのが主な仕事です。
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('▶セットアップ')
.addItem('初期化', 'getMySheetId')
.addToUi();
}
//フォーム表示用
function doGet(){
var html = HtmlService.createHtmlOutputFromFile('workflow')
.setSandboxMode(HtmlService.SandboxMode.IFRAME)
.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
return html;
}
//自分自身のIDを取得するコード
function getMySheetId(){
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var myid = sheet.getId();
var Properties = PropertiesService.getScriptProperties();
Properties.setProperty("sheetid", myid);
return myid;
}
function telepon(senddata){
//書き込み用変数を準備
var Properties = PropertiesService.getScriptProperties();
var editsheet = Properties.getProperty("sheetid");
var uid = Properties.getProperty("uid"); //masteridとして使用するもの
var lotnum = Properties.getProperty("lotnum"); //Lot Numberとして使用するもの
var sheetman = SpreadsheetApp.openById(editsheet);
var ss = sheetman.getSheetByName("シート1");
var dataArray = []; //書き込み用のメインとなる配列
var tantou = senddata[0][1];
//書き込み用に配列の整備を行う
var rowlength = senddata.length;
var collength = senddata[0].length;
//ロットナンバーを生成する
lotnum = Number(lotnum) + 1;
Properties.setProperty("lotnum",lotnum);
//配列データを作成
for(var i = 0;i<rowlength;i++){
var testArray = [];
uid = Number(uid) + 1;
testArray.push(uid);
testArray.push(lotnum);
for(var j = 0;j<collength;j++){
testArray.push(senddata[i][j]);
}
//一時生成配列を書き込み用配列にpushする
dataArray.push(testArray);
}
//IDを書き戻す
Properties.setProperty("uid",uid);
//スプレッドシートの最終行以降に配列データを一気に書き込む
var endrow = Number(ss.getLastRow()) + 1;
var lastColumn = dataArray[0].length; //カラムの数を取得する
var lastRow = dataArray.length; //行の数を取得する
ss.getRange(endrow,1,lastRow,lastColumn).setValues(dataArray);
}
- ID列は単純に連番を取ってるレコードのIDです。
- 対して、lotnumとは、複数送られてくる塊に対してのIDをつけています。つまり同じIDならばそれらは１度に送信された塊と判別することが可能です。
- HTML側から送られてくる配列に、連番のuidとlotnumを加えた配列に作り変えてあげてます。
- 最後に、スプレッドシートの一番最後の行に、appendRowのように一気に書き込みをしています。
- このコードだけですと、複数名同時にアクセスされた場合、連番がおかしくなったり、書き込みがバッティングする可能性があるので、排他制御も同時に扱うようにすると良いでしょう。
HTML側コード
今回は、テーブルレイアウトの為に色々なCSSを組み込んでいます。主にUIを作るに当って、以下のような仕組みを施してあります。
- 入力画面そのものは、jQuery Dialogを使用しています。
- 一覧表示部分はテーブルとし、そこに新規レコードが挿入されていきます。
- 一定の数以上になると自動でスクロールバーが出て、テーブル内でスクロールするようにしています。
- データの更新時のダイアログだけ、削除ボタンが表示されるようになっています。
- ダイアログ画面を綺麗に整形する為にCSS側で動的生成されたjQuery UIのコンポーネント用のCSSを設定しています。
<head>
<link rel="stylesheet" href="https://officeforest.org/wp/library/iyakuhin/iyakuhin.css">
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
<link rel="stylesheet" href="https://code.jquery.com/ui/1.11.4/themes/cupertino/jquery-ui.css" />
<script src="https://code.jquery.com/jquery-1.10.2.js"></script>
<script src="https://code.jquery.com/ui/1.11.2/jquery-ui.js"></script>
<style>
.ponyo{
height: 31px;
padding-bottom: 12px;
vertical-align: middle;
}
</style>
<script>
//ダイアログ表示用
$(function() {
$( "#dialog" ).dialog({
autoOpen: false,
closeText: "保存せずに閉じます",
width: 750,
height: 450,
title: "作業内容",
modal: true,
show: {
effect: "clip",
duration: 500
},
hide: {
effect: "clip",
duration: 500
}
});
});
$(function() {
$( "input[type=submit], a, button" )
.button()
.click(function() {
disp();
});
});
$(".ponyo").css({ width: '300px', 'padding-top': '10px', 'padding-bottom': '10px' });
//グローバル変数
var seibun;
var yakuzai;
var addeditflag;
var masterman = []; //データ格納用の配列
var choiceid;
var getrec = "";
var spreadman;
var uid = 0;
var insflag;
//入力ダイアログ表示用
//flagが0の時は新規追加、1の時は編集。編集時のみidの値を使用する
function makedown(flag,id){
//変数の宣言
var titleman;
var dlength = masterman.length;
insflag = flag;
getrec = "";
choiceid = "";
//入力すべき項目が空の場合キャンセルさせる
var inputget = document.getElementById("wasabi1").value;
if(inputget == ""){
alert("担当者名が空っぽですよ。");
document.getElementById("wasabi1").focus();
return;
}
//flag別の処理
if(flag == 0){
titleman = "廃棄情報の新規追加";
//削除ボタンの非表示
document.getElementById("button2").style.visibility="hidden";
}else{
//該当のレコードを配列から探し出す
for(var i = 0;i<dlength;i++){
if(id == masterman[i][0]){
getrec = i;
choiceid = id;
break;
}
}
//タイトルの変更
titleman = "編集中...";
//各項目に値をロードする
$('#wasabi2').val(masterman[getrec][2]);
$('#wasabi3').val(masterman[getrec][3]);
$('#wasabi4').val(masterman[getrec][4]);
//削除ボタンを表示する
document.getElementById("button2").style.visibility="visible";
//削除ボタンにイベントハンドラーを付け加える（引数はiとid）
document.getElementById("button2").setAttribute("onClick","deleterec(" + i + ")");
}
//ダイアログ表示
$('#dialog').dialog({
title: titleman,
close : function(){
//閉じた時の動作を指定
dialogclear();
}
});
$( "#dialog" ).dialog( "open" );
$( "#dialog" ).dialog("moveToTop");
}
//ダイアログの中身を初期化する
function dialogclear(){
$('#wasabi2').val("");
$('#wasabi3').val(0);
$('#wasabi4').val(0);
}
//日付を整形して返す関数
function henDate(dateman){
var date = new Date(dateman);
var year = date.getFullYear();
var month = date.getMonth() + 1;
var date = date.getDate();
if (month < 10) {
month = "0" + month;
}
if (date < 10) {
date = "0" + date;
}
var strDate = year + "/" + month + "/" + date;
return strDate;
}
//ダイアログのデータを集めて、配列に格納する関数
function getdialog(id){
var tempArray = [];
var validata = "";
//IDをpushする
tempArray.push(id);
//フォームデータを収集する
validata = document.getElementById("wasabi1").value
tempArray.push(validata);
validata = document.getElementById("wasabi2").value
tempArray.push(validata);
validata = document.getElementById("wasabi3").value
tempArray.push(validata);
validata = document.getElementById("wasabi4").value
tempArray.push(validata);
return tempArray;
}
//tableからレコードを削除する関数
function deleterec(index){
//tableを取得する
var table = document.getElementById("tableman");
//削除ダイアログを出して処理を行う
if(window.confirm("この登録情報を削除しますか？")){
//配列からデータを削除(http://www.gesource.jp/weblog/?p=4112)
masterman.splice(index, 1);
//テーブルからtrを削除（http://scrap.php.xdomain.jp/javascript_table_control/）
var rows = table.deleteRow(index);
//ダイアログの中身をクリアしてダイアログを閉じる
dialogclear();
$( "#dialog" ).dialog( "close" );
}else{
window.alert('削除はキャンセルされました');
}
}
//tableにレコードを追加する関数
function insertrec(dataarray){
//フラグによって処理を分岐する
//tableを取得する
var table = document.getElementById("tableman");
if(insflag == 0){
//新規作成データの追加処理
//ダイアログデータを取得する
uid = Number(uid) + 1;
var setid = uid;
var dialogdata = getdialog(setid);
//テーブル用のデータをdialogdataから取得する
var temptd = [];
temptd.push(dialogdata[2]);
temptd.push(dialogdata[3]);
temptd.push(dialogdata[4]);
// -1で末尾に追加。
var rows = table.insertRow(-1);
//trにid,class,onclickを付け加える
rows.setAttribute("id",setid);
rows.setAttribute("class","sinsei");
rows.setAttribute("onClick","makedown(1, this.id)");
//tdセルを追加し値を追加する
for(var i = 0;i<3;i++){
var cell = rows.insertCell(-1);
cell.setAttribute("class","hoge");
cell.innerHTML = temptd[i];
}
//配列にデータを追加する
masterman.push(dialogdata);
}else{
//既存のデータを上書き処理（配列修正とtd修正）
//ダイアログデータを取得する
var dialogdata = getdialog(choiceid);
//テーブルデータを書き換える
var targetrow = table.rows[getrec];
targetrow.cells[0].innerText = dialogdata[2];
targetrow.cells[1].innerText = dialogdata[3];
targetrow.cells[2].innerText = dialogdata[4];
//配列データを書き換える(http://www.openspc2.org/reibun/javascript/table/008/)
masterman[getrec][2] = dialogdata[2];
masterman[getrec][3] = dialogdata[3];
masterman[getrec][4] = dialogdata[4];
}
//ダイアログの中身をクリアしてメッセージ表示
dialogclear();
$( "#dialog" ).dialog( "close" );
}
//フォームデータをスプレッドシート側に送信する（http://d.hatena.ne.jp/chiheisen/20090803/1249315452）
function dispsend(){
//配列データが空の場合警告を出す
var dlength = masterman.length;
if(dlength == 0){
alert("送信するデータが空ですよ。");
return;
}
if(window.confirm("廃棄登録情報を送信しますか？")){
//送信後処理
window.alert('送信されました。');
document.getElementById("mainform").innerHTML = "<p><b><div style='color:red; font-size:16pt;'>送信完了．．．。</div></b>";
document.getElementById("karakasa").focus();
//ホスト側に配列データを渡す
google.script.run.telepon(masterman);
}else{
window.alert('データの送信はキャンセルされました。');
}
}
</script>
</head>
<body>
<!-- 基本情報入力部分 -->
<div id="karakasa"></div>
<div id="mainform">
<div><label><b>担当者名</b></label></div>
<INPUT type='text' class='wasabi' id='wasabi1' placeholder='あなたの氏名を入力。' size='20' title='今このフォームを入力してる人の名前'><p><p>
<div class="y_data_area">
<table class="y_data_title">
<col style="width: 50%;" />
<col style="width: 25%;" />
<col style="width: 25%;" />
<tr>
<th>品名</th>
<th>数量</th>
<th>金額</th>
</tr>
</table>
<div class="y_scroll_box">
<div class="y_hidden">
<table class='y_data' id="tableman">
<col style='width: 50%;' />
<col style='width: 25%;' />
<col style='width: 25%;' />
</table>
</div>
</div>
</div>
<!-- 送信ボタン -->
<div style="text-align: right;width:1000px">
<p><button onClick='makedown(0)' style="font-size: 14px;vertical-align: middle" class="ponyo" title='登録対象を追加します。'><img src='https://officeforest.org/wp/library/icons/add2.png' /> 追加</button>
<button onClick='dispsend()' style="font-size: 14px;vertical-align: middle" class="ponyo" title='複数項目を送信します。'><img src='https://officeforest.org/wp/library/icons/submit.png' /> フォームを送信</button></p>
</div>
<!-- ダイアログ用 -->
<div id="dialog" title="Basic dialog">
<!-- ヘッダ部分を作成 -->
<div><label><b>品名</b></label></div>
<INPUT type='text' class='yaku' id='wasabi2' placeholder='商品の名称を入力。' size='70'><p>
<hr>
<!-- メイン部分を作成 -->
<!-- 左サイド -->
<table class="raspberry3" width='100%'><tbody>
<tr>
<td width="10%" style="text-align:right">
<label><b>数量：</b></label>
</td><td width="50%">
<input type="text" value=0 class="kinoko" id="wasabi3" style="width: 50px">
</td><td width="10%" style="text-align:right">
<label><b>金額：</b></label>
</td><td width="30%" >
<input type="text" value=0 class="kinoko" id="wasabi4" style="width: 50px">
</td>
</tr>
</tbody></table>
<hr>
<div class='boxContainer'>
<div class='box'><span><button style="visibility: hidden;" id="button2" style="font-size: 14px;vertical-align: middle" class="ponyo" title='この登録を削除します。'><img src='https://officeforest.org/wp/library/icons/cross.png' /> 削除</button></span></div>
<div class='box2'>
<span><button onClick='insertrec()' style="visibility: visible;" id="button1" style="font-size: 14px;vertical-align: middle" class="ponyo" title='この内容で登録します。'><img src='https://officeforest.org/wp/library/icons/icon_check2.png' /> 確定</button></span>
</div>
</div>
</div>
</div>
</body>
- 今回は、グリッドについては特別なライブラリを使用せずに、tableタグのみで実現しています（jQueryだけは使ってます）。
- テーブルタグではありますが、ある程度の件数が追加されるとスクロールバーが出るようになっています。
- 追加でデータを追加し、送信をするとスプレッドシートに複数レコードが一気に書き込まれます。
- 複数データをもとに、例えば請求書のような複数レコードを要する書類を生成し、PDF化して送ることも可能です。
- この手のフォームの全ての原型になるので、身に付けるとGASでフォームアプリケーションの幅が一気に広がりますよ。
実行結果
今回のアプリケーションは、Google Formではなくウェブアプリケーションとしてのフォームなので、最後に以下の手順で公開作業が必要です。
- スクリプトエディタの「公開」⇒「ウェブアプリケーションとして公開」をクリック
- プロジェクトバージョンはNewとする
- 次のユーザとしてアプリケーションを実行で、自分とすると、他人であっても自分の権限で書き込まれるので、スプレッドシートを自分のみにしても、書き込みが可能な状態にすることが出来ます。
- アプリケーションにアクセスできるユーザは基本は自分のドメイン内に限定しましょう。
図：こんな形で複数のレコードを送り込めます
図：登録用のダイアログが必要です。
ポイント
- GAS側で書き込む処理はそれほど複雑ではありません。HTML側から配列の塊を受け取ったら、ちょっと加工してスプレッドシートの最終行にappendRowするかのように、改めて作成した配列データをドカっと書き込むといった具合です。
- HTML側は少々複雑で、主な仕組みは空のテーブルと空の配列を用意し、データの追加削除をそれぞれに対して同時に行っています。データの書き込み時はその時点での配列データをGAS側に渡しています。
- データ修正時は、trタグに設定してあるIDを元に配列データからダイアログ側に値を抽出して復元しています。また、データ削除はtrまるごと削除と配列からspliceさせています。
- insertrecとdeleterec、およびdialogclear、getdialogのそれぞれのメソッドが実作業を行う担当関数です。データの挿入・削除・終了時にダイアログのコントロール値をクリアする、ダイアログの値を収集するといった作業を行っています。
- makedown関数に渡す引数によって、それが修正時なのか新規追加時なのかを判定させています。
- jQuery UIのCSS等はそのままだと馬鹿でかい文字とかボタンの大きさになるので、それをスマートにするために、自分が用意したCSS内でコントロールのstyleを再定義させています。このへんは好みですね。
実行結果
動画：実際に複数データを送ってみた
Google Formで擬似的に複数レコード挿入
概要
メールで質問をいただきまして、Google Formでも擬似的に特定の条件の時に複数レコードを作れるのではないだろうか？ということで作ってみました。アルバイトのシフト表を登録するGoogle Formです。なんとか実現できないかなぁと思いついた手法がこれでした。利用シーンは限られるとは思いますが、こういった形であれば、複数データの擬似的な送信が実現します。
登録を行うと、1回の送信でスプレッドシートに複数登録され、その登録データを元にQuery関数でシフト表を組み上げるものとなっています。ただし、いくつかこれを実現する為に、トリッキーなテクニックを使っています。
- Query関数でのPivotではカラム名でのソートができないので、数字+文字列だとオカシナ並び順になる。これを防ぐ為に1桁の場合には頭に0をつけてあげるようにした。（1ならば01となるようにする）。
- Pivotの集計はA列のカウントにしておいた。本来は労働時間などを入れて集計が良いのかもしれない。
- 複数登録の為に出勤可否の項目は「選択式（グリッド）」を使っています。これが複数個登録される基準になります。
- 出勤の時だけ配列に加えて書き込み対象にしています。
- グリッドの選択肢を増やしたり、チェックボックスを使ったり。グリッドの場合ならば、その回答内容で条件分岐させて、時間を計算して値を配列に入れるといったような工夫ができれば、4、５個あっても対応可能。
図：こんな感じのグリッドパーツを使います。
ソースコード
//書き込み先スプレッドシートのID
var sheetid = "ここに書き込み先スプレッドシートのIDを入れる";
//自動応答メールを送信する
function sendForms(e) {
//プロパティから値を取得する
var prop = PropertiesService.getScriptProperties();
//日付用カウンタ
var daycnt = 1;
//ID連番用カウンタ
var uid = prop.getProperty("uid");
//メール送信設定
var dataArray = []; //申請書書き込み用配列
try{
//送信データを取得する
var formdata = e.response.getItemResponses();
//入力項目用の変数
var username; //氏名を受け取る
var busyo; //部署名を受け取る
var workman; //出勤データを配列で受け取る
//入力項目を受け取る
for (var j = 0; j < formdata.length; j++){
//氏名を受け取る
if(formdata[j].getItem().getTitle() == "氏名" && formdata[j].getResponse() != ""){
username = formdata[j].getResponse();
}
//部署名を受け取る
if(formdata[j].getItem().getTitle() == "部署名" && formdata[j].getResponse() != ""){
busyo = formdata[j].getResponse();
}
//出勤データを受け取る
if(formdata[j].getItem().getTitle() == "出勤可否" && formdata[j].getResponse() != ""){
workman = formdata[j].getResponse();
}
}
//workmanのデータを元に書き込み用配列を作る
var array = [];
var tempid = Number(uid);
for(var i =0;i<workman.length;i++){
//一時配列を用意する
var temparray = [];
//IDをpushする
temparray.push(tempid);
//氏名をpushする
temparray.push(username);
//部署名をpushする
temparray.push(busyo);
//出勤データをpushする
if(workman[i] == "出勤"){
//出勤日を追加（ソートするためにちょっと改造）
if(daycnt <10){
temparray.push("0" + daycnt + "日");
}else{
temparray.push(daycnt + "日");
}
//uidを回す
tempid = tempid + 1;
//書き込み用配列にpushする
array.push(temparray);
}
//カウンタを回す
daycnt = daycnt + Number(1);
}
//IDをプロパティに書き戻す
uid = tempid;
prop.setProperty("uid", uid);
//書き込み用配列データをスプレッドシートに書き込む
var ss = SpreadsheetApp.openById(sheetid);
var sheet = ss.getSheetByName("シフト表");
var endrow = sheet.getLastRow() + 1; //現在のシートの最終行+1の値を取得
var lastColumn = array[0].length; //カラムの数を取得する
var lastRow = array.length; //行の数を取得する
sheet.getRange(endrow,1,lastRow,lastColumn).setValues(array);
}catch(e){
Logger.log(e.message);
}
}
- 同時に登録がないとは思いますが、万が一もあるので排他処理を入れておくと良いでしょう（複数データ登録があるので）
- 選択式グリッドの値はまとめて配列で送られてきます。[出勤,出勤,休暇,出勤....]といった具合に。
- フォームの各種値、uid生成、選択式グリッドの日付と一致するように生成するdaycntを用意して、書き込み用配列を用意する。
- データの書き込みはendrowを調べてまとめてガッツリ書き込みます。
使い方
フォームのソースコード内に書き込み先スプレッドシートのIDを入れてあげます。また、Formの送信時トリガーにシートへの書き込みをしてるsendFormsを登録してあげます。Form自身のスプレッドシート書き込み機能は利用しません。
これで送信を行うと、スプレッドシートに1回の送信で複数登録され、それを元にクロス集計シートでquery関数にてPivot化される仕組みになっています。
※忘れがちですが、Formのスクリプトエディタに入って、1度sendformsを実行して認証をしないと、スクリプトは動かないです。地味なハマりポイントです。
図：トリガーを設置する必要がある
図：1回の送信で複数登録される
図：Query関数のPivot化でシフト表が完成する
