質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
Google Apps Script

Google Apps ScriptはGoogleの製品と第三者のサービスでタスクを自動化するためのJavaScriptのクラウドのスクリプト言語です。

Q&A

解決済

2回答

7877閲覧

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

mitsuba

総合スコア20

Google Apps Script

Google Apps ScriptはGoogleの製品と第三者のサービスでタスクを自動化するためのJavaScriptのクラウドのスクリプト言語です。

0グッド

0クリップ

投稿2018/03/01 03:11

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")

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

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

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答2

0

ベストアンサー

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

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

GAS

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

javascript

1function doGet(e){ 2 //変数定義 3 /*省略*/ 4 var formulaE, formulaF, idx; 5 6 //設定・情報取得 7 /*省略*/ 8 idx = stL.getLastRow()+1; 9 formulaE = "=VLOOKUP(B" + idx + ",'所属一覧'!A:L," + (Today.getMonth()+1) + ",0)"; 10 formulaF = '=TEXT(A' + idx + ',"yyyymm")'; 11 //反映 12 stL.appendRow([Today, UserID, PageName, PageURL, formulaE, formulaF]); 13}

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にまとめて記述することも可能ですが、視認性が悪くなるため列ごとにまとめています。

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

投稿2018/03/01 14:56

退会済みユーザー

退会済みユーザー

総合スコア0

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

mitsuba

2018/03/05 01:14

stshishoさん GASおよび ARRAYFORMULAにて、対応方法をお教えいただき、ありがとうございました。 とても勉強になりましたし、GASで無事に動きまして、大変たすかりました。 本当にありがとうございました。
退会済みユーザー

退会済みユーザー

2018/03/05 01:41

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

0

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"))

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

投稿2018/03/01 03:26

true

総合スコア440

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

mitsuba

2018/03/01 05:50

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) ご存じでしたら、お教えいただえけます様、お願いします。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問