【GASで自動化】古いデータを別シートに退避する

シートにあるN日より前の行を、別のシートに退避するGoogle Apps Scriptです。データベース代わりに使っているシートが重くならないよう予防的にデータ退避をしたいときにどうぞ。

こんな悩みを解決します

• スプレッドシートにログを出力しているが、行が増えてくるとスクリプトの動作遅延につながって困っている
• 古いデータが邪魔だが消すわけにはいかないので、別のシートに退避したい

用意するもの

特にありません。今お使いのスプレッドシートに組み込めます。

このスクリプトで出来ること

• 日時型の列があれば、指定日数より前の行を別のシートに退避することができます。
• 手動でもトリガーでも実行可能です。

使い方

1.事前準備

スクリプトの「事前設定エリア」を編集します。下記項目をご自身の環境にあわせて書き換えてください。

• スプレッドシートのURL
• ログが書き込まれているシート名
• どの列に日時が入っているか
• ログは何行目から入っているか
• 何日分のログを元のシートに残したいか（1と入れた場合は本日より2日以上前のログが退避されます）

書き換えて保存したら、関数「onOpen」を一度実行して権限の承認をしてください。

2.動作確認

A列に日時を入れたサンプルファイルを用意しました。
1日分のログを残す設定で、この記事を書いているのは4/29なので、4/28以降のログは「ログ」シートに残り、古いものは退避先のシートに移行されるはずです。

実際はトリガーで実行しますが、確認のため手動で実行してみます。
上のメニューにある「スクリプト実行」から「N日以上前の行を退避する」を実行します。

日付のシートが生成され、古いデータが退避されました。

元のシートを見てみると、4/28が最も古いログになっています。問題なさそうですね。

3.トリガーの設定

トリガーを登録して、自動的に退避するようにしましょう。
データの増える速度によって変わると思いますが、毎晩退避させたい場合はこのような設定になります。

• 実行する関数: logrotate_old_records
• 実行するデプロイ: HEAD
• イベントのソース: 時間主導型
• 時間ベースのトリガーのタイプ: 日付ベースのタイマー
• 時刻をを選択: 午前0時〜1時

4.注意点

• このスクリプトは日時が昇順（下のほうが日付が新しい）で並んでいることが前提です。内部では日付（YYYYMMDD）に変換してから判定しているので日をまたがない程度に前後するのは問題ないはずです。
• 退避先のシート名が日付（YYYYMMDD）のため、1日に2回以上動作させた場合はシート名が重複するのでエラーになってしまいます。日付に加えて時間も追加するなどの対応をお願いします。

スクリプト

/**
 * このスクリプトの説明、使い方はこちら。
 * https://myfunc.jp/items/00076/index.html
 */

function onOpen() {
  // スプレッドシートを開いたときに実行される関数

  // UIの取得
  const ui = SpreadsheetApp.getUi()

  // メニューの表示名
  const menu = ui.createMenu('スクリプト実行');

  // メニューに追加するボタン
  menu.addItem('N日以上前の行を退避する', 'logrotate_old_records');

  // メニューを画面に追加する
  menu.addToUi();
}


function logrotate_old_records() {

  //////////////// 事前設定エリア ここから /////////////////

  // 対象のスプレッドシートを指定
  const ss_url = 'https://docs.google.com/spreadsheets/d/***************';

  // ログが書き込まれているシート名を指定
  const sheet_name = 'ログ';

  // 日時がどの列に入っているかを指定（A列なら1）
  const date_column = 1;

  // ログが何行目から書き込まれているかを指定（2行目から書き込まれていれば2）
  const offset = 2;

  // 何日分のログをメインのシートに残したいかを指定
  const need_days = 1;

  //////////////// 事前設定エリア ここまで /////////////////

  // スプレッドシートを開く。トリガーで動作させたいのでID or URLで開く
  const ss = SpreadsheetApp.openByUrl(ss_url);

  // ログが書き込まれているシートを開く
  const sheet = ss.getSheetByName(sheet_name);

  // ログをすべて取得する
  const values = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues();

  // need_daysより前のログは退避対象となるのでその日付を設定する
  let date_ago = new Date();
  date_ago.setDate(date_ago.getDate() - need_days);
  date_ago = Utilities.formatDate(date_ago, 'JST', 'yyyyMMdd'); //比較に使用するためYYYYMMDD形式にする

  // 最終的に何行消す（退避）のかを格納する変数
  let delete_rows_counter = 0;

  // 退避対象の行を別シートに書き込むための変数
  let rotate_rows = [];

  // ログを1行ずつチェックする
  for (let i = offset - 1; i < values.length; i++) { //オフセットで指定された行（0スタートなので-1）は飛ばす

    // 比較するためにyyyyMMdd形式に揃える
    let date_log = Utilities.formatDate(new Date(values[i][date_column - 1]), 'JST', 'yyyyMMdd'); //ログの日付

    // N日前より以前のログであれば、カウンタを加算し退避対象の行を配列に追加する
    if (date_log < date_ago) {
      delete_rows_counter++;
      rotate_rows.push(values[i]);
    }

  }

  // カウンタが1以上であれば退避と元シートから行の削除を実行する
  if (delete_rows_counter > 0) {

    // 退避先のシート名は日付にするので時間を取得する
    const sheet_name = Utilities.formatDate(new Date(), 'JST', 'yyyyMMdd');

    // シート名を変更する
    const new_sheet = ss.insertSheet().setName(sheet_name);

    // 退避先シートの書き込み範囲を定義する
    const range = new_sheet.getRange(1, 1, rotate_rows.length, rotate_rows[0].length);

    // 退避先シートに書き込む
    range.setValues(rotate_rows);

    // 元のログシートから退避した分を削除する
    sheet.deleteRows(offset, delete_rows_counter);
  }

}

関連するアプリ