GASによるシートのコピーと関数の実行

Question

前提・実現したいこと

GoogleAppsScriptでGoogleSpreadsheetのシートをコピーしていますが、コピー回数に応じて
スプレッドシートでの処理を変えたいのですが、思った様に動作しません。
アドバイスを頂けますと大変助かります。

◆構成
スプレッドシートには3つのシートがあります。
①入力シート：必要な情報を入力するシートです。(imp_Sheet)
②処理シート：①で入力した情報によりスプレッドシート内部で処理を行います。
③テンプレートシート：②で処理した情報を再構成したコピー用のシートです。

◆処理の流れは以下の様になると考えました。
1.GASでの処理はfor文を使用して、必要な数だけ③のテンプレートシシートをコピーします。
この時、ただコピーするだけでは同じ内容のシートがコピーされてしまうので、

2.GASでコピー処理する時、for文の中で②の処理シートに「現在のコピー回数」を渡し
それに応じて処理シートの中身が書き換わる様にします。

3.書き換わった処理シートが反映された③のテンプレートシシートをコピーする。という流れを考えました。

◆補足
・「何回コピーするか？」は入力シート(imp_Sheet)のセルに入力してあります。
・コピーされたシートは、コピー時にそれぞれ名前を付けています。
名前は②処理シート内のセルに書かれており、コピー回数と連動します。

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

シートのコピーは行われますが、コピーされたシートを見ますとGASから渡した、「コピー回数に応じたスプレッドシート内での処理」が行われず、コピーされたシートの内容が全て「最後にコピーされた状態」でコピーされてしまいます。
例）コピー回数を4とした場合4回コピーされますが、コピー内容は全て4回目の内容でシートがコピ
結果だけ見ると、for文の{}の中で前半部分だけが先に処理されてしまい、for文後半「シートのコピー」が後から実行されている様に見えます。

該当のソースコード

function myFunction() {
  var myss = SpreadsheetApp.getActiveSpreadsheet();//スプレッドシート処理
  var imp_Sheet = myss.getSheetByName('imput');//①入力シート指定
  var data_mobSheet = myss.getSheetByName('data_mob');//②処理シート指定　名前データ・カウント数が格納されているシート取得
  var tmp_sheet = myss.getSheetByName('roottab');//③テンプレートシート指定

  
  var variationCount = imp_Sheet.getRange('B5').getValue();//入力シートからコピーする回数の取得。
　data_mobSheet.getRange('B1').setValue(1)//処理前に開始番号をセット、前回の処理した「回数」をリセットするために開始番号には1を入れる

 //処理
  for(var i = 1; i <= variationCount; i++) {// i=1　初期値
    data_mobSheet.getRange('B1').setValue(i);//処理シートに回数を入力。
    var checkname = data_mobSheet.getRange('C1').getValue();//コピー時のシート名をセルから取得
    var copy_sheet = tmp_sheet.copyTo(myss);//③のテンプレートシートをコピー
    copy_sheet.setName(checkname);//コピーしたシートをリネーム
  }
}

###試したこと
スプレッドシートの関数の方がGASよりも速度が速く、処理が追いついていないのでは？と思い、
for文中のコピー前にディレイを入れてみましたが変化はありませんでした。

Utilities.sleep(1000);

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

for文内の↓の箇所でfor文で繰り返した回数をセルに代入していますが、これがコピー時に反映されていません。

data_mobSheet.getRange('B1').setValue(i);//処理シートに回数を入力。

GASの処理とスプレッドシート内での関数の処理が混ざっていることが問題の様な気がします・・
自分の希望することをやろうと思うとfor文の中で
GASの処理　＞(GASから値を受け取り)スプレッドシートの関数動作　＞GASの処理(シートのコピー)　
という流れだと思うのですが、ここに問題があるのでしょうか？

Comments

[papinianus]

確認ですが、データモブには何かしらのシート関数があるということですか？

[sam]

はいそうです。入力シートの条件からIFやIFERROR(VLOOKUP、FILTER()等で文章を組んでいます。それを③のテンプレートシートに発注書用のフォーマットとして当てはめています。

[macaron_xxx]

記載されているスクリプトで再現しましたが、シート名が連番で付与されます。もう少しシートの中身など再現できる状況をお教えいただけますか。

Answer 1

■解決しました。
最低限のテスト版に置き換えて動作を確認していた所、gasでシートをコピーすると関数・数式ごと
コピーされていることに気が付きました。(今考えれば、コピーなのだから当たり前だろうと思います・・)

結果、
①for文を回した回数をセルが受け取る
②回数に応じた内容に書き換える
③シートをコピーする
(ここまで思い通りです。)
しかし、コピーしたシートにも数式が有効になっているので、仮にfor文を4回実行したとした場合、
4つコピーしたシート全てで関数が生きているため、全シートで4回目の結果が全てのシートに反映される
と言うことでした。
これが自分が書いた「なぜか結果が全て最終回数の値になってしまう」ということになっていました。
思い込みで「シートをコピーした場合、数式セルが全てただに入力値に置き換わる」と勘違いしていました。

■解決策
スマートな解決策ではありませんが、③テンプレートシートの他にもう1枚、同じレイアウトの
空シートを用意し(copybase)、for文が回るたびにテンプレートシートから値をコピー・ペースト。
回数分コピーするシートをcopybaseシートにした所、期待通りの動作になりました。
以下コードになります。

function myFunction() {
  var myss = SpreadsheetApp.getActiveSpreadsheet();//スプレッドシート処理
  var imp_Sheet = myss.getSheetByName('imput');//①入力シート指定
  var data_mobSheet = myss.getSheetByName('data_mob');//②処理シート指定　名前データ・カウント数が格納されているシート取得
  var tmp_sheet = myss.getSheetByName('roottab');//③テンプレートシート指定
  var CB_sheet = myss.getSheetByName('copybase');//④コピーシート
  
  
  var variationCount = imp_Sheet.getRange('B5').getValue();//入力シートから作成する数の取得。
　data_mobSheet.getRange('B1').setValue(1)//処理前に開始番号をセット、前回の処理した「回数」をリセットするために開始番号には1を入れる

 //処理
  for(var i = 1; i <= variationCount; i++) {// i=1　初期値
    data_mobSheet.getRange('B1').setValue(i);//処理シートに回数を入力
    var tmp_sheet_val = tmp_sheet.getRange('A1:M200').getValues();//テンプレートのセル値をコピー
    CB_sheet.getRange('A1:M200').setValues(tmp_sheet_val);//セル値をコピーシートに貼り付け
    var checkname = data_mobSheet.getRange('C1').getValue();//コピー時のシート名をセルから取得
    var copy_sheet =CB_sheet.copyTo(myss).setName(checkname);//コピーシートをコピーしてリネーム
    SpreadsheetApp.flush();//セルリフレッシュ   
  }
 
}

シートをコピーすれば数式は消えるという勘違いでした。
お騒がせ致しました。
アドバス有難うございます。

Answer 2

未検証ですがとりあえずスリープのかわりに下記でどうでしょう？

SpreadsheetApp.flush();

Comments

[sam]

アドバイス有難うございます。

シートのリネーム等はやめ、for文を単純化してSpreadsheetApp.flush();試してみましたが、結果に変化は無く解決には至ませんでした。

for(var i = 1; i <= variationCount; i++) {// i=1　初期値
data_mobSheet.getRange('B1').setValue(i);//処理シートに回数を入力
tmp_sheet.copyTo(myss);//コピー
SpreadsheetApp.flush();
}

又、ディレイを少し多めのにして（Utilities.sleep(8000);)、スクリプト実行中の②の処理シートを見た所、
1.for文から回数を取得し、その値に従って処理シート内では正常に処理(文章が書き換わって)いること。
2.処理シートの処理が完了してからシートがコピーされていること。
を目視できました。
しかしコピーされたシートの中身を見ると、全て同じ「指定したコピー回数の最大値の処理」(例えばコピー数「5」の場合、全て5の文章)でコピーされていました。

[papinianus]

検証ありがとうございます。今日一杯コードが書けないので他の回答なければ夜にでもやってみます

[macaron_xxx]

横槍すいません。
>>しかしコピーされたシートの中身を見ると、全て同じ「指定したコピー回数の最大値の処理」(例えばコピー数「5」の場合、全て5の文章)でコピーされていました。
これ、roottabシートにdata_mobシートを参照した関数とかいれてないですよね？