011-フォーム送信時にスプレッドシートへ編集用URLを挿入する

フォーム回答編集用URLを __メールに送信する__んではないんです。
スプレッドシートに記載したかったのです、というお話。

# 作りたかった内容
今回の内容は
1. FAQリストをスプレッドシートで作成したい
2. だけどスプレッドシートを直接いじらせたくない（閲覧のみにしたい）

というリクエストに応えるべく作成しました。

## 出来上がりイメージ
### 入力フォーム
![kobito.1524976705.063497.png](https://qiita-image-store.s3.amazonaws.com/0/110567/c8cb1442-9203-be86-ba6b-b0940437f387.png "kobito.1524976705.063497.png")

### スプレッドシート
![kobito.1524977046.006731.png](https://qiita-image-store.s3.amazonaws.com/0/110567/5d31b156-2294-b647-82e2-566aab0936f7.png "kobito.1524977046.006731.png")

###　編集用URLをクリックした際に表示されるフォーム
「No.1」というリンクをクリックすると、編集用画面が表示されます。
![kobito.1524977158.979284.png](https://qiita-image-store.s3.amazonaws.com/0/110567/19003230-8cd8-ec52-050d-add091f8d687.png "kobito.1524977158.979284.png")

# 結論
## 動作イメージ
1. スプレッドシート用の「フォーム送信トリガー」で、作成関数`onFormSubmit(e)`が実行されるよう設定する
2. `onFormSubmit(e)` にて `FormApp.openById(fid).getResponses()`を行い全送信データを取得する
3. `etEditResponseUrl()`で編集用URLを取得する
4. スプレッドシートの最終行のどこかに`setValue()`する

スプレッドシートイベントなのに、引数`e`を全く使わないのがミソです。

## 動作確認スクリプト
```js:sample-code1.gs

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挿入は新規送信時のみ行えば良いため、「最終行にデータがある」＝「既存データの編集・更新を検知」した場合はここでスクリプトを終了します[^2]

[^2]: 新規送信されたデータが（スクリプト実行のタイミングで）シートに記載されている前提が必要になるためGoogleフォーム側でForm Submitトリガーを設定してもうまくいきません。この辺は[一個前のエントリー](http://bit.ly/2FpNNNK "010-Form Submitの違いを「４０秒で説明しな」 | Qiita")を参照のほど。

### (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が挿入された「だけ」になり、見易さ的にはあまり変わりません。
![kobito.1524980246.574322.png](https://qiita-image-store.s3.amazonaws.com/0/110567/f6a66b24-1006-3186-8617-33539f0abde0.png "kobito.1524980246.574322.png")



## 入力データを更新日：降順で並び替える。
ここはスクリプトではなく、スプレッドシートの[sort(範囲, 並べ替える列, 昇順, [並べ替える列2, 昇順2, ...])](https://support.google.com/docs/answer/3093150 "SORT ドキュメントエディタ ヘルプ")関数を利用します。

新しくシート「シート2」を追加して、A2セルに`=sort('フォームの回答 1'!A2:D,2,false)`を設定してみました。

![kobito.1524980294.610568.png](https://qiita-image-store.s3.amazonaws.com/0/110567/6176c3e2-81f3-683f-d070-b3be6e024049.png "kobito.1524980294.610568.png")

「並び替える列＝B列」が降順でソートされているのがわかります。
ここでNo.2のデータを更新してみましょう。

![kobito.1524980378.259875.png](https://qiita-image-store.s3.amazonaws.com/0/110567/eceaeb93-ef81-fc34-6495-08d7c8195559.png "kobito.1524980378.259875.png")

送信ボタンを押すと・・・

![kobito.1524980412.560539.png](https://qiita-image-store.s3.amazonaws.com/0/110567/c05fcb07-ffb5-6bde-edc3-ec4211ccc917.png "kobito.1524980412.560539.png")

先ほどまで
「No.3」　→「No.2」　→「No.1」だった並びが、
「No.2」　→「No.3」　→「No.1」に修正されていますね。

あとは枠線をつけたりセル背景色をつけるなど装飾してあげれば、それっぽいFAQになりそうな予感がします。

なお[IMPORTRANGE(スプレッドシートキー, 範囲の文字列)](https://support.google.com/docs/answer/3093340 "IMPORTRANGE ドキュメントエディタ ヘルプ")関数などを使って別スプレッドシートに書き出してしまうとHYPERLINKが無効化されちゃうので注意[^4]。

[^4]: やり方は色々ありそうなのですが力尽きました。どなたか良いアイディアありましたらご教示くださいませ。

# 蛇足2。
動作確認スクリプトの説明欄で
> standalone scriptとして作成しているため`SpreadsheetApp.openById(sid)`という回りくどいやり方で指定していますが、

という内容を書きました。
standalone scriptでは「編集」＞「現在のプロジェクトトリガー」メニューを開いても、 __どのスプレッドシートに紐づけられたフォーム送信をトリガーにすればいいか__ 指定することができません。

## Standalone Scriptでトリガーってどうやるの？
`ScriptApp.newTrigger()`を使って設定します。
`onFormSubmit()`が指定できるよう、先のスクリプトと同じファイルに記載しましょう[^3]。

[^3]: 別ファイルにある関数を指定することもできますが、それできる人は多分この蛇足は不要かと思うので考慮外で…

```js:sample-code2.gs
function triggerSet() {
  //対象スプレッドシートの指定
  var id   = '~~~~~~~~~~~~';
  
  //トリガーの作成
  ScriptApp.newTrigger('onFormSubmit')
  .forSpreadsheet(id)
  .onFormSubmit()
  .create();
}
```