スプレッドシートで背景色を参照した処理を行うGASを構築したい

Question

特定のセルと同じ背景色を探し出し同じ入力内容にしたい

スプレッドシートでカレンダーを作成し予定を入力しているのですが、同じ内容は同じ色のセルにしてまとめています。
ある時データを吹っ飛ばしてしまい、内容が無いままセルの色だけ残ってしまいました。

幸い手帳など他のデータから復元は可能なのですが、過去5年分くらいあるため手作業でやるのはちょっと面倒そう。
そこで、GASで処理できないかと一念発起しコードを書いてみましたが、中々思い通りに動いてくれません。

イメージとしては下記のようなカレンダー表そのままです。

A1
～年～月 
1 月 C1 D1 E1 F1 G1
2 火 C2 D2 E2 F2 G2
3 水 C3 D3 E3 F3 G3
4 木 C4 D4 E4 F4 G4
5 金 C5 D5 E5 F5 G5

例えば<A1>の背景色を"赤"にして"出張"と入力した場合、C1:G5の範囲内で背景が赤いセル全てに出張と入力される、そういったコードを書きたいのです。

現状では1行目しか判定されず、どうしたら範囲内全てを判定されるようになりますでしょうか？
宜しくお願いいたします。

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

TypeError: Cannot read property '0' of undefined
identifyColor	@ 色.gs:17

該当のソースコード

function identifyColor(){
  var sheet = SpreadsheetApp.getActiveSheet();

  var targetColor = sheet.getRange("任意のセル").getBackground();

  var colorsListRange = sheet.getRange("任意の範囲");
  var colorArray = colorsListRange.getBackgrounds();
  var contentArray = colorsListRange.getValues();

  for(var i = 0; i < 100; i++){
    var cellColor = colorArray[i][0];
    if(cellColor == targetColor){
      contentArray[i][0] = "=任意のセル";
    }else{
      contentArray[i][0] = "";
    }
  }
}

試したこと

17行目にエラーが出てしまいます。
i<7にすると動作しますが、今度は1行7セルだけしか判定してくれません。

参考資料

https://qiita.com/310ma3/items/6dcb70ee345b9c65d313

Answer 1

myFunction の A1 とか C1:G5 とかを意図する範囲にして、実行してください。

スクリプトエディタを開いた時点のアクティブなシートを対象に作用します。
あと、A1 と同じ色だったら、そこに何かが書かれていても強制的に "出張" に上書きされますのでご注意ください。
(やっていると思いますが、被害を拡大させないように、現状のものは一旦コピーしてバックアップを取りましょう)

const myFunction = () => {
    q364697("A1","C1:G5");
}
const q364697 = (sourceA1, targetA1, sheetName) => {
    sourceA1 = sourceA1 || "A1";
    targetA1 = targetA1 || "C1:G5";
    const workingSheet = sheetName ? SpreadsheetApp.getActive().getSheetByName(sheetName) : SpreadsheetApp.getActiveSheet();
    const sourceRange = workingSheet.getRange(sourceA1);
    const keyColour = sourceRange.getBackground();
    const designatedValue = sourceRange.getValue();
    const targetRange = workingSheet.getRange(targetA1);
    const targetColours = targetRange.getBackgrounds();
    const targetValues = targetRange.getValues();
    targetRange.setValues(targetValues.map((r,i)=> r.map((c,j) => targetColours[i][j] === keyColour ?designatedValue:c)));
}

TypeError: Cannot read property '0' of undefined

i を 100 とかにしてますが、おそらく 100 ないんじゃないですかね。
"任意の範囲"みたいにぼやかすと、助言も↑のようにふわっとしたものになります。

Comments

[papinianus]

勝手にコードをかきかえたのは、バグだけ直したところで getValues して得たデータに "=A1" のような関数を入れても何も起きないですし、そもそも setFormula とか別の考慮が必要なんで、最初からやりたいことに近づけました。

[退会済みユーザー]

papinianus 様
書き換えて頂いたコードを実行しましたところ、希望通りの処理が叶いました。
いやはや、自身の知識の拙さには恥じ入るばかりです。
ご教授頂きましたコードを紐解きながら、もっと勉強したいと思います。

この度は誠にありがとうございました。

Answer 2

17行目にエラーが出てしまいます。
i<7にすると動作しますが、今度は1行7セルだけしか判定してくれません。

C1:G5の範囲で実行するとcolorArrayの要素数は4ですが、100回ループしているのでインデックスがArrayの要素数を超えています。

デバッグすると、わかりやすいと思います。

// 配列の要素数に達したらループ終了
for(var i = 0; i < colorArray.length; i++){

期待通りの動作になるかわかりませんが、これでエラーは解消されるはずです。

Comments

[papinianus]

その修正を入れましたが

> 17行目にエラーが出てしまいます。
> i<7にすると動作しますが、今度は1行7セルだけしか判定してくれません。

回答冒頭で引用されている↑の 2 行目の課題が解消しておらず、たかだか 5 セルしか判定してくれませんでした。