##前置き
皆さんはGoogle Apps Script(以下GAS)を使った事がありますか？
GoogleドライブやGmail等、Googleが提供するサービスをJavascriptベースのGASと言うスクリプトで扱う事が出来る物なのですが、中々使い易く、機能も豊富なので短いコードでかなり便利な物を作る事が出来ます！
(｀･ω･´)b
今回は仕事で作った物を少しコンパクトにして、備忘録的な感じで紹介してみたいと思います。
##サンプル
まず初めにサンプルを紹介しますねー。
Googleドライブ関連：
【GAS】 スプレッドシートと連携したAjaxフォーム - Google Apps Script
【GAS】 スプレッドシートと連携したAjaxフォーム - Google スプレッドシート
CODEPEN:
【GAS】 スプレッドシートと連携したAjaxフォーム
※ちょっと見てみたらSlimが上手く変換出来ないバグが発生しているみたいだったので、HTMLバージョン作りましたー。↑のリンク先が正しく表示されない場合は下記のリンク先をお試し下さい。
【GAS】 スプレッドシートと連携したAjaxフォーム Slimじゃないバージョン
CODEPENページのフォームを送信すると、上記リンクのGASが動き、同じくリンク先のスプレッドシートにデータが入っていくと言う仕組みになってます。
それでは上記の物について簡単にですが解説していきたいと思いまーす。
##何でGASを使うの？
この投稿のタイトルを見て、「フォームとスプレッドシートを連携したいなら、Googleフォームの機能使えば良くない？」と思った方、その通りです！
Googleフォームの機能で満足しているのならば、Googleフォームを使ってスプレッドシートと連携させるのが、一番手っ取り早く、簡単です。
では何故今回はそれを使わないのか？それを簡単に説明していきたいと思います。
###Googleフォームを使うデメリット
####デザインの自由が効かない
一番にして最大の理由はこれです。
Googleはテーマ機能を作ってこの問題を解決しようとしていますが、やはり限界があります…
(´・ω・｀)
iframeの中身をコピーして、無理やりデザインを適用する方法もありますが、利用規約の「本サービスのご利用」の項目を見ると、それはちょっと怖いですし、更新が煩雑になる事間違い無しですよね…
Google 利用規約 – ポリシーと規約 – Google
本サービスを不正に利用しないでください。たとえば、本サービスの妨害や、Google が提供するインターフェースおよび手順以外の方法による本サービスへのアクセスを試みてはなりません。ユーザーは、法律（輸出、再輸出に関して適用される法規制を含みます）で認められている場合に限り、本サービスを利用することができます。ユーザーが Google の規約やポリシーを遵守しない場合、または Google が不正行為と疑う行為について調査を行う場合に、Google はユーザーに対する本サービスの提供を一時停止または停止することができます。
####iframeを使っている所
ページ内の埋め込みにはiframeが使われるのですが、それの所為でページ内データとの連携が難しくなっています。
例えば簡単な見積計算みたいな機能を作って、計算が終わったらそれをフォームに挿入する等の処理が出来ません…
###Googleフォームを使うメリット
しかし逆にGoogleフォームを使うメリットも勿論あります！
####更新が簡単
Googleフォームを使う最大のメリットはこれです！
フォームを作成する為のUIはとても分かり易く、Web関連の知識が無い人でも簡単に更新が出来、かなり凝ったフォームも簡単に作る事も出来るでしょう。
####統計を表示する機能がある
Googleフォームには統計を見易く表示する機能がデフォルトであります。まぁスプレッドシートと連携出来れば、統計を出すのは結構簡単なのですが、Googleフォームを使うメリットの一つかなーって思います。
###つまり
ウェブページの更新をする人があまりWebの知識を持っていない場合は、Googleフォームを使うのが適当かなと思います。
でも逆に、その辺の知識がある人からするとGoogleフォームを使うデメリットはかなり大きいかなーと感じます…
まぁその辺はトレードオフな感じですね、状況によってより良いと思われる物を選択しましょう。
そして今回は、Googleフォームを使わずに、GASを使ったスプレッドシートとの連携方法について説明していきたいと思います。
##サンプルを利用した作り方説明＆サンプル解説
今回のサンプルは「Googleフォームのスプレッドシート連携機能と、なるべく同じような仕様にしたい」って言うのと「自動返信メール機能」を目標に作りました。
具体的には以下の機能が欲しいなーと思って制作しました。ご参考までにどうぞ。
・タイムスタンプ機能
・スプレッドシートの列を移動しても、ちゃんと対応した列にフォームデータが入っていく機能
・送信されたフォームデータに、スプレッドシートのヘッダーに無いnameの物が有ったら、それをヘッダーに追加する機能
・送信データ確認付き自動返信メール機能（テキストメール＆HTMLメール）
それでは、早速作っていきましょう！
最初に下記リンクをクリックして自分のマイドライブに移動します。
https://drive.google.com/drive/my-drive
↓
そして今回は２つのファイルを使うので、分かり易い様にフォルダを作るのが良いでしょう、左上の「新規」をクリック＞「フォルダ」をクリックして適当な名前でフォルダを作りましょう。
今回は「サンプル」と言う名前にしました。
↓
ファイル一覧に「サンプル」と言うフォルダが有ると思うので、ダブルクリックしてフォルダ内に移動します。
↓
もう一度「新規」をクリックして、新しいGoogleスプレッドシートを作成します。これも適当な名前を付けておきましょう、今回は「Ajaxフォーム」と言う名前にしました。
↓
次にスプレッドシートのシート名を変更します。左下の所から「名前を変更」でOKです。今回は「フォームデータ」と言う名前にします。
↓
一旦「サンプル」フォルダに戻り、「Ajaxフォーム」を右クリック＞「リンクを取得」をクリックします。
そのリンクの最後「?id=」以降の文字列がこのスプレッドシートのIDになります。後で使用するのでメモ帳等を起動して、コピペしておきましょう。
(｀･ω･´)b
↓
次はGASのファイルを作って行きます。
「新規」をクリック＞「その他」から「Google Apps Script」を選択しましょう。
もし「その他」の中に「Google Apps Script」が無い場合は、「アプリを追加」をクリックし、左上のドロップダウンを「By Google」にすると「Google Apps Script」が出て来るので、それを追加して下さい。
↓
GASにも適当に名前を付けましょう。今回はスプレッドシートと同じ「Ajaxフォーム」と言う名前にしました。
↓
それが出来たら↓のサンプルを参考にファイルを作成、コピペして行きます。
【GAS】 スプレッドシートと連携したAjaxフォーム - Google Apps Script
先ずは「コード.gs」に「main.gs」を
次に「ファイル」＞「新規作成」＞「HTML ファイル」を選択し、「html_mail」と「text_mail」を作り、それぞれサンプルからコピペします。
↓
それぞれの作成とコピペが終わったら、最後にGASの「公開」を行います。
上のメニューから「公開」＞「ウェブアプリケーションとして導入」をクリックします。
すると下画像のポップアップが出るので、「アプリケーションにアクセスできるユーザー」を「全員（匿名ユーザーを含む）」に変更して「導入」をクリックしましょう。
※初回公開時や、コードの変更後の公開時に、コード内で使用されるサービスに関する承認を求められた場合は「続行」をクリックして承認して下さい。
「ウェブアプリケーションとして導入」が終わると下画像のポップアップが出ます。「現在のウェブ アプリケーションのURL」を後で使うので、これもメモ帳等にコピペしておきましょう。（これがフォームの送り先になる訳です）
※このURLは「公開」＞「ウェブアプリケーションとして導入」をクリックすれば、いつでも確認出来ます。
↓
これでGoogleドライブ側で必要な事は終了です。
次にフォームページ側の解説をしていくのですが、その前にサンプルの「main.gs」について軽く解説していきたいと思います。
まぁほぼコメントで説明しているのでそれを見てみて下さいｗ
ただ、サンプルを利用して作る場合「ignore_array」に「メールアドレス」が有る事に注意して下さい！フォームから送られてくる「メールアドレス」のデータをスプレッドシートに入れたい場合は「ignore_array」内の「'メールアドレス', 」部分を削除してください。
function doGet(e){
// フォームから受け取った値を使って必要な変数を作っていきます --------
var sheet = SpreadsheetApp.openById(e.parameter.SPREADSHEET_ID).getSheetByName(e.parameter.SHEET_NAME)
var last_row = sheet.getLastRow()
// スプレッドシートに追加したくない値を「ignore_array」で指定して、
// 必要なデータだけを「form_data」に入れていきます。
var ignore_array = ['メールアドレス', 'SPREADSHEET_ID', 'SHEET_NAME', 'callback', '_']
var form_data = {}
for(var key in e.parameter){
if(ignore_array.indexOf(key) === -1){
form_data[key] = e.parameter[key]
}
}
// スプレッドシートの初期設定 --------
// スプレッドシートに何も値が入っていないと以降の処理で問題が起きる+フォームの送信日時を挿入する為に、
// A1に「タイムスタンプ」を挿入します。
if(last_row === 0 && sheet.getLastColumn() === 0){
sheet.getRange(1, 1).setValue("タイムスタンプ")
last_row++
}
// Spreadsheetにフォームのデータを挿入 --------
// スプレッドシートの１行目をヘッダーとして取得します。
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
// 「form_data」に「for in」を使ってループ処理を行います。
for(var key in form_data){
for(var i = 0; i < headers.length; i++){
if(headers[i] === key){
// ヘッダーとkeyが一致した時に、そのヘッダーの列の新しい行（最後にデータが入ってる行の次の行）に
// フォームの値を挿入します。
sheet.getRange(last_row + 1, i + 1).setValue(form_data[key])
break
} else if(headers.length === i + 1){
// ヘッダーとkeyが最後まで一致なかった時は、そのkeyを新しいヘッダーとして追加して、
// そのヘッダーの列の新しい行にフォームの値を挿入します。
var new_column = sheet.getLastColumn() + 1
sheet.getRange(1, new_column).setValue(key)
sheet.getRange(last_row + 1, new_column).setValue(form_data[key])
}
}
}
// フォーム以外のデータ挿入と個別処理 --------
// 今回はタイムスタンプだけですが、例えば個別の「お問い合わせ番号」が要るとか、
// セルの最後に「進捗状態」を入れる等用の処理です。
headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
for(var i = 0; i < headers.length; i++){
switch(headers[i]){
case 'タイムスタンプ':
// 「タイムスタンプ」の場所に日時のデータを挿入。
sheet.getRange(last_row + 1, i + 1).setValue(new Date())
break
}
}
// フォームのデータにメールアドレスが有ればメールを送信 --------
if(e.parameter.メールアドレス){
send_mail(e.parameter.メールアドレス, headers, form_data)
}
// JSONPを返す処理 --------
// 今回は単純なデータしか返していませんが、頑張れば色々出来そうですねー。
var return_json = JSON.stringify({work:'success'})
return ContentService.createTextOutput(e.parameter.callback + '(' + return_json + ')').setMimeType(ContentService.MimeType.JAVASCRIPT)
}
function send_mail(mail_address, headers, form_data){
// HTMLメールとテキストメールのテンプレートを準備します。
var html_mail = HtmlService.createTemplateFromFile('html_mail')
html_mail.headers = headers
html_mail.form_data = form_data
var text_mail = HtmlService.createTemplateFromFile('text_mail')
text_mail.headers = headers
text_mail.form_data = form_data
// メール送る際のオプションを設定します。
var options = {}
options.noReply = true
// 「evaluate()」を行う事でテンプレート内のスクリプトを実行する事が出来ます。
options.htmlBody = html_mail.evaluate().getContent()
// テキストメールとオプションを設定してメールを送信。
GmailApp.sendEmail(mail_address, '自動返信メール - 【GAS】 スプレッドシートと連携したAjaxフォーム', text_mail.evaluate().getContent(), options)
}
↓
では次にフォームページ側について解説していきます！
CODEPENを使ったサンプルを元に説明していきたいと思います。
【GAS】 スプレッドシートと連携したAjaxフォーム
リンクを開いたらページ上部にある「Fork」をクリックしましょう。そうする事で独自のCODEPENページを作る事が出来ます。便利ですねーｗ
CODEPENは編集も楽だし、slim等の色々なフォーマットに対応しているので、これ系のサービスの中ではかなりお勧めだと思います。
そして、ここでようやく出てきました、申し訳程度の「Slim」「Sass」「CoffeeScript」要素です…ｗ
Validateはjquery.validateを使ってサクッとやってます。
先ずはHTML部分からー。
ポイントは「input type='hidden'」の要素に「SPREADSHEET_ID」と「SHEET_NAME」を指定している所です。GAS側はこのデータを使って、フォームデータを入れるスプレッドシート、シート名を判別しています。
これによって同じGASを使ってもフォームデータを、違うスプレッドシートや、シートに挿入出来る様な作りになっています。
.container
h1.text-center
| 【GAS】 スプレッドシートと連携したAjaxフォーム
hr
form.form-horizontal action='さっきコピペした「現在のウェブ アプリケーションのURL」を入れて下さい'
input type='hidden' name='SPREADSHEET_ID' value='ちょっと前にコピペした「スプレッドシートのID」を入れて下さい'
input type='hidden' name='SHEET_NAME' value='フォームデータ'
.form-group
label.col-md-2.control-label メールアドレス
.col-md-10
input.form-control name='メールアドレス' placeholder='test@test.com' type='email'
p.help-block
| メールアドレスを入力すると、そのアドレス宛に自動返信メールを送信します。
br
| メールアドレスはスプレッドシートに登録されないので、安心してお試し下さい。
.form-group
label.col-md-2.control-label Qiitaのユーザー名
.col-md-10
input.form-control name='Qiitaのユーザー名' placeholder='良ければQiitaのユーザー名を教えて下さーい'
.form-group
label.col-md-2.control-label 一言コメント
.col-md-10
input.form-control name='一言コメント' placeholder='適当でOK。何かコメントを貰えるだけで喜びます！(｀･ω･´)b' required='required'
.form-group
.col-md-offset-2.col-md-10
button.btn.btn-primary type='submit' 送信
次にCSS部分です。
特に説明する事はありませんね…(´・ω・｀)
「必須」ラベルの見た目を良くしてるだけです。
※個人的にはSassの方がSCSSより断然使い易いのに、SCSSが主流なので色々辛いです…
form.form-horizontal
.label.label-danger
border-radius: 4px
border-bottom-left-radius: 0px
border-bottom-right-radius: 0px
input[required]
border-top-left-radius: 0px
最後にJS部分です。
ポイントは「.validate()」と「$.ajax()」のオプション部分です。
まずは「.validate()」から、独自のAjax処理を使うので「debug: true」を付けましょう。これにより勝手にsubmitされなくなります。
次に「$.ajax()」ですが「dataType: 'jsonp'」になっている事を確認して下さい。これが「dataType: 'json'」等になっているとCORS(Cross-Origin Resource Sharing)関係でエラーが出るので注意が必要です…
(´・ω・｀)
$ ->
required_tag = '<span class="label label-danger">必須</span>'
requires = $('form [required]').each (i, elm)->
$(elm).before required_tag
$('form').validate
debug: true
submitHandler: (e)->
form = $ e
btn_submit = form.find('button[type=submit]')
$.ajax
url: form.attr 'action'
dataType: 'jsonp'
data: form.serialize()
beforeSend: ->
btn_submit.attr 'disabled', true
complete: ->
btn_submit.attr 'disabled', false
success: (response)->
console.log response
# 成功したらサンクスページとかに飛ばす処理の例↓
# window.location.href = 'http://codepen.io/snowsunny'
error: (response)->
console.log response
HTML部分の「form action=」「SPREADSHEET_ID」部分の置き換えが完了したら完成です！
GAS側やフォームページ側の処理等を変更してみたり、自由にお試し下さい！
(｀･ω･´)b
##まとめ
どうでしょうか？GASのコメント部分を除いた行数は６０行くらいです。
その割には結構便利な物になっていませんか？
Google側も色々な機能強化（アドオン機能等）したり、ドキュメントを充実させたりして、もっとGASを使って欲しいのかなーと言う印象です。
これからもっと色んな人に広まって、便利なアドオンや、困った時用のリソースが増えていけば嬉しいなーと思いますｗ
皆さんも機会があれば、便利なGASをドンドン使っていきましょう！
(｀･ω･´)b
※lab.snowsunny@gmail.comのアカウントは普段ほとんど使わないので、何か聞きたい事等が有る人は、この投稿のコメントにお願いしまーす。
###参考リンク
https://developers.google.com/apps-script/guides/content
https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app
https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet
https://developers.google.com/apps-script/guides/html/templates
https://developers.google.com/apps-script/reference/html/html-service
##あとがき
あとがきでーす、興味がある人はどぞー。
実はこの投稿ですが、最初は週一くらいの連載形式でやってみようかなーと思っていたのですが、連載が止まるのが目に見えていたので一つの投稿にまとめましたーｗ
その所為で、全体が長くなっていたり、説明が雑になっていたりしています…すみませんでした…
GASも行数を圧縮するために、関数分けとかをほとんどしていないので、必要があれば適宜行って下さーい。
(´・ω・｀)
###Googleに対する要望
疲れたのでまた後で書きます…
###本当はやりたかった事、良く分からなかった事
サンプルを作っていて良く分からなかったを書いてみようと思います。GASに詳しい方がいれば、知恵を貸して頂ければ幸いです。
でも疲れたのでまた後で書きますね…