スプレッドシートの行を絞り込むとき、通常はセルに入力された値で絞り込みを行います。
ですが、時として
「色をつけた行だけ抽出したい！」
ということもあると思います。
今回は、スプレッドシートで色付きの行のみ絞り込む方法をご紹介します。
絞り込みの基本はこちら
セルに入力された値で、行を絞り込む方法はこちらをご参照ください。
スプレッドシートで色付きの行のみ絞り込む方法
このような社員の名前と所属部署のリストがあったとします。
このリストをサンプルデータとして、行の絞り込み処理を見ていきます。
色指定での絞り込み
黄色に背景色が指定されている行のデータのみを絞り込んでみます。
function main() {
const spreadsheet = SpreadsheetApp.openById([id])
const dataSheet = spreadsheet.getSheetByName("データ")
const backgroundArray = dataSheet.getRange(2, 1, 10, 5).getBackgrounds()
console.log("背景色の取得")
console.log(backgroundArray)
const dataArray = dataSheet.getRange(2, 1, 10, 5).getValues()
console.log("各セルに入力された値の取得")
console.log(dataArray)
const filteredArray = []
for (let i = 0; i < dataArray.length; i++) {
if(backgroundArray[i][0] === "#ffff00") {
filteredArray.push(dataArray[i])
}
}
console.log("絞り込み後の値")
console.log(filteredArray)
}実行結果
背景色の取得
[ [ '#ffffff', '#ffffff', '#ffffff', '#ffffff', '#ffffff' ],
[ '#ffffff', '#ffffff', '#ffffff', '#ffffff', '#ffffff' ],
[ '#ffffff', '#ffffff', '#ffffff', '#ffffff', '#ffffff' ],
[ '#ffffff', '#ffffff', '#ffffff', '#ffffff', '#ffffff' ],
[ '#ffffff', '#ffffff', '#ffffff', '#ffffff', '#ffffff' ],
[ '#ffff00', '#ffff00', '#ffff00', '#ffff00', '#ffff00' ],
[ '#ffffff', '#ffffff', '#ffffff', '#ffffff', '#ffffff' ],
[ '#ffffff', '#ffffff', '#ffffff', '#ffffff', '#ffffff' ],
[ '#ffff00', '#ffff00', '#ffff00', '#ffff00', '#ffff00' ],
[ '#ffffff', '#ffffff', '#ffffff', '#ffffff', '#ffffff' ] ]
各セルに入力された値の取得
[ [ 1, 'テストデータ1', '営業本部', '営業一課', '法人チーム' ],
[ 2, 'テストデータ2', '営業本部', '営業一課', '法人チーム' ],
[ 3, 'テストデータ3', '営業本部', '営業二課', '法人チーム' ],
[ 4, 'テストデータ4', '経営本部', '経営企画', 'データ管理チーム' ],
[ 5, 'テストデータ5', '経営本部', '経営企画', 'データ管理チーム' ],
[ 6, 'テストデータ6', '経営本部', '経営企画', '経営企画チーム' ],
[ 7, 'テストデータ7', '開発本部', '開発部', 'サーバチーム' ],
[ 8, 'テストデータ8', '開発本部', '開発部', 'サーバチーム' ],
[ 9, 'テストデータ9', '開発本部', '開発部', 'クライアントチーム' ],
[ 10, 'テストデータ10', '開発本部', '開発部', 'インフラチーム' ] ]
絞り込み後の値
[ [ 6, 'テストデータ6', '経営本部', '経営企画', '経営企画チーム' ],
[ 9, 'テストデータ9', '開発本部', '開発部', 'クライアントチーム' ] ]最後の [絞り込み後の値] の後に表示されているのが、目的のデータです。
黄色く背景色が指定されている行のデータのみが表示されていることがわかると思います。
サンプルプログラムの解説
実際にデータの絞り込みを行っているのは、この部分です。
const filteredArray = []
for (let i = 0; i < dataArray.length; i++) {
if(backgroundArray[i][0] === "#ffff00") {
filteredArray.push(dataArray[i])
}
}セルの値が格納されている配列をforループで回して、何らかの条件と一致した要素のみ別の配列に移し替えているようです。
backgroundArrayには、どんな値が入っているのか？
なぜ配列をわざわざ移し替えているのか？
・・・と、あまりまだ処理全体にピンと来てないと思います。
サンプルプログラムを各ステップに分けて詳しく見ていきましょう。
背景色の取得
const backgroundArray = dataSheet.getRange(2, 1, 10, 5).getBackgrounds()
console.log("背景色の取得")
console.log(backgroundArray)2行目、3行目はログの出力なので、実質絞り込みに必要な処理は1行目のみです。
getRange関数は、引数で指定した範囲のセル(Range)を取得する関数です。
その後に呼び出されるgetBackgrounds関数は、背景色をカラーコードで取得する関数です。
スプレッドシート2～11行目、画像の赤く囲まれた部分の背景色を取得するイメージです。
ここの部分のログ出力を今一度見てみましょう。
上の画像と見比べると、黄色く塗られている6行目、9行目だけ他と違い [#ffff00]の値が格納されいるのがわかると思います。
背景色の取得
[ [ '#ffffff', '#ffffff', '#ffffff', '#ffffff', '#ffffff' ],
[ '#ffffff', '#ffffff', '#ffffff', '#ffffff', '#ffffff' ],
[ '#ffffff', '#ffffff', '#ffffff', '#ffffff', '#ffffff' ],
[ '#ffffff', '#ffffff', '#ffffff', '#ffffff', '#ffffff' ],
[ '#ffffff', '#ffffff', '#ffffff', '#ffffff', '#ffffff' ],
[ '#ffff00', '#ffff00', '#ffff00', '#ffff00', '#ffff00' ],
[ '#ffffff', '#ffffff', '#ffffff', '#ffffff', '#ffffff' ],
[ '#ffffff', '#ffffff', '#ffffff', '#ffffff', '#ffffff' ],
[ '#ffff00', '#ffff00', '#ffff00', '#ffff00', '#ffff00' ],
[ '#ffffff', '#ffffff', '#ffffff', '#ffffff', '#ffffff' ] ][#ffff00] とは黄色を表す値で、カラーコードと呼ばれています。
赤緑青の光の三原色の強さをそれぞれ16進数で表した値です。
・・・と言われても、よくわかりませんね。
なので、今回は以下の表の対応関係だけ覚えておいてください。
| カラーコード | 色 |
|---|---|
| #ffff00 | 黄色 |
| #ffffff | 白色 |
ログ出力をもう一度見てみると、黄色く塗られている6行目、9行目のデータには、 [#ffff00]＝黄色が格納されています。
getBackgrounds関数はカラーコードを取得する関数ですので、つまり各セルの背景色と配列の形式で取得できていることがわかりました。
セルの値の取得
const dataArray = dataSheet.getRange(2, 1, 10, 5).getValues()
console.log("各セルに入力された値の取得")
console.log(dataArray)背景色を取得した範囲と同様の範囲の値を取得しています。
背景色の配列と同じサイズの配列が取得できています。
各セルに入力された値の取得
[ [ 1, 'テストデータ1', '営業本部', '営業一課', '法人チーム' ],
[ 2, 'テストデータ2', '営業本部', '営業一課', '法人チーム' ],
[ 3, 'テストデータ3', '営業本部', '営業二課', '法人チーム' ],
[ 4, 'テストデータ4', '経営本部', '経営企画', 'データ管理チーム' ],
[ 5, 'テストデータ5', '経営本部', '経営企画', 'データ管理チーム' ],
[ 6, 'テストデータ6', '経営本部', '経営企画', '経営企画チーム' ],
[ 7, 'テストデータ7', '開発本部', '開発部', 'サーバチーム' ],
[ 8, 'テストデータ8', '開発本部', '開発部', 'サーバチーム' ],
[ 9, 'テストデータ9', '開発本部', '開発部', 'クライアントチーム' ],
[ 10, 'テストデータ10', '開発本部', '開発部', 'インフラチーム' ] ]カラーコードで絞り込む
const filteredArray = []
for (let i = 0; i < dataArray.length; i++) {
if(backgroundArray[i][0] === "#ffff00") {
filteredArray.push(dataArray[i])
}
}背景色のカラーコードの配列を一つ一つ調べて、[#ffff00]=黄色であれば配列に格納しています。
backgroundArray(背景色の配列)とdataArray(値の配列)が同じサイズの配列であることを利用して、同じインデックスを使いまわして、同じセルの背景色・値を1回のループで同時に参照しています。
社員番号が1の行の背景色と値をサンプルとして抜き出しました。
背景色は、[#ffffff]=白色ですので、配列には格納されません。
[ '#ffffff', '#ffffff', '#ffffff', '#ffffff', '#ffffff' ]
[ 1, 'テストデータ1', '営業本部', '営業一課', '法人チーム' ]次に社員番号が6の行を抜き出しました。
背景色は、 [# ffff00]=黄色ですので、配列に格納されます。
[ '#ffff00', '#ffff00', '#ffff00', '#ffff00', '#ffff00' ],
[ 6, 'テストデータ6', '経営本部', '経営企画', '経営企画チーム' ]この処理をすべての行に対して行うと以下のように背景色が黄色の行の値のみ絞り込むことができました。
絞り込み後の値
[ [ 6, 'テストデータ6', '経営本部', '経営企画', '経営企画チーム' ],
[ 9, 'テストデータ9', '開発本部', '開発部', 'クライアントチーム' ] ]まとめ
今回は、スプレッドシートで色付きの行のみ絞り込む方法をご紹介しました。
業務では、何かのフラグを色で表す機会が度々あると思います。
値とは違い、GASでの絞り込みにひと工夫必要ですが今回ご紹介した内容を思い出してプログラムを書いてみてください！
(個人的にはGASで扱いやすいように色ではなく値でフラグを付けてもらうように運用を変えることを推奨しますが…)
参考
getRange関数
Class Sheet | Apps Script | Google Developers
getBackgrounds関数
Class Range | Apps Script | Google Developers
https://developers.google.com/apps-script/reference/spreadsheet/range#getbackgrounds
