Googleスプレッドシートユーザ関数-検索結果一覧表示 | エクセルマクロ(VBA)実践蔵(じっせんぐら)

トップ項目一覧

Googleスプレッドシートでも、エクセルと同様、ユーザ関数を作成できます。

Googleスプレッドシートでは配列を返すことで、複数のデータが返せます。Excelでは2019よりスピル機能に対応され、配列が返せるようになりました。

その配列を返す特徴を活かして、今回は、Google Apple Scriptを使って、指定された範囲を検索して、一致するセルへのリンクリストを作成することができるユーザ関数を作成してみました。

テキストエディタでよくある、Grep結果一覧のようなイメージです。

使用例

検索対象データ(シート2)

検索結果表示シート(関数呼び出し側)

関数の定義

 
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; 
  } 
}