Qiitaの記事で基本的にできた！（ありがとうございます！）
マスターのデータが読み込まれない！？
マスターのデータを上記のQiita記事に従い、スプシに作成していたのだが、読み込まれない、と思って、AIに聞きながら色々調べたら、スプシのアクセス権を設定してなかったことが原因だった。
→画面右上から共有にする。編集権限は不要で、読み取り権限だけあればよい。
もしかしたらそれだけでも動いていたかもしれない。
コードを修正
コードも少し不備があったようで、AIにキキながら修正した。
最終的に動いたコード：
自動削除したい項目があるコントロールが「プルダウンメニュー」の場合
function myFunction() {
//https://docs.google.com/spreadsheets/d/xxx/editのxxxの部分
//マスター
var masterSpreadSheetID = 'あいでぃーをいれる';
//回答用
var answerSpreadSheetID = 'あいでぃーをいれる';
// シート名
var masterSheetName = 'マスター';
var answerSheetName = 'フォームの回答';
// 選択肢を動的にするプルダウンのタイトル
var questionName = '日程候補';
// スプレッドシートIDでシートを取得
var masterSheets = SpreadsheetApp.openById(masterSpreadSheetID);
var masterSheet = masterSheets.getSheetByName(masterSheetName);
var answerSheets = SpreadsheetApp.openById(answerSpreadSheetID);
var answerSheet = answerSheets.getSheetByName(answerSheetName);
// マスターシートのA行の2行目から下の値を配列で取得
var sheetLastRow = masterSheet.getLastRow();
if (sheetLastRow > 1) {
var candidate = masterSheet.getRange(2, 1, sheetLastRow - 1, 2).getValues();
} else {
return;
}
// 回答シートの状況も取得（A行の2行目から）
var answerSheetLastRow = answerSheet.getLastRow();
if (answerSheetLastRow > 1) {
var questionNames = answerSheet.getRange(1, 1, 1, answerSheet.getLastColumn()).getValues()[0];
var colCount = -1;
for (var i = 0; i < questionNames.length; i++) {
if (questionNames[i] === questionName) {
colCount = i;
break;
}
}
if (colCount >= 0) {
var answerData = answerSheet.getRange(2, colCount + 1, answerSheetLastRow - 1, 1).getValues();
} else {
// questionNameが見つからなかった
return;
}
}
// フォーム取得→項目も取得
var form = FormApp.getActiveForm();
var items = form.getItems();
// 動的に選択肢を作成
items.forEach(function(item){
// 名前が"日程候補"のプルダウンの項目を
if(item.getTitle() === questionName){
var listItemQuestion = item.asListItem();
var choices = [];
candidate.forEach(function(nameAndCapacity){
if(nameAndCapacity[0] != ""){
// 定員が0(null) or 無回答 の場合は選択肢を表示
if (answerData == null || nameAndCapacity[1] == 0 || nameAndCapacity[1] == ""){
choices.push(listItemQuestion.createChoice(nameAndCapacity[0]));
} else {
var counter = 0;
// 何人埋まってるか確認
for(var i = 0; i < answerData.length; i++){
if (nameAndCapacity[0] == answerData[i][0]){
counter++;
}
}
// 埋まってなければ選択肢を表示
if (counter < nameAndCapacity[1]){
choices.push(listItemQuestion.createChoice(nameAndCapacity[0]));
}
}
}
});
//選択肢の残数によってFormを受け付けるかどうか判断
if (choices.length > 0) {
// フォームの回答を受け付ける
form.setAcceptingResponses(true);
listItemQuestion.setChoices(choices);
} else {
// 回答受付終了
form.setAcceptingResponses(false);
}
return;
}
});
}
自動削除したい項目があるコントロールが「ラジオボタン」の場合
function myFunction() {
//https://docs.google.com/spreadsheets/d/xxx/editのxxxの部分
//マスター
var masterSpreadSheetID = 'あいでぃーをいれる';
//回答用
var answerSpreadSheetID = 'あいでぃーをいれる';
// シート名
var masterSheetName = 'マスター';
var answerSheetName = 'フォームの回答';
// 選択肢を動的にするプルダウンのタイトル
var questionName = '日程候補';
// スプレッドシートIDでシートを取得
var masterSheets = SpreadsheetApp.openById(masterSpreadSheetID);
var masterSheet = masterSheets.getSheetByName(masterSheetName);
var answerSheets = SpreadsheetApp.openById(answerSpreadSheetID);
var answerSheet = answerSheets.getSheetByName(answerSheetName);
// マスターシートのA行の2行目から下の値を配列で取得
var sheetLastRow = masterSheet.getLastRow();
if (sheetLastRow > 1) {
var candidate = masterSheet.getRange(2, 1, sheetLastRow - 1, 2).getValues();
} else {
return;
}
// 回答シートの状況も取得（A行の2行目から）
var answerSheetLastRow = answerSheet.getLastRow();
if (answerSheetLastRow > 1) {
var questionNames = answerSheet.getRange(1, 1, 1, answerSheet.getLastColumn()).getValues()[0];
var colCount = -1;
for (var i = 0; i < questionNames.length; i++) {
if (questionNames[i] === questionName) {
colCount = i;
break;
}
}
if (colCount >= 0) {
var answerData = answerSheet.getRange(2, colCount + 1, answerSheetLastRow - 1, 1).getValues();
} else {
// questionNameが見つからなかった
return;
}
}
// フォーム取得→項目も取得
var form = FormApp.getActiveForm();
var items = form.getItems();
// 動的に選択肢を作成
items.forEach(function(item){
// 名前が"日程候補"のラジオボタンの項目を
if(item.getTitle() === questionName){
var radioItemQuestion = item.asMultipleChoiceItem(); // ここを変更
var choices = [];
candidate.forEach(function(nameAndCapacity){
if(nameAndCapacity[0] != ""){
// 定員が0(null) or 無回答 の場合は選択肢を表示
if (answerData == null || nameAndCapacity[1] == 0 || nameAndCapacity[1] == ""){
choices.push(radioItemQuestion.createChoice(nameAndCapacity[0])); // ここを変更
} else {
var counter = 0;
// 何人埋まってるか確認
for(var i = 0; i < answerData.length; i++){
if (nameAndCapacity[0] == answerData[i][0]){
counter++;
}
}
// 埋まってなければ選択肢を表示
if (counter < nameAndCapacity[1]){
choices.push(radioItemQuestion.createChoice(nameAndCapacity[0])); // ここを変更
}
}
}
});
//選択肢の残数によってFormを受け付けるかどうか判断
if (choices.length > 0) {
// フォームの回答を受け付ける
form.setAcceptingResponses(true);
radioItemQuestion.setChoices(choices); // ここを変更
} else {
// 回答受付終了
form.setAcceptingResponses(false);
}
return;
}
});
}
定員オーバーしても選択肢から消えない！？→全角スペースのせいだった。
定員オーバーしても選択肢から消えなかった。
結論から言うと、選択肢の文字列に全角スペースがあったのが原因だった。
フォーム送信されたときにアンケート結果を溜めるスプシ「フォームの回答」シートを見てみると、全角スペースのハズの選択肢が、半角スペースに変換されて登録されてた。
対策としては、選択肢から全角スペースをなくすか、プログラムの比較時に全角スペースを半角スペースに変換する。
トリガー ＞ イベントの種類が「フォーム送信時」
上記の記事だと、イベントのタイミングは「フォーム送信時」となっているが、それだと、フォームやスプシのマスターデータの変更を行ってから、変更の内容が即時にフォーム側に反映されず、ユーザーがフォームで送信を行ってから反映されることになる。
自分でフォーム送信すればいい話だが、仕事でやっている場合、お客さん先にメールが行くことになり、事前の連絡が必要。
即座に反映させたい場合、「起動時」の方を選択したい。
ちなみに「起動時」については以下の注意点があるので要注意。
Googleフォームには「起動時」のトリガーがあるが、これは、フォーム回答者がフォームを開いた時ではなく 、フォーム作成者がフォーム編集画面を開いたときに発火するトリガーなので注意。
https://qiita.com/ynaito/items/c21f16c9c612449defc8#%E3%83%95%E3%82%A9%E3%83%BC%E3%83%A0%E8%B5%B7%E5%8B%95%E6%99%82%E3%81%AE%E3%83%88%E3%83%AA%E3%82%AC%E3%83%BC%E3%81%AF%E3%83%95%E3%82%A9%E3%83%BC%E3%83%A0%E7%B7%A8%E9%9B%86%E7%94%BB%E9%9D%A2%E8%B5%B7%E5%8B%95%E6%99%82%E3%81%AA%E3%81%AE%E3%81%A7%E6%B3%A8%E6%84%8F
しかし、「起動時」を選択すると、上記のコードをそのまま使うと、今度は上限のカウントを行わなくなる。そのため、即座に反映させたいが、送信時にGASが動いてくれないので、上限に達したかなどの処理は別に切り分ける必要が出てくる。
GASのトリガーを、以下の２つにすればいい。
- フォーム送信時
- 起動時
GASの処理内容が、（スプシの内容を基に）フォーム内容を更新する、というものだから、編集画面起動時およびフォーム送信時両方で発火しても問題ない。
むしろリアルタイムに発火しまくっても良いぐらい。
コメント