GASでスプレッドシートのセルの書式を文字列に設定する

GAS 便利ですよね。
スクリプトでサクッとかける上にもともと拡張性の高いスプレッドシートなのでちょっとした実装でも良いものが作れます。
スプレッドシートなんかのインタフェースはエンジニア以外の人にも馴染み深いのでコストをかけて画面系を実装するよりも適している場合がよくあります。

なのですがGASを利用する際に正しくAPIやその動作を理解していないと、非常に重い動作になってしまうことがあります。
テストしてみたらループ処理なんかでずーっと時間がかかってとまらないスクリプトなんてこともあります。

今回GASでスプレッドシート上のセルの書式を文字列にできないかというところを試行錯誤にして改善してみたので、一つのパフォーマンス改善のアプローチとして参考にしてもらえればと思います。

やりたいこと

具体的にやりたいことはシンプルで、セルの値の先頭に + を入力したいということです。しかしそのままやろうとするとこれがうまくいかない。
デフォルトの状態ではスプレッドシートは先頭に+を認識すると値を数式であると認識して、展開しようとします。
実際には文字列ですので式の値が不正でありエラーとなります。

エクセルですと書式設定で テキスト なんていう書式があるようですが、現在のスプレッドシートの書式にはこのケースを許容してくれる書式は無いようでした。

ではどうするかというと先頭にシングルクォート(‘)を打ち込みます。
こうすることでセルに入力された値をテキストだと認識してくれます。

問題はこれを大量のセルに対して実施したいということ。
ちまちま一つ一つのセルを手作業で編集していたのでは気が遠くなります。

GASを使って解決してみた・・・

始めに単純な思いつきで実装してみます。
レンジに関してはあまり深く考えないで良いです。

var valueRange = sheet.getRange('A1:A'); // A列に存在するデータに対して実施
for (var i=0; i<valueRange.getNumRows(); i++) {
  var cell = valueRange.getCell(i, 0);
  var value = cell.getValue();
  if (value == "") {
    continue;
  }
  cell.setValue("'" + values);
}

余裕のある人は一度実装してみてください。
動かしてみるとわかるのですがこの実装、めちゃめちゃ遅いです。

何故かと言うと getValue, setValue というAPIをforループ内で毎回呼び出しているからなんですね。

スプレッドシートは基本的にサーバ側で反映されたデータがブラウザに反映されます。
ですので上記のスクリプトでデータを書き換えている箇所は、ブラウザ上に表示されているデータを書き換えているのではなく実際にはGoogleのサーバ上にあるスプレッドシートのデータを編集しているような設計になっています。
で、ブラウザは逐次その反映されたデータを更新して表示しているということになります。
なのでループ毎に getValue, setValue を呼び出すことがものすごい遅延につながります。

ここまでの話で想像できるかと思いますが、GASの動作を高速化する際にまず考えることは GASのAPI呼び出しをなるべく少なくする ということです。
スプレッドシートを取り上げますがその他のGServiceでも同じことだと思います。
まーRDBでもなんでもバッチ処理にする。ということは基本ですね。。

この点について留意して最適化したコードを実装してみましょう。

処理を最適化する

下記が最適化されたコードになります。

var valueRange = sheet.getRange('A1:A');
var values = valueRange.getValues();
for (var i=0; i<valueRange.getNumRows(); i++) {
  if (values[i][0] == "") {
    continue;
  }
  values[i][0] = "'" + values[i][0];
}
valueRange.setValues(values);

こちらも実行していただけるとわかるのですが、一瞬で実行が完了します。
見ての通り API呼び出しを getValues, setValues にすることでたったの二回に減らすことができています。

APIマニュアルを良く読んで自分がやりたいことが他の方法で実現できないか？とよく考えると意外と様々なAPIが存在しているのでぜひ確認してみてください。
今回はこの方法で目的を達成する事ができました。
それでは。