重複しないデータをグループ分けしたい

Question

実現したいこと

下記のサイトのようにgoogleFormの2段階の選択肢を作ろうとしています。
https://qiita.com/hayaball/items/73e99db1446cec5ca158
その際の選択肢のシートを作ろうとしています。
しかし、選択肢は増えていくので動的にする必要があります。
そこで、

A列に任意のテキストが入っています。（重複あり）
B列にも任意のテキストが入っています。

これを別のシートに自動で
1行目に重複しないA列
2行目以降にA列に連動したものを入力されたいと考えました。

ここに実現したいことを箇条書きで書いてください。
動的な選択肢の2段階のプルダウンのgoogleフォームの選択肢を作りたいです。

前提

googleformへの2段階式の選択肢の作り方は見つけたので、
残りは動的な選択肢を作ればよいということで、
A列が重複されずに1行目に表示され、2行目以降以降にはそのテキストが表示されるような
オリジナル関数を作成し、別シートのA1にそれを入力されれば表示されるように試みました。

発生している問題・エラーメッセージ

該当のソースコード

function copyUniqueTexts() {
  var sourceSheetName = "ユーザー"; // ここに元のシートの名前を入力してください
  var targetSheetName = "項目シート"; // ここに別のシートの名前を入力してください

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName(sourceSheetName);
  var targetSheet = ss.getSheetByName(targetSheetName);

  var data = sourceSheet.getDataRange().getValues();
  var uniqueContentMap = {};

  // データのループを行い、重複のないA列の内容を収集
  for (var i = 0; i < data.length; i++) {
    var content = data[i][0];
    var text = data[i][1];

    if (content && !uniqueContentMap[content]) {
      uniqueContentMap[content] = [text];
    } else if (content) {
      uniqueContentMap[content].push(text);
    }
  }

  // 別のシートにデータを書き込む
  var targetData = [];
  for (var content in uniqueContentMap) {
    targetData.push([content].concat(uniqueContentMap[content]));
  }

  targetSheet.clear();
  targetSheet.getRange(1, 1, targetData.length, targetData[0].length).setValues(targetData);
}

試したこと

ChatGPTにエラーを入力し、アクセス制限も解除し、
それでもエラー内容をコピーアンドペーストしましたが
よい解決策はもらえませんでした

補足情報（FW/ツールのバージョンなど）

ここにより詳細な情報を記載してください。

Answer 1

カスタム関数の中でopenByIdを使うことはできません。
対象シートの公開設定やアクセス制限の解除、appsscript.jsonへのpermission付与記述にかかわらず、カスタム関数でopenById関数を呼び出している場合、画像のようにPermission Errorになります。

カスタム関数ではなく、Apps Scriptエディタから直接copyUniqueTexts関数を実行してください。

Comments

[YAmaGNZ]

提示されているコードにはopenByIdは使用されていないんですよね。
なんでなんでしょ？

Answer 2

まずこちらにあるように

カスタム関数がエラー メッセージ You do not have permission to call X service. をスローした場合、サービスはユーザー認証が必要なため、カスタム関数では使用できません。

カスタム関数はreturnで返す値以外にセルに影響を与えることができません。
なので、質問に書かれているような関数となると、カスタムメニューを用意し、そのメニューから実行するようにしなくてはなりません。

このことから提示されたコードの関数はreturnで値を返す必要があり、２次元配列として返せば、式を記述したセルを起点に値が入ります。
ただし、この方法でもすでに値が入っているセルに上書きすることはできません。

function copyUniqueTexts(sourceSheetName) {
  // ２次元配列の行列を入れ替える関数の定義
  const transpose = a=> a[0].map((_, c) => a.map(r => r[c]));

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName(sourceSheetName);

  var data = sourceSheet.getDataRange().getValues();
  var uniqueContentMap = {};

  // データのループを行い、重複のないA列の内容を収集
  for (var i = 0; i < data.length; i++) {
    var content = data[i][0];
    var text = data[i][1];

    if (content && !uniqueContentMap[content]) {
      uniqueContentMap[content] = [text];
    } else if (content) {
      uniqueContentMap[content].push(text);
    }
  }

  // 別のシートにデータを書き込む
  var targetData = [];
  for (var content in uniqueContentMap) {
    targetData.push([content].concat(uniqueContentMap[content]));
  }

  // 行列を入れ替える
  let retdata = transpose(targetData);
  return retdata;
}

シート１

シート２

Comments

[sloth824]

ありがとうございました。大変助かりました。
求めている数式が他のスプレッドシートでしたら出来、
Open By IDの原因を確認しましたら、記載してあるサイトのスプレッドシートのコピーを使用し、すでに書いてあるコードが原因だったようです。
そこの解除が未だに不明ですが、じっくり解読していきたいと思います。
ありがとうございました。