ステータス表から抽出(フィルター)を全シート一気にやりたい！/スプレッドシート_GAS
at-tecj業務の中で私は、進行状況＝ステータスをスプレッドシートで管理しています。
月別にシートを準備して、情報を書き加えていって、
ステータスが変わったらプルダウンで選択できるようになっており。
ですが月の変わり目など、対応中の情報が
2シートにまたがってしまうことも。
そうすると見落としが出て、
対応しそびれてしまう恐れも...
ということで、スプレッドシートの一番頭に
"対応中"というシートを設け、そこに
文字通り対応中の情報を詰め込むことにしました。
最初は、全部関数でやっていました。
「FILTER」ですね。
でも、
・月が変わったら(シート追加されたら)、毎回関数入力
・だんだんステータスが細分化されて増えた
っていう状況で、トンデモ不便になってしまったのです。
そこで、なんでもできそうなGASなら
FILTER関数の代わりができるんじゃないかと思い、
どうにか作ってみました。
（>時間かかっちゃったんですが...）
それではいってみましょう！
↓ひとまず完成コード
function statusFilter(){
//スプレッドシート取得
const ssId='/*スプレッドシートのID*/';
const ss=SpreadsheetApp.openById(ssId);
//書き出し先配列
const dataArray=[];
//シート番号で順番に取得
for(let i=1; i<=7; i++){
const sheet=ss.getSheets()[i];
//ステータス判定
const itemArray=['未対応','メール済','日程調整中','日程確定','要連絡'];
const countRow=sheet.getRange('A3:A').getValues().filter(String).length;
const data=sheet.getRange(3,1,countRow,4).getValues();
for(let t=0; t<countRow; t++){
if(itemArray.includes(data[t][1])==true){
dataArray.push(data[t]);
}//条件ifのとじかっこ
}//配列に含まれるか検索する繰り返しforのとじかっこ
}//シート取得繰り返しforのとじかっこ
//書き出し
const writeSheet=ss.getSheets()[0];
writeSheet.getRange('A4:D').clearContent();
const colum=dataArray[0].length;
const row=dataArray.length;
writeSheet.getRange(4,1,row,colum).setValues(dataArray);
}書き出したあとの見た目↓
コード解説
全体の構成はざっくりこんな感じです。↓
処理をひとつの部品と捉えて、
この部品(ブロック)を繰り返す、と考えると
非常に分かりやすいです。
結構細かく書いていくつもりですので、
「そんなこと分かってるよ！」ってとこは
各々読み飛ばして進めてくださいね！
前提条件の定義
まずはスプレッドシートの取得と、
配列の定義から。
スプレッドシートは、私はIDで取得しています。
IDは、スプレッドシート開いた時に
上の方に表示されているアドレスの一部です。
docs.google.com/spreadsheets/d/asdfghklqwertyuiopzxcvbnm/edit?gid=0#gid=0
/d/と/editの間の、オレンジで色つけた部分です↑
※文字列はダミーです。
このIDにssIdと名前を付けてスプレッドシート自体を取得し、
> const ssId='/スプレッドシートのID/';
取得したスプレッドシートにssと名前をつけておきます。
> const ss=SpreadsheetApp.openById(ssId);
それから、あとで抽出したデータの一時保存用の
空の配列を先に準備しておきます。
> const dataArray=[];
これ上の方に書いておかないと、
何かの繰り返しの途中に置いてしまったら
どんどんデータが上書きされていって
最後に取得したデータしか残らなかったので、
みなさんはご注意ください...
（byやらかした人）
//シート番号で順番に取得
コード構成図で言うと、「②シート取得繰り返し」の部分です。
あとにやりますステータス判定を、
指定するシート分繰り返す処理です。
取得し判定していくシートに
「sheet」と名前を付けておきます。
実際組むときは、中のステータス判定の方を
先に考えると思いますので、
一旦とばして後で戻って読むことをお勧めします。
・・・
シートはてっきり名前でしか取得できないと思っていたら、
番号で取得できることが分かりました。
知らなかった...大変便利じゃあございませんか。
数字で取得できるなら、ひとつひとつシートを名前で取得しなくても、
繰り返しforを使って短いコードで仕上げられるってものです。
↑分かりやすく番号でシートに名前をつけてみました。
左から数えるのですが、プログラムあるある
一番最初は【0】番目になります。
この場合"対応中"シートが【0】番目になりますので、
取得したいのは【1】～【7】番目。
つまり、1～７の間繰り返したい。
ということで、変数【 i 】の中に
1から7まで順番に数字が入るようにしていきます。
※文字は多分なんでも大丈夫なんですが、
iを使うことが多いみたいです。
理由は忘れました(￣▽￣)
constは定数で、中身を変化させることができないので
ここではletを使っています。
スタートは1
> let i=1;
７まで繰り返す（以下なので<=）
> i<=7;
※ i<8でも可
1から7まで1ずつ増やす
> i++
回数の分かっている繰り返しは
forを使いますので、
> for(let i=1; i<=7; i++)｛ //この中に繰り返したい処理を書く ｝
シートを番号で取得する時は、
const sheet=ss.getSheets()[2];
のように、[ ]の中にシート番号を入れます。
我々今回は1～7まで順番に取得したく、
その数字は【 i 】で伝わるようにしたので、
> const sheet=ss.getSheets()[i];
と書いておきます。
これで、判定するシートを順番に取得することが
できるようになりました。
//ステータス判定
コード構成図で言うと、①ステータス判定繰り返し(条件分岐)のとこです。
ここでは、条件判定のための準備として、
・当てはまったら抜き出したいステータス群
・条件判定する範囲
を定義しています。
その後、指定した範囲を一行ずつ
条件と照らし合わせて判定し、
当てはまったら配列に入れる
...ってのを繰り返していきます。
条件判定の準備
条件に使うステータス群
未対応、メール済、日程調整中、日程確定、要連絡
...のステータスだったら、対応中として認識していたいので、
どれかに当てはまったら抜き出したい。
それらを一個の配列にまとめて、
itemArrayと名前をつけておきます。
const itemArray=['未対応','メール済','日程調整中','日程確定','要連絡'];
条件判定する範囲
一行ずつ判定していくのですが、
何回繰り返すのか＝行数を取得したいので、
名前の行で数えていきます。
そうすると、例えデータが追加されて
行数が変わっても問題なく対応できます。
とりあえず、行数にcountRowと名前を付けておきます。
> const countRow=sheet.getRange('A3:A').getValues().filter(String).length;
sheetは、ステータス判定を行う
シートにつけた名前です。
ここで、さっき一旦とばしたところに戻ってみましょう＾＾
名前の行で数えると確実かなと思ったので、
名前が入っているA列を、A3以下から全部取得します。
> . getRange（’ A3 : A ’）
で、一旦中身を取得して、
> . getValues ( )
文字が入っているデータだけ抜き出したいので、
> .filter ( String )
↑これで、例えばよくわかんない記号だけ入ってる...など
意図せずできてしまったデータを省き、
有効なデータだけにできます。
見る感じそんなとこないけど、念のため。
（別の解説ページ参考にしました）
そして、取得したデータの数
> .length
A列に文字が入っているデータの行数
＝繰り返し処理を行う数
になります。
これで、繰り返す回数を
countRowと呼ぶことができます。
>（再） const countRow=sheet.getRange('A3:A').getValues().filter(String).length;
その他の取得したい範囲は分かっているので、
数字で指定してあげます。
> const data=sheet.getRange(3,1,countRow,4).getValues();
. getRangeで取得する時、
（開始行 , 開始列 , 取得行数 , 取得列数）
を番号で取得することができます。
こっちは【0】番目とかないので、
一列(行)目は1と書けます。
今回は、A3（縦は3行目、横は1列目）スタートで
countRow分の行数、A~Dまでの4列取得したいので、
> ( 3 , 1 , countRow , 4 )
ということになります。
範囲を指定できたので、. getValues ()
でデータを取得します。
取得したデータの範囲に
dataと名前をつけたので、
>（再） const data=sheet.getRange(3,1,countRow,4).getValues();
よし！
これでようやく、ステータス判定の準備が整いましたよ！
条件分岐でステータス判定
構成図の①ステータス判定、
条件分岐のところに進んでいきます。
考え方的には、
①一行ずつステータスの部分見る
②抜き出したいやつなのか判定
③抜き出したいやつだったらとっておく
って感じです。
スクリプト的に言うと、
①指定した範囲(dataと名付けた)を一行ずつ取得
②ステータスが表示されているデータを場所で指定
③②が予め定義しておいた配列(itemArray)に含まれるか判定
④③が含まれるなら空で準備した配列(dataArray)に格納
という流れになります。
> for(let t=0; t<countRow; t++){
if(itemArray.includes(data[t][1])==true){
dataArray.push(data[t]);
}
①一行ずつ取得
シート取得と同じで、
文字を使って範囲(繰り返し回数)を指定し、
一行ずつ見ていきます。
i の文字は使ってしまっていて、
もう一回使おうと思ったら
動作しなかったので、
仕方なく違う文字を使います。
適当にｔとでもしておきますか。
ｔが0番目から、データの行数分
繰り返してもらうよう、
1ずつ増えるようにします。
さっき取得したい行数に
countRowと名前をつけておいたので、
それを使っちゃいます。
> for(let t=0; t<countRow; t++) { //処理を書く }
これで、0スタートで1行ずつ、
ｔ行目まで順番に判定できるようになりました。
②ステータス取得
上の{ //処理を書く }の
中身を書いていきます。
範囲を指定して取得した「data」
のなかで、ステータスが表示されている部分を
場所で特定して取得します。
「data」は下の画像で言うと、
A3：D10までの範囲です。
この中で、例えばAさんのステータスを判定したいときは、
B3にステータスが書かれていますので、
dataの中の、1行目の2列目を取得したい。
書き方的には、
data [ 行 ] [ 列 ]
となります。
何番目...で指定する時は0スタートなので、
B3はdata[ 0 ] [ 1 ]
となります。
行数はある分だけ順番に取得したいので、
ｔで表せるようにしておきましたね。
なのでそれぞれのステータスの場所は
data [ t ] [ 1 ]
でよし。
③ステータス判定
②で指定して取得した文字列
data [ t ] [ 1 ]が、
判定して抜き出したいステータス群
const itemArray=['未対応','メール済','日程調整中','日程確定','要連絡'];
の中に含まれていれば、trueを返してくれる、
includes ( )
を使います。
なので言葉にすると、
”もしdata [ t ] [ 1 ] が itemArrayに含まれる(true)ならば...”
といった具合に。
スクリプトにすると、↓↓
if( itemArray . includes ( data [ t ] [ 1 ] ) = = true ) {
//当てはまったときに行う処理
}
④抜き出したもの格納
↑に書いた、
//当てはまった時に行う処理
の部分です。
ステータス判定（includes）して、
抜き出すやつ（true）だった場合、
あとで全部まとめて表示したいので、
一時保存箱に入れておきます。
一時保存箱は、このスクリプトの書き始め、
いっちばん最初に用意しておきました。
配列「dataArray」です。
データの順番的には、
後ろにどんどん追加していってほしいので
「push」を使います。
> dataArray.push(data[t])
データ範囲を取得する時点で
4列分取ってこいよって言ってあるので(data)、
何番目の行か(ｔ)だけ指定すれば
氏名、ステータス、日付、対応中チェックの
4列とも格納してくれます。
これで、抽出が完了です！ためしに、
console.log（dataArray）;
でちゃんと格納されているか確かめてみてください＾＾
//書き出し
いよいよ、抽出したものを
一番頭のシートに書き出します。
やることとしてはこんな感じ↓
①シート取得
②元データの削除
③書き出すデータの数取得
④範囲を指定して書き出し
①シート取得
シート取得方法は名前でもいいんですが、
わざわざ２行書くのが面倒だったので、
私は番号で取得しました。
> const writeSheet=ss.getSheets()[0];
頭なので【0】番目ですね。
writeSheetと名付けました。
②元データの削除
今後何度も更新するので、
古い情報は一旦消します。
見出しの行は変えずに置いておきたいので、
消すのは「A3：D」にしました。
> writeSheet.getRange('A3:D').clearContent();
getRangeで消したい範囲を取得してから、
clearContentでさよならです。
スクリプトで消したものは、
スプレッドシートに戻ってCtrl+Zしても
元に戻せないのでご注意くださいね！
③書き出すデータの数取得
何行分、何列分あるかを取得します。
配列dataArrayに格納したデータの
数、長さを数えると指定できます。
列はもう決まってるので別に4でいいんですが、
データに合わせるべく一応取得しましょう。
columと名付けることにします。
> const colum=dataArray[0].length;
[ ]の中は別に何番目のデータでもいいんですが、
ひとまず一番最初のもので数えてもらいましょう。
今回の私の例で言うと、
「氏名」、「ステータス」、「日付」、「対応中チェック」の
4つが長さ(length)として取得できます。
行は、4列分をひとつのくくりとした
データの数です。
rowとでも名付けておきます。
> const row=dataArray.length;
４列＝一人分のデータなので、
何人分格納されているのかを
取得できます。
④範囲を指定して書き出し
開始行、開始列、書き込む行数、書き込む列数を
指定してあげます。
開始はA3から始めてほしいので、
3行目、1列目。
書き込む行数は、さっきrowと名付けたもの。
書き込む列数は、4なんだけどさっきcolumと名付けて取得しました。
これらを合わせて、
> .getRange(3,1,row,colum)
で範囲を取得します。
あとは貼り付けるだけなので、
> .setValues(dataArray);
書き込むシートを指定して、
> writeSheet.getRange(3,1,row,colum).setValues(dataArray);
これで完成です！
一番頭のシートは、
こんな感じになっています↓
名前（AさんとかBさんとか）の隣に、
分かりやすいようにシート番号を書いています。
これで、シートを手前から見ていって、
該当データが順番に格納されているのが
見て取れますね。
一覧に表示したいものを
「対応中」の列にチェック入れておいたので、
全部trueが表示されています。
うん。ちゃんと取得できてますね。
なんで日付の列だけこんなに
引き延ばしてあるのか不明ですが(今気づいた)、
それ以外は大丈夫でしょう(笑)
ということで。
長々書いてしまいましたが、
分かりづらいところはなかったでしょうか。
GAS触りはじめたばかりだと
疑問に思いそうなこと(私が思ったこと)を
盛り込んだつもりです。
これができれば、
・配列の扱い
・繰り返しのfor文
・条件分岐のif文
をおよそマスターしたと言えるのでは！
ifは、もし条件に合致しなかった場合(else)を
今回使ってないので、もうちょっと
お勉強の余地がありそうですね。
さらにさらに。
隣で開発やってるグルメ社員さんに、
分岐が3つ以上になるならswitchとか
使うことになるよ、と聞きました。
まだまだありますね、GAS。
ということで、今回はこの辺で。
なにかご意見ご要望ありましたら、
是非お気軽にお寄せください！
この記事の投稿者
入社1年目の本社アシスタント兼広報担当。
学歴高卒/完全未経験で拾っていただき、日々勉強しながら＆調べながら発信中。
とりあえずITパスポートは入社後３週間ちょいで取得済。
1-2か月の間、隣で開発中のシステムのテスト作業を少し手伝わせてもらったことも。
趣味は絵(アナログ)とカラオケ(週1～)、九州の実家には拾った猫３匹＆カメ１匹が鎮座。
