引数ではなくて、セルの数式(A1形式)で取得したい

Question

=test("A1:A5")で実行したときにA1:A5を返す※例A1:A5

function test (range)｛
return range.getFormula();
｝

以下のエラーメッセージが発生しました。

Cannot find function getFormula in object 30.(行 82)が発生しました。

###ほかに良い方法があれば教えていただけると助かります。

###一致する背景色のセルの合計
function SumColor(m){
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("B1:B5");
var getcolors = range.getBackgrounds();
var values = range.getValues();
var lastRow = sheet.getLastRow();
var sumfirst = 0;
var sum = parseInt(sumfirst);
for(var n = 0; n <= lastRow;n++){
var value = parseInt(values[n]);
var getcolor = getcolors[n];
if(m == getcolor){
sum = sum + value
}
}
return sum;
}
//m
function getColor(){
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("D6");
var xcolor = range.getBackground();
return xcolor;
}

カスタム関数で、
=SumColor(getColor())

###以下のように使いたい
=SumColor(A1:A5,getColor(D6))

Comments

[macaron_xxx]

ちょっと言っている意味がわからないのですが、A1:A5で返すというのは、文字列ではないのですか？？何を返したいのかがまったく理解できません。

[papinianus]

これって、どう使うのですか？

[ymk]

A1:A5は、例で値を選択するので変わります！

[papinianus]

ですので、それをどのように利用するのでしょうか？

[ymk]

セルの背景色を取得して、その色と同じセルの合計を出すときに使う予定です。

[ymk]

背景色や合計を出すセルが変わっても機能するようにセルを決めて記述したくないのです！

[macaron_xxx]

Rangeオブジェクトを返すということでいいのかしら。。

[ymk]

セルの中身を取得するのはなくて、選択した範囲のA1:A5みたいなのが欲しいんです。

[macaron_xxx]

あ、なんとなく理解しました。test関数って、カスタム関数としてセルに"=test(A1:A5)"みたいな形で入力して使おうとしているわけですね。その上で、そのRangeオブジェクトを取得したい、と。

[papinianus]

ymk様の仰る用途で、範囲が欲しいというのが分からないので、具体的に「もしできたらこういう関数をセルに書きたい」(今はエラーでもいいので)というのを示していただくことはできませんか？

[ymk]

そうです！そうです！！
カスタム関数！！！

[ymk]

載せるので、少々お待ちください

[macaron_xxx]

=SumColor(A1:A5,getColor(D6))　この形式では無理なので　=SumColor("A1:A5",getColor("D6"))　この形式になります。

[papinianus]

最初からこれ書いてたら、マイナス評価は回避できたと思いますよ。

[ymk]

初投稿でした笑

Answer 1

=test(A1:A5)のようにセルを指定する場合には、できません。

Arguments
Like a built-in function, a custom function can take arguments as input values

https://developers.google.com/apps-script/guides/sheets/functions

とあるように、引数は入力値として受け取ります。そのRangeを受け取ることはできません。

セル選択ではなく文字列として
=test("A1:A5")
と入力した場合には、下記のようにそのRangeを返すことができます。

function test(range){
  return SpreadsheetApp.getActiveSheet().getRange(range);  
}

実践的にいえば
セルの入力：=yellowSum("A1:A5")

function yellowSum(range) {
  var targetRange = SpreadsheetApp.getActiveSheet().getRange(range)
  var values = targetRange.getValues();
  var bgColors = targetRange.getBackgrounds();
  
  var summary = 0;
  bgColors.forEach(function(row, iRow) {
    row.forEach(function(bgColor, iCol) {
      if(bgColor === "#ffff00") {
        summary += values[iRow][iCol];
      }
    });
  });
  return summary;
}

たぶん色々考慮漏れてる気はしますが…。

追記

function SumColor(strRange, color){
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange(strRange);
  var getcolors = range.getBackgrounds();
  var values = range.getValues();
  var lastRow = sheet.getLastRow();
  var sumfirst = 0;
  var sum = parseInt(sumfirst);
  for(var n = 0; n <= lastRow;n++){
    var value = parseInt(values[n]);
    var getcolor = getcolors[n];
    if(color == getcolor){
      sum = sum + value
    }
  }
  return sum;
}

//m
function getColor(strRange){
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange(strRange);
  var xcolor = range.getBackground();
  return xcolor;
}

カスタム関数で、
=SumColor("A1:A5",getColor("D6"))

こうですね。

Comments

[ymk]

はい、"を自分でつけます…
ありがとうございました。

Answer 2

function test(rangetext){
  return SpreadsheetApp.getActiveSheet().getRange(rangetext).getFormula();  
}

---

追記

=SUM(test("H8:I8")という風に使いたいということですかね？
無理じゃないですか？(macaron_xxx様の案の方式も試してますが計算してくれませんでした)
単に=SUM($H$8:$I$8)ではダメなんでしょうか？

--
質問

セルの背景色を取得して、その色と同じセルの合計を出すときに使う予定です。
背景色や合計を出すセルが変わっても機能するようにセルを決めて記述したくないのです！

のところ、具体的に利用時の関数の例を示してもらえませんか？
それって変動するのは背景色だけなので、質問のようなことをする必要がないように思うのですが？

Comments

[papinianus]

やりたいことが判明したら、書き直しになると思いますが、今のコードを動くように直したものです。

[macaron_xxx]

SUM関数は引数に2次元配列をとりますので、=SUM(test("H8:I8"))のような風に使う場合には単純に
function test(range){
return SpreadsheetApp.getActiveSheet().getRange(range).getValues();
}
でいけるはず。

[papinianus]

そうなんですね(シート関数ヘルプをみつつ回答しているのですが、ヘルプからはその仕様は分かりませんでした)。勉強になります。

まあ、そもそもSUMだったら文字にする意味がないので、いずれにしても読解が違うのだと思います。
やりたいこと(sumifとかではいかと思うのですが)に対して、範囲表現オブジェクトが欲しいというのがまだ腑に落ちないです。

[macaron_xxx]

選択された範囲の中で背景色が黄色のセルの合計数を取得したい
とかそういうことだと思います。
用意された関数では、背景色でSUMIFとかできないですし。
ただ、セル一つ一つみていくので、めちゃくちゃ遅くなりそうな…。

[papinianus]

正解書かれちゃうと思うのでこちらで。
sumif色取れないんですね。
そもそもsumif相当のやつが2引数とれば済む話しやないかなーと思いました。

[papinianus]

覚え書きとして。
そもそもフィルターすら背景色が効かないのね。Excelを置きかえる日をまってるのに。