【GAS活用術⑧】フォームからスプレッドシートに送信されたデータに書式設定をする

Google Apps Script (GAS)を、もっと身近に、日々の暮らしに。

表題の、「フォームからスプレッドシートに送信されたデータに書式設定をする」方法ですが、ふと、GAS活用術として、記事にしていなかったことに気づきました。

ということで、王道の書式設定の方法と、裏技的な書式設定の方法の２パターンをご紹介していきたいと思います。

あらかじめ書式設定していても

フォームからスプレッドシートに送信されたデータに、書式設定したい場合は、GASを設定する必要があります。

前回の【GAS活用システム④-2】でも書きましたが、スプレッドシート側であらかじめ何らかの書式を設定しておいても、

新規にフォームからデータを送信されたデータには、書式設定がされません。

フォームから送信されたデータは、「新規の行」として挿入されるためです。

ということで、フォームから送信されたデータに自動的に書式を設定したい場合は、フォームからデータが送信されたタイミング（トリガー＝フォーム送信時）で、表示書式を設定するGASを実行する必要があるわけです。

書式設定の例

何か具体例があった方がわかりやすいので、引き続き、【GAS活用システム①-1】立替と会費の精算を自動化するのスプレッドシートを使います。
このスプレッドシートで、以下のような書式設定をしたいとします。

• A列のタイムスタンプの日時データを、時間を表示にして曜日を表示したい

• D列は金額データなので、¥マークをつけて千単位にカンマをつけたい

フォームからスプレッドシートにリンクした場合、A列に「タイムスタンプ」が自動設定されて、フォームから送信すると、時間つきの日時が自動的に設定されると思います。

とても便利ですが、時間まで表示しなくても、、という場合もあるかと思います。この例では立替データを入力しているわけですが、「入力日」として日付だけ表示されれば十分、ですよね。

そんな時は、実際のデータとしては時間まで保持したとしても、「2008/09/26(金)」の書式を指定すれば、表示上、時間が表示されなくなります。

ちなみに、最近気づきましたが、「タイムスタンプ」というA列の見出しは変更できます。試しに「入力日」と変更してみました。

王道の書式設定の方法

それでは、実際にGASを登録してみましょう。まずは王道バージョンです。

GASを登録（王道バージョン）

スプレッドシートのメニューバーから、拡張機能 > Apps Script を選択します。

無題のプロジェクトが生成され、Apps Scriptのスクリプトエディタが表示されました。ファイル：コード.gsに、「function myFunction」が表示されています。

「function myFunction ( ) { }」のところに、上書きする形で下記のコードをコピぺします。

function submitForm(e) {
  const sh = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0]; //一番左のシートを取得
  const row = e.range.getRow();   //シートに書き込まれたデータの行番号
  const dateCol = 1;          //（★要変更★）日付書式の列番号を指定
  const priceCol = 4;         //（★要変更★）通貨書式の列番号を指定

  //書き込まれた行の指定の列に日付書式を設定
  sh.getRange(row,dateCol).setNumberFormat("yyyy/mm/dd(ddd)");

  //書き込まれた行の指定の列に通貨書式を設定
  sh.getRange(row,priceCol).setNumberFormat("[$¥-411]#,##0"); 
}

変更が必要なのは、4行目と5行目です。
今回の例は、日付の書式を設定したい列がA列なので列番号1、通貨の書式を設定したい列がD列なので列番号4を指定していますが、必要に応じて列番号を変更してください。

「無題のプロジェクト」をクリックして、プロジェクト名を変更します。

ツールバーの保存ボタンでプロジェクトを保存します。

トリガーの設定

続けて、トリガーの設定をします。スクリプトエディタを表示し、画面左端のメニューバーで「トリガー」を選択します。

「トリガーを追加」をクリックします。

トリガーを追加の画面で、「実行する関数の選択」でsubmitForm、「イベントのソースを選択」でスプレッドシートから、「イベントの種類を選択」でフォーム送信時を選択します。

保存ボタンをクリックすると、しばらく待たされた後、以下のようなメッセージが表示されることがあります。

しかも、トリガーが保存されていません。。実はこれ、未承認のスクリプトをトリガーで指定したために発生しています。

再度、トリガーを追加の画面で保存ボタンをクリックすると、今度は以下のような英語の画面が表示されたりもします。
臆せずに、下記の手順で実行権限の承認を実施してください。まず、
→ 自分のアカウントを選択

→ 「Advanced」、「Go to [プロジェクト名] (unsafe)」を順番にクリック

→ 「Allow」をクリックすることで、実行権限の承認完了

トリガーが設定されると、以下のような画面が表示されます。

書式設定の動作確認

では、フォームからテストデータを入力して送信してみましょう。
スプレッドシートを開いてみると、

上記のように確かに書式設定がされていることが確認できると思います。

この王道の書式設定の方法の要領で、他の書式設定を行うことが可能です。今回は、setNumberFormatメソッドを使って

• 曜日付きの日付書式は、"yyyy/mm/dd(ddd)"

• 通貨端数切捨ては、"[$¥-411]#,##0"

と指定しました。どういう書式設定をしたいのか、そのためにはどのメソッドを使ってどのように指定すればいいのかを調べて、GASを変更すればいいわけです。

裏技的な書式設定の方法

ただ、、、うーん、一つずつ指定するのが面倒かも、、、と思ったあなたに、お勧めの方法があります。

copyFormatToRangeメソッドで2行目の書式をがっつりコピーする方法です。

GASはぐっとシンプルに（裏技バージョン）

実際のコードが↓こちらです。

function submitForm(e) {
  const sh = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0]; //一番左のシートを取得
  const row = e.range.getRow();   //シートに書き込まれたデータの行番号
  //2行目の書式をフォームから送信された行にがっつりコピー
  sh.getRange(2,1,1,sh.getLastColumn()).copyFormatToRange(sh,1,sh.getLastColumn(),row,row);
}

２行目の書式をがっつりコピーするので、どの列にどんな書式を設定する、という記述が不要になり、スクリプトがシンプルになりました。

王道バージョンのスクリプトを上記のスクリプトで置き換えて、スクリプトの保存をします。トリガーは設定済みなので、あとは試してみるだけです。

フォームからテストデータを入力して送信すると、

ちゃんと最後の行も書式設定がされましたね！

なぜ2行目から書式をコピーするのか

ポイントは「2行目から」書式をコピーするということです。

初めは挿入される一つ上の行から書式をコピーすることを考えましたが、GAS失敗談「ほぼ同時にフォーム送信されたらダメだった話」に書いたように、複数データがフォームからほぼ同時に送信されたら、一つ上の行に書式がコピーされる前に書式をコピーしてしまう可能性もあります。

1行目は見出しなので、2行目にはフォームから送信された1件目のデータということになります。1件目のデータに対して、手動で書式をあれこれ設定しておけばよいわけです。

残りの行は、フォームから送信されるタイミングで、2行目の書式をコピーする、という形にすれば、書式コピーのリレーが途中で途切れることはないはずです。

copyFormatToRangeメソッドの構文

copyFormatToRangeメソッドですが、getRangeとパラメータの指定の仕方が同じと思いきや、全然違って、少々戸惑いましたので、まとめておきます。

getRangeが行・列・行・列なのに、copyFormatToRangeはシート・列・列・行・行、なんですね。。う～ん、ややこしい。。

---

裏技バージョンの方は書式を設定していないセルも含めてまるっと書式のコピーしていますので、処理的な無駄は多い、とも言えます。

王道バージョンで一つずつ書式設定するのと、どちらがいいか、、比較検討してお試しいただければと思います。

さて、久々の【GAS活用術】となりましたが、いかがだったでしょうか。

最近の記事の【GAS活用システム】は長文になりがちなので、たまには短めの一話完結の【GAS活用術】もいいな、と思いました。

それでは、また。