Googleフォームの日付をスプシから読み込む

Googleフォームで日付のアンケートを取っているのですが，その日付をスプレッドシートから読み込ませ，毎月アンケートの日付を変えるのを楽にしたいと思っています。
下記のコードをとにかく並べてみて動かしているのですが，あと少しのところでうまくいきません。
（ドドド素人が書いていますので，ぐちゃぐちゃですがお許しください）

流れとしては，
①スクリプトを実行すると，日付を載せているシートから日付を読み取りフォーム側に上書き
②フォームの日付を上書きすると，回答シートの後ろにどんどん列が増設されていくので，
③一旦，フォームの回答が集まるシート「Aスタッフの回答」シートのリンクを解除
④「Aスタッフの回答」シートを削除し，新たにシートを作りフォームと再リンク
⑤新たに作り再リンクさせたシートの名前を「Aスタッフの回答」にする
がしたいです。

しかし，下記のコードを実行すると，
⑤がうまくいきません。
Googleフォームは再リンクを繰り返すと，新たに作られるシート名が「フォームの回答 2」→「フォームの回答 3」→「フォームの回答 4」・・・と，どんどん数字が大きくなります。
そこの部分の対策ができていないのか，シート名が変わらず「フォームの回答〇〇」（数字）になってしまいます。
色々と調整しながら試していたのですが，シート名の重複エラーが出て無理だった時もありました...
そして，今に至っています......

どのようにコードを書き直したら良いのかが分からず困っております...　
初心者で何も分からずすみません。ここまでできたことが奇跡でもあります......

途中で諦めているので，最後の方はぐちゃぐちゃかもしれません。
整理もまだしていないので，お許しください。

Googleフォームの質問は，
名前
７日間の日付のチェックボックス
備考欄
７日間の日付のチェックボックス
備考欄
になっています。

//フォームの日付を上書き

function NewDay() {

  //スプレッドシートのID
  var sheetid = "XXXXXXXXXXXXXXXXXX";
  //スプレッドシートを取得
  var sheet = SpreadsheetApp.openById(sheetid).getSheetByName("Aスタッフの回答");
  

  //早朝：日付リストを取得する
  var data = sheet.getRange("A2:A").getValues();
  var dlength = data.length;
  
  //１次元配列として作り直す
  var array = [];
  for(var i = 0;i<dlength;i++){
    if(data[i][0] == ""){
      //空白の時は何もしない
    }else{
      array.push(data[i][0]);
    }
  }
 
  //アクティブフォームを取得する
  var formId = "XXXXXXXXXXXXXXXXXXX";
  var form = FormApp.openById(formId)
  // 質問項目がチェックボックスのもののみ取得
  var items = form.getItems(FormApp.ItemType.CHECKBOX_GRID);
  
  items.forEach(function(item){
    //対象のグリッドパーツを見つける
    if(item.getTitle().match(/早朝.*$/)){
      //質問セクションを取得する
      var question = item.asCheckboxGridItem();
 
      //選択肢を上書きする
      question.setRows(array);
    }
  });


 //夕方：日付リストを取得する
  var data2 = sheet.getRange("C2:C").getValues();
  var dlength2 = data2.length;
  
  //１次元配列として作り直す
  var array2 = [];
  for(var i = 0;i<dlength2;i++){
    if(data2[i][0] == ""){
      //空白の時は何もしない
    }else{
      array2.push(data2[i][0]);
    }
  }
 
  //アクティブフォームを取得する
  var formId2 = "XXXXXXXXXXXXXXXXXXXXXXXX";
  var form = FormApp.openById(formId2)
  // 質問項目がチェックボックスのもののみ取得
  var items = form.getItems(FormApp.ItemType.CHECKBOX_GRID);
  
  items.forEach(function(item){
    //対象のグリッドパーツを見つける
    if(item.getTitle().match(/夕方.*$/)){
      //質問セクションを取得する
      var question2 = item.asCheckboxGridItem();
 
      //選択肢を上書きする
      question2.setRows(array2);
    }
  });
 


 　////////////////////
//フォームとのリンクを解除//
  ////////////////////

 // フォームオブジェクト
  var form = FormApp.openById(["XXXXXXXXXXXXXXXXXXXXXX"]);
  // 回答先スプレッドシートを格納しているフォルダ
  var folder = ["XXXXXXXXXXXXXXXXXXXX"];
  
  
  // 回答先スプレッドシートを取得する
  var old_ss = SpreadsheetApp.openById(sheetid);

  // 回答先リンクを外す
  form.removeDestination();
  
  // フォームに残っている回答を削除
  form.deleteAllResponses();




  //////////////////
//シートの削除スクリプト//
  //////////////////



 //あらかじめ削除したくないシート名を記載してください。例「["シート１","シート5","シート10"]」
const notDelSheet = ["名簿シート","アドレスシート","勤務状況"];
// 現在アクティブなスプレッドシートを取得
let mySheet = SpreadsheetApp.getActiveSpreadsheet();
//取得したスプレッドシートにある全てのシートを配列として取得
let sheetData = mySheet.getSheets();
//末尾のシートを削除するかを決めるフラグ
let flag =0;
//forループでシートを削除する削除を行う
for(i=0;i<sheetData.length;i++){
//削除したくないシート存在しない場合、末尾のシートは削除せずスキップする
if(flag ==0 && i == sheetData.length-1){
break;
}
//削除対象から除外するシートにヒットした場合は、削除処理は行わず、フラグを立てる
if(notDelSheet.indexOf(sheetData[i].getSheetName()) != -1){
flag = 1;
}
//削除除外シートではない場合は、削除処理を実行する
else{
mySheet.deleteSheet(sheetData[i]);
}
}


  // スプレットシート取得
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  form.setDestination(FormApp.DestinationType.SPREADSHEET, ss.getId());


  // シート名を変更
  var sheets = ss.getSheets();

/**
 * 有効な新規シート名を取得します。
 　return: シート名
 */

　return('Aスタッフの回答')
  for (var i = 0; i < sheets.length; i++) {

var name = sheets[i].getName();
if (name.indexOf('Aスタッフの回答') != -50
&&name.indexOf(sheetName) == -1) {
 if( !existsSheetAs(sheetName) )
sheets[i].setName(sheetName);

  for(x = 1; existsSheetAs(sheetName = sheetName + '_' + ++x); )
      continue;
  return sheetName;
}
}
/**
 * 指定の名称のシートが存在するかどうかを判定
 * param1: シート名
 * return: 存在する場合はtrue、そうでない場合はfalse
 */

function existsSheetAs(sheetname) {
  var sheet = spreadSheet.getSheetByName(sheetname);
  return (sheet != null);
}

退会済みユーザー

2022/07/06 11:59 編集

コードを読むと「Aスタッフの回答」というシート、つまりフォームの回答を集めているシートから回答者のタイムスタンプを読み取り、その日時をチェックボックスに設定しようとしているように見えるのですが、これは正しいのでしょうか？チェックボックスに設定する日付は「Aスタッフの回答」というシートではなく、別の名前のシートから読み取るのではないですか？というのは質問文には「フォームの内容は名前７日間の日付のチェックボックス備考欄７日間の日付のチェックボックス備考欄」となっていますが、上記のように回答者のタイムスタンプを新たにチェックボックスの回答欄に設定してしまうと、チェックボックスの数は不定になる気がするからです。

shopp_life

2022/07/06 12:05

ありがとうございます。ご指摘の通り，フォームの日付は「日付」シートから読み取るようにしています。書き間違いでした....正しくは下記ですね。 var sheet = SpreadsheetApp.openById(sheetid).getSheetByName("日付");

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

回答1件

ベストアンサー

フォームの回答シートの内容と名前をリセットする処理だけ書きます。（そのほかの部分が複雑すぎてちょっと読み取れないので・・・）
＜考え方＞
①form.getDestinationId() で、そのフォームがリンクしているスプレッドシートのIDを取得可能。（フォーム→スプレッドシート）
②sheet.getFormUrl() で、そのシートがリンクされているフォームのURLを取得可能。（シート→フォーム）

ただし①で取得できるのは、スプレッドシートIDだけで、そのスプレッドシート内のどのシートがリンクされているかはわかりません。
そこで、スプレッドシートを特定した後、そのスプレッドシート内にあるシートを１つ１つ調べ、そのシートのリンク元フォームのurlと、指定したフォームのurlが一致するか判定します。
一致した場合は、そのシートのシート名を変更します。
(下記は2つの関数に分かれていますが２つセットで記述する必要があります。実行するのは1番目のresetAnswerSheetです）

js
1function resetAnswerSheet() {
2  // フォームID
3  var formId = 'フォームID';
4  //スプレッドシートのID
5  var answerSheetId = 'スプレッドシートのID';
6  // 設定したいシート名
7  var answerSheetName = 'フォームの回答';
8
9  var form = FormApp.openById(formId);
10  if (form == null) {
11    console.log(`フォームID:${formId}は存在しないか、読み取り権限がありません。`);
12  }
13
14
15  //スプレッドシートを取得
16  var ss = SpreadsheetApp.openById(answerSheetId);
17  if (ss == null) {
18    console.log(`シートID：${answerSheetId}は存在しないか、読み取り権限がありません。`);
19    return;
20  }
21
22  var destSheetId = form.getDestinationId();
23  if (destSheetId !== answerSheetId) {
24    console.log(`指定したスプレッドシートIDには、指定したフォームIDの回答を集めるシートが存在しません`);
25    return;
26  }
27
28  var targetSheet = getDestinationSheet_(ss, form);
29  if (targetSheet == null) return;
30
31  // 回答先リンクを外す
32  form.removeDestination();
33
34  // フォームに残っている回答を削除
35  form.deleteAllResponses();
36
37  // 回答シートを削除
38  ss.deleteSheet(targetSheet);
39
40  // 回答を集めるシートを新規作成
41  form.setDestination(FormApp.DestinationType.SPREADSHEET, answerSheetId);
42  SpreadsheetApp.flush();
43
44  var targetSheet = getDestinationSheet_(ss, form);
45  if (targetSheet == null) return;
46  // 指定した名前に変更する
47  targetSheet.setName(answerSheetName);
48}
49
50/**
51 * 指定したスプレッドシートの中から、指定したフォームの回答を収集しているシートを返す
52 * 引数：
53 *  spreadSheet：回答収集シートを探す対象のスプレッドシートオブジェクト
54 *  form：対象となる回答を保持しているフォームオブジェクト
55 * 
56 * 戻り値：フォームの回答を収集している最初のシートオブジェクト
57 * 　該当するシートが見つからない場合、または spreadSheet, form が指定されていない場合： undefined
58 */
59function getDestinationSheet_(spreadSheet, form) {
60  if (spreadSheet == null) {
61    console.log(`引数 spreadSheetにスプレッドシートIDが指定されていません`);
62    return;
63  }
64  if (form == null) {
65    console.log(`引数 formにフォームIDが指定されていません`);
66    return;
67  }
68  // スプレッドシート内にあるシートを１つ１つ調べ、そのシートのリンク元フォームのurlと、
69  // 指定したフォームのurlが一致するか判定
70  var formUrl = form.getEditUrl().replace('/edit', '');
71  var destinationSheet = spreadSheet.getSheets().find(sheet =>
72    sheet.getFormUrl()?.replace('/viewform', '') === formUrl);
73
74  if (destinationSheet == null) {
75    console.log(`回答が設定されているシートが見つかりませんでした。`);
76    return;
77  }
78  return destinationSheet;
79}