GoogleスプレッドシートにGASでExcelマクロを移行してみた

こんにちは、ブロックチェーン事業部の佐野です。

今回はi18nで読み込む言語ファイル(JSON)を作成するExcel VBAのマクロをGoogleスプレッドシート＋GASに移行したお話です。

弊社ブログで何度か登場していますが皆さんはGAS知っていますか？
実は私はGASという言葉は知っていましたがどのような言語か全く知りませんでした(汗)
そもそもGASってなんだよって方もおられると思いますので簡単におさらいしてみたいと思いいます。

GASとは

「Google Apps Script」の略称で、GmailやGoogleカレンダー、Googleマップなどといった、Googleのツールやサービスを連携して利用するためのプログラミング言語で、JavaScriptがもとになっており、「スクリプト言語」の中でも、「比較的、簡単に習熟できる」という利点があるとのことです。
（参考：【Google Apps Script入門】GASでできることや活用方…｜Udemy メディア）

開発自体はブラウザ上で行うことができます。
Google謹製のCLIツールClaspを利用することで使い慣れたエディタで作成したプログラムを反映させることも可能なようです。
今回はブラウザでの開発を選択しました。

言語ファイルの作成の流れ

スプレッドシートを読み込み、キーによる階層を持ったオブジェクトを作成する
オブジェクトをテキストに変換
Google Drive にテキストファイルを出力

GAS API パフォーマンス

さて実際にスプレッドシートを読み込んで見ました。

const value = Sheet.getRange(row, column).getValue()

ひとセル、ひとセルをループで読み込むと…ん！？遅い…
Excelだと一瞬で読み込めるシートの内容が５分経っても読み込めない…

ちょっと調べたらAPIの処理が遅いようで、呼び出し回数を極力減らすと良いらしい。（参考：Google Apps Scriptのスプレッドシート読み書きを格段に高速化をする方法）

ということで、一度に対象範囲のシート内のデータを読み込むことにしました。

const values = Sheet.getRange(startRow, startColumn, endRow, endColumn).getValues();

startRow	開始行
startColumn	開始列
endRow	終了行（Sheet.getLastRow() を設定すると使用済みの最終行の行番号が設定可能）
endColumn	終了列（Sheet.getLastColumn() を設定すると使用済みの最終列の列番号が設定可能）

この一文でシート内の全てのセルのデータを配列として読み込むことができるため、この後にAPIを使用してシートのセルを読む必要がないので、格段に処理は早くなります。

あとは values[0, 0] のように値を取得すればセルのデータが取得できます。
一点だけ添え字の注意が必要で、Row=1, Column=1 のセルのデータは、values[0, 0] に入っています。取得した範囲のセルが配列の添え字０番目から詰められていることです。

APIは呼べば呼ぶだけ処理が遅くなりますので、まとめて処理できるAPIがある場合はそちらを利用しAPIを呼ぶ回数を少なくするように心掛けましょう。

キーの階層処理

f:id:teco_sanoh:20200727011434p:plain

上図のようなシートを読み込み、階層１～５の階層でデータをオブジェクトに取り込む必要があります。このシートへの登録者の手間を少なくするため、一行上と同じ階層の部分は入力を省略可能とします。（A2～A6は空白ですがA2と同様に”title”という一階層目となります。B4はB3と同様に”disp-2”、B6はB5と同様に”disp-3”となります。）

この時、ひとつ前の行の階層の値をどう空白のセルの部分に当て込むのに悩みます。

私は、ひとつ前の行の値を配列でキープし、現在の行の値がある階層に到達するまで空白の部分に複写することとしました。

例）２行目の読み込みの場合

ひとつ前の行の値：[ ’title’, ‘disp-1’, ‘’, ‘’, ‘’ ]
現在の行：[ ‘’, ‘disp-2’, ‘disp-2-1’, ‘’, ‘’ ]
当て込み後の現在行：[‘title’, ‘disp-2’, ‘disp-2-1’, ‘’, ‘’]

この場合は、現在の行の添え字0番目は空白なので、ひとつ前の行の値’title’を当て込み、現在の行の添え字1番目で値があるので当て込み処理を終了します。

もっと簡単な方法がありましたら是非ご連絡頂けたらと嬉しいです。

VBAではできないJSON出力

VBAにはJSON形式のテキストを出力する機能は標準では用意されていません。外部のDLL（ダイナミックライブラリファイル）を利用すればできるかも知れませんが、今回の移行元のVBAマクロでは、オブジェクトをループしながら地道にテキストを作成していました。

GASの利点としては今では多くに使用されている JavaScript ですのでJSON操作のモジュールが標準で用意されています。
JSONファイルに出力したいイメージでオブジェクトを作成さえできれば、たった一文でJSON形式のテキストを作成可能です。

const text= JSON.stringify(object, undefined, 2);

ここで注目したいのは第３パラメータの2です。
ここに値を設定すると2スペースでのインデントを自動で行ってくれます。またプロパティ毎に改行も自動で行ってくれます。

出力した日本語用のテキストのイメージは以下のようになります。

{
  "title": {
    "disp-1": "画面１",
    "disp-2": {
      "disp-2-1": "画面２－１",
      "disp-2-2": "画面２－２"
    },
    "disp-3": {
      "disp-3-1": "画面３－１",
      "disp-3-2": "画面３－２"
    },
  }
}

パラメータにより出力するテキストの体裁を整えてくれる機能があるなんで知りませんでしたとても便利な機能ですね。

今回は標準で用意されているJSON操作のモジュールを利用しましたが、ネット上に無数にある拡張の外部モジュールも利用できるようです。

次に何かを作る時には外部モジュールも使用する機会があったら勉強してみたいと思います。