search
LoginSignup
25

More than 1 year has passed since last update.

posted at

updated at

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

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

作りたかった内容

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

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

出来上がりイメージ

入力フォーム

kobito.1524976705.063497.png

スプレッドシート

kobito.1524977046.006731.png

 編集用URLをクリックした際に表示されるフォーム

「No.1」というリンクをクリックすると、編集用画面が表示されます。
kobito.1524977158.979284.png

結論

動作イメージ

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

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

動作確認スクリプト

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挿入は新規送信時のみ行えば良いため、「最終行にデータがある」=「既存データの編集・更新を検知」した場合はここでスクリプトを終了します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が挿入された「だけ」になり、見易さ的にはあまり変わりません。
kobito.1524980246.574322.png

入力データを更新日:降順で並び替える。

ここはスクリプトではなく、スプレッドシートのsort(範囲, 並べ替える列, 昇順, [並べ替える列2, 昇順2, ...])関数を利用します。

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

kobito.1524980294.610568.png

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

kobito.1524980378.259875.png

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

kobito.1524980412.560539.png

先ほどまで
「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

sample-code2.gs
function triggerSet() {
  //対象スプレッドシートの指定
  var id   = '~~~~~~~~~~~~';

  //トリガーの作成
  ScriptApp.newTrigger('onFormSubmit')
  .forSpreadsheet(id)
  .onFormSubmit()
  .create();
}

  1. 新規送信されたデータが(スクリプト実行のタイミングで)シートに記載されている前提が必要になるためGoogleフォーム側でForm Submitトリガーを設定してもうまくいきません。この辺は一個前のエントリーを参照のほど。 

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

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

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
What you can do with signing up
25