【GAS】Google Apps Script 活用事例　Google Formsの内容を、回答者ごとに特定のSpreadsheetに転記するスクリプト

nepia_infinity

2020年11月28日 05:10

ありがちな実務なのに、汎用的なスクリプトが無い。

フォームの回答内容を、特定のスプレッドシートに転記する仕事が回ってきました。ありがちな実務なのに今まで書いた事がなかったので、その備忘録です。

Google Formsの質問項目に従業員番号ないし、スタッフIDがあれば、VLOOKUPが使えるはずなので、自動化とか難しい事を考える前に、質問項目を見直すべきなんだろうなぁ......。多分。

今回のポイントは、Google Formsで回答した順序と、名簿の順序が必ずしも一緒ではないという事です。なので、setValues()で、まとめて書き込むという事は出来ません。

何をやっているかというと.....。

1.　スプレッドシートA（フォームの回答）から、名前と内容の取得
2.　1で取得した名前が、他のスプレッドシートBのどこの行にあるか検索
3.　1で取得した名前と、スプレッドシートBの名前が合致したら書き込む

function main(){
 const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 const sheet       = spreadsheet.getSheetByName('フォームの回答 1');
 const values      = spreadsheet.getDataRange().getValues();
 
 //見出し行を検索する。列の判定
 const headerRow        = values[0];
 const nameColumnIdx    = headerRow.indexOf('お名前');
 const answerColumnIdx  = headerRow.indexOf('その他を選んだ理由について');
 
 let count = 0;
 
 //見出し行は除外するため、iは、 1 からスタート
 for(let i = 1; i < values.length; i++){
 
   //Google Formsのタイムスタンプが無いものをスキップする。
   if(!values[i][0]){continue}
   
   //回答者の名前から空白行と、全角スペースを削除
   const person = values[i][nameColumnIdx]
   .replace(/\s/,'')
   .replace('　','');
   
   //今回は、特定の質問項目の回答のみ、転記したかったため、特定の列の値を取得しています。
   const answer = values[i][answerColumnIdx];
   //console.log(person, answer);
   
   //別関数に名前と、回答内容を渡して、任意の行に転記する
   targetSheet_(person, answer);
   
   count += 1;
   
 }//for
 console.log('対象者の人数：　', count);
}//end


/*
* Formsの回答者と、その回答内容を、転記先のシートを検索して、名前が一致したら転記する。
* 
* @param {string} Google Formsの回答者の名前
* @param {string}　今回は、F列の回答内容
* @return  なし
*
*/

function targetSheet_(name, answer){
 const url         = 'https://docs.google.com/spreadsheets/d/*************';
 const spreadsheet = SpreadsheetApp.openByUrl(url);
 const sheet       = spreadsheet.getSheetByName('回答者名簿');
 
 
 //名前のみの1次元配列を作成し、半角や全角スペースを除く
 //※getRange()の範囲は、お使いの環境に合わせて変えてください。
 const nameArray      = sheet.getRange('E12:E29').getValues().flat();
 nameArray.map(person => person.replace(/\s/,'').replace('　',''));
 
 
 //見出し行の検索 
 //※getRange()の範囲は、お使いの環境に合わせて変えてください。
 const values      　　 = spreadsheet.getRange('A10:W29').getValues();
 const headerRow       = values[0];
 const nameColumnIdx   = headerRow.indexOf('回答者');
 const targetColumnIdx = headerRow.indexOf('その他を選んだ理由について');
 
 //console.log('見出し行を1次元配列で取得', headerRow);
 //console.log('名前のみの1次元配列', nameArray);
 //console.log('ファイル名の確認', spreadsheet.getName());
 
 
 //引数で渡した対象者の名前と一致するか確かめる
 if(nameArray.indexOf(name) !== -1){
   const index       = nameArray.indexOf(name);
   
   //シートが、A1セルから始まっていないため、+ 12 しています。
   //配列は0から始まるため、通常は、+ 1 でOK
   const row         = index + 12;
   const column      = targetColumnIdx + 1;
   
   const targetRange = sheet.getRange(row, column);
   targetRange.setValue(answer);
   
   console.log(name, targetRange.getA1Notation());
   console.log(answer);
   
 }//if
}//end

余談ですが、テストする際は、targetRange.setValue(answer); 　をコメントアウトして、ログを見て、書き出す行と列が合っているか確認しながら行うといいかもしれません。今回、テストしなかったら、大惨事になっていました。それだけ空白行は、厄介なのですよ。

出来るだけ不要な空白行を作らない。ホント、それに限る....。

sheet.getRange('E12:E29').getValues().flat();

上記のように、空白行とセルの結合を避けるため、中途半端な範囲を2次元配列で取得した後、1次元配列化しています。

スマートに1次元配列を作れるのが.....理想

/*
* 二次元配列から、指定の列のデータを抽出し、一次元配列を生成する
*
* @param {Object[][]} 二次元配列
* @param {number} 配列の列数（0以上のインデックス）
* @return {Object[]} 特定列の、一次元配列
*
*/


function generateArray_(values, column){
  return values.map(record => record[column]).filter(value => value);
}

function myFunction() {
   const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
   const sheet       = spreadsheet.getSheetByName('シート名');
   const values      = spreadsheet.getDataRange().getValues(); 
   
   //見出し行を検索して、1次元配列を作成する。
   const headerRow     = values[0];
   const nameColumnIdx = headerRow.indexOf('回答者');
   const nameArray     = generateArray_(values, nameColumnIdx);

}

まぁ、理想は理想ですね.....。

僕もすぐ忘れてしまうのですが、generateArray_という関数、省略前はこんな感じです。

function test() {
  const array  = ['', '', '', 'a', 'b', 'c'];
  
  // true を返した要素は残され、false を返した要素は取り除かれる。
  // 要素が、空白以外だったら、trueとなり、配列に残るが、空白だったら、falseとなり、配列から削除される。
  const newArray = array.filter(function(value, index, array){
    return value !== '';
  })
  
  console.log(newArray)//['a','b','c'];;
}

元々、いつも隣にITのお仕事　を模写した際に、この関数便利ねって感嘆し、それ以来愛用しています。

今回の自動化のような汎用的に使える活用事例を、もっとたくさん作りたいなと思っています。ソースコードの再利用と、ちょっとした編集で、めんどい仕事がミスなく秒で片付く、そんな風になれたらいいなと思っています。

今回の自動化に近い過去記事は、こちら

Google Formsの自動化はこちら。

まだ、V8対応出来ていない記事が多いですが、参考になれば幸いです。

この記事が気に入ったらサポートをしてみませんか？