#前提
シート名「Origin」が以下のような設定になっているものとします。
【変更前のシート（A1セル〜C3セル）】
| / | A | B | C | D |
|---|---|---|---|---|
| 1 | 項目 | 課題１ | 項目 | 課題2 |
| 2 | 4/1までの成果 | 4/1までの成果 | ||
| 3 | 4/8までの目標 | 4/8までの目標 | ||
| 4 | 4/8までの成果 | 4/8までの成果 | ||
| 5 | 4/15までの目標 | 4/15までの目標 | ||
| 6 | 4/15までの成果 | 4/15までの成果 | ||
| 7 | 4/22までの目標 | 4/22までの目標 |
B列、D列が報告記載列です。
「スプレッドシートを直接操作させたくない」「Webフォームから入力したい」という要望があったものとします。
#結論
doGet()関数でフォーム画面を呼び出しつつ、スプレッドシートから取得したデータをHTML上に展開していきます。
Webフォームの「送信」ボタンがクリックされたらJavascriptで「regData」関数を呼び出し、元のスプレッドシートにデータを上書き保存するというものです。
#動作確認スクリプト
##1. HTMLコンテンツをデータに変換するための関数
sample-code1.gs
function doGet(e) {
//HTMLテンプレートファイルを取得
var html = HtmlService.createTemplateFromFile('form');
//変数idにて対象スプレッドシートを取得
var SS = SpreadsheetApp.openById(id);
var Sheets = SS.getSheets();
var Sheet = "";
//アクセスしたユーザのEmailアドレスがシート名になっているシートを取得
for(i = 0; i < Sheets.length; i++){
if(inputUser == Sheets[i].getSheetName()){
Sheet = Sheets[i];
break;
}
}
//2回目以降のアクセスならシートから値を取得
if(Sheet)
var data = Sheet.getDataRange().getValues();
else{
//1回目ならシート名「Origin」をコピー、シート名変更およびフォーム入力項目を挿入
var source = SS.getSheetByName("Origin");
var Sheet = source.copyTo(SS).setName(inputUser);
}
html.data = data;
try{
return html.evaluate().setTitle("週報提出").setSandboxMode(HtmlService.SandboxMode.NATIVE);
}
catch(e){
errMailSend(e,"doGet");
}
}
###メール本文に使うHTMLテンプレート
form.html
<head>
</head>
<div class='header' id='header'>週報提出</div>
<form id='formstyle' name='applyform' method='post' enctype="multipart/form-data">
<table>
<thead>
<tr>
<th><label><?= data[0][0]?></label></th>
<th><label><?= data[0][1]?></label></th>
<th><label><?= data[0][2]?></label></th>
<th><label><?= data[0][3]?></label></th>
</tr>
</thead>
<tbody>
<? for(var i = 1;i < data.length; i++){ ?>
<tr>
<th align = 'left' scope="row"><label><?= data[i][0]?></label></th>
<td><textarea cols="25" rows='4' name='<?= i ?>_1' id='<?= i ?>_1'><?= data[i][1] ?></textarea></td>
<th align = 'left' scope="row"><label><?= data[i][2]?></label></th>
<td><textarea cols="25" rows='4' name='<?= i ?>_3' id='<?= i ?>_3'><?= data[i][3] ?></textarea></td>
</tr>
<? } ?>
</tbody>
</table>
<input type='button' id='sbmt0' name='sbmt0' value='更新' onClick='return confirmSubmit(this.parentNode)'>
</form>
<div class='inProgress' id="inProgress" style="display: none;">
<!-- Progress starts hidden, but will be shown after form submission. -->
処理中・・・・
</div>
<div id="result" class="center">
</div>
<?!= include('Javascript'); ?>
###form.html中で利用するJavascript
Javascript.html
<script>
//送信ボタンクリック後、内容確認ポップアップ表示用ファンクション//
function confirmSubmit(formValues){
document.getElementById("result").innerHTML = "";
var str = "";
var submit = confirm("＋＋＋＋＋情報を送信します＋＋＋＋＋\n");
if(submit){
//「処理中」を表示する
toggle_visibility('inProgress','block');
//code.gs内のregData関数へデータを送信する
google.script.run.withSuccessHandler(outputInfo).withFailureHandler(outputFail).regData(formValues);
}
else
outputFail("＋＋＋＋＋送信をキャンセルしました＋＋＋＋＋\n");
}
// ****************************************
function outputInfo(value){//操作完了ポップアップを表示する
document.getElementById("result").innerHTML = "";
toggle_visibility('inProgress','none');
// alert(value);
if(value){
var span = document.createElement('span');
span.innerHTML = [value].join('');
document.getElementById("result").insertBefore(span,null);
document.getElementById('sbmt').disabled = true;
}
else{
var span = document.createElement('span');
span.innerHTML = ["＋＋＋＋＋処理は正常に完了しました＋＋＋＋＋<br>\n（このまま連続して登録頂くことも可能です）"].join('');
document.getElementById("result").insertBefore(span,null);
}
}
function outputFail(value){//操作エラーポップアップを表示する
var span = document.createElement('span');
span.innerHTML = [value].join('');
document.getElementById("result").insertBefore(span,null);
}
function toggle_visibility(id,value) {//form.html上の要素を出したり消したりする
var e = document.getElementById(id);
e.style.display = value;
}
</script>
##2. フォームから送信ボタンが押された時に実行する関数
sample-code2.gs
function regData(formValues){
//格納先スプレッドシートを取得
var SS = SpreadsheetApp.openById(id);
var Sheet = SS.getSheetByName(inputUser);
var Range = Sheet.getRange(DataRange);//DataRangeはデータを格納する範囲（例えば"A1:D7"など）
var data = Range.getValues(); //現在のシート内容を二次元配列dataへ格納
var str_1,str_3;
Logger.log("formValues is \n%s",formValues);
//data（＝各ユーザーシート）の2行目（i=1）から先の2・4列目にformValuesデータを挿入
for(var i = 1; i < data.length; i++){
//form側から受け取ったデータの中で、idが「i（＝行数）_1」のデータを配列data[i][1]へ挿入
str_1 = i + "_1";
data[i][1] = formValues[str_1];
//form側から受け取ったデータの中で、idが「i（＝行数）_3」のデータを配列data[i][３]へ挿入
str_3 = i + "_3";
data[i][3] = formValues[str_3];
}
//スプレッドシートへデータをセットする（何が変わったのかは検知せず、端から端まで上書きする）
Range.setValues(data);
//変更をメール通知する
var Title = "【週報】変更が反映されました";
var Message =
"（本メールはシステムにより自動送信されています）\n\n"
+ "いつもお世話になっております。\n"
+ "週報の更新を検知・完了致しました。\n"
+ "<https://〜〜〜>\n\n"
+ "次回更新や内容修正の際は上記URLよりアクセスし「更新」ボタンをクリックしてください。\n"
+ "何卒宜しくお願い致します。\n";
GmailApp.sendEmail(inputUser, Title, Message);
}