Googleスプレッドシート GAS シート名を変更するとたびに指定セルに反映させたい。

Question

Googleスプレッドシート GAS で シート名を変更するたびに指定セルにシート名を取得することができません。
基本を理解してないので恥ずかしいですが教えていただきたいです。

function sheetname() {
return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
}

関数を埋め込んだ初回のみ、シート名を取得してくれるのですが再度シート名を変更した時に取得したいです。
トリガーを設定してもダメだったので、どのような考え方をすれば解決できるか教えていただきたいです。

宜しくお願いします。

Answer 1

残念ながら、「シート名の変更」をきっかけにカスタム関数を実行することはできません。

コンテナバインドスクリプト(個別のスプレッドシートに紐付いたスクリプト)に記述した関数をスプレッドシートで利用する「カスタム関数」は、関数の引数に記述されたパラメーターの値が変更されたときにだけ再実行されるように作られています。それまでは、以前の値をキャッシュして使用します。

たとえば、どこかのセルに=myFunction(A1)みたいな式が設定されていると、このセルのカスタム関数myFunctionが実行されて計算結果が更新されるのは、A1セルの値が変化したときだけなのです。

それなら、「GASを使わずにシート名を得てカスタム関数で参照すればいい」と考えるところですが、これも現状では無理ですね。
たとえば、Excelのように=CELL("filename", A1)として(ファイルパスとともに)シート名を得ることはできません。GoogleスプレッドシートのCELL関数の第1引数に指定できるのは、ADDRESS, COL, CONTENTS, PREFIX, ROW, TYPE, WIDTHに限定されています。

いっそのこと、「シート名の変更に関係なく変化する値をカスタム関数で参照すればいい」と考え、NOWやRANDOMをカスタム関数の引数に直接指定したり、それらを使ったセルを参照したりしても、結果は#ERROR!となって、「この関数は、NOW()、RAND()、RANDBETWEEN() を含むセルは参照できません」と表示されます。

---

というわけで、次善の解決策としては、「値が変更されるセルをカスタム関数で参照する」です。たとえば、A1セルでもどこでもいいので、セルに設定する数式中のカスタム関数の引数に指定し(カスタム関数の側ではそれを使用する必要はありません)、そのセルを手動で書き替えると、カスタム関数を使っているセルがいっせいに再計算されます。

Comments

[airspook3677]

有難うございます。大変に良くわかりました。

シート名変更時に直接取得できないのは残念ですが、必ず最初に値を入力するセルがあるので対応します。

色々と調べましたが解らなくて困ってました。できるできないの判断がつくと先に進めるので助かりました。

本当に有難う御座いました。これからもよろしくお願いします。