スプレッドシートに数式を自動で反映させたい。

Question

Googleサイトのログを取得し、スプレッドシートへ反映して以下の様なリストを作成しております。

A・B列に、ログ履歴の日時とIDが取得された場合、E列とF列に記載されている数式を、
自動で反映させたいのですが、可能でしょうか？

E列とF列には、以下の様な数式が記載されています。

E列：=VLOOKUP(B3,'所属一覧'!A:B,2,0)
F列：=TEXT(A3,"yyyymm")

E列・F列以降に自動で、以下の様な数式を表示させたいのですが、
例えば、A4/B4に日時とIDが取得されると、E4/F4に以下の数式が記載される形です。
E4：=VLOOKUP(B4,'所属一覧'!A:B,2,0)
F4：=TEXT(A4,"yyyymm")

なお、アクセスログは、最下部に表示される様に設定している為、事前に数式を入力しておく事ができません。

対応方法をご存じでしたら、お教えいただけます様、お願いします。

Answer 1

Google Apps Script（以降、GAS）でもできますし、trueさんも言及されているように、ARRAYFORMULAでもできます。後者はややトリッキーですが。

いずれにしましても、機械的に判断するためにVLOOKUPの指数部分の移動（といいますか変動）に法則性がある、という前提となります。
※B列が2月、C列が3月より参照先のテーブルがA:Lであると仮定

GAS

数式をセットするためだけの setFormula() というメソッドがありますが、setValue() や appendRow() でも数式文字列をそのままセットできます。数式にはARRAYFORMULAを使わず、当初の通り、都度、数式をセットします。
よって、最終行と現在月を取得できれば以下のように組み立てることができます。

function doGet(e){
   //変数定義
   /*省略*/
   var formulaE, formulaF, idx;

   //設定・情報取得
   /*省略*/
   idx      = stL.getLastRow()+1;
   formulaE = "=VLOOKUP(B" + idx + ",'所属一覧'!A:L," + (Today.getMonth()+1) + ",0)";
   formulaF = '=TEXT(A' + idx + ',"yyyymm")';
   //反映
   stL.appendRow([Today, UserID, PageName, PageURL, formulaE, formulaF]);
}

ARRAYFORMULA

ややトリッキーですが、配列を使用し、ヘッダも含め出力します。
VLOOKUPの指数部分はMONTHで求めます。

E1:={"所属組織";ARRAYFORMULA(IF(A2:A<>"",VLOOKUP(B2:B,'所属一覧'!A:L,MONTH(A2:A),0),""))}
F1:={"日付変換";ARRAYFORMULA(IF(A2:A<>"",TEXT(A2:A,"yyyymm"),""))}

※上記応用でE1にまとめて記述することも可能ですが、視認性が悪くなるため列ごとにまとめています。

動作確認はしておりませんので、参考までに。

Comments

[mitsuba]

stshishoさん

GASおよび ARRAYFORMULAにて、対応方法をお教えいただき、ありがとうございました。
とても勉強になりましたし、GASで無事に動きまして、大変たすかりました。

本当にありがとうございました。

[退会済みユーザー]

何よりです。
補足ですが、ARRAYFORMULAを使う場合、シート上に２行以上あることが大前提となります。
まぁ、スクリプト等で意図的に処理しない限り問題ないと思います。

Answer 2

ARRAYFORMULAで解決しませんか？

E列：=VLOOKUP(B3,'所属一覧'!A:B,2,0)
F列：=TEXT(A3,"yyyymm")

いったんE2,F2より下方のセルをクリアしたうえで、

E2：=ARRAYFORMULA(VLOOKUP(B2:B,'所属一覧'!A:B,2,0))
F2：=ARRAYFORMULA(TEXT(A2:A,"yyyymm"))

としておけばよいと思います。

Comments

[mitsuba]

trueさん

早々のご回答ありがとうございます。

記載しておりました様に、アクセスログは、最下部に表示される様に設定している為、
事前に数式を入力しておく事ができません。

お教えいただきました方法で対応いたしますと、E2およびF2に数式を入力している為、
A4に日時とIDが表示されていなくても、数式が表示されてしまい、最新のログは
最下部に新規で行が追加されて、表示されてしまいます。

ログ取得の際に、例えばA列やB列をキーとしてA列やB列の最下部に表示設定ができる様であれば、
上記問題は、解決するかと思いますが、下記GASをどの様に修正すればよいかご存じでしょうか？

ログ取得のGASは、以下です。

【ログ取得のGAS】
function doGet(e){
//変数定義
var ss;var stL;
var Today;var UserID;var PageName;var PageURL;
//設定・情報取得
ss = SpreadsheetApp.openById(ssID);
stL = ss.getSheetByName(sheetName);
Today = new Date();
UserID = Session.getActiveUser().getEmail();
PageName = SitesApp.getActivePage().getTitle();
PageURL = SitesApp.getActivePage().getUrl();
//反映
stL.appendRow([Today,UserID,PageName,PageURL]);
}

また、お伝えし忘れていたのですが、E列のVLOOKUPは検索キーとなる列が月（F列）によって変更いたします。
自動での表現は難しいでしょうか？

F列が201802の場合：=VLOOKUP(B3,'所属一覧'!A:B,2,0)
F列が201803の場合：=VLOOKUP(B4,'所属一覧'!A:C,3,0)

ご存じでしたら、お教えいただえけます様、お願いします。