![見出し画像](https://assets.st-note.com/production/uploads/images/102934017/rectangle_large_type_2_2cf7fd1e96731768f579dc26c233a0b3.png?width=1200)
Googleスプレッドシート【GASで】 選択セルをハイライト表示する
関数ネタが続いたので、軽めの GAS(Google Apps Script)ネタを入れましょう。
お題は Excelではお馴染みの 選択セル の行、列を色付けしてハイライト表示するアレです。Googleスプレッドシート上でGASでも出来るのか? 検証してみましょう。
別シリーズ前回の記事
Excelでは見かけるアレ(ハイライト表示)
![](https://assets.st-note.com/production/uploads/images/102968606/picture_pc_ca7be93d67213f0221472dbd7c40f818.gif?width=1200)
ハイライト表示ってなに?
って方は上の画像を見れば「ああ、これね!」ってなるでしょう。選択している(アクティブな)セルの行・列を色付けして目立たせています。
特にみっちり詰まった大量データの場合、自分が選択しているセルの行・列が何のデータか?をわかりやすくする利点があります。ミスの防止にもつながりますね。
Excelだと昔からある手法で、VBAは必要なものの 短いコードと 条件付き書式を組み合わせることで簡単に実現できるので、使ったことある人も多いんじゃないでしょうか?
"Excel アクティブセル ハイライト" あたりで検索すると、やり方を紹介しているサイトがわんさか見つかります。
![](https://assets.st-note.com/img/1681438684868-qhCAk9XCEv.png?width=1200)
Excelでのハイライト表示方法
サクッと触れておきましょう。
まずは シート全体を選択して、条件付き書式を設定します。
![](https://assets.st-note.com/img/1681537118668-VngDGLRPvH.png?width=1200)
「数式を使用して」を選び以下の式を入れます。
=OR(CELL("ROW")=ROW(), CELL("COL")=COLUMN())
VBA設定前ですが、この段階でも以下のように ダブルクリックでセルを入力モードにしてから Enterしたり、キーボード F9で 再計算 させることで、フォーカスされます。
![](https://assets.st-note.com/production/uploads/images/103065221/picture_pc_2d6000553b42cfc17d2842baf9549bbc.gif?width=1200)
これを セルを選択しただけで フォーカスされるようにする為、マクロを設定します。
コードは ThisWorkbook にコピペします。
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.ScreenUpdating = True
End Sub
![](https://assets.st-note.com/img/1681538253397-jn1k1UwQ5R.png?width=1200)
このコードよってセルを選択した際に 画面更新が実行され、先ほどのマクロ設定前の F9キーを押したのと同じ効果が得られ、最初に掲載した画像の動きとなります。
これを Googleスプレッドシートでやってみましょう!
Googleスプレッドシートで 選択セルをハイライト表示する
![](https://assets.st-note.com/production/uploads/images/103066803/picture_pc_8bed9a2fc3c5ff130dbe21388dc4bfc9.gif?width=1200)
Googleスプレッドシートの場合は GASを使う必要があるので、特有の動作のもったり感はあるものの Excelと同じように選択セルのハイライトが出来てますね!
これをどう作るか?
選択セルをハイライト表示する GASコード
いきなり答えです。
function onSelectionChange(e) {
const color = "#FFE4C4" //明るいオレンジ3の近似色
const target = "A:Z" // 条件付き書式適用範囲
const targetSheet = "シート1"; //ハイライト表示するシート名
const range = e.range;
const sheet = range.getSheet();
const row = range.rowStart;
const col = range.columnStart;
if(sheet.getName() != targetSheet) return ;
const targetRange = sheet.getRange(target);
const rules = sheet.getConditionalFormatRules();
const rule = SpreadsheetApp.newConditionalFormatRule()
.whenFormulaSatisfied(`=OR(ROW()=${row},COLUMN()=${col})`)
.setBackground(color)
.setRanges([targetRange])
.build();
rules.splice(0,1,rule);
sheet.setConditionalFormatRules(rules);
}
対象シートの対象範囲に対して、選択セルの 行 または列と一致するセルを色付けするという条件付き書式を セル選択の度に書き換えるというコードになっています。
常に条件付き書式の1つ目を差し替えて更新する動きなので、他にも条件付き書式を利用している場合は、このまま使うと書き換えられちゃいます。先にシートの範囲に対してダミーの条件付き書式を1つ目に仮設定しておく必要があります。
ちなみにコレの元ネタは、mirが 2020年8月に 知恵袋で回答したコードになります。(この時はまだ コード書く回答もしてたのかw)
解説していきましょう。
選択セルをハイライト表示する為の2つのハードルを越える
Excelと同じことをする為に 2つのハードルをクリアする必要があります。
スプレッドシート CELL関数は 第2引数を省略できない
選択セルを変えただけで発動するトリガーってあるの?
1. スプレッドシート CELL関数は 第2引数を省略できない
エクセルで使った条件付き書式の式ですが、
CELL("ROW")
こんな記述をしてました。あまり見かけない CELL関数の書き方ですが、公式にも説明があります。
CELL(検査の種類, [対象範囲])
範囲 情報が必要なセルを指定します。
任意
省略すると、計算時に選択info_type引数に指定された情報が返されます。 参照引数がセル範囲の場合、CELL 関数は、選択した範囲内のアクティブ セルの情報を返します。
選択info_type引数??ってなりますが、要は計算が走った時(F9キー押した時)に選択しているセルのことです。
![](https://assets.st-note.com/production/uploads/images/103069386/picture_pc_cc7415b9e6bfdf24d3f6b98c9a6eb51d.gif?width=1200)
このように セルを選択して F9キーで再計算させることで、VBAなしで 行番号、列番号を出力したB1:B2セルが 更新されます。面白いですね。
この CELL関数は Googleスプレッドシートにもあるのですが、残念ながら 第2引数の 範囲は省略できません。
![](https://assets.st-note.com/img/1681541895976-JvWmBTl7VW.png?width=1200)
Googleスプレッドシートの場合は、選択しているセルの行番号、列番号を GASで取得する必要があるってことです。
2. 選択セルを変えただけで発動するトリガーってあるの?
それでは GASを手動で実行する以外で、選択したセルの情報を取得するにはどうすればよいでしょうか?
セルを「編集」した場合は onEdit(e) というシンプルトリガーで 編集されたセルの情報が取得できます。これはGASに触れたことがある人なら知っているかと思います。
今回は編集じゃなくて、単にセルを選択しただけでGASを実行させたい・・・。そんなこと出来るのか?
実はあまりメジャーではありませんが、スプレッドシートには 選択を変更した際に発動する シンプルトリガーがあるんです。
それが 上のコードの関数名の onSelectionChange(e) です。
シンプルトリガー onSelectionChange(e) を使ってみる
どんな感じに使えるのか?わかりやすい例を見てみましょう。
function onSelectionChange(e){
const range = e.range;
const sheet = range.getSheet();
const row = range.rowStart;
const col = range.columnStart;
sheet.getRange("B1:B2").setValues([[row],[col]]);
}
こんなコードを書くと 以下のように選択セルの変更に合わせて、そのセルの行番号、列番号を B1:B2に出力することができます。
![](https://assets.st-note.com/production/uploads/images/103072060/picture_pc_098e8d500213dbf75a9224376fa2ad28.gif?width=1200)
GASなんで少し反応が遅いですが、ExcelのCELL関数 + F9キーと同じようなことが出来ました。
ちなみに、このセルに書き出した数値を使えば、条件付き書式の部分の GASコードは省略できます。
今回の場合だと条件付き書式で カスタム数式で
=OR(ROW()=$B$1,COLUMN()=$B$2)
とすれば良いです。
でも、データが入ってるシートだと 書き出す場所が悩ましいんで、条件付き書式の更新部分も入れ込んだコードを作成しました。
`=OR(ROW()=${row},COLUMN()=${col})`
ここですね。範囲内の セルの行番号が rowと一致する、または 列番号が colと一致する場合に trueを返す(色がつく)という式を GASで条件付き書式のカスタム数式にセットしています。
このバッククォートを使った記述は、テンプレートリテラルと言われるものです。
${row} このようにすることで、+での連結不要で 文字列内に変数を組み込めるんで、GASで シート関数、数式を扱う時にはこれが超便利です。
ちなみに この onSelectionChange(e)ですが、公式にも掲載されているのに なんであまり知られてない(広まってない)のか?
それはこのトリガーが 選択を変えただけでバンバン発動する上に、やや動作が不安定だからです。
今回の処理も一人用スプレッドシートなら良いですが、複数人が同時アクセスする場合は思うように動かないことも多く、利用をお勧めしません。
また、シートの条件付き書式の適用範囲が大きすぎる場合も かなり動作が重くなります。
このトリガーの処理の動きが悪い、もしくは動かないといった場合は、Ctrl + F5 で リロードすると改善されたりします。
選択しただけでトリガー実行できる onSelectionChange(e) を使う処理は色々思いつきますが、不安定で動作しないこともあることを理解した上で使用し、重要な処理には使わないようにしましょう。
GASで条件付き書式を設定する
条件付き書式を GASで扱う際は、ConditionalFormatRule クラスを使用します。
// 現在シートに設定されている全ての条件付き書式を取得
const rules = sheet.getConditionalFormatRules();
//新しいルール(条件付き書式)を用意
const rule = SpreadsheetApp.newConditionalFormatRule()
.whenFormulaSatisfied(`=OR(ROW()=${row},COLUMN()=${col})`)
.setBackground(color)
.setRanges([targetRange])
.build();
ハイライト表示が 初回(条件付き書式が無い状態)なのか、2回目以降(既に条件付き書式が設定されている状態)なのか 判定が面倒だったんで、毎回新しい条件付き書式を用意して、1つ目の条件付き書式と差し替えています。
条件付き書式の部分のコードを細かく説明すると話が広がってしまうので、ここではさくっと割愛します。
この条件付き書式のGASコードを書くのがハードル高いって場合は、、ぶっちゃけ手動で条件付き書式を設定する流れを マクロ記録して、その取得したコードを修正利用って方法でも良いです。
条件付き書式の1番目を差し替える splice
//既存の条件付き書式 の配列1つ目を rule(作成した条件付き書式)に差し替え
rules.splice(0,1,rule);
//更新された 条件付き書式配列 をシートに反映
sheet.setConditionalFormatRules(rules);
配列の要素差し替えは splice() という配列メソッドを使います。
array.splice(start, deleteCount, item1)
↓
rules.splice(0,1,rule);
rulesという配列の 0(1つ目)から 1つ分の要素を取り除き、
その位置に rule を挿入する
これのメリットは、初回で 既存の条件付き書式が無い場合(配列が空)でもエラーにならず 要素の1つ目に入るという点です。
以下のコードで動作が分かるかと思います。
rules1 の場合は 1つ目の要素が差し替えとなり、空っぽの rules2 の場合はそのまま rule("test") が配列に入っています。
![](https://assets.st-note.com/img/1681545509502-ou2yRsAwxJ.png)
Googleスプレッドシート GASで 選択セルをハイライト表示する方法まとめ
まとめです。コードの書き方や 処理手順としては他の方法もありますが、いずれのアプローチでも
条件付き書式を使う
onSelectionChange(e) で選択したセルの情報を取得する
この2つをおさえる必要があることを覚えておきましょう。
![](https://assets.st-note.com/production/uploads/images/103076893/picture_pc_006659a20e4a7aa17d2c232a69f4950a.gif?width=1200)
GAS 選択セルハイライトコード
function onSelectionChange(e) {
const color = "#FFE4C4" //明るいオレンジ3の近似色
const target = "A:Z" // 条件付き書式適用範囲
const targetSheet = "シート1"; //ハイライト表示するシート名
const range = e.range;
const sheet = range.getSheet();
const row = range.rowStart;
const col = range.columnStart;
if(sheet.getName() != targetSheet) return ;
const targetRange = sheet.getRange(target);
const rules = sheet.getConditionalFormatRules();
const rule = SpreadsheetApp.newConditionalFormatRule()
.whenFormulaSatisfied(`=OR(ROW()=${row},COLUMN()=${col})`)
.setBackground(color)
.setRanges([targetRange])
.build();
rules.splice(0,1,rule);
sheet.setConditionalFormatRules(rules);
}
mirの noteは関数ネタはかなり重めの式を扱いますが、基本的にGASネタは 今回のような 軽めのものを取り扱ってます。(そんな複雑なコード書けないんでw)
しかも、〇〇システムみたいな完成形じゃなくて、あくまでも小ネタ、便利なようなそうでもないようなって所を突いていきたいってスタンスですw
最近忙しくて noteも結構ギリギリ週1で書いてるんで、初期のころに比べネタ要素が減ってきちゃったのが自分でも不満・・・。
でも、まだまだ書きたいことはあるんで単に時間と体力がないってだけなんですよね。
次回はまた関数ネタに戻ろうと思います。
2024年6月追記
Chromeの拡張機能でいいやつがありますね。こちらおススメ。
いいなと思ったら応援しよう!
![mir](https://assets.st-note.com/production/uploads/images/85302011/profile_6bb7e63e3aff027fa87115b6d37e1556.jpg?width=600&crop=1:1,smart)