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

記事
IT・テクノロジー

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

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

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

可能でした!\(^o^)/

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


フォームの回答


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

picture_pc_bba4cc8981752efe00836be0841f9b5a.jpg

picture_pc_d79b8684f88090bcfaf8951f6206ba98.jpg



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


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();
// 最終行+1の値を取得(末尾に追加して転記するため)
var LastRow_PlusOne = tolastRow + 1;
転記先用に利用する変数は、このような感じでOKかと思います。

罫線(けいせん)を入れる


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

picture_pc_7bfd370d5a2bbd539c5d68cae00ec29d.jpg


//罫線を入れる
s_name_to.getRange(LastRow_PlusOne, 1,1,tolastCol).setBorder(true, true, true, true, true, true);

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

挿入番号の変数を準備


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

// 挿入の開始列番号
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);
}

トリガーをセットする


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

編集 > 現在のプロジェクトのトリガー
picture_pc_b967be51dbb1ee7732cf6a244af93cab.jpg


トリガーを追加

picture_pc_264a6c3cb99a5293f1cc942d6ea5cdce.jpg

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

picture_pc_0163df2d21dc8207d96b7260e0f42f82.jpg



はい!完成ー!!!\(^o^)/
と、言いたいところでしたが、ここで思わぬ問題が発生しました。

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


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

picture_pc_63dc490ff465ac0be26e34c9020fce65.jpg

picture_pc_b29d4b7a0e596026c0e29045f92273a1.jpg
picture_pc_2c9d7d54cbd3602a6a9dc39aa5cc2b4e.jpg


itemResponses.lengthの数が変わる!


//ログを取る
console.log(“回答の数:”+itemResponses.length);

picture_pc_e78eb0ecf6f36c9f494b100dec17d604.jpg
picture_pc_7e3665510c27a23364a9d2cda79cd990.jpg


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

picture_pc_f7ce3c2fcdce87111c4cfef69c702547.jpg



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



picture_pc_73349dbb4d48730b82d008e717611086.jpg

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


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

ログを取りまくってみる


何がどういった結果を返してくれるのか良く分からなかったので、思い当たりそうなものを全てログを取ってみました。
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());

picture_pc_6cc6aede5a704a395d79da7240f3b055.jpg


picture_pc_106d4211bde7900cbb4bf906528a09dc.jpg

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());
}

おぉ!取れていそうですが…
picture_pc_2897cee68eb1965dd6e944fd4409502a.jpg



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

formItems[j].getResponse()では、回答は取れないようです。
何だかもう少しでいけそうなので、色々と試してみます。

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



getIndex()


たぶん、これでいけるのでは…!
console.log(“getインデックス:”+formItems[j].getIndex());
picture_pc_6b5853baeb62f7a84ab3fd460962e9d7.jpg


いけてそうな感じが…!?

picture_pc_37dc2912126ea84acddcf7e08c86f1c9.jpg


picture_pc_242fb032251fc31e67d573d212bc2625.jpg


回答は、getItemResponsesのgetResponse()で取れますが、
console.log(“answer:”+itemResponses[j].getResponse());

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

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

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


.getId()


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

picture_pc_ba54f009637d0acc681e4e0cb727f287.jpg


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

・・・しかし。

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



ということは、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++) {
// タイトルを配列に突っ込んでいく(配列に15個入る)
 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() の何番目かを検索して、取得をします。

picture_pc_447b5fe1be2cf051407558c2f84379f1.jpg


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

picture_pc_e76f63450651527983e4dd3c3799ef99.jpg


おぉ、いい感じに取れていそうです!!
あとは、それぞれの列のセルに、インデックスの回答を入れ込んでいけば完成です!

//マスタの指定した列目からタイトルが一致した列番号に回答を挿入する
s_name_to.getRange(LastRow_PlusOne, START_COLUMN + arrValue_question.indexOf(arrValue_answer_question[i]),1,1).setValue(arrValue_answer[i]);

picture_pc_6d2c91dc0eadf9f8dead3d65bf4a56a2.jpg


picture_pc_98a5d8bc738b776e91371cc9e8343b8b.jpg

picture_pc_c3dec7f314bf64be867db08713109790.jpg


できましたっっ!!

まとめ


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

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

ご参考下さい(^o^)/
サービス数40万件のスキルマーケット、あなたにぴったりのサービスを探す