私が趣味で所属している団体では、外部からの問い合わせをGoogleフォームで受け付けています。Googleフォームへの投稿はGoogleSpreadSheetに記録されるので、問い合わせ内容を共有するときはスプレッドシートのリンクを教えればOK！ ……と思っていたのですが。
しばらく運用していると不便さがでてきた
団体内から「問い合わせが確認しにくい」という声が上がってきました。
よく話を聴いてみると、ほとんどのメンバーはスプレッドシートをスマートフォンから参照していました。スマートフォンの表示だと縦にも横にもスクロールしないと、問い合わせの全容が把握できません。特に問題なのは、新しい問い合わせを確認するときにいちいち一番下まで頑張ってスクロールしないと見られないことでした。
そこで「Google Apps Scriptでフォームが投稿されたとき、タイムスタンプの降順でソートする」ことにしました※。
※当初はスプレッドシートのフィルタ機能を使って、タイムスタンプ列の降順ソートを設定してあげれば解決！ と思っていましたが、閲覧権限しか持っていないユーザはフィルタが効かないようで、あきらめました。
Google Apps Script 降順ソートの設定
それでは実際にGoogleフォームを用意し、降順ソートをかけるGASを書いていきましょう。説明のため、質問が一つだけあるシンプルな問い合わせフォームを用意しました。
このフォームに対して、適当に３回質問を送信しました。回答が記録されるスプレッドシートは、次の画像のようになります。
数秒おきの送信で読みにくいですが、タイムスタンプの昇順にならんでいることがわかります。このまま運用しつづけていると、新しい回答はどんどん下に連なり、冒頭で話したようにスクロールが必要になるわけですね。
GoogleAppsScriptを書いていこう
それでは、GoogleAppsScriptのエディタに移りましょう。ツールメニューのスクリプトエディタをクリックして、エディタを開きます。
エディタが開きました。からっぽの関数が一つ定義されているだけですね。myFunction()をこんな感じに書き換えてあげましょう。
function sortByTimestamp() {
// 問い合わせ回答のスプレッドシートへの参照を取得する
var sheets = SpreadsheetApp.getActiveSpreadsheet();
// ソートをかける対象のシートを取得する（indexは左側のシートから０〜）
var sheet = sheets.getSheets()[0]
// sort({ソートをかける列の指定}, {true: 昇順, false: 降順})
sheet.sort(1, false)
}
sort()の第1引数にはどの列を基準にソートをかけるかを指定します。ここで、一番左の列は0ではなく、1であることに注意してください。今回ソートをかける列「タイムスタンプ」は一番左なので、設定値は1となります。
スクリプトの記述はここまでです。続いて、この関数が実行するトリガーを設定していきましょう。
関数実行のトリガーを設定する
エディタの「編集」メニューから、「現在のプロジェクトのトリガー」を選択します。
トリガーを管理する画面に遷移しました。最初はトリガーが設定されていないので、「結果がありません」と表示されています。新しくトリガーを登録するには、右下の「トリガーを追加」をクリックします。
トリガーの設定画面が表示されます。
今回は「問い合わせフォームに回答があったとき、タイムスタンプで降順ソートする」ことを実現したいので、上記画像のように設定します。それぞれの設定の意味は次のとおりです。
- 実行する関数を選択
- 実行対象の関数。先程作成したsortByTimestampを選択する。
- 実行するデプロイを選択
- GoogleAppsScriptにバージョンを設定できます。今回バージョンは１つしかないので、最新を表す“Head“を設定します。
- イベントのソースを選択
- イベントどこから受け取るか設定します。今回はフォームからスプレッドシートに回答を送信したタイミングをトリガーとしたいので、「スプレッドシートから」を選択します。
- 他にもカレンダーをソースに選択して、指定したタイミングで関数を実行させることも可能です。
- イベントの種類を選択
- イベントソースの選択と同じ理由で「フォーム送信時」を選択します。
上記の画像だとわかりにくいのですが、下にスクロールすると完了、キャンセルボタンが隠れています。「完了」ボタンをクリックするとトリガーの設定完了です！
※続いて、トリガーの実行許可を与えるための認可処理を行いますが、この記事では割愛します。
トリガーの一覧にさきほどの設定が表示されていればOKです。
動作確認
もういちど問い合わせフォームから回答を送信してみましょう。
一番最後に送信された回答が、一番上の行になっていますね！ 他の行もタイムスタンプの降順になっています。
以上、Google Apps Scriptを使った、フォーム投稿日時の降順にソートをかける方法でした。