※本記事はプロモーションを含みます
目次
Google スプレッドシートを運用していると、セルに表示されている計算結果の数値ではなく、そこに埋め込まれている計算式そのものをスクリプト側で読み取りたい場面が頻繁に出てきます。なぜなら売上集計表や請求管理シートなどで、現場の担当者が誤って計算式を直値で上書きしてしまっていないか巡回チェックしたり、月次更新のタイミングで計算式の設定状態をバックアップしたりする用途があるからです。ただしこの記事では、GASでセルの数式を取得する基本的な手順から、単一セルと範囲指定での挙動の違い、値取得用メソッドとの混同によるトラブルを防ぐ書き方までを詳しく掘り下げていきます。
getValue()を呼んで計算結果しか取れない罠とgetFormula()の役割
スプレッドシートのスクリプトを書き始めた人がまず遭遇しやすいのは、セルの中身を取り出すつもりで一般的な取得メソッドを呼び出し、計算式ではなく計算が終わったあとの数値や文字列が返ってきて途方に暮れる現象です。というのは普段のデータ処理ではセルの評価結果を扱うことが大半であるため、直感的に値取得用の処理を記述してしまいがちですが、GASでセルの数式を取得する目的においては、この挙動が根本的な原因となって意図した判定を行えなくなります。
スプレッドシートの内部構造において、セルは表面上に描画されている表示値と内部で保持している実際のデータ値とそのデータを算出するために定義された計算式という複数の属性を別々に管理しています。なので普段よく使われる値取得のメソッドは、計算式が評価された後の最終結果を JavaScript の数値型や文字列型や日付オブジェクトなどに変換してスクリプト側に渡すように作られています。ただしセルに「=SUM(A1:A10)」と入力されていて結果が「1500」だった場合、値取得を実行しても返ってくるのは数値の「1500」だけであり、SUM関数が使われているかどうかをスクリプトから検知することは一切できません。
数式そのものを文字列データとして手元に取り込みたいときは、セルオブジェクトに対して専用の取得命令を発行する必要があります。というのはこの目的で使用するのが getFormula() メソッドだからです。ただしこのメソッドは、セル内に保存されている数式定義を解析し、「=」記号を含んだ生のテキストとしてそのままスクリプトに返してくれます。なので先ほどの例であれば、数値の1500ではなく「”=SUM(A1:A10)”」という文字列が得られるわけです。ただしこの戻り値の違いを明確に意識できていないと、後続の文字列検索やパターンマッチングが正しく動作せず、予期しないバグを抱え込む原因になります。
ただし計算式が設定されていないセルに対して getFormula() を実行したときの挙動も重要なポイントです。なので数式が入っていないプレーンな数値や手入力のテキストが入っているセルにこの命令を投げても、エラーでスクリプトが止まるわけではありません。というのはその代わり、空の文字列、すなわち「””」が返却されるからです。ただしこの仕様を把握していないと、値が入っているはずなのに空文字が返ってきたからデータが存在しないと誤認してしまい、データ消去処理などを誤って走らせてしまう恐れがあります。なので値の有無と数式の有無は完全に独立した状態であることを頭に入れておきましょう。
単一セルとセル範囲で変わる数式取得の基本構文
単一のセルから数式を抜き出す処理は、対象のセル範囲を特定したうえで getFormula() を呼び出すという非常にシンプルな構造になっています。なので対象となるシートを取得し、アドレスを指定して範囲を確定させ、そこから数式文字列を抜き出す流れを押さえておけば、さまざまな監査処理やデータ抽出処理に応用できます。
どのようなタイミングでこの処理を走らせるかというと、代表的なのは入力規則やテンプレートの崩れを監視する定期バッチや、ユーザーが編集した直後に特定セルが数式のまま残っているかを確認するトリガー処理です。というのはたとえば、合計欄の数式が消去されていないかチェックするような場面で活躍するからです。
function checkSingleFormula() { ... }このコードを実行すると、アクティブなシートのC1セルに設定されている数式がログに出力されます。なのでもしC1セルに「=A1+B1」と入力されていれば、ログにはそのまま「=A1+B1」という文字列が表示されます。ただしここで注意が必要なずれのポイントとして、対象セルが空白であったり、手入力された固定値であったりする場合には、ログに何も出力されず空文字が記録される点です。というのは数式が存在しないこと自体を検知したい場合は、取得した文字列の長さが0であるかどうか、あるいは空文字と一致するかどうかを判定条件に組み込む必要があるからです。
ただし実務では1セルずつ個別に取得するケースよりも、列全体や表全体といった複数セルの数式をまとめて調べたいケースが圧倒的に多くなります。なのでその際に getFormula() をループ処理の中で何度も呼び出してしまいますと、Google Apps Script とスプレッドシート基盤との間で通信が頻繁に発生し、スクリプトの実行時間が著しく増大して実行時間上限に引っかかる原因になります。というわけで複数のセルを対象とする場合は、単数形のメソッドではなく、複数形である getFormulas() を使用するアプリ処理にしましょう。
getFormulas() は、指定した範囲に含まれる全セルの数式を2次元配列として一括で返してくれます。というのは縦方向の行と横方向の列に対応した配列構造になっており、数式が設定されているセルには「”=SUM(B2:B10)”」のような文字列が入り、数式が入っていないセルには空文字「””」が格納されるからです。なのでこの2次元配列を受け取ってメモリ上で走査することにより、APIの呼び出し回数を最小限に抑えつつ、シート全体の数式設定状況を高速に読み解くことが可能になります。
空白や生データが混在するシートで空文字を踏むケースの判定
実際の業務シートを走査すると、数式が入っているセル、手入力の数値が入っているセル、完全に何も入力されていない空セルが混在しているのが普通です。なのでこのような環境で getFormula() や getFormulas() を使って数式を取得していくと、数式が設定されていない箇所はすべて等しく空文字「””」として扱われます。ただしここで発生しやすいのが、手入力データが存在しているのに数式がないセルと、そもそも何も入力されていないセルを同一視して処理を進めてしまうという判断ミスです。
たとえば売上管理シートの各行をチェックして、計算式が消えてしまっているセルを検出したいとします。ただしもし getFormula() だけを見て返り値が空文字だから異常と判定してしまうと、まだデータが登録されていない未来の日付行や、意図して空欄にしている備考欄などまで全て数式破損としてアラートが上がってしまいます。なのでこれを回避するためには、値を取得する getValue() や getValues() と組み合わせて、セルの状態を多面的に判定するロジックを組みましょう。
具体的には、まずそのセルに何らかの入力データが存在するかどうかを確認します。というのはセルの値が空文字や null ではない、すなわち何らかの値が表示されているにもかかわらず、getFormula() の結果が空文字である場合、そのセルは計算式ではなく直接入力された固定値で埋められていると断定できるからです。逆に値も空文字であり数式も空文字であるならば、そのセルは単なる未入力の空白セルとして無視するのが適切な振る舞いになります。なのでこのように数式取得メソッド単体で完結させるのではなく、値取得メソッドと突合させることで、入力ルールが崩れたセルだけを正確に絞り込みましょう。
ただし数式文字列のフォーマットに関する落とし穴にも配慮が必要です。というのは取得される数式文字列は、スプレッドシート上で設定されている言語環境や関数の記述形式に影響を受けるからです。なので基本的に関数名は英大文字で返され、先頭には必ず半角のイコール記号が含まれます。ただしスクリプト内で特定の関数が使われているかを調べる際は、文字列の先頭が「=」で始まっていることを確認したうえで、大文字小文字の違いによる不一致を防ぐために toUpperCase() を適用してから判定するなどの前処理を挟むように確認しましょう。
参照ズレやR1C1形式の差異を防ぎながら監査ログを作るアプローチ
数式をシート間でコピーしたり、別のセルへ再適用したりする目的で数式を取得する場合、A1形式の表記ゆれと相対参照のズレが深刻な問題を引き起こします。というのは通常の getFormula() で取得できる数式はA1形式と呼ばれる表記法であり、セル番地が列のアルファベットと行の数字で表現されているからです。なのでこの形式は人間にとって読みやすい反面、スクリプトで別の場所にそのまま貼り付けようとすると、参照先の相対的な関係が狂ってしまうという致命的な欠点があります。
たとえばC2セルに「=A2*B2」という計算式が入っていたとします。なのでこの数式を getFormula() で取得すると「”=A2*B2″」という文字列がそのまま手に入ります。ただしこの文字列をそのままC3セルに流し込んでしまうと、C3セルでも「=A2*B2」が計算されてしまい、本来期待される「=A3*B3」という1行ずれた参照関係にはなりません。なので手動でコピー＆ペーストしたときのように参照先を自動で追従させたい場合は、単純に getFormula() で取った文字列をそのまま別セルに流し込むのではなく、相対参照を正しく維持できる手段を考慮しましょう。
数式の構造をテンプレートとして保持したり、数式の整合性を厳密に比較監査したりする際には、セル番地を絶対的な位置関係ではなく相対的な距離で表現するR1C1形式の活用が有効な選択肢になります。というのはGASには標準で getFormulaR1C1() や getFormulasR1C1() というメソッドが用意されており、これらを使用すると対象セルから見て何行上何列左といった相対位置に基づいた数式表現を取得できるからです。たとえば同じ行の2列左と1列左を掛け合わせるという構造であれば、どの行であってもまったく同一の数式文字列として取得できるため、行ごとの数式パターンが崩れていないかを比較検証する監査ログの作成において非常に強力になります。
ただし数式文字列内に含まれるシート名にスペースや特殊記号が含まれている場合のシングルクォーテーションの有無や、外部スプレッドシートを参照する IMPORTRANGE 関数の引数文字列など、エスケープが必要な要素が含まれている場合にも注意が必要です。というのは取得した数式を正規表現などで解析しようとすると、参照している別シート名の揺らぎによって意図しないマッチング失敗が起こることがあるからです。なので数式の中身を細かく分解して検査するスクリプトを作成する際は、数式の表記ゆれを想定したテストケースをあらかじめ用意しておきましょう。
テンプレート反映や数式バックアップ時に処理速度を落とさない組み方
実務の大規模なシートにおいて、数百行から数万行に及ぶ計算式をバックアップしたり、月次の初期化処理としてテンプレートの数式を一括流し込みしたりする運用では、処理の組み立て方がスクリプトの実行パフォーマンスを左右します。なのでGASでセルの数式を取得する処理を大規模データに対して行う場合、セル単位の個別アクセスを徹底的に排除したバッチ指向の設計にしましょう。
最も避けるべきパターンは、行数分のループを回し、その中で毎回 getRange() と getFormula() を実行して1セルずつスプレッドシート基盤に問い合わせを行う構造です。というのはスプレッドシートのAPI呼び出しはネットワーク越しのオーバーヘッドを伴うため、1回の呼び出しにコストがかかるからです。なので1000行のデータに対してループ内で個別取得を繰り返すと、それだけでスクリプトの制限時間である6分に到達してしまい、途中で異常終了してしまいます。
高速かつ安定して処理を完結させるためには、対象となるデータ領域全体の Range を一度だけ取得し、getFormulas() で全数式を2次元配列としてメモリ上に一括展開しましょう。なぜならその後、JavaScriptの配列操作を用いてメモリ内で数式の検証や比較や変換をすべて終わらせることができるからです。ただしもし数式の修正や復元が必要になった場合も、修正後の数式を格納した2次元配列をメモリ上で完成させておき、最後に setFormulas() メソッドを使って1回のAPI呼び出しでシート全体へ書き戻すように確認しましょう。
