GAS setValues の処理時間が5倍以上になったのは何が原因でしょうか？

Question

1日1回約100万件の2次元配列データをGASでシートに書き込んでいます。
setValuesの呼び出しは1回だけです。
書き込む先は同じシートの同じ位置です。
過去1週間は毎日約4分で処理が終わっていたのですが、昨日は6分のタイムアウトになって、書き込まれた件数が20万件から30万件なので、処理時間が5倍以上になっていました。時間をおいて5回以上試したのですが、結果は変わらずです。
何が原因でしょうか？

補足です。書き込むデータが文字型よりも数字型のほうが処理が数倍早いと感じています。データは全て数字型で、文字型になったということはありません。

Comments

[CHERRY]

スプレッドシートは、毎回新規作成ですか？　それとも前回のファイルに値を上書きでしょうか。

[CodeQuality]

同じスプレッドシートの同じシートに上書きしています。
新規作成するとアクセス権の認証が出てしまうので同じスプレッドシートにしています。

Answer 1

同じシートに　値を上書きしているということですので...

ヘルプのファイルの変更内容を確認する 等をみると Google スプレッドシートは過去の変更履歴を保存しているようですので、考えられる原因としては「変更履歴の保存に時間がかかっている」可能性がありそうです。

新規ファイルに対して試してみて時間短縮するのであれば、これが原因の可能性が高いと思われます。

対策ですが、変更履歴の削除や履歴を保存しない設定は確認した範囲ではできないようですので、毎回新規ファイルを作成するように変更するしか無いかもしれません。

Comments

[CodeQuality]

ご回答ありがとうございます。今回は新規ファイルにしても速度はそのままだったのですが、このような原因が考えられることは思いつきませんでした。どうもありがとうございました。

Answer 2

考えられる原因

・Google側で、計算リソースのバランス調整により、処理速度を落とされた可能性
・既存シートへの追記／更新が繰り返されたことにより、データの読み書きに、なんらかのボトルネックが発生している可能性

　

解決案

1．
・同じデータを別のファイルで新規作成し直すと、速度が改善する可能性はあります。(Issue Tracker参照)

2．batchUpdate を利用する。

2018年のベンチマークによると、10列・3万行を書き込むテストで、batchUpdate による書き込みは、setValues よりも４～５倍速いようです。

下記は、コピー元シートのA1列を左上とする10000行、100列分のデータを、同じスプレッドシートの別のシートのA1列を左上とする範囲にペーストする例です。

function myFunction2() {
  var row1 = 1;  // コピー元開始行
  const col1 = 1;  // コピー元開始列
  const row2 = 100000;  // コピーする行数
  const col2 = 100;  // コピーする列数
  const sheetSource = 'コピー元シート名';
  const sheetDestination = 'コピー先シート名';
  console.log({numrows:row2, numcols:col2} );
  const rng = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetSource).getRange(row1,col1,row2,col2);
  const valuesToSet = rng.getValues();
  // コピー元と同じ位置にペーストすると仮定。変更したい場合はrow1,col1を変更する。
  const rng2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetDestination).getRange(row1,col1,row2,col2);

  const request = {
    'valueInputOption': 'USER_ENTERED',
    'data': [
      {
        'range': `'${sheetDestination}'!${rng2.getA1Notation()}`,
        'majorDimension': 'ROWS',
        'values': valuesToSet
      }
    ]
  };
  Sheets.Spreadsheets.Values.batchUpdate(request, SpreadsheetApp.getActiveSpreadsheet().getId());
  console.log('コピー完了');
}

参考： https://stackoverflow.com/questions/64977032/google-apps-script-setvalues-issue-timing-out-intermittently

3．速度の改善はあきらめて、制限時間内におさまるよう、処理を分割する。
→これについては「GAS 時間制限 分割」等で検索するとやり方は出てきます。

Comments

[CodeQuality]

何度も様々な考察をいただきどうもありがとうございます。最終的には batchUpdate を使用することで、20秒で処理が終わりました。さらによい方法がみつかりありがとうございました。

[退会済みユーザー]

解決してよかったです。

Answer 3

実は、同一スプレッドシートにシートを追加していました。
スプレッドシート内のシート数を元に戻したら実行速度が元に戻りました。
さらに良い解決策をご教授いただきありがとうございました。