Google Apps Script で数値のフォーマットをカスタム形式で表示する方法と具体例
はじめに
Google Apps Scriptで扱う数値を色々なフォーマットをカスタム形式で指定して表示することができます。以下に日付や金額などの数値のフォーマットの指定方法と一緒に具体例をメモします。
数値のフォーマット指定方法
Google Apps Scriptでは、セルのフォーマットを以下のようなコードで指定できます。以下は適当なセル範囲(numberRange
は適当なセル範囲を指定済の前提)にnumFormats
で指定したフォーマットを適用しています。
// 数値のフォーマットを指定。
var numFormats = '#,##0;[Red](#,##0)';
// 指定したセル範囲にフォーマットを適用
numberRange.setNumberFormat( numFormats );
上記のようなフォーマットが設定されたセルでは、数値が以下のように表示されます。
- 正の値 → 1,235
- 負の値 → (1,235)
つまりフォーマットの指定は正の値、負の値の場合で分けて指定可能です。さらに数値が0の場合のフォーマット、テキストの場合のフォーマットも指定できます。
よって数値のフォーマットの記述方法(上記の'#,##0;Red'
部分)さえわかれば後は範囲を指定して自由にセルのフォーマットを指定可能です。以降ではフォーマットの記述方法を具体例と一緒にメモします。
カスタム形式の記述方法
Googleの公式ドキュメントの説明は以下のようになっています。
カスタム表示形式を作成する際は、セミコロンで区切った 4 つまでのパターン(正;負;ゼロ;非数値)を表示形式に含めることができます。会計用の表示形式もサポートされています。
具体的に上記のカスタム形式の記述#,##0;Red
を説明すると、これは以下のようになります。
#,##0
は正の値に対するフォーマットを指定しています。#
は数値の1桁を表すため、ここの#,##0
は3桁目からカンマを表示することを意味します。すなわち、###0
のようにした場合は、カンマは表示されません。最後の0
は小数点第何位までを表示するかを指定する時に使います。例えば、#,##0.0
ならば小数点第1位、#,##0.00
ならば小数点第2位まで表示します。[Red](#,##0)
は負の値に対するフォーマットを指定しています。[Red]
は文字色の指定です。英語表記を[]
で囲んで指定します。指定できる色は最後に載せている公式サイトに載っています。()
はただの括弧で、負の値を括弧で囲んで表示します。
なお、カスタム形式を指定するためのフォーマットの記述方法は、スプレッドシートから確認することができます。
具体的には、以下のように、メニューの表示形式
→数字
→表示形式の詳細設定
→カスタム数値形式
をクリックします。
すると、以下のようにカスタム数値形式のサンプルが表示されます。
この画面で適当なサンプルをクリックすると、以下のように具体的な数値が入った場合の表示例が見れるので便利です。
もし自分が使いたいフォーマットの書き方がわからない場合はこれを参考にすると分かる場合があるかもしれません。
ちなみに、上の画像のカスタム形式の表示に、)
が含まれていますが、これはどうやらGoogleのバグ?のようです。この)
は無くて大丈夫です。試したところ、Google Apps Scriptで)
を含んだ状態でフォーマットを指定してみたところ、一応問題なくエラーなども出ず指定できました。
具体的には、#,##0
);(#,##0)
は#,##0;(#,##0)
と同じです。
_)
についてのフォーラムでの質問と回答
My text in Sheets has an underscore and parentheses around it? | Google Docs Help Forum
なお、色や記号の意味については、以下の公式ドキュメントに掲載されています。
スプレッドシートで数値の表示形式を設定する | Google ドキュメント エディタ ヘルプ
金額のフォーマット指定
上のカスタム形式の指定とほぼ同じですが、以下のように¥マークを入れれば金額用のフォーマットとしてももちろん指定できます。
// 金額のフォーマット
var moneyFormats = '¥ #,##0;¥ (#,##0);"0";@[blue]';
// 指定したセル範囲に金額のフォーマットを適用
moneyRange.setNumberFormat( moneyFormats );
上記のフォーマット指定では以下のように表示されます。@
はテキストに置換されます。
- 正の値:
¥ #,##0
→:¥ 1,234 - 負の値:
¥ (#,##0)
→:¥ (1,234) - ゼロ:
0
→:0 - テキスト:
@[blue]
→ テキスト
日付のフォーマット指定
日付のフォーマット指定ももちろん可能です。
// 日付のフォーマットを指定。
var dateFormats = 'yyyy-mm-dd';
// 指定したセル範囲に日付のフォーマットを適用
startRange.setNumberFormat( dateFormats );
yyyy
は年、mm
月、dd
は日に該当し、yyyy-mm-dd
の場合は2016-10-10
のような形式でされます。
他にも以下のような形式が指定可能です。
var dateFormats = 'yyyy年mm月dd日'; // 2016年10月10日
var dateFormats = 'yyyy年mm月dd日 h:mm a'; // 2016年10月10日 12:08 PM
var dateFormats = 'yyyy年mm月dd日 h:mm a'; // 2016年10月10日 12:08 PM
var dateFormats = 'yyyy-MMM-dd (EEE)'; // 2016-Oct-10 (Mon)
以下のGoogle公式ドキュメントにも書かれているように、Java SEの形式に則っているようです。
Class Utilities | Google Apps Script Class SimpleDateFormat | Oracle
参考
以下の記事に実際にフォーマット指定を使っているスクリプトを掲載しているので必要な方は見てみてください。 Google Apps Scriptでスプレッドシート間のデータコピーやファイル作成を行うスクリプト | virtualiment
関連記事
- 公開日:2019/09/28 更新日:2019/09/28
Google Apps Scriptと連携してAmazon Alexaに次のバスの時間を教えてもらう
この記事では、ユーザーの呼びかけに応答してAlexaからGoogle Apps Scriptで公開しているスクリプトにPOSTリクエストを送信し、その応答をもとにユーザーに返答させる手順をまとめます。実際の例として、Alexaに直近のバス時間を教えてもらうシステムを構築しました。
- 公開日:2019/09/27 更新日:2019/09/27
iOSのショートカットでGoogle Apps ScriptにPOSTリクエストを送信する
iOSのショートカット機能を使って指定したGoogle Apps Scriptに任意のデータをPOSTさせたのでその手順をまとめます。
- 公開日:2019/06/02 更新日:2019/06/02
Google Apps Scriptを使って翻訳作業を自動化する手順
Google Apps Scriptは様々な便利な関数が使用可能であり、その中の1つにLanguageAppというクラスがあります。LanguageAppは、自動翻訳を提供するクラスです。この記事では、Google Apps ScriptとLanguageAppを使用して翻訳作業を自動化する手順をまとめます。
- 公開日:2018/10/31 更新日:2018/10/31
Google Apps Scriptをウェブアプリケーションとして公開する手順
Google Apps Scriptで作成したコードをウェブアプリケーションとして公開する手順をメモします。
- 公開日:2018/10/08 更新日:2018/10/08
ユーザの投稿メッセージに応答するSkypeボットをGoogle Apps Scriptで作成する手順
Skypeにてユーザからボットのへの投稿メッセージに応じて適当なメッセージを送り返すボットをGoogle Apps Scriptで作成します。ここではあらかじめ決めたメッセージを返すだけですが、この記事での内容を元に色々な応用に使えると思います。