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

Question

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クラスの定義終了

Answer 1

  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ともに正しい値となります。なぜか二回フラッシュすると，スプレッドシートへのフォーム入力データの反映をしてくれるようです。

Comments

[handymatsu]

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

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

Answer 2

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

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

Comments

[handymatsu]

ありがとうございます。
スプレッドシート側ですね。
対象行がとれるのであれば，フォーム入力データの反映が遅くても，
イベントパラメータから，データは取れるので，なんとかなりそうです。
もう少し色々試してみます。

[handymatsu]

とりあえず，フォーム側のGASで，次のように二回フラッシュしたところ，ほぼすぐにスプレッドシートに入力され，思った通りに動作するようになりました。今，19時ですからまだネットが混んでいないのかも。しばらく様子を見てみます。

var ssOrg = new Ssheet(ssID0);//formで入力されたデータが入っているスプレッドシート
ssOrg.flush(); //SpreadsheetApp.flush(); // シートの再描画，これで書き込み遅延を解消できるはずなのだが，できない(T-T)
ssOrg.flush(); //SpreadsheetApp.flush(); // シートの再描画

[macaron_xxx]

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

[handymatsu]

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

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

[handymatsu]

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

Answer 3

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

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

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

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

Comments

[handymatsu]

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

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

困りました。

[papinianus]

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

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

[handymatsu]

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

[handymatsu]

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

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