【GoogleAppsScript(GAS)入門】itemResponses.lengthで未回答になっている空白タイトルも考慮して他のファイルのシート列に転記をする方法

Googleフォームの内容をメール送信

Googleフォームで設問を作ってフォームの回答を配列にしてメール本文に入れる、といった方法を以前書きました。

ふと思ったのが、例えば「この回答結果をGoogleフォームの回答シート以外の他のスプレッドシートに転記したい」という場合に、可能なのかどうか・・・？

可能でした！＼(^o^)／

ただ、色々と試していて、困った課題があったので、何か解決方法が無いかと模索しながら、色々と試してみました！

フォームの回答

通常は、設定した「フォームの回答」シートに回答が自動入力されますが、フォームの回答があったと同時に別のシート（ファイル）に自動転機させてみます。

まずは転記用の関数を作成

function autoreply(e) {

var ss2_copy_to = SpreadsheetApp.getActiveForm();

var s_name_to = ss2_copy_to.getSheetByName(“シートの名称”);

// 転記先のシートの最終列と行を取得する

var tolastCol = s_name_to.getLastColumn();

var tolastRow = s_name_to.getLastRow();

// 最終行＋１の値を取得（末尾に追加して転記するため）

var LastRow_PlusOne = tolastRow + 1;

転記先用に利用する変数は、このような感じでOKかと思います。

罫線（けいせん）を入れる

これは必要があればで良いかと思いますが、罫線が入っていたほうが見栄えが良いかと思いますので、データを書き込む時に罫線を入れてみます。

//罫線を入れる

s_name_to.getRange(LastRow_PlusOne, 1,1,tolastCol).setBorder(true, true, true, true, true, true);

追記：先に罫線を入れると上手く入らないことがあるので、どうやら罫線の書き込みは末尾に持ってきたほうが良さそうです。

挿入番号の変数を準備

単純に１列目から書き込むのであればいいのですが、もし指定した列番号から挿入したい場合のために、別途変数を用意しておきます。

// 挿入の開始列番号

var START_COLUMN = 4;

フォームの回答を取得して転記する

// 回答のアイテム（項目名・回答）を取得する

var itemResponses = e.response.getItemResponses();

あとは、回答の長さ分をループさせます。

// 入力項目を全て取得します

for (var i = 0; i < itemResponses.length; i++) {

// 質問を取得します

　question = itemResponses[i].getItem().getTitle();

// 回答を取得します

　answer = itemResponses[i].getResponse();

// 指定した列番号から回答をひとつずつ挿入します

　s_name_to.getRange(LastRow_PlusOne, i + START_COLUMN,1,1).setValue(answer);

}

トリガーをセットする

あとは、フォームの送信があった時にプログラムが動くように、トリガーをセットします！

編集 ＞ 現在のプロジェクトのトリガー

トリガーを追加

イベントの種類は「フォーム送信時」

はい！完成ー！！！＼(^o^)／

と、言いたいところでしたが、ここで思わぬ問題が発生しました。

ラジオボタンやチェックボックスの回答が空白の場合、回答に含まれない…？

チェックボックスやラジオボタンの部分の回答が入っていたり入っていなかったりで、送信をした時に、「回答最後」という回答の部分が、ずれているのが分かるかと思います。

itemResponses.lengthの数が変わる！

//ログを取る

console.log(“回答の数：”+itemResponses.length);

回答の数が変わっています。

どうやら「記述式」の項目は回答が空白でも、回答数に反映されるのに対して・・・

チェックボックス・ラジオボタンは、未回答の場合は「回答の数」に含まれないようです。

回答の数が変わってしまう…！！

このままでは実現が出来なさそうなので、他のアプローチ方法を試してみます。

ログを取りまくってみる

何がどういった結果を返してくれるのか良く分からなかったので、思い当たりそうなものを全てログを取ってみました。

var responses = FormApp.getActiveForm();

var getRes = responses.getResponses();

var formItems = responses.getItems();

var itemResponses = e.response.getItemResponses();

var itemResponses_all = e.response.getGradableItemResponses()

//ログを取ってみる

console.log(“responses.length：”+getRes.length);

console.log(“itemResponses.length：”+itemResponses.length);

console.log(“itemResponses_all.length：”+itemResponses_all.length);

console.log(“formItems.length：”+formItems.length);

//エラーが出た取り方（インデックスは取れない）

//console.log(“itemResponses_all：”+itemResponses_all.getIndex());

//エラーが出た取り方（回答は取れない）

// console.log(“itemResponses_all：”+itemResponses_all.getTitle());

formItems.length

色々と試していたらヒットしました！＼(^o^)／

formItems.length：15

これです！これがフォームの質問数と同じなので、おそらくこれが正しそうな予感がします！！！

.getItems()

アイテムの数を取得する…？

var formItems = responses.getItems();

試しにタイトルを全部取得できるか、試してみます。

// 項目を全て取得

for (var j = 0; j < formItems.length; j++) {

　console.log(“今jは　”+(j+1)+”　番目”);

　console.log(“getタイプ:”+formItems[j].getType());

　console.log(“getタイトル:”+formItems[j].getTitle());

　console.log(“get回答:”+formItems[j].getResponse());

}

おぉ！取れていそうですが…

実行に失敗: TypeError: オブジェクト Item で関数 getResponse が見つかりません。

formItems[j].getResponse()では、回答は取れないようです。

何だかもう少しでいけそうなので、色々と試してみます。

getできるものがこれだけしか無いので、この中から「回答」と一致させられそうな何かを使ってみます。「getIndex」か「getId」あたりが、何だかいけそうな予感がします。

getIndex()

たぶん、これでいけるのでは…！

console.log(“getインデックス:”+formItems[j].getIndex());

いけてそうな感じが…！？

回答は、getItemResponsesのgetResponse()で取れますが、

console.log(“answer:”+itemResponses[j].getResponse());

実行に失敗: TypeError: undefined のメソッド「getResponse」を呼び出せません。

上記のようにするとエラーになります。

結局、こうしてしまうと質問項目数のformItems.length(=15)の数に対して回答数のgetResponse() (=9～13など変動) が足りなくなるため、途中でループが止まりエラーになります。

.getId()

console.log("answer:"+formResponses[j].getId());

getIdで一致させられるのでは？

・・・しかし。

responses.getItems().getId()とresponses.getResponses().getId()では、どうやら意味合いが違ったようです。

ということは、getIdでは難しそうです。

配列に格納して一致させる

他に考えられそうな方法は・・・

①質問一覧（formItems.length）の取得と回答一覧（itemResponses.length）の取得は別々のループにして、それぞれ別の配列に格納する。

②「質問一覧で取得した質問名」と「回答の配列で取得した質問名」が一致した時に、回答の配列のインデックス番号を取得して、質問一覧のインデックス番号（＋START_COLUMN）の列に、回答一覧の回答を順番に書き込む。

という方法でいけそうな気がします。

// 結果を格納する配列を準備する

var arrValue_question=[];

var arrValue_answer=[];

var arrValue_answer_question=[];

for (var j = 0; j < formItems.length; j++) {

// タイトルを配列に突っ込んでいく（配列に１５個入る）

　arrValue_question.push(formItems[j].getTitle());

}

for (var i = 0; i < itemResponses.length; i++) {

// 回答と回答時のタイトルを突っ込んでいく（配列の長さは、回答数により変動する）

　arrValue_answer_question.push(itemResponses[i].getItem().getTitle());

　arrValue_answer.push(itemResponses[i].getResponse());

}

タイトル一覧のインデックス番号に回答データを挿入する

indexOfを使って、itemResponses[i].getItem().getTitle()のタイトルが formItems[j].getTitle() の何番目かを検索して、取得をします。

arrValue_question.indexOf(arrValue_answer_question[i]);

console.log(“回答は、質問の何番目になるか？:”+arrValue_question.indexOf(arrValue_answer_question[i]);

おぉ、いい感じに取れていそうです！！

あとは、それぞれの列のセルに、インデックスの回答を入れ込んでいけば完成です！

//マスタの指定した列目からタイトルが一致した列番号に回答を挿入する

s_name_to.getRange(LastRow_PlusOne, START_COLUMN + arrValue_question.indexOf(arrValue_answer_question[i]),1,1).setValue(arrValue_answer[i]);

できましたっっ！！

まとめ

紆余曲折ありましたが、 色々とやりながら考えていたら、 何だかんだで出来ました！！

まとまっていない部分もありますが、どこかしら役に立つ部分がありましたら幸いです！

ご参考下さい(^o^)／