入力されたセルの下〇行を塗りつぶし

Question

前提・実現したいこと

今まで、シフト表をエクセルで作っていましたが、未来のクラウド化に向けて、グーグルスプレッドシートで作り直すことにしました。
ただ、日々のシフト作成は上司がやるので、あまりフォーマットは変えずに、ＧＡＳなどを利用して、作成する際の手間を省こうとしています。

前置き長くなりましたが、
指定した文字が入力されると、入力したセルの下10行をセルを塗りつぶしたいのですが、可能でしょうか？

例：”休み”と入力したセルの下10行が赤く塗りつぶされる。
その際の対象範囲は、シート全体で、”休み”と入力するところは決まっていません。

調べても、入力したセルの行全体だったり、入力するセルが決まっていたりと、欲しい情報がありませんでした。

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

該当のソースコード

試したこと

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

Comments

[y_waiwai]

それはExcelでは実装できてるんでしょうか。
そのコードを提示しましょう

[kuwacci]

エクセルではやっていません。
エクセルでは、手動で塗りつぶしています。

[sawa]

ExcelでもGoogleスプレッドシートでも、条件付き書式で対応可能な要件（というか、そっちでやった方がスムーズ）ですが、ＧASを使って実現したいってことでしょうか？

[kuwacci]

条件付き書式設定でも良いのですが、関数がわからないです。
入力したセルに色を付けたり、特定のセルに入力されたら、色を付けたりするのは分かるのですが、
どこに入力しても、入力したセルから下10行を色を付けたいので、条件付き書式設定では厳しいのではないかと思っています。

Answer 1

おまたせしました。これはどうでしょうか?
私の環境では気になる遅さですが、一応動くように見えます。

function onEdit(e) {
  let word = "休み";
  let targetRange = e.source.getActiveSheet().getRange(Number(e.range.getRow() + 1), e.range.getColumn(), 10, 1);
  
  if (e.value == word) {
    targetRange.setBackground('red');
  }
  if (e.range.getValue() != word　&& ! targetRange.getValues().flat().includes(word)) {
    targetRange.setBackground('white');
  }
}

この場合、Apps scriptの方で直接onEdit()を実行したとしても、
肝心の e がセットされていない状態での実行になるので、
e == undefined;
undefined.valaue って何の事？
となってしまいます。
普通は、その e は、何かやった時に、その情報が入ってきます。

https://developers.google.com/apps-script/guides/triggers/events#edit

どうしても、onEdit()を直接実行したいとしたら、
eがundefinedの時は私のコードを実行しないようにするというのはどうでしょうか？

普通はこんな事やらないと思うのですが、
求められたことに答えるならば、
上の文全体を、if(e != undefined){ 上の文 }
のような形で包んであげるとかどうでしょうか？

function onEdit(e) {
  if (e != undefined) {
    let word = "休み";
    let targetRange = e.source.getActiveSheet().getRange(Number(e.range.getRow() + 1), e.range.getColumn(), 10, 1);

    if (e.value == word) {
      targetRange.setBackground('red');
    }
    if (e.range.getValue() != word && !targetRange.getValues().flat().includes(word)) {
      targetRange.setBackground('white');
    }
  }
}

Comments

[kuwacci]

ありがとうございます！
一応動きました。

[退会済みユーザー]

今回は範囲を指定して削除などすると、
赤いところがそのままになっているなど、
普通にやるであろう動作に対応できてないです。

パズルみたいで面白いので、思いついたらまた書いてみます。

ただ動作が重いので、sawaさんの提案してくれた条件付き書式の方が
良いと思いました。
お仕事頑張ってください。お疲れ様です。

[sawa]

この書き方だと「休み」の下のセルに 他の文字を記入したり、 他の文字が入ってたセルの文字を消したり編集すると赤の塗りつぶしが消えちゃうかと。

[退会済みユーザー]

あ、本当ですね。
やっつけ仕事になりますが、
以下はどうでしょうか。

function onEdit(e) {
if (e != undefined) {
let word = "休み";
let targetRange = e.source.getActiveSheet().getRange(Number(e.range.getRow() + 1), e.range.getColumn(), 10, 1);

/*
Object.keys(e).forEach(function (key) {
console.log(key + ":" + e[key]);
});
*/
if (e.value == word) {
targetRange.setBackground('red');
}
if (e.value == undefined && e.oldValue == word) {
targetRange.setBackground('white');
}
}
}

[sawa]

この早さで修正できるのは凄いです。

[kuwacci]

みなさんありがとうございます！
本当に助かります！

[kuwacci]

修正してくれたのを実行したら、休みを消したあと、赤いところが戻らないんですけど、わかりますか？

Answer 2

条件付き書式で対応できます。

もちろん全てのシートに対応させたいとか、シートが追加された時にも自動で適用させたい場合はGAS使った方がよいですが、対象シートが決まってるなら、どこに「休み」が入力されてもといっても、ある程度範囲は想定できると思うので、条件付き書式のカスタム数式を試してみるとよいかと思います。

「休み」を消したあとに元に「戻す」って追加要件は、もし元の状態が別の色付けしてた場合、GASだと厳しいんじゃないかなと思います。

---

こっから条件付き書式の設定の流れ

・条件付き書式で適用する範囲を A2:Z100とか A2:AZ1000 といった形で A2をスタートとして想定される範囲をセット。（「休み」入力のセルの1つ下から色を付けたいとのことなので、1行目は対象外となる為 A2を起点としてます）

・書式設定の条件でカスタム数式を選ぶ

・式に以下を入れる。（10行目より上の時だけ範囲が違ってくるのでIFで分岐）

　=COUNTIF(INDIRECT("R"&IF(ROW(A2)<11,1,ROW(A2)-10)&"C"&COLUMN(A2)&":R"&ROW(A2)-1&"C"&COLUMN(A2),FALSE),"休み")>0

・書式設定のスタイルで希望する色をセット

Comments

[kuwacci]

休みを消したあとは、空白の状態ですので、同じ条件付き書式設定で、”休み”を”　”にしたらよいですか？
また、下10行ではなくて4行や5行に変更したい場合は、どこを変更したらよいですか？
これかな？と思う数字変えてみても変わりませんでした。

初心者ですみません。

[sawa]

条件付き書式の記述なら 休みを消した後のことは気にしなくて大丈夫です。勝手に戻ります。

下10行を変えて 〇行に適用したい場合は、 <11 の 11 を 〇-1 に、ROW(A2)-10 の-10 を -〇 に変更で

Answer 3

こんなのどうでしょうか？

function onEdit(e) {
  if (e.value == '休み') {
    e.source.getActiveSheet().getRange(Number(e.range.getRow() + 1), e.range.getColumn(), 10, 1).setBackground('red');
  }
}

Comments

[kuwacci]

ありがとうございます！
どこのセルでも、休みと入力したら赤く塗りつぶされました！

ですが、実行したときにエラーが出たんですが、問題はないですか？
TypeError: Cannot read property 'value' of undefined
onEdit

あと、入力した休みを消したときに、赤いところが元に戻すことはできますか？

[退会済みユーザー]

おまたせしました。これはどうでしょうか?
私の環境では気になる遅さですが、一応動くように見えます。

```script

function onEdit(e) {
let word = "休み";
let targetRange = e.source.getActiveSheet().getRange(Number(e.range.getRow() + 1), e.range.getColumn(), 10, 1);

if (e.value == word) {
targetRange.setBackground('red');
}
if (e.range.getValue() != word　&& ! targetRange.getValues().flat().includes(word)) {
targetRange.setBackground('white');
}
}
```

この場合、Apps scriptの方で直接onEdit()を実行したとしても、
肝心の e がセットされていない状態での実行になるので、
e == undefined;
undefined.valaue って何の事？
となってしまいます。
普通は、その e は、何かやった時に、その情報が入ってきます。

https://developers.google.com/apps-script/guides/triggers/events#edit

どうしても、onEdit()を直接実行したいとしたら、
eがundefinedの時は私のコードを実行しないようにするというのはどうでしょうか？

普通はこんな事やらないと思うのですが、
求められたことに答えるならば、
上の文全体を、if(e != undefined){ 上の文 }
のような形で包んであげるとかどうでしょうか？

```script
function onEdit(e) {
if (e != undefined) {
let word = "休み";
let targetRange = e.source.getActiveSheet().getRange(Number(e.range.getRow() + 1), e.range.getColumn(), 10, 1);

if (e.value == word) {
targetRange.setBackground('red');
}
if (e.range.getValue() != word && !targetRange.getValues().flat().includes(word)) {
targetRange.setBackground('white');
}
}
}
```