GAS

【GAS】数式と値を区別して取得する方法

GASでスプレッドシートのセルからデータを取得する際に、値と数式で使用する関数が異なり使い分けが必要です。

値の取得getValues()
数式の取得getFormulas()

決まったセルの位置にあらかじめ値が入っているのか、数式が入っているのかがわかっている場合は問題がないですが、混在している、または広範囲のセルからデータを取得する場合に「自動で値か数式化を判断してくれたらいいのに・・・！」と思ったことはないでしょうか。

今回は、そんな「数式と値を区別して取得する方法」をご紹介します。

数式と値を区別して取得する関数

さっそく、プログラムを紹介します。そのままコピペで使えます。

function getValuesOrFormulas(range) {
  let valueOrFormulas = range.getValues()
  let tempFormulas = range.getFormulas()

  for (let column = 0; column < valueOrFormulas[0].length; column++) {
    for (let row = 0; row < valueOrFormulas.length; row++) {
      if (tempFormulas[row][column].length !== 0) {
        valueOrFormulas[row][column] = tempFormulas[row][column]
      }
    }
  }

  return valueOrFormulas
}

使用例

このようなシートがあるとします。

関数表示にするとC列に数式が埋め込まれていることがわかります。

データがあるA3:C5の範囲を一発で取得出来たら便利ですよね。

実際にコード例を見てみましょう。

function main() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  const sheet = spreadsheet.getSheetByName("シート1")
  const range = sheet.getRange(3, 1, 3, 3)

  const valuesOrFormulas = getValuesOrFormulas(range)
  console.log(valuesOrFormulas)
}

[ [ '社員 太郎', 300000, '=B3*12' ],
  [ '社員 次郎', 400000, '=B4*12' ],
  [ '社員 三郎', 250000, '=B5*12' ] ]

このように値と数式が判別されて取得されていることがわかります。引数にデータを取得したいRange(範囲)を渡すことで、このような結果を得ることができます。

コードの解説

目的とする動作は「数式と値を区別する」ことです。どのようにして、この二つを区別しているのでしょうか？

いま一度、関数を見てみましょう。

function getValuesOrFormulas(range) {
  let valueOrFormulas = range.getValues()
  let tempFormulas = range.getFormulas()

  for (let column = 0; column < valueOrFormulas[0].length; column++) {
    for (let row = 0; row < valueOrFormulas.length; row++) {
      if (tempFormulas[row][column].length !== 0) {
        valueOrFormulas[row][column] = tempFormulas[row][column]
      }
    }
  }

  return valueOrFormulas
}

重要な部分はこのif文です。

if (tempFormulas[row][column].length !== 0) {

getFormulasはセルのデータが数式でない場合、空文字を返却する仕様があります。関数の冒頭で取得したtempFormulasの値を確認して空文字でなければ、数式が格納されていると判断して数式を取得しています。

その操作をすべてのセル(配列)で行い、値と数式をそれぞれ格納していきます。

まとめ

今回は、「数式と値を区別して取得する方法」をご紹介しました。

紹介した関数自体は簡単な実装ですが、大量のデータを扱うスプレッドシートでは大きな効果を出す便利な関数です。ぜひ使ってみてくださいね。