質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.51%
Google+

Google+とは、Googleが提供するソーシャル・ネットワーキング・サービスです。情報を共有するユーザーの範囲を設定する「サークル」、Webカメラで複数名の相手と無料でビデオチャットできる「ハングアウト」などの機能があります。

Google スプレッドシート

Google スプレッドシートは、フリーで利用できる表計算ソフト。Webアプリのためインターネットに接続することで利用できます。チャートやグラフの作成のほか、シートを他のユーザーと共有したり、同時に作業を進めることも可能です。

Google Apps Script

Google Apps ScriptはGoogleの製品と第三者のサービスでタスクを自動化するためのJavaScriptのクラウドのスクリプト言語です。

Q&A

解決済

2回答

1955閲覧

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

touch-touch

総合スコア4

Google+

Google+とは、Googleが提供するソーシャル・ネットワーキング・サービスです。情報を共有するユーザーの範囲を設定する「サークル」、Webカメラで複数名の相手と無料でビデオチャットできる「ハングアウト」などの機能があります。

Google スプレッドシート

Google スプレッドシートは、フリーで利用できる表計算ソフト。Webアプリのためインターネットに接続することで利用できます。チャートやグラフの作成のほか、シートを他のユーザーと共有したり、同時に作業を進めることも可能です。

Google Apps Script

Google Apps ScriptはGoogleの製品と第三者のサービスでタスクを自動化するためのJavaScriptのクラウドのスクリプト言語です。

0グッド

0クリップ

投稿2023/03/29 07:00

編集2023/04/02 18:08

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

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

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

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

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

function

1 // スプレッドシートを開いたときに実行される関数 2 3 // UIの取得 4 const ui = SpreadsheetApp.getUi() 5 6 // メニューの表示名 7 const menu = ui.createMenu('スクリプト実行'); 8 9 // メニューに追加するボタン 10 menu.addItem('全シートに条件付き書式を適用する', 'set_conditional_format_all_sheets'); 11 12 // メニューを画面に追加する 13 menu.addToUi(); 14} 15 16 17function set_conditional_format_all_sheets() { 18 19 // UIを取得 20 const ui = SpreadsheetApp.getUi(); 21 22 // 現在開いているスプレッドシートを取得 23 const ss = SpreadsheetApp.getActiveSpreadsheet(); 24 25 // スプレッドシートに存在するすべてのシートを取得 26 const sheets = ss.getSheets(); 27 28 // 条件付き書式のキーワードを入力させる 29 const input = ui.prompt('キーワードを入力してください', ui.ButtonSet.OK_CANCEL); 30 31 // キャンセルが押されたらスクリプトを終了する 32 if (input.getSelectedButton() == ui.Button.CANCEL) { 33 return; 34 } 35 36 // キーワードを取り出す 37 const keyword = input.getResponseText(); 38 39 // 1シートずつ条件付き書式をセットしていく 40 for (let i = 0; i < sheets.length; i++) { 41 42 // 条件付き書式をセットするのはシート内で現在使われている範囲 43 let range = sheets[i].getRange(1, 1, sheets[i].getLastRow(), sheets[i].getLastColumn()); 44 45 // 条件付き書式のセット 46 let rule = SpreadsheetApp.newConditionalFormatRule() 47 .whenTextContains(keyword) //「テキストを含む」条件 48 .setBackground("#FFC0CB") //背景色をピンクにする 49 .setRanges([range]) //範囲は現在使われている範囲 50 .build(); //条件を確定 51 52 // 既存の条件付き書式のルールを取得する 53 let rules = sheets[i].getConditionalFormatRules(); 54 55 // 既存のルールに今回作成したルールを追加 56 rules.push(rule); 57 58 // 既存と新規のルールを合わせてシートにセットする 59 sheets[i].setConditionalFormatRules(rules); 60 } 61}ここに言語を入力 62コード

YellowGreen様

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

![イメージ説明]

イメージ説明

イメージ説明

イメージ説明

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

touch-touch

2023/03/29 07:12

協力してくださり、ありがとうございます。 調査したことは、近いコードを探した事です。 試したことは、そのコードを使用しても、ダメだったことです。 どうぞ、よろしくお願いします。
YellowGreen

2023/03/29 08:02

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

2023/03/29 09:47

検討してくださり、ありがとうございます。 既存のものは削除せずに、そのまま残しておきたいと考えています。 どうぞ、よろしくお願いいたします。
guest

回答2

0

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

投稿2023/03/31 18:20

YellowGreen

総合スコア680

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

YellowGreen

2023/03/31 18:23

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

0

ベストアンサー

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

javascript

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

になります。

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

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

javascript

1function set_conditional_format_all_sheets() { 2 // UIを取得 3 const ui = SpreadsheetApp.getUi(); 4 // 現在開いているスプレッドシートを取得 5 const ss = SpreadsheetApp.getActiveSpreadsheet(); 6 // スプレッドシートに存在するすべてのシートを取得 7 const sheets = ss.getSheets(); 8 // 条件付き書式を追加するシートの選択を求める 9 const input = ui.prompt('確認', '条件付き書式を追加します。\n' + 10 'シートの位置を入力してください。\n' + 11 '何も入力がないと最初のシートから追加します。', ui.ButtonSet.OK_CANCEL); 12 // キャンセルが押されたらスクリプトを終了する 13 if (input.getSelectedButton() == ui.Button.CANCEL) { 14 return; 15 } 16 // 開始位置を取り出す 17 const position = input.getResponseText() == '' ? 0 : input.getResponseText(); 18 // 1シートずつ条件付き書式をセットしていく 19 for (let i = position; i < sheets.length; i++) { 20 const lastRow = sheets[i].getLastRow(); 21 if (lastRow > 0) { 22 // 条件付き書式をセットするのはシート内で現在使われている範囲 23 const range = sheets[i].getRange(1, 1, lastRow, sheets[i].getLastColumn()); 24 // 条件付き書式のセット 25 const rule = SpreadsheetApp.newConditionalFormatRule() 26 .whenFormulaSatisfied('=($G1<>"")')//←Gに修正 27 .setBackground('#FFFFFF')//背景色に白を指定 28 .setRanges([range]) 29 .build(); 30 // 既存の条件付き書式のルールを取得する 31 const rules = sheets[i].getConditionalFormatRules(); 32 // 既存のルールに今回作成したルールを追加 33 rules.push(rule); 34 // 既存と新規のルールを合わせてシートにセットする 35 sheets[i].setConditionalFormatRules(rules); 36 } 37 } 38} 39//条件付き書式を処理 40function handleConditionalFormatRulesOfSheets(mode) { 41 const ss = SpreadsheetApp.getActiveSpreadsheet(); 42 const sheets = ss.getSheets(); 43 // シートの開始位置を入力 44 const ui = SpreadsheetApp.getUi() 45 const input = ui.prompt('確認', '条件付き書式の処理を実行します。\n' + 46 'シートの位置を数字で入力してください。\n' + 47 '入力がないと最初のシートから実行します。', ui.ButtonSet.OK_CANCEL); 48 // キャンセルが押されたらスクリプトを終了する 49 if (input.getSelectedButton() == ui.Button.CANCEL) { 50 return; 51 } 52 // 開始位置を取り出す 53 const position = input.getResponseText() == '' ? 0 : input.getResponseText(); 54 // 指示に応じた処理を実施 55 let isMatch = false;//探しているシートがあったかどうか 56 for (let i = position; i < sheets.length; i++) { 57 const rules = sheets[i].getConditionalFormatRules(); 58 if (mode == 'clear') { 59 if (rules.length > 0) {//書式があるなら全て削除 60 sheets[i].clearConditionalFormatRules(); 61 } 62 } else if (mode == 'find'){//書式がないシートを探す 63 //次の行の()内の条件を変更すると探すシートを変更できる 64 if (rules.length <= 0) { 65 ui.alert('シートの開始位置は、' + i + ' です。'); 66 isMatch = true; 67 break; 68 } 69 } else if (mode == 'shift' || mode == 'pop') {//一つを削除するときは残りを再設定 70 if (mode == 'shift') { 71 rules.shift();//最初の一つを削除する。 72 } else if (mode == 'pop') { 73 rules.pop();//最後の一つを削除する。 74 } 75 sheets[i].setConditionalFormatRules(rules); 76 } 77 } 78 if (mode == 'find' && !isMatch) { 79 ui.alert('シートはみつかりませんでした。'); 80 } 81} 82//条件付き書式のないシートを探す 83function findConditionalFormatRulelessSheet() { 84 handleConditionalFormatRulesOfSheets('find'); 85} 86//最初を削除 87function shiftConditionalFormatRuleOfSheets() { 88 handleConditionalFormatRulesOfSheets('shift'); 89} 90//最後を削除 91function popConditionalFormatRuleOfSheets() { 92 handleConditionalFormatRulesOfSheets('pop'); 93} 94//全て削除 95function clearConditionalFormatRulesOfSheets() { 96 handleConditionalFormatRulesOfSheets('clear'); 97} 98//メニューの表示 99function onOpen() { 100 // UIの取得 101 const ui = SpreadsheetApp.getUi() 102 // メニューを作成 103 ui.createMenu('スクリプト実行') 104 .addItem('全シートに条件付き書式を追加する', 'set_conditional_format_all_sheets') 105 .addItem('条件付き書式がないシートを探す', 'findConditionalFormatRulelessSheet') 106 .addItem('全シートの条件付き書式から最初の一つを削除する', 'shiftConditionalFormatRuleOfSheets') 107 .addItem('全シートの条件付き書式から最後の一つを削除する', 'popConditionalFormatRuleOfSheets') 108 .addItem('全シートの条件付き書式を全て削除する', 'clearConditionalFormatRulesOfSheets') 109 .addToUi(); 110}

投稿2023/03/30 01:47

編集2023/03/31 19:01
YellowGreen

総合スコア680

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

YellowGreen

2023/03/30 02:12 編集

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

2023/03/30 14:19

YellowGreen様、迅速にプログラムを作成してくださり、ありがとうございます。 頂いたコードを組んでみたところ、すべての背景色が白になってしまいました。 シート数が1000を超えるとお伝えしていましたが、100シートほどあるフォルダが10個ほどあるという事でした。(誤解を招く書き方をして、もうしわけありませんでした。) どこを調整するとよいでしょうか。 お手数ですが、お知恵をおかりできないでしょうか。
YellowGreen

2023/03/30 17:27

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

2023/03/30 19:44 編集

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

2023/03/30 19:45

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

2023/03/30 19:49 編集

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

2023/03/31 11:33

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

2023/03/31 11:50

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

2023/03/31 18:28 編集

> 「シートの位置入力」の画面が出て、『実行開始』したもののグルグル読み込んだままとなり、『実行完了』になりません。 「シートの位置入力」には、何を記入すると良いでしょうか。 ↓ 回答でお答えしてますように 「以下にお示しするのは、シートの数が1000を超えるとなると、実行中にタイムアウトなどのエラーが発生することも考えられるので、条件付き書式が追加されないシートが残ってしまうことを考慮したスクリプトです。 実行画面で追加するシートの開始位置(左端を0としたときのシートの順番)を指定するようにしてあります。」 画面に表示されているとおり、何も入力しないと最初のシートから処理をします。 前回、全て真っ白になったとおっしゃっててた際も同じ表示が出ていたと思いますが、今回が初めてだったのであれば、回答でお示ししたコードをそちらで何か変更されている点があるのでしょうか。
YellowGreen

2023/03/31 18:20 編集

実行開始からどのくらいの時間がたって実行完了にならないとご判断されましたでしょうか。 その際は、ご自身で処理を中断されたのでしょうか? タイムアウトになったのでしょうか。 念のため、実際にFからGに修正した後のコードをコピペでアップしてみていただけませんでしょうか。
YellowGreen

2023/03/31 19:03

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

2023/03/31 19:13

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

2023/04/01 16:11

YellowGreen様  本当にありがとうございます。 時間をかけて読み込ませると、「スクリプト実行」がメニューに追加され、「条件付き書式を追加する」で、求めていたことが可能になりました。 しかし、Apps Scriptを開こうとすると、エラーが出てしまいます。 どのように対処したらよろしいでしょうか。 (参考までに画像を添付いたします)
YellowGreen

2023/04/01 16:52 編集

そのようなエラーは、通常はブラウザのキャッシュやクッキーが破損しているときに出るようです。 対応としては、ブラウザのキャッシュやクッキーの削除になりますが、クッキーを削除するとサイトへのログイン情報なども消えますので、ご留意ください。 なお、念のため 「400 要求の形式が正しくありません」 でGoogle検索をして確認してください。
YellowGreen

2023/04/01 16:56

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

2023/04/02 09:08

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

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.51%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問