お仕事おつにゃッス🐈文系未経験から社内SEになった、あっきょ（@daily_code_JP）です！
スプシで作成したリストから特定のデータを探して別シートに貼り付けるのは大変ですよね？
GASを使えば1クリックで別シートに一覧として表示できます。
この記事ではコピペOKのGoogle Apps Scriptのコードで解決します。
こんな人におすすめ！
- シート内のデータを検索して別シートに貼り付けたい
- 簡単にデータを管理して見やすくしたい
- プログラミングでJavaScriptを学んだけど何を作っていいかわからない
無料で誰でもできますので気軽に学んでいきましょう！
サイト運営者
あっきょ
地方の中小企業で働く文系卒の総務＆社内エンジニア。自身のプログラミング学習の経験から、未経験者でも挫折しないでスキルアップの支援をするサイト『デイコー』を運営。得意な言語：GAS・JavaScript・Python
検索結果を一覧表示するイメージ
シートを2つ用意してリストから検索した結果を転記する機能を作成します。
仮に一致するデータを手動で抽出すると膨大な時間がかかって凡ミスも多発。
せっかくの作業も無駄になってしまいます。
しかし、今回紹介するコードを使えば1クリックでリスト内を検索して別シートにまとめます。
結果として、労力の削減とミスの減少につながります。
あっきょ膨大なリストなほど役に立つアプリになるッス！
検索結果を一覧表示する準備
GASで検索結果を操作する準備。
ファイルを1点用意します。
必要なファイル
- Google スプレッドシート…リストを作成＆プログラムをコピペ
Google スプレッドシートの作成
検索結果を一覧表示するスプレッドシートを作成していきます。
スプシ内に
- 企業名
- 商品名
- 数量
の3項目を1行目に記入。
2行目以降は個別のデータを行ごとに記入します。
シートの名前を変更します。
下バーの「シート１」を右クリックして「リスト」にシート名を変更。
次に赤丸で囲った「＋」ボタンを押して新しいシートを生成し、シート名を「検索」に変更します。
「検索」シート内を編集します。
A2に「企業名」、D2「商品名」、E2「数量」の項目を記入します。
検索する企業名をB2セルに入力します。
例として「株式会社B」を記入。
スプレッドシートの作成は以上です。
サンプルコードを貼り付ける場所
サンプルコードはGoogle スプレッドシートにコピペ。
上バーの「拡張機能」の中にある「Apps Script」をクリックしてコードを貼り付けます。
検索結果を表示するサンプルコード
検索結果を一覧で一斉表示するサンプルコードの公開と解説をします。
解説の項目
- サンプルコードs
- A, 操作するスプレッドシートの定義
- B, 一致する値を配列に格納
- C,「検索」シートに一致したデータを入力
サンプルコード
右上にあるマークを押すとコードのコピペを一瞬でできます。
また、コード内で文頭にアルファベットが付いているコメントごとに詳細を説明します。
function searchBoughtItemsList(){
//A,スプレッドシートの定義
const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
const listSheet = spreadSheet.getSheetByName('リスト');
const listLastRow = listSheet.getLastRow();
const searchSheet = spreadSheet.getSheetByName('検索');
const companyName = searchSheet.getRange("B2").getValue(); //「検索」シートの企業名
const matchArray = []; //一致する値を格納する配列
const START_WRITE_ROW = 3; //値を書き始める「検索」シートの行
const START_WRITE_COL = 4; //値を書き始める「検索」シートの列
//B,一致する値を配列に格納
for (var i=1; i<=listLastRow; i++){
const listedCompany = listSheet.getRange("A"+ i).getValue(); //「リスト」シートの「企業名」
if(companyName == listedCompany){
const listEach = listSheet.getRange("B" + i + ":" + "C" + i ).getValues().flat();
matchArray.push(listEach);
}else{
continue;
}
}
//C,「検索」シートに一致したデータを入力
const matchArrayLen = matchArray.length;
for(var y=0; y<matchArrayLen; y++){ //外側のfor
const elemLen = matchArray[y].length;
for(var z=0; z<elemLen; z++){ //内側のfor
searchSheet.getRange(START_WRITE_ROW+y,START_WRITE_COL+z).setValue(matchArray[y][z]);
}
}
}A, 操作するスプレッドシートの定義
//A,スプレッドシートの定義
const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
const listSheet = spreadSheet.getSheetByName('リスト');
const listLastRow = listSheet.getLastRow();
const searchSheet = spreadSheet.getSheetByName('検索');
const companyName = searchSheet.getRange("B2").getValue(); //「検索」シートの企業名
const matchArray = []; //「リスト」シートで一致する値を格納する配列
const START_WRITE_ROW = 3; //値を書き始める「検索」シートの行
const START_WRITE_COL = 4; //値を書き始める「検索」シートの列「リスト」シートと「検索」シートをそれぞれ定義。
定数companyNameで「検索」シートのB2の値を取得します。
定数matchArray は後のCの処理で
「リスト」シートの「企業名」＝「検索」シートのB2
なら値を格納するのに使います。
B, 一致する値を配列に格納
//B,一致する値を配列に格納
for (var i=1; i<=listLastRow; i++){
const listedCompany = listSheet.getRange("A"+ i).getValue(); //「リスト」シートの「企業名」
if(companyName == listedCompany){
const listEach = listSheet.getRange("B" + i + ":" + "C" + i ).getValues().flat();
matchArray.push(listEach);
}else{
continue;
}
}「リスト」シートを１行ずつ読み込んで一致する値をmatchArray配列に格納します。
企業名が一致した場合、getValuesメソッドで取得した「商品」と「数量」の二次元配列を一次元化して格納。
一致しない場合はcontinueで処理を飛ばします。
二次元配列を普通の配列に変換するflatメソッドの詳細はこちら。
C,「検索」シートに一致したデータを入力
//C,「検索」シートに一致したデータを入力
const matchArrayLen = matchArray.length;
for(var y=0; y<matchArrayLen; y++){ //外側のfor
const elemLen = matchArray[y].length;
for(var z=0; z<elemLen; z++){ //内側のfor
searchSheet.getRange(START_WRITE_ROW+y,START_WRITE_COL+z).setValue(matchArray[y][z]);
}
}一致したデータを格納したmatchArrayを「検索」シートに入力。
このfor文内の流れを下の画像で説明します。
外側のfor文は赤枠のmatchArray内の要素を取得しますが、赤枠内の要素も配列。
内側のfor文で黄色マーカーの要素を取得してシートに値を入力します。
この処理を上限まで自動で動きます。
二次元配列の要素の取り出し方はこちらから。
コードの説明は以上です。
結果を一覧表示するプログラムの実行
実際に「検索」シートに転記できるかテストします。
Apps Scriptの上バーの文字が「searchBoughtItemsList」になら「実行」を押します。
「このアプリはGoogleで確認されていません」と表示した場合はこちら。
「検索」シートに結果を出力するので確認。
画像のように表示したら成功です。
まとめ | GASのスキルを高めて残業をなくそう！
以上がGoogle Apps Scriptで検索結果をスプレッドシート上で一覧表示する方法でした。
そのままコピペしたり、プログラミングの練習や動作確認用に使ったりしてみましょう。
GASを効率的に学ぶなら以下の2つの方法が多いです。
- 参考書やオンライン教材で独学
- プログラミングスクールを利用
正しく学ぶことで非エンジニアでも実用的な業務効率化のスキルが身に付きます。
デイコーでは、GASのサンプルコードからおすすめのプログラミングまで幅広く紹介。
初心者にもわかりやすく解説しています。
あっきょプログラミングスクールは82社を分析したッス！
次回の記事もご期待ください！
それでは、よいプログラミングライフを！
