【GAS】PHPフォーム+スプレッドシートで集計システムを作ってみた

2019/6/12 2019/10/31

この記事は最終更新から4年以上が経過しています。情報が古くなっている可能性があります。

こんにちは、しばです。
最近、Webサイトに設置しているお問合わせフォームからの送信内容を集計したいなぁと感じることがありました。
例えば直近一ヶ月でメールくれた人の男女の比率は？どこの都道府県に住んでる人が多いの？などなど。
メールを見ればなんとなくの傾向はわかるしエクセルに起こせばいいんですが、折角なので自動化したい…(｀-ω-´)

そんな経緯から、PHPフォームとGoogle スプレッドシートを連携させて、送信内容を集計するプログラムを作成しました！
コードを掲載するので、同じようなことをしたい方のご参考になれば幸いです。

Contents

1 集計システムの内容
2 サンプル
- 2.1 PHP側
- 2.2 スプレッドシート側
3 実際の動作
4 結論

集計システムの内容

Google Apps Script（以下GAS）のコードの大元はこちらを参考にさせていただきました。

phpフォームからスプレッドシートに書込み – 箱庭のエンジニア

サイトのフォームから送信された内容を、Google スプレッドシートで（以下スプレッドシート）集計します。
具体的には、

フォームに性別「男性」「女性」「その他」等のラジオボタンを用意、送信
スプレッドシートで2019/1/1〜2019/3/1と期間指定して集計を実施
スプレッドシートにその期間に送られてきた項目の数を表示（例：女性10、男性8、その他3）

このような機能を想定。

処理の流れは次のようなイメージで、お問合わせの内容をスプレッドシートのデータ記録用Aシートに記録していき、集計用Bシートで期間を指定・結果を出力させます。

スプレッドシートへの書き込み
・フォームからPOSTでGASにデータを送る
・GASからスプレッドシート(Aシート)へ受け取った内容を反映
GASで集計を実施 (上記とは別シート)
・スプレッドシート(Bシート)に期間を入力、GASで集計処理を行う
・GASで計算した内容をスプレッドシート(Bシート)へ反映

サンプル

今回は都道府県(プルダウン)、個人or法人(ラジオボタン)、業種(テキスト)の3項目のフォームを用意し、スプレッドシートへの送信と集計を行ないます。

画面イメージ

PHP側

<?php 
if(isset($_POST['address'])){

	define('POST_URL', 'https://script.google.com/macros/s/AKfycbyPYeapdBr4YnaFgrkaRfmq6m2AVcP4GqW5E67FhFlWoGAjG1Y0/exec');

	$post_data = [
		'address' => $_POST['address'],
		'kind' => $_POST['kind'],
		'job' => $_POST['job'],
	];

	$ch = curl_init();
	curl_setopt_array($ch, [
	    CURLOPT_URL => POST_URL,
	    CURLOPT_RETURNTRANSFER => true,
	    CURLOPT_POST => true, 
	    CURLOPT_POSTFIELDS => http_build_query($post_data),
	]);
	$response = curl_exec($ch);
	$header_size = curl_getinfo($ch, CURLINFO_HEADER_SIZE);
	$header = substr($response, 0, $header_size);
	curl_close($ch);
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
	<meta charset="UTF-8">
	<title>Document</title>
</head>
<body>

	<form action="#" method="post" id="form" name="form">
		<dl>
			<dt>都道府県（プルダウン）</dt>
			<dd><select name="address" id="">
				<option value="北海道">北海道</option>
				<option value="東北">東北</option>
				<option value="関東">関東</option>
				<option value="関西">関西</option>
				<option value="四国">四国</option>
				<option value="九州">九州</option>
				<option value="沖縄">沖縄</option>
			</select></dd>
			<dt>個人or法人（ラジオボタン）</dt>
			<dd>
				<input type="radio" name="kind" value="個人"> 個人<br>
				<input type="radio" name="kind" value="法人"> 法人
			</dd>
			<dt>業種（テキスト）</dt>
			<dd>
				<input type="text" name="job">
			</dd>
		</dl>
		<input type="submit" value="送信">
	</form>
</body>
</html>

スプレッドシート側

スプレッドシート側ではまずシートの用意をしていきましょう。
フォームから送信される内容を記録していく「データ」シート、集計プログラムを処理・出力「集計」シートを作成し、それぞれ必要な値を設定します。

「データ」シートの1行目A列にはtimestamp、B列以降にはフォームから送信されてくるnameの値を入力。

「集計」シートの1行目のB列、C列には集計する開始日付と終了日付を入力、ボタンには次に定義するpushBtn関数を設定します。

そして、スクリプトエディタに次のコードを保存します。

function doPost(e) {
 // スプレッドシート読込
 var sheet = SpreadsheetApp.getActive().getSheetByName('データ');
 var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
 var last_row = sheet.getLastRow(); 
  
 // スプレッドシートにフォームのデータを挿入
 // 「timestamp」列に時刻を挿入
 if(headers[0] === 'timestamp'){
   sheet.getRange(last_row + 1, 1).setValue(Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyy/MM/dd HH:mm'));
 }
 for(var key in e.parameter){
  for(var i = 1; i < headers.length; i++){
   if(headers[i] === key){
    sheet.getRange(last_row + 1, i + 1).setValue(e.parameter[key]);
    break;
   } else if(headers.length === i + 1){
    var new_column = sheet.getLastColumn() + 1;
    sheet.getRange(1, new_column).setValue(key);
    sheet.getRange(last_row + 1, new_column).setValue(e.parameter[key]);
   }
  }
 }
}

function pushBtn(){

  var sheet = SpreadsheetApp.getActive().getSheetByName('集計');
 
  var last_col = sheet.getLastColumn(); 
  var last_row = sheet.getLastRow(); 
  sheet.getRange(2,1,last_row,last_col).clear();
  
  last_row = sheet.getLastRow(); 

  var data_sheet = SpreadsheetApp.getActive().getSheetByName('データ');
  var data_headers = data_sheet.getRange(1, 2, 1, data_sheet.getLastColumn() - 1).getValues(); //headder取得
  var data_last_row = data_sheet.getLastRow(); 

  //開始日付終了日付行数をデータシートから取得
  var sobj = new Date(sheet.getRange(1, 2).getValue());
  var eobj = new Date(sheet.getRange(1, 3).getValue());
  eobj.setDate(eobj.getDate() + 1);
  var flg = false;
  var sdate_row, edate_row;
  for(var i = 0; i < data_last_row; i++){
    //[address, kind, job, ]を回す
    var tdate = data_sheet.getRange(i + 2, 1).getValue();
    var tobj = new Date(tdate);
    if(sobj < tobj && flg == false){
      sdate_row = i + 2;
      flg = true;
    }
    if(eobj　< tobj){
      edate_row = i + 1;
      break;
    }
    if(!edate_row){
      edate_row = data_last_row;
    }
  }

  for(var i = 0; i < data_headers[0].length; i++){
    sheet.getRange(last_row + 1 + i, 1).setValue(data_headers[0][i]);
    var myCurrentArray = data_sheet.getRange(sdate_row,i + 2,edate_row,1).getValues();
    var counts = {};
 
    for(var c=0;c< myCurrentArray.length;c++) {
      //Logger.log(c);
      var key = myCurrentArray[c];
      counts[key] = (counts[key])? counts[key] + 1 : 1 ;
    }
    
    var t = 0;
    for (var k in counts) {
      if (k != '' && counts.hasOwnProperty(k)) {
        var value = counts[k];
        sheet.getRange(last_row + 1 + i, t + 2).setValue(k + ':' + value);
        t++;
      }
    }
  }
}

これで準備は完了です。

ここまでできたら、スクリプトエディタの公開メニューから「ウェブアプリケーションとして導入」をクリック。
「アプリケーションにアクセスできるユーザー:全員（匿名ユーザーを含む）」にして公開しましょう。

実際の動作

PHPフォームに適当な値を入力して送信します。
すると、スプレッドシートに下記のように追記されていきます。（送信日時昇順）

これに対して、集計をしていきたいと思います。
集計シートに2019/4/1、2019/6/8と入力し、集計実行ボタンをクリック。

2行目以降にフォームの項目(name属性)ごとに集計結果が表示されました！

上記では、2019/4/1〜2019/6/8の間に送信された個人or法人（name属性kind)項目は、個人が10回・法人が6回ということがわかります。

補足

ちなみにフォームのメール送信処理も一緒に行いたい場合は、（１）フォームからGASにデータを送る処理にメール送信処理を併せて書く、（２）GASに書き込まれてからGAS側でメール送信を行う、この二つのいずれかの方法で実装が可能です。
（２）の方は前述した参考サイトに方法が掲載されていたので、そちらをご参照ください。

結論

GAS、便利ですね。こちらの記事（「LINE Botで画像テキスト抽出&翻訳&記録保存botを作ってみた【GAS】」）でも感じた簡易データベースとしての使い勝手の良さを改めて感じました。

また、スプレッドシートと連携させることで、お客様が見慣れた・使い慣れたUIでプログラムを提供できるというメリットがあります。
色々痒いところに手が届きそうだなぁと感じるので、他にもGASをウェブアプリケーションとして連携させる方法を色々と試していこうと思います( ｀・ω・´)