【Google Apps Script(GAS)】setFormulaによるsum関数やVLOOKUP関数の使用

Google Apps Scriptではスクリプトからスプレッドシートに関数を埋め込んで、sumやVLOOKUPといった関数を使用する事ができます。
今回はその一例についてご紹介します。

setFormulaでsum関数をスプレッドシートに埋め込んで使用する

まず、Google Apps Scriptでスクリプトからスプレッドシートに関数を埋め込むには、下記スクリプトを使用します。

setFormula()

下記のサンプル１は、

sh.getRange(6,1).setFormula(“=sum(A1:A5)”);

でアクティブシートのセルA6に、「=sum(A1:A5)」の関数を埋め込んでA1~A5の数値を足し算する、シンプルな例です。

・サンプル１

function myFunction() {

var ss = SpreadsheetApp.getActiveSpreadsheet();　//アクティブなスプレッドシートを取得
var sh = ss.getActiveSheet();　//アクティブなスプレッドシートのアクティブシートを取得

sh.getRange(6,1).setFormula(“=sum(A1:A5)”);

}

setFormulaでVLOOKUP関数をスプレッドシートに埋め込んで使用する

下記のサンプル２では、

sh.getRange(1,5).setFormula(‘VLOOKUP(‘+str+’,$A$1:$B$5,2,FALSE)’);

検索キーであるセルD1に入力されている文字列「テスト3」をセルA1~A5から検索し、存在した場合は右隣りのセルの文字列をセルE1に反映しています。

・サンプル２

function myFunction() {

var ss = SpreadsheetApp.getActiveSpreadsheet();　//アクティブなスプレッドシートを取得
var sh = ss.getActiveSheet();　//アクティブなスプレッドシートのアクティブシートを取得
var last_row = sh.getLastRow();

sh.getRange(1,5).setFormula(‘VLOOKUP($D$1,$A$1:$B$5,2,FALSE)’);

}

連続してVLOOKUP関数をスプレッドシートに埋め込んで使用する

下記のサンプル３では、

for(var i = 0; i < last_row; i++) {

で、変数iの初期値を0とし、変数iがアクティブシートの最終列である変数last_rowに代入された数値（今回は5）より小さい間は処理を繰り返すものとします。

sh.getRange(1 + i, 5).setFormula(‘VLOOKUP($D’+ (1 + i) +’,$A$1:$B$’+last_row+’,2,FALSE)’);

では、検索キーであるセルD1~D5の文字列を順にセルA1~A5から検索し、存在した場合は右隣りのセルの文字列をセルE1~E5に反映しています。

・サンプル３

function myFunction() {

var ss = SpreadsheetApp.getActiveSpreadsheet();　//アクティブなスプレッドシートを取得
var sh = ss.getActiveSheet();　//アクティブなスプレッドシートのアクティブシートを取得
var last_row = sh.getLastRow(); //アクティブシートの最終行を取得

for(var i = 0; i < last_row; i++) {

sh.getRange(1 + i, 5).setFormula(‘VLOOKUP($D’+ (1 + i) +’,$A$1:$B$’+last_row+’,2,FALSE)’);

}
}