【GAS】以前書いた「参照元のセルにジャンプするスクリプト」をバージョンアップして vlookup()にも対応した

【Googleスプレッドシート】GoogleAppScript (GAS)で参照元のセルにジャンプするショートカットを実現する – pplog という記事を以前書いて日々愛用しているのだけど、vlookup()で参照している場合には使えなかったので、週末にえいやっと対応した。

すごい地味なデモ。値を参照しているセルを選択して、関数を実行すると参照元にジャンプします。単純なセル参照にも対応。

ここでいう参照とは

このように別シートから値を参照しているケースや、

vlookupで特定のセルの値を参照しているケースを指している。

こういう参照元のセルを見たいときって、一日2万回くらい訪れますよね?

vlookup()は便利だが

vlookup()って設定するときは便利だけど、後日見直したり、他の人が設定したシートをチェックするときは、参照元のセルへたどり着きにくくってレビューが地味大変。

何が大変かっていうと vlookup()の第3引数の【指数】ですよ。

スプレッドシートのvlookupの説明

指数が2とか3なら目で追えるけど、検索対象が膨大で指数が20とか30だったりすると、確認するのが億劫になるんですよね。

指数に20を設定すべきところを21とかになってても間違いに気づきにくい。

ということでGASで該当セルへ飛べるようにするぞー(前置き終わり)。

GASで解決する

書いたスクリプトは以下。
使いたいスプレッドシートのスクリプトエディタにコピペすれば動きます。

function JumpToCellReference() {
  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var formula = sheet.getCurrentCell().getFormula()
  
  var str = "vlookup"
  if(formula.toLowerCase().match(str)) {
    JumpToReferrerVlookup();
  }else{
    JumpToReferrer();
  }
}


function JumpToReferrerVlookup() {
  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var formula = sheet.getCurrentCell().getFormula().replace("=vlookup(","").replace("=VLOOKUP(","").replace(")","").split(",");
  var vColumn = sheet.getRange(formula[1]).getColumn();
  var vKeyword = sheet.getRange(formula[0]).getValue();
  if(isFinite(formula[2])) {
    var vIndex = parseInt(formula[2]) -1;
  }else{
    var vIndex = sheet.getRange(formula[2]).getValue() -1;
  }

  var vSheetName = sheet.getRange(formula[1]).getSheet().getName(); 
  var vSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(vSheetName);

  var textFinder = vSheet.createTextFinder(vKeyword);
  var ranges = textFinder.findAll();  
  var rows = [];
  var targetCol = vColumn;
  
  for (var i = 0; i < ranges.length; i++ ) { 
    var row = ranges[i].getRow(); 
    var col = ranges[i].getColumn();
    
    if(col === targetCol) {
      col = col + vIndex;
      var tagetCell = vSheet.getRange(row,col);
      tagetCell.activate();
      
      rows.push(ranges[i].getRow());
      break;
    }
  }
}


function JumpToReferrer() {
  
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getActiveSheet();
  var value = sheet.getCurrentCell().getFormula().replace("=","");
  var objRange = sheet.getRange(value);
  objRange.activate();

}

選択したセルの中にvlookupという文字列があるかどうかで処理を分けています。

文字列があればvlookup用の関数 JumpToReferrerVlookup()を実行し、そうでなければ以前書いた単純なセル参照とみなし JumpToReferrer() を実行する。
書き方がイケてるかどうかは分からないが、動いているから良し。

おまけ: スプレッドシートのメニューから関数を呼び出せるようにする

ついでに[関数]メニューを追加しておくと、作った関数を呼び出しやすい。

こちらもコピペで動きます。

function onOpen() {
   var ui = SpreadsheetApp.getUi();
   var menu = ui.createMenu('関数');
   menu.addItem('参照元のセルにジャンプ', 'JumpToCellReference');
   menu.addToUi();
 }
スクリプトエディタの様子。2つのスクリプトを用意してそれぞれまるっとコピペすれば動きます(たぶん)。

次はhlookup()にも対応しようかなーと思いつつあんまり使わないので腰が重い。

コメントを残す

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください