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

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

ただいまの
回答率

90.10%

【GAS:スプレッドシート】実行時間オーバー(行削除)

解決済

回答 3

投稿

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

anakama

score 9

前提・実現したいこと

特定条件を満たす行を削除するにあたり、
その行数が合計で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/ツールのバージョンなど)

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

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

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

回答 3

checkベストアンサー

+1

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

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

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

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

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2019/02/18 23:45

    ご回答いただきましてありがとうございます。
    こちらで試したのですが、数万行でも早く処理することができました...!
    やはり、for文を使わないことが一番の要因でしょうか?

    おっしゃる通り、スプレッドシートで大量処理すべきではないと思いますが、
    Googleのデータポータルで簡易的なデータベースとしてスプレッドシートを使っている
    という経緯があってのことです。。

    キャンセル

  • 2019/02/19 09:20

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

    基本的にざっくりデータを拾って、加工はスクリプトで完結して、戻す、が早くなるやり方です。

    キャンセル

0

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

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

サンプルスクリプト

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var checkrows = sheet.getDataRange().getValues();
  var today = new Date();
  var before30Date = new Date(today.getYear(), today.getMonth(), today.getDate() - 30).getTime();
  var spreadsheetId = ss.getId();
  var sheetId = sheet.getSheetId();
  var reqs = checkrows.reduceRight(function(ar, e, i) {
    if (new Date(e[0]).getTime()  > before30Date) {
      ar.push({"deleteDimension":{"range":{
        "sheetId": sheetId,
        "dimension": "ROWS",
        "startIndex": checkrows.length - i - 1,
        "endIndex": checkrows.length - i,
      }}});
    }
    return ar;
  }, []);
  Sheets.Spreadsheets.batchUpdate({"requests": reqs}, spreadsheetId);
}

参考

実際に試していただいてそれでもまだ実行時間が6分を超えてしまう場合は、削除したい行を分けて複数回の実行で削除することも一つの回避策になるかと考えます。

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

0

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

function myFunction1() {

    var sheet = SpreadsheetApp.getActiveSheet();

    // A列(日付)を基準に昇順に並び替える
    sheet.sort(1);

    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--) {

        // 30日前までの行を一度で削除
        if (checkrows[i][0] < before30Date) {

            sheet.deleteRows(i + 2, checkrows.length - i - 1);
            break;
        }
    }
};

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

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

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