Googleフォームのプルダウンの選択内容を、スプレッドシートと同期させて一元管理するGASを書く

はじめに

●スプレッドシートの特定の場所に、フォームで選択させるようにしたらフォームをいちいちアップデートせずにすむ
●フォームとスプレッドシートで差分がうまれなくて済む
●スプレッドシートが大本のデータとなってくれて一覧性があがる

上記の利点があるため、Googleフォームのプルダウンの選択内容を、スプレッドシートと同期させるスクリプトを書いた

完成イメージ

これがフォームの回答に自動で入ってほしい内容。
１つ目は素直に値をフォームに表示させて、そのまま回答をスプレッドシートに集めるパターン
２つ目は、表示名は「ディズニーランド」としていが、スプレッドシートに収集する時はそれに対応したIDで記録したいパターンだ。

下記のように、それぞれ入ってほしい値から選択できる

実際にさわれるフォームはこちら

回答の収集は下記のように、振られたIDで記録されている

実際のスプレッドシートはこちら

Google スプレッドシート - オンラインでスプレッドシートを作成/編集できる無料サービスです

作り方

1.スプレッドシートを用意する

この時、スプレッドシートのシートの名前と内容をお手本といっちさせてください。スプレッドシートをコピーするのが楽だと思います。

2.Google フォームを生成して、プルダウンの質問を２つ用意する

今回のスクリプトでは、質問に「どれですか？」と「場所はどこですか？」とそのまま入れないと動かない。
上記のフォームのURLから、そのままコピーして動かすのがはやい

3.スプレッドシートのIDとフォームのIDを取得しておく
(それぞれURLから取得できる。詳しくは検索してください)

4.作成したGoogle フォームから、スクリプトを開く

5.スクリプトエディタを開いたら、下のコードをコピペ

もしくはこちらを御覧ください

Google ドライブ - 1 か所であらゆるファイルを保管

スプレッドシートのIDとフォームのIDをGASのスクリプトに差し替える

6.トリガーを関数に設定する

sendValue関数はフォームに値が送られた時に、実行される関数

updateForm関数は、1時間おきにスプレッドシートを見て、フォームの内容を最新に更新する関数

GASコード

スプレッドシートのIDとフォームのIDを自分で作ったものにしないと動かないので注意してください。

////フォームをアップデートする関数　一時間ごとに実行する
function updateForm() {  
 //GoogleフォームのURLからIDを取得する
 //こういうの "xxxxxxxx-xxxxxxxxxxx-xxxxxx"
 var formId = "1MChQyYpbkQYhLW9fwC9JZaeTomO2ratFFDr9Jiz5h1w"
 const formFile = FormApp.openById(formId);
 var items = formFile.getItems();

 var ssId = "1KtVy0zaN5YGgU8HeKAFAFX6q3r3ksdVHefzlqVOTDtg"; //スプレッドシートのIDを指定
　var ssName = "一覧表"; //スプレッドシートのシート名を指定
　var sheet = SpreadsheetApp.openById(ssId).getSheetByName(ssName);
 //そのシートで一番下の行の番号を取得する
　var last_row = sheet.getLastRow();
 Logger.log(sheet);

 //１つ目の質問をスプレッドシートの内容と同期させる
 var itemOfService = items[0];
 //3行目2列目,1セル取得
 var sitelistNumbers = dropNullItemFromArray(sheet.getRange(3,2,last_row,1).getValues());
 var sitelistNames = dropNullItemFromArray(sheet.getRange(3,3,last_row,1).getValues());
 var sitelist = [];
 for ( var i = 0; i<sitelistNames.length; i++ ) {
   sitelist[i] = sitelistNumbers[i] + "," + sitelistNames[i]
 }

 //１つ目の質問を反映させる
 itemOfService.asListItem().setChoiceValues(sitelist).setRequired(true);
 
 //2つ目の質問をスプレッドシートの内容と同期させる
 var itemOfService = items[1];
 var sitelistNumbers = dropNullItemFromArray(sheet.getRange(3,5,last_row,1).getValues());
 var sitelistNames = dropNullItemFromArray(sheet.getRange(3,7,last_row,1).getValues());
 var sitelist = [];
 for ( var i = 0; i<sitelistNames.length; i++ ) {
   sitelist[i] = sitelistNumbers[i] + "," + sitelistNames[i]
 }

 //2つ目の質問を反映させる
 itemOfService.asListItem().setChoiceValues(sitelist).setRequired(true);
}

//配列の空配列をいい感じに消す関数
function dropNullItemFromArray(array){
 var new_array = [];
 array.forEach(function(value){
   if(value != null && value != "") {
     new_array.push(value);
   }
 });
 return new_array;
}

//////フォームを投稿するとスプレッドシートに値を入れる関数
function sendValue(e) {
 //スプレッドシートのIDを指定
 var ssId = "1KtVy0zaN5YGgU8HeKAFAFX6q3r3ksdVHefzlqVOTDtg"; 
 //スプレッドシートのシート名を指定して、データをいれるシートに参照できるようにする
　var ssName = "出力例"; 
　var sheet = SpreadsheetApp.openById(ssId).getSheetByName(ssName);
 Logger.log("///////sheet//////")
 Logger.log(sheet)
 
 //スプレッドシートのシート名を指定して、cidの命名ルールがのったシートを参照できるようにする
 var ssNameForRule = "一覧表"; //スプレッドシートのシート名を指定
　var sheetForRule = SpreadsheetApp.openById(ssId).getSheetByName(ssNameForRule);
 
 //cidの詳細が入っている最後の行を取得して、一番下の行に値を入れられるようにする
　var lastRow = sheet.getRange(1, 1).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
 Logger.log("///////lastRow//////")
 Logger.log(lastRow)
 
 //フォームで入力された値を取り出す
 FormApp.getActiveForm();
 var itemResponses = e.response.getItemResponses();
 var result = [];
 
 for (var i = 0; i < itemResponses.length; i++) {
   var itemResponse = itemResponses[i];
   var question = itemResponse.getItem().getTitle();
   var answer = itemResponse.getResponse();
   
   //cidを発行した日の日付をいれる
   var today = Utilities.formatDate(new Date(), "Asia/Tokyo", "yyyy/MM/dd");
　　 sheet.getRange(lastRow + 1, 1).setValue(today);
   
   //質問によって、どの列にいれるか分ける
   switch (question) {
     case "どれですか？":
       sheet.getRange(lastRow + 1, 2).setValue(answer)
       Logger.log("どれですか？")
       Logger.log(answer)
       break
       
     case "場所はどこですか？":
       var str =  answer.split(",")
       var caseId = parseInt(str[0])
       var caseName = str[1]
       //スプレッドシートから値を読み取り、ディズニーランドをDLのIDに変換している
       var caseAbbreviationName = sheetForRule.getRange(caseId + 2, 6).getValue()
       Logger.log("場所はどこですか？")
       Logger.log(answer)
       Logger.log(caseId)
       Logger.log(caseName)
       Logger.log(caseAbbreviationName)
       sheet.getRange(lastRow + 1,3).setValue(caseAbbreviationName)
       break
       
     case "入力した人は誰ですか？":
       sheet.getRange(lastRow + 1, 4).setValue(answer)
       Logger.log("だれですか？")
       Logger.log(answer)
       break
       
     default:
       Logger.log("ケースはありません")
       break
   }
   result.push([question, answer]);
 }
 Logger.log(result);
}