GASで生成したドロップダウンでエラーが発生する

実現したいこと

スプレッドシート上の表の要素をもとに複数箇所にドロップダウンリストを生成したい

発生している問題・分からないこと

同じ表を参照し複数箇所に内容の同じドロップダウンリストを生成し、リストを選択したとき片方だけ無効判定になる

エラーメッセージ

error
1無効: 指定したリスト上のアイテムを入力してください

該当のソースコード

GAS
1const ss = SpreadsheetApp.getActiveSpreadsheet();             //スプレッドシート(ss<定数>)を取得
2const sh1 = ss.getSheetByName("分類・撮影者識別記号設定");         //シート「分類・撮影者識別記号設定」(sh1<定数>)を取得
3const sh2 = ss.getSheetByName("分類設定");                     //シート「分類設定」(sh2<定数>)を取得
4
5function dropDown() {
6  let gameName = sh1.getRange('B3:B32').getDisplayValues();       //sh1B3:B32セルの値の二次元配列(gameName,分類名)を取得
7  let teamName = sh1.getRange('E3:E32').getDisplayValues();       //sh1E3:E32セルの値の二次元配列(teamName,チーム名)を取得
8  let opponentTeamName = sh1.getRange('E3:E32').getDisplayValues();  //sh1E3:E32セルの値の二次元配列(opponentTeamName,チーム名)を取得
9  
10  sh2.getRange('C6').clearDataValidations();                      //sh2C6セルの入力規則をクリア
11  let dropDownGame = SpreadsheetApp.newDataValidation();          //新規入力規則を作成
12  dropDownGame.requireValueInList(gameName);                      //gameNameを元にプルダウンリストを作成
13  sh2.getRange('C6').setDataValidation(dropDownGame);             //sh2C6セルにプルダウン(分類)を記述
14
15  sh2.getRange('G2').clearDataValidations();                      //sh2G2セルの入力規則をクリア
16  let dropDownTeam = SpreadsheetApp.newDataValidation();          //新規入力規則を作成
17  dropDownTeam.requireValueInList(teamName);                      //teamNameを元にプルダウンリストを作成
18  sh2.getRange('G2').setDataValidation(dropDownTeam);             //sh2G2セルにプルダウン(チーム)を記述
19
20  sh2.getRange('E7').clearDataValidations();                      //sh2E7セルの入力規則をクリア
21  let dropDownOpponentTeam = SpreadsheetApp.newDataValidation();  //新規入力規則を作成
22  dropDownOpponentTeam.requireValueInList(opponentTeamName);      //opponentTeamNameを元にプルダウンリストを作成
23  sh2.getRange('E7').setDataValidation(dropDownOpponentTeam);     //sh2E7セルにプルダウン(対戦チーム)を記述
24}

試したこと・調べたこと

teratailやGoogle等で検索した
ソースコードを自分なりに変更した
知人に聞いた
その他

上記の詳細・結果

一番最初

GAS
1function dropDown() {
2  let gameName = sh1.getRange('B3:B32').getDisplayValues();       //sh1B3:B32セルの値の二次元配列(gameName,分類名)を取得
3  let teamName = sh1.getRange('E3:E32').getDisplayValues();       //sh1E3:E32セルの値の二次元配列(teamName,チーム名)を取得
4  
5  sh2.getRange('C6').clearDataValidations();                      //sh2C6セルの入力規則をクリア
6  let dropDownGame = SpreadsheetApp.newDataValidation();          //新規入力規則を作成
7  dropDownGame.requireValueInList(gameName);                      //gameNameを元にプルダウンリストを作成
8  sh2.getRange('C6').setDataValidation(dropDownGame);             //sh2C6セルにプルダウン(分類)を記述
9
10  sh2.getRange('G2').clearDataValidations();                      //sh2G2セルの入力規則をクリア
11  let dropDownTeam = SpreadsheetApp.newDataValidation();          //新規入力規則を作成
12  dropDownTeam.requireValueInList(teamName);                      //teamNameを元にプルダウンリストを作成
13  sh2.getRange('G2').setDataValidation(dropDownTeam);             //sh2G2セルにプルダウン(チーム)を記述
14
15  let opponentTeamName = teamName;
16  sh2.getRange('E7').clearDataValidations();                      //sh2E7セルの入力規則をクリア
17  let dropDownOpponentTeam = SpreadsheetApp.newDataValidation();  //新規入力規則を作成
18  dropDownOpponentTeam.requireValueInList(opponentTeamName);      //opponentTeamNameを元にプルダウンリストを作成
19  sh2.getRange('E7').setDataValidation(dropDownOpponentTeam);     //sh2E7セルにプルダウン(対戦チーム)を記述
20}

一回修正後

GAS
1function dropDown() {
2  let gameName = sh1.getRange('B3:B32').getDisplayValues();       //sh1B3:B32セルの値の二次元配列(gameName,分類名)を取得
3  let teamName = sh1.getRange('E3:E32').getDisplayValues();       //sh1E3:E32セルの値の二次元配列(teamName,チーム名)を取得
4  
5  sh2.getRange('C6').clearDataValidations();                      //sh2C6セルの入力規則をクリア
6  let dropDownGame = SpreadsheetApp.newDataValidation();          //新規入力規則を作成
7  dropDownGame.requireValueInList(gameName);                      //gameNameを元にプルダウンリストを作成
8  sh2.getRange('C6').setDataValidation(dropDownGame);             //sh2C6セルにプルダウン(分類)を記述
9
10  sh2.getRange('G2').clearDataValidations();                      //sh2G2セルの入力規則をクリア
11  let dropDownTeam = SpreadsheetApp.newDataValidation();          //新規入力規則を作成
12  dropDownTeam.requireValueInList(teamName);                      //teamNameを元にプルダウンリストを作成
13  sh2.getRange('G2').setDataValidation(dropDownTeam);             //sh2G2セルにプルダウン(チーム)を記述
14
15  sh2.getRange('E7').clearDataValidations();                      //sh2E7セルの入力規則をクリア
16  let dropDownOpponentTeam = SpreadsheetApp.newDataValidation();  //新規入力規則を作成
17  dropDownOpponentTeam.requireValueInList(teamName);      //opponentTeamNameを元にプルダウンリストを作成
18  sh2.getRange('E7').setDataValidation(dropDownOpponentTeam);     //sh2E7セルにプルダウン(対戦チーム)を記述
19}

補足

・スプレッドシートを読み込み直すとエラーが消えます。
・G2以外の二つでは異常は発生しません。
・試しにG3セルにリストを生成してみるようにしても同じような結果になりました。
・G2セルに生成するプログラムとE7セルに生成するプログラムの順番を逆にしてもG2セルのみエラーが出ました
・このfunctionのトリガーはonEdit関数によってsh1(画像1枚目)シートが編集された時に処理されるように設定しています。

行動規範の内容に同意します

回答1件

ベストアンサー

おそらくこう書くべきかな？

解決済みとなってからも更新してごめんなさい。

既存のプルダウンの規則があった時は、
削除してスプレッドシートを更新してから新たに設定するのではなく、
既存のプルダウンの選択肢のみを更新するようにしてみました。
同じことを繰り返すので、更新の関数を別にしました。

入力済みの値が新たな選択肢に含まれていないとエラーになりますので注意喚起になります。

js
1const ss = SpreadsheetApp.getActiveSpreadsheet();             //スプレッドシート(ss<定数>)を取得
2const sh1 = ss.getSheetByName("分類・撮影者識別記号設定");         //シート「分類・撮影者識別記号設定」(sh1<定数>)を取得
3const sh2 = ss.getSheetByName("分類設定");                     //シート「分類設定」(sh2<定数>)を取得
4
5// プルダウンを更新
6function dropDown() {
7  // 分類及びチームのプルダウンリストを取得
8  const gameName = sh1.getRange('B3:B32').getDisplayValues().flat();
9  const teamName = sh1.getRange('E3:E32').getDisplayValues().flat();
10
11  // それぞれのセルの入力規則を更新
12  updateDataValidation(sh2.getRange('C6'), gameName);
13  updateDataValidation(sh2.getRange('G2'), teamName);
14  updateDataValidation(sh2.getRange('E7'), teamName);
15}
16
17// 指定のセルとデータから、プルダウンの入力規則を作成または更新する
18function updateDataValidation(range, values) {
19  // 指定のセルにプルダウンの入力規則があるか確認
20  let rule = range.getDataValidation();
21  if (rule) { // 既存の入力規則（プルダウン前提）があるなら選択肢を更新
22    rule = rule.copy().withCriteria(rule.getCriteriaType(), [values, true]).build();
23  } else { // 入力規則がない(null)なら新たな入力規則を設定
24    rule = SpreadsheetApp.newDataValidation().requireValueInList(values, true).build();
25  }
26  range.setDataValidation(rule);  
27}

2回目の回答: 理由はわかりませんが、

変更前の選択肢が残っていることも原因のようです。
当初の回答のコードで選択肢をそのままにしておいて、
その選択肢がないプルダウンにデータを書き換えるとエラーになります（当たり前？）

それと、今回はbuild()を入れていますが、
これまでbuild()していなくてもプルダウンが設定されていたのも不思議です。

こちらのコードは、選択肢をクリアしています。
スプレッドシートを更新しなくてもエラーにならないようです。

js
1const ss = SpreadsheetApp.getActiveSpreadsheet();                 //スプレッドシート(ss<定数>)を取得
2const sh1 = ss.getSheetByName("分類・撮影者識別記号設定");            //シート「分類・撮影者識別記号設定」(sh1<定数>)を取得
3const sh2 = ss.getSheetByName("分類設定");                         //シート「分類設定」(sh2<定数>)を取得
4
5function dropDown() {
6  const gameName = sh1.getRange('B3:B32').getDisplayValues().flat();     //sh1!B3:B32セルの値の一次元配列(gameName,分類名)を取得
7  const teamName = sh1.getRange('E3:E32').getDisplayValues().flat();     //sh1!E3:E32セルの値の一次元配列(teamName,チーム名)を取得
8
9  const dropDownGame = SpreadsheetApp.newDataValidation()         //新規入力規則を作成
10    .requireValueInList(gameName).build();                        //gameNameを元にプルダウンリストを作成
11  const dropDownTeam = SpreadsheetApp.newDataValidation()         //新規入力規則を作成
12    .requireValueInList(teamName).build();                        //teamNameを元にプルダウンリストを作成
13
14  sh2.getRange('C6').clearDataValidations()                       //sh2!C6セルの入力規則をクリア
15    .clearContent()                                               //sh2!C6セルの値をクリア
16    .setDataValidation(dropDownGame);                             //sh2!C6セルにプルダウン(分類)を記述
17  sh2.getRange('G2').clearDataValidations()                       //sh2!G2セルの入力規則をクリア
18    .clearContent()                                               //sh2!G2セルの値をクリア
19  .setDataValidation(dropDownTeam);                               //sh2!G2セルにプルダウン(チーム)を記述
20  sh2.getRange('E7').clearDataValidations()                       //sh2!E7セルの入力規則をクリア
21    .clearContent()                                               //sh2!E7セルの値をクリア
22    .setDataValidation(dropDownTeam);                             //sh2!E7セルにプルダウン(対戦チーム)を記述
23}
24

当初の回答

入力規則が設定されていないシートに登録したときは、元のコードでエラーになりませんでしたので、
事前の入力規則の削除が反映されずに、重複して入力規則が登録されているのでしょうか。

次のようにしてみたら、エラーにはなりませんでしたので、参考まで。

なお、別々に登録しても、選択肢が同じだとシート上は同じ規則になってしまうので
最初から同じ選択肢を登録しています。

js
1const ss = SpreadsheetApp.getActiveSpreadsheet(); //スプレッドシート(ss<定数>)を取得
2const sh1 = ss.getSheetByName("分類設定"); //シート「分類・撮影者識別記号設定」(sh1<定数>)を取得
3const sh2 = ss.getSheetByName("分類・撮影者識別記号設定"); //シート「分類設定」(sh2<定数>)を取得
4
5function dropDown() {
6  const gameName = sh1.getRange('B3:B32').getDisplayValues().flat(); //sh1!B3:B32セルの値の一次元配列(gameName,分類名)を取得
7  const teamName = sh1.getRange('E3:E32').getDisplayValues().flat(); //sh1!E3:E32セルの値の一次元配列(teamName,チーム名)を取得
8
9  sh2.getRange('C6').clearDataValidations(); //sh2!C6セルの入力規則をクリア
10  sh2.getRange('G2').clearDataValidations(); //sh2!G2セルの入力規則をクリア
11  sh2.getRange('E7').clearDataValidations(); //sh2!E7セルの入力規則をクリア
12
13  SpreadsheetApp.flush(); // スプレッドシートを更新
14
15  const dropDownGame = SpreadsheetApp.newDataValidation(); //新規入力規則を作成
16  dropDownGame.requireValueInList(gameName); //gameNameを元にプルダウンリストを作成
17  sh2.getRange('C6').setDataValidation(dropDownGame); //sh2!C6セルにプルダウン(分類)を記述
18
19  const dropDownTeam = SpreadsheetApp.newDataValidation(); //新規入力規則を作成
20  dropDownTeam.requireValueInList(teamName); //teamNameを元にプルダウンリストを作成
21  sh2.getRange('G2').setDataValidation(dropDownTeam); //sh2!G2セルにプルダウン(チーム)を記述
22  sh2.getRange('E7').setDataValidation(dropDownTeam); //sh2!E7セルにプルダウン(チーム)を記述
23}