🧠たうこののうみそ

[GAS]シートで利用可能なカスタム関数をGASで定義しよう

—

by

たうこ
in

GAS上に実装されたトップレベル(グローバルスコープ)の関数は特定の条件を満たした場合、特に設定しなくてもシート上で使用する事が可能です(以下カスタム関数と呼びます)。
本記事では、GAS上に定義したカスタム関数の使用方法を解説します。

前提

GASでカスタム関数を実装する場合、出来ることと出来ない事があるため、以下の点をあらかじめ押さえておくと良いでしょう。

  • setValue, setFormulaなどのセルへの書き込みはエラーになります
  • urlFetchAppなどで外部サイトやAPIの情報を取得することは可能(スクレイピングが可能)
  • 30秒でタイムアウトになる
  • ダイアログの表示やカスタムメニューの追加などUIの変更は不可

基本的な実装

本項では、カスタム関数の基本的な実装を説明します。

①引数なしで値のみ返す関数

例えば今日の日付をYYYY/MM/DD形式で返すgetToday関数を実装した場合以下のようになります。

const getToday = () => {
  const today = new Date()
  const year = today.getFullYear()
  const month = (today.getMonth() + 1).toString().padStart(2, '0')
  const day = today.getDate().toString().padStart(2, '0')

  return `${year}/${month}/${day}`
}

これをスプレッドシートのセル内で関数を呼び出すと、「2024/09/15」のようなYYYY/MM/DD形式の文字列がセルに表示されます。

このように引数なしの関数も定義する事が可能です。

②引数を受け取って処理結果を返す関数

次に引数を受け取って、関数内部で処理を行った結果を返す関数を定義してみます。

引数name, ageを渡すと挨拶文を返す、greeting関数を実装してみましょう

const greeting = (name, age) => {
  return `私の名前は${name}です。年齢は${age}です。`
}

シート上で「=greeting(“田中”, 28)」と入力すると「私の名前は田中です。年齢は28です。」と表示されます。

関数といえば通常このようなものをイメージされると思います。

カスタム関数の便利な使い方

本項では応用編として、カスタム関数を実際に業務で使用する場合などにどのように活用しているか具体例を提示して説明します。

在庫マスタからIDをキーに在庫情報を返す関数

通常シート上で他シートのデータを表引きする場合、VLOOKUPなどの関数を使用すると思いますが、可読性が悪いという問題があります。

またVLOOKUPでは検索値は必ず参照元データ範囲の一番左側にする必要があるため、データによってはそもそも使う事が難しい場合もあります(INDEXとMATCHを組み合わせたテクニックを使えば回避可能ですが、さらに可読性は悪くなります)。

それらの問題を解消するため、カスタム関数を別途定義する事で可読性の高い数式にする事が可能です。また内部はGASなので基本的にデータがどのような形式でも対応する事が可能です。

例として以下のような在庫マスタがあるとします。

在庫IDをキーにその商品の在庫数を取得したい、というような状況は結構あると思います。

これをカスタム関数で定義してみましょう。

const getStockById = (targetId) => {
  const sheet = SpreadsheetApp.getActive().getSheetByName('在庫マスタ')
  const values = sheet.getDataRange().getValues().slice(1)

  // targetIdと在庫IDが一致するデータを検索
  const foundValue = values.find(([id, _, stock])=>id === targetId)

  // 存在しないIDの場合は0を返す
  if (foundValue === undefined) return 0

  // 存在した場合は在庫数を数値で返す
  return foundValue[2]
}

セル上で「=getStockById(“I-0025”)」と入力すると、結果として在庫IDが「I-0025」である商品の在庫数97が表示されました。もちろん「=getStockById(A2)」のように引数にセル参照を渡すことも可能です。

このように既存の関数と同じ動きをカスタム関数で再定義する事で、何をしている数式なのかがかなり判別しやすくなります。

また在庫マスタのカラム位置などが変更になった場合でもカスタム関数側の実装を修正するだけで済みますので、管理面での恩恵もあります。

ちなみにカスタム関数名は日本語でも可能なので、上記関数名を「在庫IDから在庫数検索」みたいなわかりやすいネーミングにする事で、数式に不慣れな人でも使用方法をイメージ出来ると思います。

最後に

今回はスプレッドシート上で使えるカスタム関数について解説をおこないました。

実はGoogle スプレッドシートに「名前付き関数」という機能がリリースされており、その機能を使用する事でGASの理解が無くても比較的簡単にユーザー定義の関数が作れるようになりました。

あくまで複合的な数式を一つにまとめる関数を作る機能のため、GASで実装するカスタム関数よりも自由度が下がりますが、興味がある方は試してみてもいいと思います。

それではグッナイ👋