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

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

ただいまの
回答率

90.12%

Googleフォームの入力結果の反映前に,GASプログラムが走ってしまう

解決済

回答 3

投稿 編集

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

handymatsu

score 24

Google フォームでアンケート入力をしてもらっています。その結果は,スプレッドシートに吐き出しています。

それで,フォームでGASプログラムを書いています。プロジェクトのトリガーは,「フォームから-フォーム送信時」です。

GASプログラムでは,スプレッドシートに入力された内容を参照し,一番右端の列のさらに右側に,データ入力しています。

ここで,困ったのが,フォームで入力されたデータが,実際にスプレッドシートに追加されるのが,とても遅く,時間帯によってなのかもしれませんが,30秒くらいかかるようで,反映される前の内容で,GASプログラムが実行されてしまいます。

つまり,最下行(フォーム入力されたばかりの最新データ)の一番右側の列の隣にデータ入力したいのですが,スプレッドシートへのフォーム入力データの反映が遅いため,結果として,一つ上の行のデータに対して,プログラムが実行されてしまいます。

それで,

SpreadsheetApp.flush();


でフラッシュしてから,作業に取りかかったのですが,フォーム内容がフラッシュされる前に,GASプログラムでの内容が進んでしまい,うまくいきません。

苦肉の策で,

  var ssOrg = new Ssheet(ssID0);//formで入力されたデータが入っているスプレッドシート
  ssOrg.flush(); //SpreadsheetApp.flush(); // シートの再描画,これで書き込み遅延を解消できるはずなのだが,できない(T-T)
  while(ssOrg.getValue(0,1,1)!="wait") ssOrg.setValue(0,1,1,"wait"); 
  ssOrg.setValue(0,1,1,"タイムスタンプ"); //苦肉の策
  ssOrg.flush(); //SpreadsheetApp.flush(); // シートの再描画

のように,書いたのですが,これもうまくいきません。"タイムスタンプ"が,一瞬"wait"に切り替わるのは,目で見ていて分かりますが,それはフォームデータが反映される前なのです。

フォームの内容が反映されてから,GASプログラムを実行するようにしたいのですが,よい方法がありませんでしょうか?

例えば,スプレッドシートの一番左上の「タイムスタンプ」に現在入力されているデータ数を記録しておき,GASではその数とデータの行数が一致しなくなるまで待つ,という手はあるのですが,フォオーム新規入力の場合は,okなのですが,修正の場合が対応できません。(T-T)

(参考)
なお,newしているクラスは,次のように定義しています。

-------------------

//////////Ssheetクラスの定義開始(コンストラクタとメンバ関数で構成)2019/02/05
//Ssheetクラスのコンストラクタの記述
Ssheet = function(id){
  this.id = id;
  this.ssFile = SpreadsheetApp.openById(id);
  this.ssFileName = this.ssFile.getName();
  SpreadsheetApp.setActiveSpreadsheet(this.ssFile);//値を返さない
  this.activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
}
//Ssheetクラスのメンバ関数(メソッド)の定義開始
//spreadsheetのファイル名を返すメソッド
Ssheet.prototype.getFileName = function(){
  return this.ssFileName;
}
//spreadsheetのファイル名を変更するメソッド
Ssheet.prototype.rename = function(newName){
  this.ssFile.rename(newName);
}
//スプレッドシートを再描画し,書き込み遅延を防止するメソッド
Ssheet.prototype.flush = function(){
  SpreadsheetApp.flush();
}


//特定の列を非表示colからnum列
Ssheet.prototype.hideColumns = function(sheet,col,num){
  this.activeSheet = this.activeSpreadsheet.getSheets()[sheet];
  this.activeSheet.hideColumns(col,num);
}
//特定の列を表示colからnum列
Ssheet.prototype.unhideColumns = function(sheet,col,num){
  this.activeSheet = this.activeSpreadsheet.getSheets()[sheet];
  this.activeSheet.unhideColumns(col,num);
}

//hyperlinkするメソッド
Ssheet.prototype.hyperlink = function(sheet,row,col,url,str){
  this.activeSheet = this.activeSpreadsheet.getSheets()[sheet];
  var cell = this.activeSheet.getRange(row,col);
  var tmp = "=HYPERLINK(\"" +url+ "\",\"" + str + "\")";
  cell.setFormula(tmp);
}

//現在のcellをゲットする,うまくいってないように見える
//Ssheet.prototype.getCurrentCell = function(){
//  this.activeSheet = this.activeSpreadsheet.getActiveSheet();
//  var currentCell = this.activeSheet.getCurrentCell();
//  return currentCell.getColumn();
//}


//spreadsheetのsheetでrow行cal列にデータ入っているかどうかをtrue, falseで返すメソッド
Ssheet.prototype.isBlank = function(sheet,row,col){
  this.activeSheet = this.activeSpreadsheet.getSheets()[sheet];
  var cell = this.activeSheet.getRange(row,col);
  return cell.isBlank();
}
//spreadsheetのsheetでrow行cal列にデータを入れるメソッド
Ssheet.prototype.setValue = function(sheet,row,col,value){
  this.activeSheet = this.activeSpreadsheet.getSheets()[sheet];
  var cell = this.activeSheet.getRange(row,col);
  cell.setValue(value);
}
//spreadsheetのsheetでrow行cal列を数値形式にセットするメソッド
Ssheet.prototype.setStdNumFmt = function(sheet,row,col){
  this.activeSheet = this.activeSpreadsheet.getSheets()[sheet];
  var cell = this.activeSheet.getRange(row,col);
  cell.setNumberFormat("0");
}
//spreadsheetのsheetでrow行cal列をクリアするメソッド
Ssheet.prototype.clear = function(sheet,row,col,value){
  this.activeSheet = this.activeSpreadsheet.getSheets()[sheet];
  var cell = this.activeSheet.getRange(row,col);
  cell.clear(value);
}
//spreadsheetのsheetからrow行のcal列のデータをもらってくるメソッド
Ssheet.prototype.getValue = function(sheet,row,col) {//0,1,1が先頭
  this.activeSheet = this.activeSpreadsheet.getSheets()[sheet];
  var value = this.activeSheet.getRange(row, col).getValue();
  return value;
}
//背景の色を設定するメソッド
Ssheet.prototype.setBackgroundColor = function(sheet,row,col, r,g,b) {
  this.activeSheet = this.activeSpreadsheet.getSheets()[sheet];
  var cell = this.activeSheet.getRange(row,col);
  cell.setBackgroundRGB(r,g,b);
}
//spreadsheetのsheet数を指定の数増やすメソッド
Ssheet.prototype.insertSheet = function(num){
  var sheetNum = this.activeSpreadsheet.getNumSheets();
  while(num>sheetNum){
    this.activeSpreadsheet.insertSheet();
    sheetNum++;
  }
}
//spreadsheetの指定sheetを削除
Ssheet.prototype.deleteSheet = function(sheet){
  this.activeSpreadsheet.deleteSheet(this.activeSpreadsheet.getSheets()[sheet])
}
//spreadsheetのsheetの名前をセットするメソッド
Ssheet.prototype.renameSheet = function(sheet,newName){
  this.activeSheet = this.activeSpreadsheet.getSheets()[sheet];
  this.activeSheet.setName(newName);
}

//spreadsheetの指定sheetの指定範囲のデータを取得
Ssheet.prototype.getValues = function(sheet,row1,col1,row2,col2){
  this.activeSheet = this.activeSpreadsheet.getSheets()[sheet];
  return this.activeSheet.getDataRange(row1,col1,row2,col2).getValues();//シートの指定範囲のデータを取得
}  
//spreadsheetの指定sheetの全データを取得
Ssheet.prototype.getValues = function(sheet){
  this.activeSheet = this.activeSpreadsheet.getSheets()[sheet];
  return this.activeSheet.getDataRange().getValues();//シートの全データを取得
}  
//spreadsheetの指定sheetを取得
Ssheet.prototype.getSheet = function(sheet){
  this.activeSheet = this.activeSpreadsheet.getSheets()[sheet];
  return this.activeSheet;//シートを返す
}
//spreadsheetの指定sheetの最後の行番号を取得
Ssheet.prototype.getLastRow = function(sheet){
  this.activeSheet = this.activeSpreadsheet.getSheets()[sheet];
  return this.activeSheet.getLastRow();//最後の行番号を取得
}
//spreadsheetの指定sheetの最後の列番号を取得
Ssheet.prototype.getLastColumn = function(sheet){
  this.activeSheet = this.activeSpreadsheet.getSheets()[sheet];
  return this.activeSheet.getLastColumn();//最後の列番号を取得
}
//spreadsheetの指定行rowを削除
Ssheet.prototype.deleteRow = function(sheet,row){
  this.activeSheet = this.activeSpreadsheet.getSheets()[sheet];
  this.activeSheet.deleteRow(row);//行を削除
}
//spreadsheetの指定行rowを挿入
Ssheet.prototype.insertRow = function(sheet,row){
  this.activeSheet = this.activeSpreadsheet.getSheets()[sheet];
  this.activeSheet.insertRows(row);//行を挿入
}
//spreadsheetの指定列colを削除
Ssheet.prototype.deleteColumn = function(sheet,col){
  this.activeSheet = this.activeSpreadsheet.getSheets()[sheet];
  this.activeSheet.deleteColumn(col);//列を削除
}
//spreadsheetの指定列colを挿入
Ssheet.prototype.insertColumn = function(sheet,col){
  this.activeSheet = this.activeSpreadsheet.getSheets()[sheet];
  this.activeSheet.insertColumns(col);//列を挿入
}
//n(0,1,2...)枚目のsheetをアクティブにして,シートのIDを返す
Ssheet.prototype.getSheetIdByNum = function(sheet){
  this.activeSheet = this.activeSpreadsheet.getSheets()[sheet];
  var sheetId = this.activeSheet.getSheetId();
  return sheetId;
}
//////////Ssheetクラスの定義終了
  • 気になる質問をクリップする

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

回答 3

check解決した方法

0

  var ssOrg = new Ssheet(ssID0);//formで入力されたデータが入っているスプレッドシート
  ssOrg.flush(); //SpreadsheetApp.flush(); // シートの再描画,これだけで書き込み遅延を解消できるはずなのだが,できない(T-T)
  ssOrg.flush(); //SpreadsheetApp.flush(); // シートの再描画,なぜか二回続けると書き込み遅延を解消できるようだ
  var maxRow = ssOrg.getLastRow(0);
  var maxColumn = ssOrg.getLastColumn(0);
//  log.print("maxRow "+maxRow+", "+"maxColumn "+maxColumn+"\n");

のようにすると,maxRow, maxColumnともに正しい値となります。なぜか二回フラッシュすると,スプレッドシートへのフォーム入力データの反映をしてくれるようです。

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2019/07/20 21:22

    flushの後に,maxRowとmaxColumnを読みに行っているのも,うまくいく原因かもしれません。もし,セルに書き込みに行くと,フォーム入力データの反映に関わらず,書き込みのスレッドが動いてしまうような印象です。この辺りは,サーバ側での処理の関係なので,想像するしかありませんが・・・。

    いずれにしましても,flushを二回とその後,読みに行くことで,フォーム入力データが瞬時にSpreadSheetに瞬時に反映されるようになりました。結果オーライとします。

    キャンセル

0

フォームに書いているとのこと。

必ず起こるなら、最終行の次を処理行とみなせばよいように思います。また、フォームの値はイベントパラメータから取ればよいはず。
修正は逆に何をキーに修正対象行をとっているのでしょうか?

全然別の方法としてやりたい処理をシート関数として作って、スプレッドシートの一行目にarrayformulaで置いとくというのもあり得る選択肢かと思います。

スプレッドシート側のトリガーにするというのも一つですが、どうもスプレッドシート側のフォーム送信時トリガーは複数回発火するらしい(しかも入力値が不完全な状態で発火する)ので適切に値のチェックが必要になると思います(発火のタイミングで全行再計算するのも可能でしょうかね)。

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2019/07/17 17:51

    お返事,ありがとうございます。
    ●必ず起こるわけではないです。
     フォームの値は,イベントパラメータから取っています。
     修正は修正URLをイベントパラメータから,取っています。
    ●やりたい処理は,修正URLのハイパーリンクをセルに入力したい,です。
    ●スプレッドシート側にするにしても,発火タイミングがうまく合わないとなるとちと使いづらいところです。

    今年の3月,4月位は,フォーム側にGASプログラムを書いておいても,スプレッドシート側がフォーム入力値で反映されてから,動いていました。最近は,そうでもなくなったように見えます。

    困りました。

    キャンセル

  • 2019/07/17 18:37

    修正urlのルールが、わからないのですが、計算可能ならarrayformulaでできそうです。

    修正は、修正されたと知ることではなく、修正対象行をどう特定しているのか気になりました。

    キャンセル

  • 2019/07/17 19:38

    回答ありがとうございます。
    タイムスタンプで,最新のセルを探してます。
    上に書きましたが,SpreadsheetApp.flush();を2度続けて実行することで,瞬時にスプレッドシートにデータが入るようになりました。今,10件ほど入力を試しましたが,うまく動いています。対処療法ですが,なんとかなっています。

    キャンセル

  • 2019/07/17 19:39

    最初の列のタイムスタンプで,最新の行を探してます。

    の間違いでした。失礼しました。

    キャンセル

0

回答スプレッドシートの操作を行うのであれば、スプレッドシート側にスクリプトを書くことをおすすめします。

トリガ設定した関数の第一引数eからe.range.getRow()で対象行、e.namedValuesで値のオブジェクトを取得することができます。

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2019/07/18 09:10

    flushは根本的な解決になっていないと思いますがね…。

    キャンセル

  • 2019/07/20 12:14

    コメントありがとうございます。

    私もそう思うのですが,劇的にSpreadSheetへのデータの入力が早くなりました。
    flushの後,maxRow, maxColumnを読んでいるのですが,その読み込む動作も関係しているのかもしれません。
    2つのgasプログラムで運用していますが,今のところ問題は起きてないです。対処療法かもしれませんが,しばらく様子を見てみます。

    キャンセル

  • 2019/07/20 12:17

    SpreadSheet側に,gasプログラムを置いても良いのですが,SpreadSheetは開発中は,削除することもあり,間違ってgasプログラムもなくなってしまいそうで,怖い,という面もあります。ゴミ箱から拾ってくれば良いのですけど・・・。

    キャンセル

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

  • ただいまの回答率 90.12%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる