【GAS：スプレッドシート】実行時間オーバー（行削除）

前提・実現したいこと

特定条件を満たす行を削除するにあたり、
その行数が合計で2,000以上あるのですが、以下のスクリプトだと実行時間を超えてしまいます。

【スプレッドシート内のカラム】
日付け,地域,売上

処理速度が上がるような記述があれば、ご教示頂けますと幸いです。
また、肌感覚で結構ですので、GASで行を削除するスクリプトを組む際に、最大で何行ほど実行時間内に削除可能かご教示頂けますと大変助かります。

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

実行時間のオーバー

該当のソースコード

function myFunction() {
  
  var sheet = SpreadsheetApp.getActiveSheet();
  
  var checkrows = sheet.getDataRange().getValues();
  
  var today = new Date();
  var before30Date = new Date(today.getYear(), today.getMonth(), today.getDate() - 30);
  
  

  for(var i = checkrows.length - 1; i >= 0 ; i--){
    
    if(checkrows[i][0]  > before30Date){
      
      sheet.deleteRow(i + 1);
    }
  }
}

試したこと

ここに問題に対して試したことを記載してください。

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

ここにより詳細な情報を記載してください。

行動規範の内容に同意します

回答3件

ベストアンサー

私だったらこうする、というのを。

javascript
1function myFunction() {
2  var sheet = SpreadsheetApp.getActiveSheet();
3  var checkrows = sheet.getDataRange().getValues();
4  sheet.getDataRange().clearContent(); //全消し
5  var today = new Date();
6  var before30Date = new Date(today.getYear(), today.getMonth(), today.getDate() - 30);
7  var filtered = checkrows.filter(function(e){ return e[0] <= before30Date;});
8  sheet.getRange(1,1,filtered.length, filtered[0].length).setValues(filtered);
9}

肌感覚で結構ですので、GASで行を削除するスクリプトを組む際に、最大で何行ほど実行時間内に削除可能か

が知りたかったら計測するしかないです。これでは検証作業の依頼です。
個人的には、↑のようなことをするのと、googleシートで大量処理は最初からしないので、行数制限にかかった経験がそもそもないです。

投稿2019/02/12 02:02

papinianus

総合スコア12705

anakama

2019/02/18 14:45

ご回答いただきましてありがとうございます。こちらで試したのですが、数万行でも早く処理することができました...！やはり、for文を使わないことが一番の要因でしょうか？おっしゃる通り、スプレッドシートで大量処理すべきではないと思いますが、 Googleのデータポータルで簡易的なデータベースとしてスプレッドシートを使っているという経緯があってのことです。。

papinianus

2019/02/19 00:20

早く処理について。問題はforで行削除していることです。間違ってますがイメージでいいます。行を削除する、という処理はシートでやりますよね。スクリプトはスクリプトエディタで動きます。 forで行を削除するっていうのは、計算するときはスクリプトエディタで、削除するときはブラウザのタブ移動して消す、戻って計算、また移動して消すの繰り返しです。私が書いたパターンでは、最初にシートで全コピーしてスクリプトエディタで計算して戻って全部貼り付けする、みたいな動きです。なんとなく回りくどい操作してるのが伝わればと思います。基本的にざっくりデータを拾って、加工はスクリプトで完結して、戻す、が早くなるやり方です。

行動規範の内容に同意します

シートのデータを並べ替えても良ければ、
行の削除が一度で済むので、処理時間が短縮されると思います。

JavaScript
1function myFunction1() {
2
3    var sheet = SpreadsheetApp.getActiveSheet();
4
5    // A列(日付)を基準に昇順に並び替える
6    sheet.sort(1);
7
8    var checkrows = sheet.getDataRange().getValues();
9
10    var today = new Date();
11    var before30Date = new Date(today.getYear(), today.getMonth(), today.getDate() - 30);
12
13    for (var i = checkrows.length - 1; i >= 0; i--) {
14
15        // 30日前までの行を一度で削除
16        if (checkrows[i][0] < before30Date) {
17
18            sheet.deleteRows(i + 2, checkrows.length - i - 1);
19            break;
20        }
21    }
22};

投稿2019/02/11 22:46

編集2019/02/11 23:27

teritama

総合スコア126

一つの解決策として、Sheets APIを使用するのは如何でしょうか。Sheets APIのbatchUpdateメソッドを使うと、一度のAPI使用で複数の行を一度に削除することが可能です。これにより、現状の実行時間を短縮できるのではないかと思われます。

このスクリプトを使用する際は、事前にスクリプトエディタからGoogleの拡張サービスとAPIコンソールでSheets APIを有効にしてください。

サンプルスクリプト

javascript
1function myFunction() {
2  var ss = SpreadsheetApp.getActiveSpreadsheet();
3  var sheet = ss.getActiveSheet();
4  var checkrows = sheet.getDataRange().getValues();
5  var today = new Date();
6  var before30Date = new Date(today.getYear(), today.getMonth(), today.getDate() - 30).getTime();
7  var spreadsheetId = ss.getId();
8  var sheetId = sheet.getSheetId();
9  var reqs = checkrows.reduceRight(function(ar, e, i) {
10    if (new Date(e[0]).getTime()  > before30Date) {
11      ar.push({"deleteDimension":{"range":{
12        "sheetId": sheetId,
13        "dimension": "ROWS",
14        "startIndex": checkrows.length - i - 1,
15        "endIndex": checkrows.length - i,
16      }}});
17    }
18    return ar;
19  }, []);
20  Sheets.Spreadsheets.batchUpdate({"requests": reqs}, spreadsheetId);
21}