全シートに条件付き書式をセットする方法の知恵をお貸しください

Question

今使用しているスプレッドシートの状態は、対応が終えた案件は、チェックボックスにチェックを入れて、終了確認が出来るようにしています。
チェックボックス（F列）にチェックをいれると、その右横（G列）に当日の日付が記入され、その行の背景色が白になるようにしています。

変更したい点は、チェックボックス（F列）にチェックを入れなくても、G列に何か文字が記入された時に、その行の背景色が白になるようにしたいです。

条件付き書式を変更すればよいのですが、シートが1000枚を超えるため、GASを使って全シートに条件付き書式をセットする方法を探しています。

近いもの（参照のためコードを添付）を見つけたのですが、「カスタム数式」ではなく、「次を含むテキスト」を条件にしているため、無理でした。

何か、良いアドバイスをいただけないでしょうか。

  // スプレッドシートを開いたときに実行される関数

  // UIの取得
  const ui = SpreadsheetApp.getUi()

  // メニューの表示名
  const menu = ui.createMenu('スクリプト実行');

  // メニューに追加するボタン
  menu.addItem('全シートに条件付き書式を適用する', 'set_conditional_format_all_sheets');

  // メニューを画面に追加する
  menu.addToUi();
}


function set_conditional_format_all_sheets() {

  // UIを取得
  const ui = SpreadsheetApp.getUi();

  // 現在開いているスプレッドシートを取得
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  // スプレッドシートに存在するすべてのシートを取得
  const sheets = ss.getSheets();

  // 条件付き書式のキーワードを入力させる
  const input = ui.prompt('キーワードを入力してください', ui.ButtonSet.OK_CANCEL);

  // キャンセルが押されたらスクリプトを終了する
  if (input.getSelectedButton() == ui.Button.CANCEL) {
    return;
  }

  // キーワードを取り出す
  const keyword = input.getResponseText();

  // 1シートずつ条件付き書式をセットしていく
  for (let i = 0; i < sheets.length; i++) {

    // 条件付き書式をセットするのはシート内で現在使われている範囲
    let range = sheets[i].getRange(1, 1, sheets[i].getLastRow(), sheets[i].getLastColumn());

    // 条件付き書式のセット
    let rule = SpreadsheetApp.newConditionalFormatRule()
      .whenTextContains(keyword) //「テキストを含む」条件
      .setBackground("#FFC0CB") //背景色をピンクにする
      .setRanges([range]) //範囲は現在使われている範囲
      .build(); //条件を確定

    // 既存の条件付き書式のルールを取得する
    let rules = sheets[i].getConditionalFormatRules();

    // 既存のルールに今回作成したルールを追加
    rules.push(rule);

    // 既存と新規のルールを合わせてシートにセットする
    sheets[i].setConditionalFormatRules(rules);
  }
}ここに言語を入力
コード

YellowGreen様

一つ目に頂いたコードもこのようにエラーが出てしまいます。
申し訳ありません。

![]

Comments

[touch-touch]

協力してくださり、ありがとうございます。

調査したことは、近いコードを探した事です。
試したことは、そのコードを使用しても、ダメだったことです。

どうぞ、よろしくお願いします。

[YellowGreen]

条件付き書式は、設定した順に配列として保存されているのですが、
1000枚のシートに新たに条件付き書式を追加する際に、既存の条件付き書式は全て削除とか
最初のものを残して残りは削除とか、その辺の仕様はどうなりますか。
既存のものを全て削除するようなスクリプトも必要でしょうか。

[touch-touch]

検討してくださり、ありがとうございます。

既存のものは削除せずに、そのまま残しておきたいと考えています。

どうぞ、よろしくお願いいたします。

Answer 1

回答にあるように一つ目のコードは、設定方法をお示ししたもので、関数名も入れてありません。関数名をつけると実行可能になるわけではありません。
実行可能なスクリプトは、関数名がある2番目のものです。

Comments

[YellowGreen]

iPhoneからだったので、コメントを追加するつもりが、回答を追加してしまいました。

Answer 2

カスタム数式での条件付き書式を追加するスクリプトをお示しします。
カスタム数式の条件付き書式の設定方法は、

const rule = SpreadsheetApp.newConditionalFormatRule()
  .whenFormulaSatisfied('=($F1<>"")')
  .setBackground('#FFFFFF')//背景色に白を指定
  .setRanges([range])
  .build();

になります。

以下にお示しするのは、シートの数が1000を超えるとなると、実行中にタイムアウトなどのエラーが発生することも考えられるので、条件付き書式が追加されないシートが残ってしまうことを考慮したスクリプトです。

実行画面で追加するシートの開始位置（左端を0としたときのシートの順番）を指定するようにしてあります。
削除やシート探索のスクリプトを追加し、、メニュー表示も修正しました。

function set_conditional_format_all_sheets() {
  // UIを取得
  const ui = SpreadsheetApp.getUi();
  // 現在開いているスプレッドシートを取得
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  // スプレッドシートに存在するすべてのシートを取得
  const sheets = ss.getSheets();
  // 条件付き書式を追加するシートの選択を求める
  const input = ui.prompt('確認', '条件付き書式を追加します。\n' +
    'シートの位置を入力してください。\n' +
    '何も入力がないと最初のシートから追加します。', ui.ButtonSet.OK_CANCEL);
  // キャンセルが押されたらスクリプトを終了する
  if (input.getSelectedButton() == ui.Button.CANCEL) {
    return;
  }
  // 開始位置を取り出す
  const position = input.getResponseText() == '' ? 0 : input.getResponseText();
  // 1シートずつ条件付き書式をセットしていく
  for (let i = position; i < sheets.length; i++) {
    const lastRow = sheets[i].getLastRow();
    if (lastRow > 0) {
      // 条件付き書式をセットするのはシート内で現在使われている範囲
      const range = sheets[i].getRange(1, 1, lastRow, sheets[i].getLastColumn());
      // 条件付き書式のセット
      const rule = SpreadsheetApp.newConditionalFormatRule()
        .whenFormulaSatisfied('=($G1<>"")')//←Gに修正
        .setBackground('#FFFFFF')//背景色に白を指定
        .setRanges([range])
        .build();
      // 既存の条件付き書式のルールを取得する
      const rules = sheets[i].getConditionalFormatRules();
      // 既存のルールに今回作成したルールを追加
      rules.push(rule);
      // 既存と新規のルールを合わせてシートにセットする
      sheets[i].setConditionalFormatRules(rules);
    }
  }
}
//条件付き書式を処理
function handleConditionalFormatRulesOfSheets(mode) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  // シートの開始位置を入力
  const ui = SpreadsheetApp.getUi()
  const input = ui.prompt('確認', '条件付き書式の処理を実行します。\n' +
    'シートの位置を数字で入力してください。\n' +
    '入力がないと最初のシートから実行します。', ui.ButtonSet.OK_CANCEL);
  // キャンセルが押されたらスクリプトを終了する
  if (input.getSelectedButton() == ui.Button.CANCEL) {
    return;
  }
  // 開始位置を取り出す
  const position = input.getResponseText() == '' ? 0 : input.getResponseText();
  // 指示に応じた処理を実施
  let isMatch = false;//探しているシートがあったかどうか
  for (let i = position; i < sheets.length; i++) {
    const rules = sheets[i].getConditionalFormatRules();
    if (mode == 'clear') {
      if (rules.length > 0) {//書式があるなら全て削除
        sheets[i].clearConditionalFormatRules();
      }
    } else if (mode == 'find'){//書式がないシートを探す
      //次の行の（）内の条件を変更すると探すシートを変更できる
      if (rules.length <= 0) {
        ui.alert('シートの開始位置は、' + i + ' です。');
        isMatch = true;
        break;
      }
    } else if (mode == 'shift' || mode == 'pop') {//一つを削除するときは残りを再設定
      if (mode == 'shift') {
        rules.shift();//最初の一つを削除する。
      } else if (mode == 'pop') {
        rules.pop();//最後の一つを削除する。
      }
      sheets[i].setConditionalFormatRules(rules);
    }
  }
  if (mode == 'find' && !isMatch) {
    ui.alert('シートはみつかりませんでした。');    
  }
}
//条件付き書式のないシートを探す
function findConditionalFormatRulelessSheet() {
  handleConditionalFormatRulesOfSheets('find');
}
//最初を削除
function shiftConditionalFormatRuleOfSheets() {
  handleConditionalFormatRulesOfSheets('shift');
}
//最後を削除
function popConditionalFormatRuleOfSheets() {
  handleConditionalFormatRulesOfSheets('pop');
}
//全て削除
function clearConditionalFormatRulesOfSheets() {
  handleConditionalFormatRulesOfSheets('clear');
}
//メニューの表示
function onOpen() {
  // UIの取得
  const ui = SpreadsheetApp.getUi()
  // メニューを作成
  ui.createMenu('スクリプト実行')
    .addItem('全シートに条件付き書式を追加する', 'set_conditional_format_all_sheets')
    .addItem('条件付き書式がないシートを探す', 'findConditionalFormatRulelessSheet')
    .addItem('全シートの条件付き書式から最初の一つを削除する', 'shiftConditionalFormatRuleOfSheets')
    .addItem('全シートの条件付き書式から最後の一つを削除する', 'popConditionalFormatRuleOfSheets')
    .addItem('全シートの条件付き書式を全て削除する', 'clearConditionalFormatRulesOfSheets')
    .addToUi();
}

Comments

[YellowGreen]

カスタム数式の内容がF列になっていました。
（）の中を
'=($G1<>"")'
でG列になります。

[touch-touch]

YellowGreen様、迅速にプログラムを作成してくださり、ありがとうございます。

頂いたコードを組んでみたところ、すべての背景色が白になってしまいました。
シート数が1000を超えるとお伝えしていましたが、100シートほどあるフォルダが10個ほどあるという事でした。（誤解を招く書き方をして、もうしわけありませんでした。）

どこを調整するとよいでしょうか。
お手数ですが、お知恵をおかりできないでしょうか。

[YellowGreen]

まずは、確認です。
1) 私のコメントのF列をG列に修正の件はどうされましたか。
2) 1)で対象となっている列には、何か入力されていますか。何か入力されていると白くなるようにしてあるのですが。

[YellowGreen]

質問に添付の画像ではG列は空欄になっていたので、私がアップしたスクリプトを「コメントのFをGにする修正」をしないまま実行してますでしょうか。
F列はチェックボックスがあってすべてのセルにTRUEかFALSEが入っていて空欄ではないので背景色が白くなったのではないでしょうか。
仮にそうなら、全シートの条件付き書式から最後の一つを削除するを実行して一度追加した分を消したあとで、
スクリプトをG列を対象にするように修正の上、再度条件付き書式の追加を実行してみてください。

[YellowGreen]

ご心配なら、修正した部分のコードをここにアップしてみてください。

[YellowGreen]

.whenFormulaSatisfied('=($F1<>"")')
の行を、
.whenFormulaSatisfied('=($G1<>"")')
に修正です。
FをGに書き換えた後に保存するだけです。

[touch-touch]

YellowGreen様

私が試してエラーが出たコードは、下記のものとなります。

function myFunction() {
const rule = SpreadsheetApp.newConditionalFormatRule()
.whenFormulaSatisfied('=($G1<>"")')
.setBackground('#FFFFFF')//背景色に白を指定
.setRanges([range])
.build()
}

FをGに書き直したのですが、
エラー
ReferenceError: range is not defined
myFunction @ 無題.gs:5

と、出てしまいます。

何か、間違った操作をしてしまっているでしょうか。

[touch-touch]

２つ目のコードをFからGにしたところ、全てが真っ白になることは、ありませんでした。
しかし、「シートの位置入力」の画面が出て、『実行開始』したもののグルグル読み込んだままとなり、『実行完了』になりません。
「シートの位置入力」には、何を記入すると良いでしょうか。

【参考までに写真を添付します】

[YellowGreen]

> 「シートの位置入力」の画面が出て、『実行開始』したもののグルグル読み込んだままとなり、『実行完了』になりません。 「シートの位置入力」には、何を記入すると良いでしょうか。

↓

回答でお答えしてますように
「以下にお示しするのは、シートの数が1000を超えるとなると、実行中にタイムアウトなどのエラーが発生することも考えられるので、条件付き書式が追加されないシートが残ってしまうことを考慮したスクリプトです。
実行画面で追加するシートの開始位置（左端を0としたときのシートの順番）を指定するようにしてあります。」

画面に表示されているとおり、何も入力しないと最初のシートから処理をします。
前回、全て真っ白になったとおっしゃっててた際も同じ表示が出ていたと思いますが、今回が初めてだったのであれば、回答でお示ししたコードをそちらで何か変更されている点があるのでしょうか。

[YellowGreen]

実行開始からどのくらいの時間がたって実行完了にならないとご判断されましたでしょうか。
その際は、ご自身で処理を中断されたのでしょうか？
タイムアウトになったのでしょうか。

念のため、実際にFからGに修正した後のコードをコピペでアップしてみていただけませんでしょうか。

[YellowGreen]

回答の二つ目のスクリプトの条件付き書式の追加対象の列をF → Gにしました。（1文字変更）

[YellowGreen]

あの、これも念のためお伺いしますか、二つ目のスクリプトが実行終了にならないときは、メニュー、エディタのどちらから実行されましたか。
エディタからだと、一度スプレッドシートの画面に戻って、ダイヤログのボタンを押さないと処理待ちでタイムアウトになりますが。それでしょうか？

[touch-touch]

YellowGreen様　
本当にありがとうございます。
時間をかけて読み込ませると、「スクリプト実行」がメニューに追加され、「条件付き書式を追加する」で、求めていたことが可能になりました。

しかし、Apps Scriptを開こうとすると、エラーが出てしまいます。
どのように対処したらよろしいでしょうか。

（参考までに画像を添付いたします）

[YellowGreen]

そのようなエラーは、通常はブラウザのキャッシュやクッキーが破損しているときに出るようです。
対応としては、ブラウザのキャッシュやクッキーの削除になりますが、クッキーを削除するとサイトへのログイン情報なども消えますので、ご留意ください。

なお、念のため
「400 要求の形式が正しくありません」
でGoogle検索をして確認してください。

[YellowGreen]

あとは、条件付き書式が何重にも設定された状態になっていませんか。
シートの左上の角をクリックしてシート全体を選択してからメニューの条件付き書式で確認してみてください。

[touch-touch]

YellowGreen様

全てが上手くいきました。
迅速かつ丁寧に、ご指導とアドバイスをくださり、大変助かりました。
心から感謝を申し上げます。