【Googleスプレッドシート】GoogleAppScript (GAS)で参照元のセルにジャンプするショートカットを実現する – pplog という記事を以前書いて日々愛用しているのだけど、vlookup()で参照している場合には使えなかったので、週末にえいやっと対応した。
ここでいう参照とは
こういう参照元のセルを見たいときって、一日2万回くらい訪れますよね?
vlookup()は便利だが
vlookup()って設定するときは便利だけど、後日見直したり、他の人が設定したシートをチェックするときは、参照元のセルへたどり着きにくくってレビューが地味大変。
何が大変かっていうと vlookup()の第3引数の【指数】ですよ。
指数が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();
}
次はhlookup()にも対応しようかなーと思いつつあんまり使わないので腰が重い。