フォーム回答編集用URLを __メールに送信する__んではないんです。
スプレッドシートに記載したかったのです、というお話。
作りたかった内容
今回の内容は
- FAQリストをスプレッドシートで作成したい
- だけどスプレッドシートを直接いじらせたくない（閲覧のみにしたい）
というリクエストに応えるべく作成しました。
出来上がりイメージ
入力フォーム
スプレッドシート
### 編集用URLをクリックした際に表示されるフォーム
「No.1」というリンクをクリックすると、編集用画面が表示されます。
結論
動作イメージ
- スプレッドシート用の「フォーム送信トリガー」で、作成関数
onFormSubmit(e)が実行されるよう設定する -
onFormSubmit(e)にてFormApp.openById(fid).getResponses()を行い全送信データを取得する -
etEditResponseUrl()で編集用URLを取得する - スプレッドシートの最終行のどこかに
setValue()する
スプレッドシートイベントなのに、引数eを全く使わないのがミソです。
動作確認スクリプト
function onFormSubmit(e){
//回答シートのIDを保存
var sid = '~~~~~~~~';
var sname = 'フォームの回答 1';
//(1)回答シートを取得
var SS = SpreadsheetApp.openById(sid);
//最終行のAセルを取得
var RNG = SS.getSheetByName(sname).getDataRange();
var lastRow = RNG.getLastRow();
var rowA = RNG.getCell(lastRow, 1);
//(2)もし最終行A列にデータ（＝編集用URL）が入っていたら関数終了
if(rowA.getValue() != ""){
Logger.log("新規登録ではなく既存送信データ編集を検知したためスクリプトを終了します");
return 0;
}
//フォームのIDを保存
var fid = '~~~~~~~~';
//(3)フォーム回答データ一覧を取得
var fRspnss = FormApp.openById(fid).getResponses();
//(4)フォーム回答一覧から最後のデータ（＝一番新しいデータ）の編集用URLを取得
var url = fRspnss[Number(fRspnss.length - 1)].getEditResponseUrl();
//動作確認（URLが正しく取得できているかLogger.logへ出力）
Logger.log("URL is %s",url);
//(5)挿入用文字列を作成
var str = '=HYPERLINK("' + url + '","No.' + Number(lastRow - 1) + '")';
//文字列を最終行Aセルへ挿入
rowA.setValue(str);
}
解説
目につく範囲を上から順に。
(1) SpreadsheetApp.openById(sid);
standalone scriptとして作成しているためSpreadsheetApp.openById(sid)という回りくどいやり方で指定していますが、Container-bound scriptを使えばgetActiveSpreadsheet()とかで良いと思います。
(2) if(rowA.getValue() != "")
Form Submitトリガーは「新規で送信された場合」「既存データを編集・更新した場合」どちらも実行されますが編集用URL挿入は新規送信時のみ行えば良いため、「最終行にデータがある」＝「既存データの編集・更新を検知」した場合はここでスクリプトを終了します1
(3) FormApp.openById(fid).getResponses()
Googleフォームのトリガーなら引数eから簡単に編集用URL取れるのですが、今回はそれができません。
そのためフォームに保存されている送信データ全てをgetResponses()にて引っ張ってきます。
(4) fRspnss[Number(fRspnss.length - 1)].getEditResponseUrl()
getResponses()の返り値は「更新日時の昇順」のようです（最古が[0]、最新が[length-1]）。
トリガーとなった送信データから編集用URLを引っ張りたいので配列上で「データ長-1」番目を指定しgetEditResponseUrl()を行います。
配列の要素指定時にわざわざNumber()でくくっている理由は…手グセですかね…
###(5) var str = '=HYPERLINK("' + url + '","No.' + Number(lastRow - 1) + '")'
スプレッドシートを閲覧した人がワンクリックで編集画面に移れるようHYPERLINK関数で囲ってあげます。
蛇足1。
##FAQシートをより「それっぽく」するために。
このままですと、編集用URLが挿入された「だけ」になり、見易さ的にはあまり変わりません。
入力データを更新日：降順で並び替える。
ここはスクリプトではなく、スプレッドシートのsort(範囲, 並べ替える列, 昇順, [並べ替える列2, 昇順2, ...])関数を利用します。
新しくシート「シート2」を追加して、A2セルに=sort('フォームの回答 1'!A2:D,2,false)を設定してみました。
「並び替える列＝B列」が降順でソートされているのがわかります。
ここでNo.2のデータを更新してみましょう。
送信ボタンを押すと・・・
先ほどまで
「No.3」 →「No.2」 →「No.1」だった並びが、
「No.2」 →「No.3」 →「No.1」に修正されていますね。
あとは枠線をつけたりセル背景色をつけるなど装飾してあげれば、それっぽいFAQになりそうな予感がします。
なおIMPORTRANGE(スプレッドシートキー, 範囲の文字列)関数などを使って別スプレッドシートに書き出してしまうとHYPERLINKが無効化されちゃうので注意2。
蛇足2。
動作確認スクリプトの説明欄で
standalone scriptとして作成しているため
SpreadsheetApp.openById(sid)という回りくどいやり方で指定していますが、
という内容を書きました。
standalone scriptでは「編集」＞「現在のプロジェクトトリガー」メニューを開いても、 どのスプレッドシートに紐づけられたフォーム送信をトリガーにすればいいか 指定することができません。
Standalone Scriptでトリガーってどうやるの？
ScriptApp.newTrigger()を使って設定します。
onFormSubmit()が指定できるよう、先のスクリプトと同じファイルに記載しましょう3。
function triggerSet() {
//対象スプレッドシートの指定
var id = '~~~~~~~~~~~~';
//トリガーの作成
ScriptApp.newTrigger('onFormSubmit')
.forSpreadsheet(id)
.onFormSubmit()
.create();
}