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

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

ただいまの
回答率

87.61%

Google Formから回答が追加されてもGoogle Spread Sheetに記述されてい関数を自動的に適用させる

受付中

回答 1

投稿

  • 評価
  • クリップ 2
  • VIEW 1,173

score 10

前提・実現したいこと

知識不足であることから、ご支援頂ければと思います。
現在、Google Formからアンケートをとっており、Google Spread Sheetで集計を行なっております。

J列からS列までに

  • =VLOOKUP(B2,'商品リスト'!$B$2:$D$999,2,false)
  • =VLOOKUP(B2,'商品リスト'!$B$2:$D$999,3,false)
  • =C2
  • =D2
  • =E2
  • =F2
  • =G2
  • =H2
  • =I2
    と、他の項目を参照させるようにしたいです。

発生している課題

Google Formで回答が入るたびに、1行自動的に追加されるため、最初から関数を入れていても自動的にVlookup等が機能しません。
検索で色々な情報を調べていたところ、Google apps scriptで処理すべきではないかと考えておりますが、
どのように対応すべきかよく見えてきません。

或いは、Google Spread Sheetにあるスクリプトエディターで達成できるのではないかとも考えております。

試したこと

Google Formから1行追加されるたびに、J列からS列までの関数を自動的に反映させるにはどのようにすれば宜しいでしょうか。
お力添えいただけると幸いです。

補足情報(FW/ツールのバージョンなど)

既に知り合いに依頼し次のようなコードがGoogle Spread Sheetのスクリプトエディターに記述済みです。

function recreateSheetByBusinessName() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  var sheetMain = spreadsheet.getSheetByName('フォームの回答 1')
  var colBusinessNames = sheetMain.getRange(2,10,spreadsheet.getLastRow()-1,1).getValues()
  var businessNames = []
  for(var i=0;i<colBusinessNames.length;i++){
    for(var j=0;j<colBusinessNames[i].length;j++){
      businessNames.push(colBusinessNames[i][j])
    }
  }
  businessNames = businessNames.filter(function(x,i,self){ return self.indexOf(x) === i }).sort()

  for(var i=0;i<businessNames.length;i++){
    var sheetName = 'XXX'+businessNames[i]; //FIXME:delete 'XXX'
    var sheetSub = spreadsheet.getSheetByName(sheetName)
    if(sheetSub){
      sheetSub.clearContents()
    }else{
      spreadsheet.insertSheet(sheetName)
      sheetSub = spreadsheet.getSheetByName(sheetName)
    }
    var query = "=Query('フォームの回答 1'!A:S,\"where J='"+businessNames[i]+"'\")"
    sheetSub.getRange(1,1).setFormula(query)
  }

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

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

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

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • papinianus

    2019/03/18 16:40

    スプレッドシートを開いて確認しようとすれば、VLOOKUPの検索処理が走って、値が見えるような気がしますが、動かないということですか?

    キャンセル

  • wu888

    2019/03/19 10:21

    アンケート回答が入ると新しい行が追加されます。その行に関数が入っていないため、手作業でコピーが必要になってしまいます。新しく入ってきたアンケート回答に対して関数を実行させたいと考えております..。

    キャンセル

回答 1

0

関数のコピーの問題であれば、ARRAYFORMULAで良いのではないでしょうか。

↓これらをJ2、K2、などに書くだけです。VLOOKUPをコピーしなくても、B列に値が入れば、勝手にJ,Kが

  • =ARRAYFORMULA(IFERROR(VLOOKUP(B2:B,'商品リスト'!$B$2:$D$999,2,false)))
  • =ARRAYFORMULA(IFERROR(VLOOKUP(B2:B,'商品リスト'!$B$2:$D$999,3,false)))
  • =ARRAYFORMULA(TEXT(C2:C, "")

=C2はそれ自身関数ではないため、ARRYFORMULAにかけないので、仮にTEXTを入れています。どう表示したいかで適当な関数をあてはめてください。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/03/19 14:34

    ありがとうございます。
    J2にご教示いただきました関数を書いても、新たにアンケート回答が集まると、行が追加され、J3のセルは関数が無いセルとなります。このため、新たに追加されたまっさらなセルに対して関数を入れることができないかと考えております。

    ARRAYFORMULAは初めてしりました。ありがとうございます。B列にあるユニークのキーをベースに、J列以降を埋めたいのですが、どうもうまくいきません...。

    キャンセル

  • 2019/03/19 14:54

    ARRAYFORMULAは、J2にだけかけば、J3から下方向に展開してくれる機能なんですが…

    たしかにformではやったことないですが、3行目4行目と増えても、ARRAYFORMULAでVLOOKUPの一つめを"B2:B"のように(通常1セルを指定するところを範囲にすれば)Bに応じて展開されるはずなんですがねぇ。
    ISERRORを外すと、わかりやすいのかもしれないです。

    キャンセル

  • 2019/03/19 14:57

    確認しましたが、私の回答意図のとおりに動作しています。
    既に19行目まで回答がある状態の回答シートに、`=ARRAYFORMULA(TEXT(B2:B, ""))`をE2に追記
    回答を送信して、シートを開いたところ、E20に、B20の値が入って表示されました。

    キャンセル

  • 2019/03/21 10:19

    何度もありがとうございます。
    "B2"にキーとなる値が入っております。
    "J2"にご教示いただきました関数を入れても”#REF”とエラーになります。
    ※=ARRAYFORMULA(IFERROR(VLOOKUP(B2:B,'商品リスト'!$B$2:$D$999,2,false)))

    何が足りていないのでしょうか...。本当に申し訳ございません。何度も助かります。

    キャンセル

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

  • ただいまの回答率 87.61%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

同じタグがついた質問を見る