【GAS】GASからVLOOKUP関数を埋め込むときはsetFormulaR1C1を使う

こんにちは！

今回は「GASからVLOOKUP関数を埋め込むときはsetFormulaR1C1を使う方法」についてお伝えしたいと思います。

スプシを使うとき、他のGASの処理の影響で、デフォルトで全行関数を埋め込むことができないことがあり、この方法を使う機会がありました。

setFormulaR1C1を使って、GASからVLOOKUP関数を埋め込み

まず、関数埋め込みの書き方は以下のようになります。

例えば、B4セルに書いたVLOOKUP関数 =VLOOKUP(B4,'単価表'!$A$2:$B$4,2,FALSE)をGASで埋め込む場合

sheet.getRange(4,3).setFormulaR1C1(`=VLOOKUP(RC[-1],IMPORTRANGE("${spreadsheetId}","単価表!$A$2:$B$4"),2,FALSE)`);

このように記述する必要があります。

スプシに直接VLOOKUP関数を埋め込む場合と、GASから埋め込む場合は記述方法が異なるのでご注意ください。

式で書いても分かりづらいと思うので、簡単な例を用いて説明します。

以下は「売上管理」シートで商品の単価を「単価表」シートからVLOOKUPで取得しています。

上記「売上管理」シートのB4列に同様のVLOOKUP関数をGASから埋め込みたいと思います。

コードは以下になります。

const setVlookupFunction = () => {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); // ブック取得
  const TARGET_SHEET_NAME = '売上管理'; // 埋め込み対象のシート名
  const REF_SHEET_NAME = '単価表'; // VLOOKUP参照先シート名
  const sheet = spreadsheet.getSheetByName(TARGET_SHEET_NAME);
  const spreadsheetId = spreadsheet.getId(); // スプレッドシート一意のID
  sheet.getRange(4,3).setFormulaR1C1(`=VLOOKUP(RC[-1],IMPORTRANGE("${spreadsheetId}","${REF_SHEET_NAME}!$A$2:$B$4"),2,FALSE)`);
};

関数を実行してみると、、

このように同様のVLOOKUP関数の結果を得られることができました！

参考

公式ドキュメント Range

以上、お疲れさまでした〜🍵