"Exception: Argument cannot be null: a1Notation"の解決について

Question

やりたいこと

spreadsheetで、行のなかで色付きのセルの数をカウントしたいと考えています。

以下がそのイメージです。

1. 上記は、あるテストの回答で、数値が選択されています。
2. 誤った回答には色がついています。
3. エラーとなっている箇所に、関数が入る。その関数をコピーし以下の行に貼り付けると、自動的に各行の色がカウントされるようにしたい。

実行したこと

function countCellsWithBackgroundColor(rangeSpecification) {

  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var ss = sheet.getSheetByName("Tally_1");
  var range = ss.getRange(rangeSpecification);
  var x = 0;
  var i = 0;
  var j = 0;
  var cell;
  var color = "#f4cccc";
  for (i = 1; i <= range.getNumRows(); i++) {
    for (j = 1; j <= range.getNumColumns(); j++) {
      cell = range.getCell(i, j);
      if(cell.getBackgroundColor() == color)
        x++;
    }
  }
  return x;
}

つまづいたこと

エラー	
Exception: Argument cannot be null: a1Notation
countCellsWithBackgroundColor	@ コード.gs:5

試みたこと

条件分岐で　!= null などを試したり、a1Notationを試したりしましたが、解決には至りませんでした。

5行目でエラーが出ていることはわかりますが、その後の解決の選択肢がなく、どう解決するべきか困っています。

参考

http://shanon-tech.blogspot.com/2015/12/2.html
https://webapps.stackexchange.com/questions/23881/calculating-shaded-cells

Answer 1

https://teratail.com/questions/338275#reply-465962

↑似たような色を数える自作関数の質問に回答してましたので、参考までに。

自作関数のセル範囲を利用する際、ダブルクォートで括らずに使う方法としては、上の質問者さんのコードのような、getActiveRange と getFormula で式を取り出して正規表現で参照範囲を取得する方法があります。

あとは範囲の列は固定されてるようなので一番簡単な方法として、シート関数 ROWあたりと組み合わせる方法もあります。

=countCellsWithBackgroundColor("E"&ROW()&":Q"&ROW())

上記で通常のシート関数と同じようにセル範囲の相対参照したいという要望は解決されると思います。

ただ、コードの書き方が一つ一つgetBackgroundColorを処理しているで動き重いんじゃないでしょうか？

• まとめて対象範囲の背景色を getBackgroundColorsで取得してから対象の色をカウントする
• 一つの式で範囲内の行毎の対象の色の数を返すような関数にする

といったことで、処理スピードが上げられるかと思いますので是非トライを。

Comments

[YousukeTanaka]

ありがとうございます。試したところ、うまくいきました。同時に、COLUMN()と合わせたやり方も試してみました。勉強させていただきました。長い時間悶々としていた課題が解決して嬉しい限りです。感謝しています。

Answer 2

rangeSpecificationがnullなのですから、問題はこの関数の中ではなくて、この関数を呼び出している側に問題があります。

関数countCellsWithBackgroundColorにnullを渡してしまっている呼び出し元を見つけて解決してください。

Comments

[YousukeTanaka]

承知しました。ご助言ありがとうございます。

[YousukeTanaka]

確かに、確認したところ呼び出し側の関数に問題がありました。
=countCellsWithBackgroundColor("$E3:$Q3")と書けば、7という具体的な数値が出ました。ただし、これでは、文字列と認識されて、大量のデータを処理できません。""を除くためにNumber()を使ったり、.setValue()を使ったり、以下の記事を参考にして試しましたが、解決できませんでした。再度、ご助言をいただけますでしょうか？
https://app.clickup.com/login

[itagagaki]

私はコードを動かさないで回答しているので具体的なことはわかりませんのでいくつか質問させてください。
x が 7 となり、countCellsWithBackgroundColor から 7 が返された、ということですか？
それは正しいのですよね？
文字列と認識されるというのは、countCellsWithBackgroundColor から return x で返された値が文字列として認識されてしまうということですか？
数値として返すと思いますが、return N(x) だとどうですか？
あるいは、=N(countCellsWithBackgroundColor("$E3:$Q3")) ではどうですか？
そして、大量のデータを処理できないというのは、どういう理由からですか？

[YousukeTanaka]

>x が 7 となり、countCellsWithBackgroundColor から 7 が返された、ということですか？
それは正しいのですよね？
=>はい、7が返されております。関数内を""で囲むことで7と返されました。

>文字列と認識されるというのは、countCellsWithBackgroundColor から return x で返された値が文字列として認識されてしまうということですか？
=>はい、そして、エラーとなり、「Exception: Range not found（行 4）が発生しました。」と出ます。

>数値として返すと思いますが、return N(x) だとどうですか？あるいは、=N(countCellsWithBackgroundColor("$E3:$Q3")) ではどうですか？
=>return N(x)の場合は、エラーですが、後者は数値を返します。これは""で範囲を囲っているからだと考えています。N()を除くとエラーとなります。

>そして、大量のデータを処理できないというのは、どういう理由からですか？
=>この関数をコピーして、全てのデータに適用できないという理由です。全てのセルでコピーした最初のセルの値になってしまいます。

[itagagaki]

まず「関数内を""で囲む」の意味がわからないので具体的に教えてください。

[YousukeTanaka]

わざわざお時間を割いていただき感謝しております。承知しました。以下、回答になっておりますでしょうか？

スプレッドシート(以下SS)の関数(スクリプトエディタ上ではない)を設定する際に、セル内に以下を設定しております。
=countCellsWithBackgroundColor()

エラーとなる場合は、以下の場合です。もともと用意されているSS関数を使う際と同じように範囲を設定しております。
=countCellsWithBackgroundColor($E3:$Q3)

エラーとならない場合は、以下のように、()内で範囲を""で囲んでいます。ただし、このようにすると、同じ列の全てのセルにこの関数を適用できなくなります。
=countCellsWithBackgroundColor("$E3:$Q3")