[Google Apps Script]フォームから取得した項目を二次元配列にまとめて一気に書き込む

二次元配列
Math.max
getRange
setValues
Math.max
fill（配列を埋める）
concat

前回までで、テスト形式のフォームから基本的な項目を取得してシートに書きこむプログラムが完成しました。

ただ、このスクリプトは[フォームから１項目ずつ取得]→[シートに１行ずつ書き込む]…の繰り返し動作になっています。

項目数が少なければ問題ないのですが、質問項目が数十個～になると取得にかかる時間が気になってきます。

Google Apps Scriptでは、時間のかかるスクリプトは途中でエラー停止してしまいます。(執筆現在6分)

参考　Google サービスの割り当て

幸い、Google Apps ScriptではappendRowでの一行ずつの入力のほかに、複数行への入力ができるメソッドも用意されています。

セル範囲に対して行うsetValuesというメソッドです。

appendRowでは、シートの新しい行に[]で指定されたデータをカンマ区切りで1つずつ入力するメソッドです。

 sheet.appendRow(['アイテムタイプ', '質問内容', '解説', '不正解解説', '配点', '選択肢']);

結果↓↓

アイテムタイプ

質問内容

解説

不正解解説

配点

選択肢

対してsetValuesは、セル範囲に[]で指定されたデータを複数行まとめて入力します。

複数行のデータは二次元配列という方式で用意します。

二次元配列って？

二次元配列というのは簡単に言うと、配列の配列です。

つまり配列[a,b,c,d]をまるごと配列の一つの要素にしてしまい、カンマ区切りで[]に入れてしまう形式です。

例えば以下のような感じです。

[[a,b,c,d],[e,f,g,h],[i,j,k,l]]

この形式はそのまま複数行の表形式に当てはめることができます。

a	b	c	d
e	f	g	h
i	j	k	l

イメージしやすいですね。

では、さっそく今までappendRowで使用していた一次元配列を二次元配列として変数に格納していきましょう。

二次元配列を作成

二次元配列を作成するためには、今まで１つのitem処理を終えるごとに

sheet.appendRow(itemSheetinput)

で入力していた部分をそのまま二次元配列への要素の追加に書き換えます。

まずは、最初に格納する変数を作成しておきます。

let allItemSheetinput = [];

そして、sheet.appendRow(itemSheetinput)を下記のように書き換えます。

allItemSheetinput = allItemSheetinput.concat([itemSheetinput]);

これで、for構文が繰り返されるごとに一行分が[]でまとまった複数行の二次元配列が追加されていきます。

これを、ループから抜けた後にシートの指定箇所へ一気に書き込みます。

複数行への書き込み

setValuesを使うには、いくつか注意すべき点があります。

ちなみに、よく似たメソッドで「setValue」というものがあるので注意してください！！
（私はこれでしばらくハマってしまいました…）

setValuesはシートではなくセル範囲(Range)に対して行う
セル範囲は、書き込む二次元配列と同じ大きさで指定
用意する二次元配列は、一行ごとのサブ配列の数をそろえる

一つずつ確認しましょう。

setValuesは、シートオブジェクトではなく、「セル範囲（Range）」に対して命令します。なので、あらかじめRangeを設定する必要があります。

さらに、設定するセル範囲は入力する二次元配列と同じ[行×列]である必要があります。数が合わないとエラー停止します。

また、二次元配列はすべての要素が同じ項目数になっていないとこれもエラーになってしまいます。

これらを踏まえてコードを変更していきます。

まず問題となるのが、取得したフォームのデータは選択肢の数によって列数が違っている、というところです。

配点・解説などは存在しない場合空欄（””）で埋めることも可能ですが、選択肢の数は無限に作成できるのであらかじめ数を決めて空欄でつぶしておくことは難しそうです。

ではどうするか？

完成した二次元配列の最大の列数を取得し、その数に合わせて足りない行には空白データを作成してやればよさそうです。

ここはChatGPTに任せてみます。最近では、質問もだいぶ具体的にできるようになりました(笑)

lengthのそろっていない二次元配列のlengthを揃える方法はありますか？
[[a,a,a,a],[a,a,a],[a,a,a,a],[a,a]]
を
[[a,a,a,a],[a,a,a,null],[a,a,a,a],[a,a,null,null]]
のようにしたいです。

提供された二次元配列の要素数を揃える方法は、以下のようにして実現できます。この例では、最も長いサブ配列の長さに合わせて、他のサブ配列の長さを揃えます。

// 元の二次元配列
const originalArray = [[a,a,a,a],[a,a,a],[a,a,a,a],[a,a]];

// 最も長いサブ配列の長さを取得
const maxLength = Math.max(...originalArray.map(subArray => subArray.length));

// 長さを揃えた新しい二次元配列を作成
const newArray = originalArray.map(subArray => {
const diff = maxLength - subArray.length;
return subArray.concat(new Array(diff).fill(null));
});

console.log(newArray);

このコードは、最も長いサブ配列の長さを maxLength に取得し、各サブ配列を concat() メソッドを使用して必要な数の null 要素で拡張しています。これにより、要素数が揃った新しい二次元配列が作成されます。

上記のコードを実行すると、要素数が揃った二次元配列 newArray が得られます。

おおおすげーー。

変数を少し書き換えるとそのまま使えそうです。ChatGPTが提示するコードは必ずしも最適解ではないこともありますが、それでも考え方の参考になります。

もしかしたら、もっと効率的な方法があるのかもしれませんが、今回はこの方法を使用してみます。

  // 最も長いサブ配列の長さを取得
  const maxLength = Math.max(...allItemSheetinput.map(subArray => subArray.length));
  // 長さを揃えた新しい二次元配列を作成
  allItemSheetinput = allItemSheetinput.map(subArray => {
    const diff = maxLength - subArray.length;
    return subArray.concat(new Array(diff).fill(null));
  });
  Logger.log(allItemSheetinput);

ログを確認すると、配列がしっかり「null」で埋まっています😃

セル範囲を指定する（getRange）

さて、次はこの二次元配列と同じ大きさのセル範囲(Range)を用意します。

セル範囲は、getRange(開始行, 開始列, 行数, 列数)という形で、指定します。

つまり、セルB1から３行４列分のセル範囲

を指定する場合は

getRange(1, 2, 3, 4)

となります。

今回必要な範囲は、（新しい行,1列目,配列の長さ,サブ配列の長さ）です。

それぞれを変数に直すと

(sheet.getLastRow()+1, 1, allItemSheetinput.length, maxLength)

となります。getLastRowでデータが入っている最終行が取得できるのでその次の行(＋１)から始まり、

サブ配列の長さは先ほど空白で埋めるにあたってChatGPTが出してくれていたもの（maxLength）を利用します。

では、セル範囲を取得してそこに書き込むコードを作成します。

 sheet.getRange(sheet.getLastRow()+1, 1, allItemSheetinput.length, maxLength).setValues(allItemSheetinput);

getRangeとsetValuesをつなげていっぺんに書いています。

これで、実行結果は同じになるはずです。

ちなみに、一行ずつ書き込む方法と一気に書き込む方法で９０問のテスト用フォームを使用して速度を計測してみたら、

一行ずつ・・・54.302秒、一気に・・・43.351秒

という結果でした。

サーバーの状況により速度にはブレがありますが、やはり一気に書き込んだ方が処理は早く済むようです。

おそらく勉強していくうちにもっと効率的な方法が見つかると思います。

少しずつプログラムをアップデートしていきます。

今回のコードはこちら

function myFunction() {
  // フォームを開く
  const form = FormApp.openByUrl(フォームのURL);
  // URLは'https://docs.google.com/fo******/edit'のように''で囲んで入力

  //フォームの要素を配列で取得
  const items = form.getItems();

  //スプレッドシートを開く。
  const spsheet = SpreadsheetApp.openByUrl(シートのURL);
  // URLは'https://docs.google.com/fo******/edit'のように''で囲んで入力
  const sheet = spsheet.getActiveSheet();
  //処理を加えるシートとして、ファイルを開いて最初に表示される(アクティブな)シートを設定

  sheet.clear(); //シートの内容をクリア

  let allItemSheetinput = [];

  //シートに行を追加し、formタイトル入力
  sheet.appendRow(['フォームタイトル', form.getTitle()]);
  // 項目タイトル行を作成
  sheet.appendRow(['アイテムタイプ', '質問内容', '解説', '不正解解説', '配点', '正解', '選択肢']);

  for (let i = 0; i < items.length; i++) { //Item数をiと置き換えて、iを0から順番に項目の数だけ１ずつ増やす >
    const itemparts = items[i]; //itemという配列のi番目の要素をitempartsとして取り出す。
    const itemType = itemparts.getType(); //取得したitempartsのタイプを確認してitemTypeに保管
    const itemText = itemparts.getTitle();//取得したitempartsの質問内容を確認してitemTextに保管
    let itemSheetinput = [itemType, itemText]; // ↑入力ではなく一旦配列に格納

    ////アイテムタイプ判別とキャストの操作は別function；switchItemTypeに移動

    switchItemTypereturn = switchItemType(itemType, itemparts);

    let testPartsItem = switchItemTypereturn[0];
    let hasChoice = switchItemTypereturn[1];
    let hasPoint = switchItemTypereturn[2];

    //配点取得処理開始
    if (hasPoint == 1) {
      const itemPoint = testPartsItem.getPoints();
      let itemFeedback;
      let itemFeedbackIc;
      //選択肢タイプでなければ、1種類のフィードバック、選択肢は2種類取得
      if (hasChoice == 0) {
        itemFeedback = testPartsItem.getGeneralFeedback()?.getText();//フィードバック
      } else {
        itemFeedback = testPartsItem.getFeedbackForCorrect()?.getText();//正解のフィードバック
        itemFeedbackIc = testPartsItem.getFeedbackForIncorrect()?.getText();//不正解のフィードバック
      }
      itemSheetinput = itemSheetinput.concat(itemFeedback, itemFeedbackIc, itemPoint);
 //アイテムタイプや質問内容の配列と合体
    }

    //選択肢取得処理開始
    if (hasChoice == 1) {
      const choices = testPartsItem.getChoices(); //選択肢を取得
      const choicesText = choices.map(x => x.getValue()); //選択肢のテキストを取得
      //正解に設定されている選択肢の番号をカンマ区切りで取得
      const choicesCorrect = choices.map(function (element, index) {
        element = element.isCorrectAnswer(); //choicesをchoices.isCorrectAnswer(); に書き換え
        if (element === true) { element = index + 1; return element; }
//正解設定されてたらインデックス番号＋１に書き換え
      }).filter(function (element) { return element !== undefined; });//正解番号の要素だけ取得し直し

      itemSheetinput = itemSheetinput.concat(choicesCorrect.join(), choicesText);
 //アイテムタイプや質問内容の配列と合体
      //正解番号は配列で格納されているのでjoinでカンマ区切り文字列に変換
    }
    // sheet.appendRow(itemSheetinput); //まとめてシートの新しい行に入力

    allItemSheetinput = allItemSheetinput.concat([itemSheetinput]);
  }
  Logger.log(allItemSheetinput);

  // 最も長いサブ配列の長さを取得
  const maxLength = Math.max(...allItemSheetinput.map(subArray => subArray.length));
  // 長さを揃えた新しい二次元配列を作成
  allItemSheetinput = allItemSheetinput.map(subArray => {
    const diff = maxLength - subArray.length;
    return subArray.concat(new Array(diff).fill(null));
  });
  Logger.log(allItemSheetinput);
  console.log(sheet.getLastRow(), 1, allItemSheetinput.length, maxLength);
  sheet.getRange(sheet.getLastRow()+1, 1, allItemSheetinput.length, maxLength).setValues(allItemSheetinput);

}