【GAS】スプレッドシートの表示形式で数字フォーマットを変更する方法(setNumberFormat)

Google Apps Script(GAS)でスプレッドシートに数値を書き込む際に、セルの表示形式をパーセントや通貨などのフォーマットを設定したい場合があります。

そこで、スプレッドシートのセルの数字フォーマットをGASで設定する方法を解説します。

セルの数字フォーマットをGASで設定したい

Google Apps Script(GAS)では様々な処理結果をスプレッドシートのセルに書き込むことが多いです。

そんな時に書き込んだ値を適切な数値形式で表示したいニーズがあります。

例えば、割合を小数点で出力した場合には、パーセント(%)表示にしたいですし、手数料の金額や合計金額を算出した結果の場合には、金額を表す数字フォーマット「\○○」といった形です。

また、GASでスプレッドシートに書き込むのは数字だけど、数値型としてではなく、文字列として認識してほしい場合があります。

こんなケースでは、先にスプレッドシート側の出力先のセルの表示形式を、希望の数字フォーマットにあらかじめ変換しておきます。

しかし、実はGASのスクリプトでもセルの表示形式を任意の数字フォーマットに設定が可能です。

GASのsetNumberFormatで数字フォーマット設定

Google Apps Script(GAS)で、スプレッドシートのセルの表示形式を設定するメソッドとして、「setNumberFormat」が用意されています。

Range.setNumberFormat(“設定したい数字フォーマット")

スプレッドシートのシートに対し、getRangeメソッドなどで取得できるセル範囲を表すRangeオブジェクトでsetNumberFormatメソッドを実行します。

実行する際には、設定したい数字フォーマットを表す文字列(後述)を引数に指定します。

スプレッドシートの数字フォーマット代表例

スプレッドシートの数字フォーマットで、メニューから選択して設定できるものとして、代表的な10種類を紹介します。

  1. 自動："general"
  2. 書式なしテキスト："@"
  3. 数値："#,##0.00″
  4. パーセント："0.00%"
  5. 指数："0.00E+00″
  6. 通貨："[$¥-411]#,##0.00″
  7. 通貨(端数切捨て)："[$¥-411]#,##0″
  8. 日付："yyyy/MM/dd"
  9. 時間："H:mm:ss"
  10. 日時："yyyy/MM/dd H:mm:ss"

例えば、セルの表示形式をパーセントにしたい場合は、設定したいセルをgetRangeで指定し、setNumberFormat(“0.00%")を実行します。

このように対象のセル範囲を指定したRangeオブジェクトに対し、setNumberFormatメソッドで上記の引数を指定することで、セルの数字表示形式を変更可能です。

また、数字のみだけど、文字列として認識してほしい場合に「書式なしテキスト」を利用することもあります。

数値フォーマットを設定するGASサンプルコード

Google Apps Script(GAS)でスプレッドシートのセルに対し、数字フォーマットを設定する方法を解説します。

スプレッドシート内の特定セルにパーセント(%)の表示形式に、数字の部分を文字列として扱うために「書式なしテキスト」を設定するGASサンプルコードです。

function numFormat() {
//アクティブなスプレッドシートを読み込み
let mySheet = SpreadsheetApp.getActiveSheet();
//小数点の値をA1セルに書き込み
mySheet.getRange(1,1).setValue("0.0234");
//A1セルの表示形式をパーセントに設定
mySheet.getRange(1,1).setNumberFormat("0.00%");
//数字とピリオドで構成されたユーザーIDの2次元配列を定義
let userId = [
[123.456789],
[456.123789],
[789.654321]
];
//定義したユーザーIDの2次元配列をB1:3セルに書き込み
mySheet.getRange(1,2,3).setValues(userId);
//B1:3セル範囲の表示形式を「書式なしテキスト」に設定
mySheet.getRange(1,2,3).setNumberFormat("@");
}

GASでsetValue(s)メソッドでセルに書き込んだ後、setNumberFormatでそれぞれに数字フォーマットを指定します。

実際にGASサンプルコードを実行させると、きちんと想定した形でパーセントと書式なしテキストが各セルに適用されています。

このようにGASのスクリプト処理の中で、セルへの出力だけでなく、セルの表示形式の調整が可能です。

setNumberFormatの引数パラメータの調査方法

なお、setNumberFormatの引数に指定するパラメータの文字列が分からないケースも多いです。

デフォルトの数字フォーマットはまだしも、カスタム数値形式の設定方法が難しいです。

そこで、引数パラメータの調査方法についても紹介します。

引数パラメータを調べたい場合には、まずスプレッドシートでその表示形式を特定範囲のセルに設定します。

D1セル～D9セルにC列に記載した数字フォーマットを設定しています。

さらにD9列にはカスタム数値形式として「○○千円」という表示形式になっています。

このように調べたい表示形式のセルに対し、GASスクリプトでgetNumberFormatメソッドを実行します。

上記のGASサンプルコードをgetNumberFormatの実行結果が、実行ログに出力されます。

各種の数字フォーマットのパラメータに加え、○○千円といった、カスタム数値形式のパラメータも確認できます。

このようにgetNumberFormatメソッドで確認したパラメータをsetNumberFormatの引数に指定すれば、カスタム数値形式も簡単に設定可能です。

シート挿入時にsetNumberFormat活用

setNumberFormatの活用ケースですが、GASスクリプトでシートを挿入するケースで利用されます。

既存のシートに値を書き込む場合、事前にスプレッドシート上で、数字のフォーマット設定が可能です。

しかし、スクリプトで新たなシートを挿入する処理では、そうしたスプレッドシートのGUI操作ができません。

そのため、GASスクリプト処理でinsertSheetを使うケースで、数値フォーマットを設定したい場合に、setNumberFormatを活用しましょう。

setNumberFormatとsetNumberFormatsの違いは？

セルの数字の表示形式を設定するメソッドには、①setNumberFormatに加え、②setNumberFormatsというもう1つあります。

setNumberFormatとsetNumberFormatsの2つの違いは、引数の指定方法です。

①setNumberFormatは、String型のデータを引数に指定し、前述したようなフォーマットの文字列を設定します。

それに対し、②setNumberFormatsは、2次元配列が引数に指定する定義になっています。

あらかじめ、シート内の各セルの数字形式をいろいろなフォーマットで指定したい場合に、セル位置に対応した2次元配列でフォーマット文字列を定義します。

そして、setNumberFormatsで定義した2次元配列を引数にして実行すれば、セルの数字形式を事細かに設定可能です。

ただ、そこまで細かな設定をするケースは少なく、また2次元配列を定義する手間も発生するため、基本的に使うのはsetNumberFormatとなります。

まとめ・終わりに

今回、Google Apps Script(GAS)でスプレッドシートのセルの数字フォーマットを設定する方法を紹介しました。

GASのスクリプトで実行した結果のままだとパーセント表示したい部分が小数点表示になったりします。

そこで、setNumberFormatメソッドを使えば、GASでセル表示形式の数字フォーマットが設定可能です。

いくつか代表的な数字フォーマットを紹介していますが、カスタム数値形式でもgetNumberFormatメソッドで簡単に確認できます。

GASスクリプトでシートを新規挿入するケースなどでは処理の中で、セルの表示形式を設定したほうが便利なケースも多いです。

ぜひ、GASでスプレッドシートのセルの表示形式を設定し、人間が見やすいフォーマットでデータを出力しましょう。

GoogleAppsScriptGAS,setNumberFormat,スプレッドシート,数字フォーマット

Posted by yamamoto