質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
Google Apps Script

Google Apps ScriptはGoogleの製品と第三者のサービスでタスクを自動化するためのJavaScriptのクラウドのスクリプト言語です。

Q&A

解決済

2回答

10372閲覧

Googleapps scriptで範囲外のセル参照ですというエラーが毎回出る。

ALHIRU

総合スコア11

Google Apps Script

Google Apps ScriptはGoogleの製品と第三者のサービスでタスクを自動化するためのJavaScriptのクラウドのスクリプト言語です。

0グッド

0クリップ

投稿2018/08/02 11:56

編集2018/08/06 02:41

前提・実現したいこと

Googleapps scriptで簡単なコードを書いているのですが、スクリプト冒頭のシートを取得してアクティブセルの列の位置を取得する時に毎回必ずエラーが出て困っています。ただしスクリプト自体は想定した結果を返しているので最後まで走っているようです。

発生している問題・エラーメッセージ

スクリプトはSpreadsheetのセルの値の変更をトリガーにしています。
スクリプトが走るたびに以下のメッセージがメールで送られてきます。

Google Apps Script CheckSheet のエラーの概要
エラー メッセージ                    トリガー
範囲外のセル参照です(行 8、ファイル「Memo」)     change

var SpreadSheet = SpreadsheetApp.getActiveSpreadsheet(); var Sheet = SpreadSheet.getActiveSheet(); var CellColumn = Sheet.getActiveCell().getColumn();//←この行が行8 ```Googleapps script  

試したこと

一応teratailの過去の質問も調べたのですが、「範囲外のセル参照です」はrangeのsortの時にエラーとして出てくるという事象が見つかりましたが、私の場合はメソッドを動かす前から取得の段階でエラーが出ています。
それとも実はもっと下流の行でエラーが出ていて行 8はGoogleapps scriptのシステムの誤認なのでしょうか?

補足情報(FW/ツールのバージョンなど)

コメント下さった方ありがとうございます。
すいません、後出しになってしまいましたが該当の行8だけでは解決しそうにないのでスクリプト全文を公開させていただきます。
このスクリプトをスプレッドシートの値の変更をトリガーにして走らせているので、ご指摘頂いているようなActiveCellが取得できていない可能性は低いと考えております。(例えば時刻で起動するようなトリガーでActiveCellが取得できない可能性はありますが、このスクリプトの場合は現にセルの編集を行った時に走っていますしアウトプットであるNoteへの書き込みもできています。)
本日気付いたのですが、このスクリプトは11~19行で定義してあるようにCheck listというタブ上でしか動かないようにしていますが、実際は他のタブ上でも動いていてログを調べるとRangeCheck = undefinedでも21行から始まるif文が走っているようです。
このあたりに何か構造的な問題があるのでしょうか?よろしくお願いします。

function MemoAsLog() {
//Active

var SpreadSheet = SpreadsheetApp.getActiveSpreadsheet();
var Sheet = SpreadSheet.getActiveSheet();

var SheetName = Sheet.getSheetName();
var CellColumn = Sheet.getActiveCell().getColumn();
var CellRow = Sheet.getActiveCell().getRow();

if(SheetName=="Check list" && CellColumn==9){
var RangeCheck = 1
}
if(SheetName=="Check list" && CellColumn!=9){
RangeCheck = 2
}
if(SheetName=="Check list" && CellColumn==8){
RangeCheck = 3
}

if(RangeCheck == 1||3){
var MyDate = new Date();
var ToDay = Utilities.formatDate(MyDate,'Asia/Tokyo', "yyyy/MM/dd");
var UserID = Session.getActiveUser().getEmail();
var User = UserID.split("@");
var ExistingNote = Sheet.getActiveCell().getNote();
var CellValue = Sheet.getActiveCell().getValue();
Sheet.getActiveCell().clearNote();
if(!ExistingNote){
Sheet.getActiveCell().setNote(ToDay+""+User[0]+""+CellValue);
}
else{
Sheet.getActiveCell().setNote(ExistingNote+"\n"+ToDay+""+User[0]+""+CellValue);
}
}
if(RangeCheck == 2){
Sheet.getActiveCell().clearNote();
if(!ExistingNote){
Sheet.getActiveCell().setNote(ToDay+""+User[0]);
}
else{
Sheet.getActiveCell().setNote(ExistingNote+"\n"+ToDay+"
"+User[0]);
}
}
}

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

macaron_xxx

2018/08/02 12:58

確かにそこでエラーは出なさそうではありますかね。ほかの部分も見てみないとなんともですが。もしかするとactivecellを取得できていない可能性はなくはないのかなと思ってます。
papinianus

2018/08/03 15:49

開いていないSpreadSheetをOpenByIdしてgetActiveCellをしたのですが、エラーになりませんでした(colは1になる模様)。ちなみに空白行も含めて8行目ですよね?
ALHIRU

2018/08/06 02:42

そうです
ALHIRU

2018/08/06 03:00

なるほど、時刻やシートのオープンなどをトリガーにしてActiveCellが無い状態で走らせても1列目を取得するわけですね?
macaron_xxx

2018/08/06 03:40

何回もgetActiveCellをしているので、処理の途中でActiveCellを変更したらおかしくなりそう。
ALHIRU

2018/08/06 11:53

なるほどgetActiveCellは1回だけにしてMyCellなどの変数にしてからそこからメソッドに繋げた方がいいということですね?やってみます。
guest

回答2

0

ベストアンサー

再現させる方法わかったかもしんない!!

あのさ、誰かメモ消去してませんか?
メモ消去すると、↓こういうログが出ました。

[18-08-06 22:39:46:553 JST] 実行を開始しています

[18-08-06 22:39:46:558 JST] SpreadsheetApp.getActiveSpreadsheet() [0 秒]
[18-08-06 22:39:46:559 JST] Spreadsheet.getActiveSheet() [0 秒]
[18-08-06 22:39:46:646 JST] Sheet.getSheetName() [0.086 秒]
[18-08-06 22:39:46:646 JST] Sheet.getActiveCell() [0 秒]
[18-08-06 22:39:46:649 JST] 実行に失敗: 範囲外のセル参照です(行 8、ファイル「activecell139305」)(合計ランタイム 0.09 秒)

いざ書いてみると、「!!」とかやったの恥ずかしくなってきたけど、あたってたらいいなー。期待を込めて"!"2つです!

-- エラー回避について追記

  • エラーの発生について

思い込みで書くとダメですね。嘘を言ってました。訂正してお詫びします。
試行を繰り返したところ、Note編集時には、Sheetは取れるが、getActiveCell()をすると、この時点でエラー。つまり、Note変更時は、activecellがないという特殊モードに入るようです。

  • エラーの回避について
  1. トリガーを変える
  2. イベント引数で判断する
  3. try catchする

あたりが可能かと思います。

  1. 今「値の変更」を使ってらっしゃいますが「編集」トリガーにすると、Noteの変更ではイベントが起動しない ようです 。ようです、と言うのは、私のアカウントが(エラーの出しすぎで?)おかしくなったのか、OnEditという関数名にすると自動的に編集時に呼ばれるという方法では動作するのですが、トリガーのところに編集時をセットしても動作しなくなり、検証できていないためです。

  2. MemoAsLog(ev)のようにすると、evに値の変更時に次のようなイベントオブジェクトが入ります

javascript

1// NOTEの挿入や削除のとき 2{"changeType":"OTHER","source":{},"user":{"nickname":"メールアドレスの@の前?","email":"メールアドレス"},"authMode":{},"triggerUid":"idの数値"} 3// セルの編集時 4{"changeType":"EDIT","source":{},"user":{"nickname":"メールアドレスの@の前?,"email":"メールアドレス"},"authMode":{},"triggerUid":"idの数値"}

このChangeTypeを見て動作をかえることができると思います。
参考として、編集時のイベントオブジェクトは次のようになります(OnEdit(ev)のようにしたときのev)

javascript

1// dをeにかえた。 2{"authMode":{},"range":{"columnStart":3,"rowStart":25,"rowEnd":25,"columnEnd":3},"source":{},"oldValue":"d","user":{"nickname":"","email":""},"value":"e"}

ちなみにこの出力はJSON.stringify(ev)で得たものですが、GASのオブジェクトは出力できないようで、実際にはauthModeやsourceには中身があり、sourceにはspreadsheetが入っています(従ってev.source.getName()とかってやると"check list"が得られるのかもしれない。トリガの動作があやしくなっており、確認できてません)

  1. 1.2.があるので、これをやる意味はないですが、一般にエラーが予期されるもので、エラーで止まって欲しくないときはtry... catch構文を使います。

javascript

1var activeCell = undefined; 2try { 3 activeCell = Sheet.getActiveCell(); 4} catch(e) { 5 Logger.log(e); // "Exception: 範囲の参照~"ってのが出ます。これもコピペを怠っており、今トリガが動作しないので正確な値をお示しできません。すみません。 6}

ただ、一般にtry catchは重い処理なので(GASの場合APIアクセスのほうが重いでしょうが)回避できれば回避するのが定石です。

投稿2018/08/06 13:42

編集2018/08/09 02:47
papinianus

総合スコア12705

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

papinianus

2018/08/06 13:45

まじめな話、複数セル選択とかいろいろやったけど再現せず。 それで思ったのが、値変更はOnEditと違ってプログラム的な書き込みでも起きるので、セルがないんじゃないかというパターン。ただこちらは確認してないけど編集領域が持てるからないと自己否定。 で、メモ消してみたら、起きた。他にも書式を消すとか、値変更が発生するけど、それがセルを指さないものだと同じエラーになるのかもしれない。 回避方法は今のところ考えてない
papinianus

2018/08/06 13:47

あと(RangeCheck == 1||3)の話で、RangeCheckが1でないときは、jsの仕様上、||の右側が評価される。0以外の数値はnot falsy、つまりtrueなので、RangeCheckがundefinedでもif内が実行される
ALHIRU

2018/08/07 03:43

おお!!ありがとうございます!!! 確かに今の使用状況とぴったり符合します。意図しないタブにNoteが書き込まれることが多々あったために相当数Noteの削除をしております。ということはNoteの削除でも値の変更の一種と認識してトリガー発動するもActiveCellが取得できないということでしょうか? ちなみにおはずかしながらこのログはどうやってとるのでしょうか?ちなみに当方Logger.log("変数 = " + 変数);をコードの途中に埋め込みまくる方法しか知らなくて、お教え頂ければ幸甚です。 当方で一度ログを取ってみて同じ結果ならばこちらをBAにさせていただこうと思います。
papinianus

2018/08/07 04:22 編集

スクリプトエディタのログを見るメニューのうえに、実行トランスクリプトというのがあります。 そちらはLogger.logをしなくても、文の実行単位で、記録が残ります(もちろんLoggerと同じく実行直後のものになります) Noteの削除も、値の変更のイベントを発火させるのだと思います。詳しい内部設計は分かりませんが、getActiveCellはできるはず。ただそこで取得される"アクティブなセル"が一般的なセルではなく、Noteを表現するセルなんだと思います。 (もし、Note編集時にアクティブなセルが指定されないのだったら、閉じているシートを参照したときのようにA1がアクティブなセルとして取得できて、column = 1.0が取れる気がします。もちろんNoteを編集したときだけ(シートを閉じているより特殊な状態にして)アクティブセルをundefinedみたいなものにすることは可能だと思いますがそういう作りにはしないでしょう。またundefinedだとundefinedのプロパティを取得できないというエラーになるので特殊処理すぎる) Noteを表現するアクティブセルは、カラムやローを持たないオブジェクトでそのオブジェクトのgetColumnをするので範囲外なんじゃないかと推測してます。
ALHIRU

2018/08/08 01:55

papinianus様 当方でもNote消去後の実行トランスクリプトで「実行に失敗: 範囲外のセル参照です」が出るのを確認できました。ありがとうございます。BAとさせていただきます。 他のご回答下さった皆様もありがとうございました。 しかしpapinianus様の仮説通りだとすると、このエラーレポートを止めるのはなかなか難しいということになりそうですね。ActiveCellを取得した時にそれが一般的なセルなのかNoteを表現する特殊なセルなのか判定できればgetColumnする前にreturnしてコロンの取得を差し止めることができそうですが、それを判定できる変数が想像もつきません。 getColumnした後ではエラーレポートが出てしまいますし、そこではundefinedさえ取得できていない。 理由が判りましたので当面はスパムメールと思って我慢します。 回避方法にお心当たりがある方はコメントよろしくお願いします。
papinianus

2018/08/09 02:47

間違ったことを言ってたり、回避する方法を考えたりしたので、回答追記しました。よければご参考になさってください
ALHIRU

2018/08/09 13:06

papinianus様 ありがとうございます。だんだん私の理解レベルを超え出したような気がしないでもないですが、しっかり勉強させて頂きます。
papinianus

2018/08/10 01:47

長く書いてしまいましたが単純にトリガーを値の変更から編集に変えてください
Yoi

2019/06/26 11:26

当方同じようなエラーで悩んでおりましたが、これ読んで解決しました。 セルの値ではなくコメントを承諾とか削除して「変更」トリガーが動いてそれでエラーを吐くようです。 トリガーを「編集」にしたら直りました。
guest

0

どこにもアクティブなセルがない、という場合はどうなるんでしょうか


言われて気づきましたw

if(SheetName=="Check list" && CellColumn==9){ var RangeCheck = 1 } というのを、 var RangeCheck = 0; if(SheetName=="Check list" && CellColumn==9){ RangeCheck = 1; } にかえてみよう ブロック{ } の中で定義した変数はそこから抜けると無効になるんじゃなかったっけ

投稿2018/08/02 14:20

編集2018/08/06 03:13
y_waiwai

総合スコア87719

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

ALHIRU

2018/08/06 02:39

スプレッドシートの変更をトリガーにして走らせているスクリプトですのでその可能性は低いのではないかと考えています。
ALHIRU

2018/08/06 03:07

今気付きましたが、12、15、18行に;が抜けているのが原因ですか?!(大汗)
y_waiwai

2018/08/06 03:13

回答を修正しました
macaron_xxx

2018/08/06 03:18

javascriptのvarはifやforのブロック外からも参照できたような…
macaron_xxx

2018/08/06 03:38

RangeCheck == 1||3 は RangeCheck == 1 || RangeCheck == 3 にしないといけないかなー。
y_waiwai

2018/08/06 03:44

別に回答立ててやってくださいw
ALHIRU

2018/08/06 04:15

そうかもですね。確か最近他のスクリプトでもこれでちゃんと動かないことがあったような。Googleapps scriptは間違ってても保存時に断固拒否の時とスルーの時があるので難しいです。
ALHIRU

2018/08/06 09:45

var RangeCheck = 0;とRangeCheck == 1 || RangeCheck == 3をやってみました。 とりあえず予期しないタブで走るのはなくなりました。「範囲外のセル参照です」は相変わらず帰ってきております。 変数は{}を抜けても次に新たな値を代入するまでは前の値を保持し続けるのではないでしょうか? Googleapps script完全入門にも「変数はスクリプト内の好きなタイミングで取り出して利用することができます。」と書いてますし。 グローバル変数宣言していないfunction XXX(){}内のローカル変数がfunctionを抜けると無効になるのは同意ですが。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問