質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.50%

  • Google Apps Script

    1262questions

    Google Apps ScriptはGoogleの製品と第三者のサービスでタスクを自動化するためのJavaScriptのクラウドのスクリプト言語です。

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

解決済

回答 2

投稿

  • 評価
  • クリップ 0
  • VIEW 643

skydalt0n

score 0

 前提・実現したいこと

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の処理(シートのコピー) 
という流れだと思うのですが、ここに問題があるのでしょうか?

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • papinianus

    2018/11/01 18:46

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

    キャンセル

  • skydalt0n

    2018/11/01 19:34

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

    キャンセル

  • macaron_xxx

    2018/11/02 10:26

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

    キャンセル

回答 2

check解決した方法

0

■解決しました。
最低限のテスト版に置き換えて動作を確認していた所、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();//セルリフレッシュ   
  }

}

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

0

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

SpreadsheetApp.flush();

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/11/01 21:04

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

    シートのリネーム等はやめ、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の文章)でコピーされていました。

    キャンセル

  • 2018/11/02 09:12

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

    キャンセル

  • 2018/11/02 10:31

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

    キャンセル

同じタグがついた質問を見る

  • Google Apps Script

    1262questions

    Google Apps ScriptはGoogleの製品と第三者のサービスでタスクを自動化するためのJavaScriptのクラウドのスクリプト言語です。