【実践GAS２】スプレッドシート関数をコードで実行
概要
前回
で、VLOOKUP同等のコードについては簡単に説明したんだけど、
そもそもVLOOK関数ってなに？
て感じな人もいるかもしれないので、
GASでVLOOKUPをコードで実践的に使う手法をご案内。
VLOOKUP関数って？
LOOKUPてゆーくらいだから、検索機能なんだけど、
Vは縦＝VerticalのV
例えば、
みたいな検索元のデータがあるとして、
名前列に入れたいって時に
=VLOOKUP(A2,'検索元'!A:B,2,false)て関数をB2列に、
な感じで入れちゃうと出せちゃう。
でN/Aって出てるところは、値がないからエラーになるので、
=IFERROR(VLOOKUP(A2,'検索元'!A:B,2,false),"")でIFERROR関数で判定を付けて
・もしエラーじゃないときは普通に実行
・エラーの時は””（空白）
にしてあげると、
みたいな感じで、N/Aの回避ができる。
と、ここまでは普通のVLOOKUP関数の説明で
こうすると、使い慣れている人は
「手作業でやればいい」
ですませちゃうんだけど、それだと実は、
コード化できてない＝コード的には作業が繋がらなくなる！
完全自動化を目指すなら、
どんな簡単な操作も、コード化する
＝手でやればいいが完全自動化の最大の敵
てことで、今やった作業を完全自動化するとコード的にはこんな感じ。
function B列の最終行までVLOOKUP関数を代入() {
let ss = SpreadsheetApp.getActiveSpreadsheet();
let shSaki = ss.getSheetByName('検索先');
let lastRow = shSaki.getLastRow();
let arrayVLOOKUP = []
for(i = 2;i <= lastRow; i++){
let funcVLOOKUP = "=IFERROR(VLOOKUP(A" + i + ",'検索元'!A:B,2,false)," + '"")'
arrayVLOOKUP.push([funcVLOOKUP])
}
shSaki.getRange(2,2,arrayVLOOKUP.length,arrayVLOOKUP[0].length).setValues(arrayVLOOKUP)
}GASの場合は、
スプレッドシート関数を、
文字列としてそのまま埋め込むイメージだから、実行後は、
こんな感じになる。
ポイントは、
行数が少ないし、単発の機能だからこんな感じだけど、
・何かの操作の間に、しかも行数が１万行とかでまとめて処理したい時に役立つ
・VBAのApplication.Worksheetfunctionみたいな複雑なメソッドがGASだといらない。
注意点としては、
・シートアクセス制限がタイミングや環境によってはある
・GSuiteじゃない限り、制限時間が６分の制約ある
ことくらいかな。
どんなスプレッドシート関数でも数字をカウンタ回数に書き換えれりゃいけます！
💃ま、いつも同じ関数を手作業でやるのがめんどくさいときなどの参考までに〜〜〜〜〜🕺
まとめ
現場のプログラマ、SE、管理者でプログラミングを勘違いしてる人が多くて、
「頑張ったけど、完全自動化できたのは３割」
て言う人がいたんだけど、完全自動化って要は、
普段手でやってる操作を、自動的に
（ボタン一発とかトリガーで時間来たら）
できること
なんだけど、現地のSEさんやちょっとかじったことがある人はどの言語にしても、なぜか
難しいコードしか書こうとしない
＝処理が部分的にしか繋がらない
→そりゃ完全自動化できない
普段やってる操作の８割は、コード化すれば１行で済む簡単なコード
だからね。
でPDFには実際に書いてるんだけど、
そんな作業は手で動かせばいい
で、コード化しないから、作業が繋がらないわけさ、、、
だって、このときはこうとか、別の時はこう
みたいな判断が入るものなんてパターンを洗い出して
GASで言えば、
if文かswitch文で条件付けして、エラー回避してしまえば、
大半の作業は機能化できちゃうからね。
てか、
簡単な作業を全てコード化して関数化しておけば、
どんな複雑な作業もそれを再利用しながら繋ぐだけで、実現できる
＝オブジェクト指向の基本中の基本