Googleスプレッドシートでお問い合わせ

らら
らら

はじめに

Google フォームとかでも可能だけど、オリジナルデザインにしたい場合

htmlだったり、CSSだったりをオリジナルでつくらないとだめだったり・・

あと、GAS（Google Apps Script）で外部コンテンツ取得先のURLを制限できる機能ができたみたいなので・・Allowlist

つくってみる

Googleスプレッドシートへログインして

空白のスプレッドシートを作る・・

Googleスプレッドシート

登録するinput typeのnameの部分で見出しを登録する。（呼び出すHTML）を参照

先頭は、Timestamp

Googleスプレッドシート

GAS（Google Apps Script）を追加する。

Googleスプレッドシート

コードを追加したらデプロイする。

Googleスプレッドシート

アクセスできるユーザーは、全員で・・

Googleスプレッドシート

デプロイIDをコピーして覚えておく・・

Googleスプレッドシート

Apps Scriptでトリガーを追加する

Googleスプレッドシート

下記のように設定

Googleスプレッドシート

Apps Scriptのコード

下記のコードを参考に・・・

https://techjd.medium.com/how-to-create-a-fully-customizable-google-form-c18c1ead4147

変更した部分

テキストメールを追加

日本語見出しを追加　name=日本語でもいいけど動くか・・

ユーザー用にサンクスメール

ユーザーメールの時にメールアドレスチェック（呼び出し側のチェックと両方で・・）

注意事項　GASには制限があるので下記を読んでおいてから・・・

https://developers.google.com/apps-script/guides/services/quotas?hl=ja#current_limitations

GAS（Google Apps Script）経由でメールを送信する場合、送信元は、Googleでログインしているメールアドレスになります。

REPLY_ADDRESSで指定することで、返信した場合のメールアドレスは、設定できるようにしてあります。

Googleスプレッドシート本体をすべて公開にしないように注意しましょう・・

Apps Scriptで修正した場合、都度デプロイが必要です。


var TO_ADDRESS		= "@";		//管理者通知用
var ADMIN_SUBJECT	= "お問い合わせがありました。";
var ADMIN_NAME		= "サイト管理者";
var USER_SUBJECT	= "お問い合わせありがとうございます。";
var SHEET_NAME		= 'シート1';
var REPLY_ADDRESS	= "@";						//返信先のアドレスを指定
var MAILTEXTMODE	= 1;
function formatMailBody(obj, order,orderjp) {
	var fieldsFromForm	= getDataColumns(obj);
	var result = "";
	if(MAILTEXTMODE == 1) {
		result += "登録がありました。" + "\n";
		result += "Googleスプレッドシートで確認してください。" + "\n";
	} else {
		result += "<p>登録がありました。</p>" + "\n";
		result += "<p>Googleスプレッドシートで確認してください。</p>" + "\n";
	}
	if(!order) {
		order = Object.keys(obj);
	}
	if(orderjp) {
		for(var idx in order) {
			var key = order[idx];
			var formIndex = fieldsFromForm.indexOf(key);
			if(formIndex > -1) {
				if(MAILTEXTMODE == 1) {
					result +=  orderjp[key] + ":" + sanitizeInput(obj[key]) + "\n";
				} else {
					result += "<h4 style='text-transform: capitalize; margin-bottom: 0'>" + orderjp[key] + "</h4><div>" + sanitizeInput(obj[key]) + "</div>";
				}
			}
		}
		return result;
	}
	for(var idx in order) {
		var key = order[idx];
		var formIndex = fieldsFromForm.indexOf(key);
		if(formIndex > -1) {
			if(MAILTEXTMODE == 1) {
				result +=  orderjp[key] + ":" + sanitizeInput(obj[key]) + "\n";
			} else {
				result += "<h4 style='text-transform: capitalize; margin-bottom: 0'>" + key + "</h4><div>" + sanitizeInput(obj[key]) + "</div>";
			}
		}
	}
	return result;
}
function sanitizeInput(rawInput) {
	var placeholder = HtmlService.createHtmlOutput(" ");
	placeholder.appendUntrusted(rawInput);
	return placeholder.getContent();
}
function doPost(e) {
	try {
		Logger.log(e);
		var mailData = e.parameters;
		var orderParameter = e.parameters.formDataNameOrder;
		var dataOrder;
		if(orderParameter) {
			dataOrder = JSON.parse(orderParameter);
		}
		var orderParameterjp = e.parameters.formDataNameOrderJP;
		var dataOrderJP;
		if(orderParameterjp) {
			dataOrderJP = JSON.parse(orderParameterjp);
		}
		//input name= email固定
		//
		var UserdEmailTo = (e.parameters.email !== "undefined") ? e.parameters.email : "";
		if(UserdEmailTo) {
			var frommail = UserdEmailTo;
			var emailPattern = /^[a-zA-Z0-9._-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,4}$/;
			if(emailPattern.test(frommail) == false) {
				return ContentService.createTextOutput(JSON.stringify({"result":"error", "inputerrorr": "メールアドレス入力エラー"})).setMimeType(ContentService.MimeType.JSON);
			}
			if(MAILTEXTMODE == 1) {
				var user_msg = "";
				user_msg += "※このメールは自動応答によって送信されています。" + "\n";
				user_msg += "この度はお問い合わせいただき誠にありがとうございます。" + "\n";
				user_msg += "XX営業日以内に担当者より回答させていただきます。恐れ入りますが、回答までしばらくお待ちくださいませ。" + "\n";
				MailApp.sendEmail(String(UserdEmailTo), USER_SUBJECT, user_msg, {
					name: ADMIN_NAME,
					replyTo: REPLY_ADDRESS,
				});
			}
		}
		var sendEmailTo = (typeof TO_ADDRESS !== "undefined") ? TO_ADDRESS : "";
		if(sendEmailTo) {
			if(MAILTEXTMODE == 1) {
				MailApp.sendEmail(String(sendEmailTo), ADMIN_SUBJECT, formatMailBody(mailData, dataOrder,dataOrderJP), {
					name: ADMIN_NAME,
					replyTo: REPLY_ADDRESS,
				});
			} else {
				MailApp.sendEmail(String(sendEmailTo), ADMIN_SUBJECT, '', {
					htmlBody: formatMailBody(mailData, dataOrder,dataOrderJP),
					name: ADMIN_NAME,
					replyTo: REPLY_ADDRESS,
				});
			}
		}
		record_data(e);
		return ContentService.createTextOutput(
			JSON.stringify({"result":"success","data": JSON.stringify(e.parameters) })).setMimeType(ContentService.MimeType.JSON
		);
	} catch(error) {
		Logger.log(error);
		return ContentService.createTextOutput(JSON.stringify({"result":"error", "error": error})).setMimeType(ContentService.MimeType.JSON);
	}
}
function record_data(e) {
	var lock = LockService.getDocumentLock();
	lock.waitLock(30000);
	try {
		Logger.log(JSON.stringify(e)); 
		var doc				= SpreadsheetApp.getActiveSpreadsheet();
		var sheetName		= SHEET_NAME;
		var sheet			= doc.getSheetByName(sheetName);
		var oldHeader		= sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
		var newHeader		= oldHeader.slice();
		var fieldsFromForm	= getDataColumns(e.parameters);
		var row = [new Date()];
		for(var i = 1; i < oldHeader.length; i++) {
			var field	= oldHeader[i];
			var output	= getFieldFromData(field, e.parameters);
			row.push(output);
			var formIndex = fieldsFromForm.indexOf(field);
			if(formIndex > -1) {
				fieldsFromForm.splice(formIndex, 1);
			}
		}
		for(var i = 0; i < fieldsFromForm.length; i++) {
			var field = fieldsFromForm[i];
			var output = getFieldFromData(field, e.parameters);
			row.push(output);
			newHeader.push(field);
		}
		var nextRow = sheet.getLastRow() + 1;
		sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
		if(newHeader.length > oldHeader.length) {
			sheet.getRange(1, 1, 1, newHeader.length).setValues([newHeader]);
		}
	} catch(error) {
		Logger.log(error);
	}
	finally {
		lock.releaseLock();
		return;
	}
}
function getDataColumns(data) {
	return Object.keys(data).filter(function(column) {
		return !(column === 'formDataNameOrder' || column === 'formDataNameOrderJP');
	});
}
function getFieldFromData(field, data) {
	var values = data[field] || '';
	var output = values.join ? values.join(', ') : values;
	return output;
}

呼び出すHTML

jquery.validationEngine.jsとかでチェックは入れましょう・・

デプロイIDとvar headingは内容に合わせて変更します。

見出しの自動化については、htmlタグなど可変になる場合、checkboxがらみがあるので手動で・・、


<!DOCTYPE html>
<html lang="ja">
<head>
	<meta charset="utf-8">
	<title>jQuery Google spreadsheets form</title>
	<script src="https://code.jquery.com/jquery-3.4.1.js" integrity="sha256-WpOohJOqMqqyKL9FccASB9O0KwACQJpFTUBLTYOVvVU=" crossorigin="anonymous"></script>
</head>
<body>
	<main>
		<form id="html_form">
		<div>
			<label for="last_name">苗字</label>
			<input id="last_name" type="text" name="last_name" required>
		</div>
		<div>
			<label for="first_name">名前</label>
			<input id="first_name" type="text" name="first_name" required>
		</div>
		<div>
			<label for="tel">電話番号</label>
			<input id="tel" type="text" name="tel">
		</div>
		<div>
			<label for="email">メール</label>
			<input id="email" type="text" name="email" required>
		</div>
		<div>
			<label for="os">ご使用OS</label>
			<input type="checkbox" name="os" value="Windows10">Windows10
			<input type="checkbox" name="os" value="Mac">Mac
			<input type="checkbox" name="os" value="Linux">Linux
		</div>
		<div>
			<label for="gender">性別</label>
			<input type="radio" name="gender" value="男性">男性
			<input type="radio" name="gender" value="女性">女性
		</div>
		<div>
			<label for="message">お問い合わせ</label>
			<textarea id="message" name="message"></textarea>
		</div>
		<div>
			<button type="submit">送信</button>
		</div>
		</form>
	</main>
	<script>
		$("#html_form").submit(function(event) {
			event.preventDefault();
			var id = "デプロイID";
			var heading = {'last_name': '苗字','first_name':'名前','tel':'電話番号','email':'メール','message':'お問い合わせ','gender':'性別','os':'ご使用OS'};
			var headingorder = JSON.stringify(heading);
			headingorder =encodeURI(headingorder);
			$.ajax({
				type: "POST",
				url: "https://script.google.com/macros/s/"+ id +"/exec",
				data:  $("#html_form").serialize() + "&formDataNameOrderJP="+ headingorder,
				success: function (response) {
					console.log(response);
					if(response.result == "success"){
						alert("success");
					} else {
						if(response.result == "error"){
							alert(response.inputerrorr);
						} else {
							alert("error");
						}
					}
				},
				error: function (err) {
					console.log(err);
					alert("error");
				},
			});
		});
	</script>
</body>
</html>

入力→確認→完了ページ遷移版

一昔前のajaxフォームです。

デプロイIDとvar headingは内容に合わせて変更します。

見出しの自動化については、htmlタグなど可変になる場合、checkboxがらみがあるので手動で・・、

jquery.validationEngine.jsとかでチェックは入れましょう・・


<!doctype html>
<html lang="ja">
<head>
<meta charset="utf-8">
<title>お問合せフォーム</title>
<script src="https://code.jquery.com/jquery-3.4.1.js" integrity="sha256-WpOohJOqMqqyKL9FccASB9O0KwACQJpFTUBLTYOVvVU=" crossorigin="anonymous"></script>
<script>
$(function() {
	$(document).on('click','#jform_check',function(){
		$('[jform-control]').each(function(index, element) {
			var id			= $(this).attr("id");
			var finput		= $("#"+id);
			var input_type	= finput.prop("type")
			switch(input_type) {
				case 'radio':
					var val = $("#"+id+":checked");
					if($(val).prop('checked') ) {
						$(finput).after('<span class="input_confirm display_hide">' + val.val() + '</span>');
					}
					break;
				case 'checkbox':
					var val = $("#"+id+":checked");
					if($(val).prop('checked') ) {
						$(finput).after('<span class="input_confirm display_hide">' + val.val() + '</span>');
					}
					break;
				case 'text':
				case 'password':
					$(finput).after('<span class="input_confirm display_hide">' + finput.val() + '</span>');
					break;
				case 'textarea':
					var val = finput.val();
					val		= val.replace(/\n/g, "<br>"); 
					$(finput).after('<span class="input_confirm display_hide">' + val + '</span>');
					break;
			}
		});
		form_change(true);
	});
	$(document).on('click','#jform_back',function(){
		form_change();
	});
	function form_change(sw) {
		if(sw === true) {
			$(".input_form").hide();
			$(".display_hide").show();
		} else {
			$(".input_form").show();
			$(".display_hide").hide();
			$(".input_confirm").remove();
		}
	}
	function form_reset() {
		$('#jform')[0].reset();
	}
	$("#jform_back").hide();
	$("#jform_send").hide();
	$(document).on('click','#jform_send',function(){
		event.preventDefault();
		var id = "デプロイID";
		var heading = {'last_name': '苗字','first_name':'名前','tel':'電話番号','email':'メール','message':'お問い合わせ','gender':'性別','os':'ご使用OS'};
		var headingorder = JSON.stringify(heading);
		headingorder =encodeURI(headingorder);
		$.ajax({
			type: "POST",
			url: "https://script.google.com/macros/s/"+ id +"/exec",
			data:  $("#jform").serialize() + "&formDataNameOrderJP="+ headingorder,
			success: function (response) {
				console.log(response);
				if(response.result == "success"){
					alert("success");
					$("#jform").html('送信しました');
					form_reset();
				} else {
					if(response.result == "error"){
						alert(response.inputerrorr);
					} else {
						alert("error");
					}
				}
			},
			error: function (err) {
				console.log(err);
				alert("error");
			},
		});
	});
});
</script>
</head>
<body>
<div id="container">
<form id="jform">
	<div>
		<dl>
			<dt><span class="input_form">※</span>苗字</dt>
			<dd>
				<input name="last_name" type="text" id="last_name" size="35" maxlength="30" jform-control class="input_form">
			</dd>
		</dl>
		<dl>
			<dt><span class="input_form">※</span>名前</dt>
			<dd>
				<input name="first_name" type="text" id="first_name" size="35" maxlength="30" jform-control class="input_form">
			</dd>
		</dl>
		<dl>
			<dt><span class="input_form">※</span>電話番号</dt>
			<dd>
				<input name="tel" type="text" id="tel" size="35" maxlength="30" jform-control class="input_form">
			</dd>
		</dl>
		<dl>
			<dt><span class="input_form">※</span>メール</dt>
			<dd>
				<input name="email" type="text" id="email" size="35" maxlength="30" jform-control class="input_form">
			</dd>
		</dl>
		<dl>
			<dt>性別</dt>
			<dd>
				<input type="radio" name="gender" id="gender1" value="男性" jform-control class="input_form"><label for="radio1" class="input_form">男性</label>
				<input type="radio" name="gender" id="gender2" value="女性" jform-control class="input_form"><label for="radio2" class="input_form">女性</label>
			</dd>
		</dl>
		<dl>
			<dt>ご使用OS</dt>
			<dd>
				<input type="checkbox" name="os" id="os1" value="Windows10" jform-control class="input_form"><label for="os1" class="input_form">Windows10</label>
				<input type="checkbox" name="os" id="os2" value="Mac" jform-control class="input_form"><label for="os2" class="input_form">Mac</label>
				<input type="checkbox" name="os" id="os3" value="Linux" jform-control class="input_form"><label for="os3" class="input_form">Linux</label>
			</dd>
		</dl>
		<dl>
			<dt>お問い合わせ</dt>
			<dd>
				<textarea name="message" id="message"	jform-control class="input_form"></textarea>
			</dd>
		</dl>
	</div>
	<div>
		<input type="reset"  id="jform_reset" value="リセット" class="input_form">
		<input type="button" id="jform_check" value="確認" class="input_form">
		<input type="button" id="jform_back" value="修正" class="display_hide">
		<input type="button" id="jform_send" value="送信" class="display_hide">
	</div>
</form>
</div>
</body>
</html>

はまった・・・

っていうか・・きちんと読んでなかった・・よ。

URL制限機能（Allowlist）は、Google Workspaceでないとできないらしい・・

注意

Google Workspaceを利用しない場合は、デプロイ ID、ウェブアプリURLがわかってしまうと利用できてしまうので、

Apps Scriptで入力チェック、独自でtokenつくるなど必要かも。ウェブアプリURLなどはphpなどで隠蔽するなど、必要です。

デプロイID隠蔽

Google Workspaceでない場合,送信部をPHPで・・・汗

ajaxの下記を


url: "https://script.google.com/macros/s/"+ id +"/exec",
↓
url: "下記のphpファイル名でしてい",
下記は削除で・・
var id = "デプロイID";

phpのサンプルソースコード


<?php
	$myhost = "自サーバーホスト名";
	define('POST_URL', 'https://script.google.com/macros/s/デプロイID/exec');
	if($_SERVER["REQUEST_METHOD"] == "POST") {
		$referer = $_SERVER['HTTP_REFERER'];
		$str = parse_url($referer);
		if(!stristr($str['host'], $myhost)){
			error_msg("接続がが許可されていません");
			return;
		}
		//入力チェックするなら・・
//		if(empty($_POST['xxxx'])){
//			error_msg("XXXは必須項目です。");
//			return;
//		}
//		if(empty($_POST['xxx'] || || 8 < mb_strlen($_POST['xxx'])) {
//			error_msg("XXXは必須項目です。[8文字いないで入力してください]");
//			return;
//		}
//		if(empty($_POST['email']) || !filter_var($_POST['email'], FILTER_VALIDATE_EMAIL))  {
//			error_msg("XXXは必須項目です。[正しい形式で入力してください。]");
//			return;
//		}
		$url = curl_init(POST_URL);
		curl_setopt($url, CURLOPT_RETURNTRANSFER, true);
		curl_setopt($url, CURLOPT_POSTFIELDS, $_POST);
		curl_setopt($url, CURLOPT_FOLLOWLOCATION, true);
		$response = curl_exec($url);
		curl_close($url);
		header("Content-Type: application/json; charset=utf-8");
		echo $response;
	} else {
		error_msg("接続がが許可されていません");
	}
function error_msg($msg) {
	$json['result'] = 'error';
	$json['inputerrorr'] = $msg;
	header("Content-Type: application/json; charset=utf-8");
	$error = json_encode($json);
	echo $error;
	return;
}
?>

HTTP_REFERERは、取得できないサーバはコメントで・・自サーバーからのみのアクセス制限です。

さいごに・・

今日・・記事アップじゃん・・1個記事たりないって。急いでつくったやつ・・チェック機能とか・・ないよん。

関連記事