Googleスプレッドシートユーザ関数-検索結果一覧表示 | エクセルマクロ(VBA)実践蔵(じっせんぐら)
- エクセルマクロ(VBA)実践蔵 >
- 項目一覧 >
- Googleスプレッドシートユーザ関数-検索結果一覧表示
Googleスプレッドシートユーザ関数-検索結果一覧表示最終更新日：2019-09-21
Googleスプレッドシートでも、エクセルと同様、ユーザ関数を作成できます。
Googleスプレッドシートでは配列を返すことで、複数のデータが返せます。Excelでは2019よりスピル機能に対応され、配列が返せるようになりました。
その配列を返す特徴を活かして、今回は、Google Apple Scriptを使って、指定された範囲を検索して、一致するセルへのリンクリストを作成することができるユーザ関数を作成してみました。
テキストエディタでよくある、Grep結果一覧のようなイメージです。
使用例
検索対象データ(シート2)
検索結果表示シート(関数呼び出し側)
関数の定義
| 関数仕様 | 指定された範囲で検索文字列に完全に一致するセル(パターン0) 又は、検索文字列を含むセル(パターン1)のリンクリストを表示する。 |
|---|---|
| 関数の型 | SearchAll ( 検索文字列 , 範囲 , パターン ) |
| 第一引数 | 検索文字列 (文字列は" "で括る) |
| 第ニ引数 | 範囲 (" "で括って、文字列で指定する) ※例1："'シート1'!A1:A100"（別シートを参照する場合） シート名は' 'で括る ※例2："A1:A100"（同じシート内のセルを検索する場合） |
| 第三引数 | パターン(0：完全一致,1：含む場合) パターンが1の場合、隣の列に見つけたセルの内容を表示します。 |
function SearchAll(searchStr,address,ptn){
var spreadsheet = SpreadsheetApp.getActive();
var link = spreadsheet.getUrl();
var SheetName;
var add;
var topadd = "";
var sht;
var sID;
var reg;
if( address.indexOf( "!", 1 ) === -1 )
{
// 同じシート内
sht = spreadsheet.getActiveSheet();
sID = sht.getSheetId();
add = address;
//正規表現でアルファベットを抽出
reg = /[A-Za-z]+/;
topadd = add.match(reg);//列名の取得
}
else
{ // 別シートの場合
var endi = address.indexOf( "'", 1 );
SheetName = address.slice(1,endi);
// シート名を除いて、セルアドレスを取り出す
add = address.replace("'"+SheetName+"'!","");
reg = /[A-Za-z]+/; //正規表現でアルファベットを抽出
topadd = add.match(reg); //列名の取得
// シートオブジェクトを取得
sht = spreadsheet.getSheetByName(SheetName);
// シートIDの取得(リンク用)
sID = sht.getSheetId();
}
var range= sht.getRange(add);
var row = range.getRow();
var values = range.getValues();
Logger.log(values);
link = link + "#gid=" + sID;
if( ptn === 0 )
{// 完全一致検索の場合
var keka_list = [];
for(var i = 0; i < values.length;i++)
{
// 列名先頭を取得
var Col = topadd[0].charCodeAt(0);
for( var j=0; j< values[i].length; j++ )
{
if( values[i][j] == searchStr )
{ // 完全一致の場合
var ColA = String.fromCharCode(Col+j);
keka_list.push(link + "&range=" + ColA + (i + row));
}
}
}
return keka_list;
}
else if( ptn === 1 )
{
var keka_list = [];
for(var i = 0; i < values.length;i++)
{
// 列名先頭を取得
var Col = topadd[0].charCodeAt(0);
for( var j=0; j< values[i].length; j++ )
{
var text = values[i][j] + "";
if( text.indexOf(searchStr) != -1 )
{
var ColA = String.fromCharCode(Col+j);
keka_list.push([link + "&range=" + ColA + (i + row),text]);
}
}
}
return keka_list;
}
}