Google スプレッドシートのログローテーション

2025.01.21

概要

Google スプレッドシートにESP32からデータ登録する
概要昔からやってみたかったのですが、未着手だったスプレッドシート連携を実験してみました。送信まで時間がかかるので省電力プロジェクトだと適していませんが、無料で気楽に利用できるのが良い点だと思います。スプレッドシートの新規作成まずは保存するス...
lang-ship.com
2025.01.10

前回ESP32から定期的にデータを送信してGoogle スプレッドシートに保存するようにしました。このまま送信を続けると上限に達してしまうので、適切にローテーションできるか確認してみました。

別名保存

// 同じフォルダに別名保存する
function backup() {
  // 対応しているスプレッドシートを取得
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  console.log("name:" + spreadsheet.getName());

  // スプレッドシートの保存されているフォルダを取得
  const ssId = spreadsheet.getId();
  const parentFolder = DriveApp.getFileById(ssId).getParents();
  const folder = parentFolder.next();

  // 親のパスを調べる
  let path = folder;
  var folderPath = folder.getName();
  while (1) {
    var searchPath = path.getParents();
    if (!searchPath.hasNext()) {
      break;
    }
    path = searchPath.next();
    folderPath = path.getName() + "/" + folderPath;
  }
  console.log("folderPath:" + folderPath);

  // 日付でスプレッドシートを別名保存
  const filename = spreadsheet.getName() + "_" + Utilities.formatDate(new Date(), SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(), 'yyyyMMdd_HHmmss');
  console.log("new name:" + filename);
  const newss = spreadsheet.copy(filename);

  // フォルダを移動
  const file = DriveApp.getFileById(newss.getId());
  file.moveTo(folder);
}

上記が対応するスプレッドシートを取得してきて、既存の名前に保存した日時をつけて保存する関数の例になります。

const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

上記でスクリプトに対応するスプレッドシートが取得できます。Web上のサンプルをみるとURLを指定している例が多いですが、スクリプトをコピペで流用できるように直値を書くのをやめて、対応するデータに対して処理する方が好ましいと思います。

  // スプレッドシートの保存されているフォルダを取得
  const ssId = spreadsheet.getId();
  const parentFolder = DriveApp.getFileById(ssId).getParents();
  const folder = parentFolder.next();

上記でスプレッドシートのIDを取得して、ドライブでフォルダを探しています。スプレッドシートのみだとデータはすべてルートディレクトリに保存されており、ディレクトリ構造を利用する場合にはドライブを利用する必要があります。

名前をつけて保存する場合にはルートディレクトリでファイルを作成するとファイルが増えたときにわかりにくくなるので、どこか専用フォルダを作成してその中に保存することをおすすめします。

また人に共有するときもファイル単体ではなく、ディレクトリ単位で保存するとその中身全部を共有できるのでおすすめです。

  // 親のパスを調べる
  let path = folder;
  var folderPath = folder.getName();
  while (1) {
    var searchPath = path.getParents();
    if (!searchPath.hasNext()) {
      break;
    }
    path = searchPath.next();
    folderPath = path.getName() + "/" + folderPath;
  }
  console.log("folderPath:" + folderPath);

上記は本来いらない処理になります。保存されているフォルダ名はさっき取得しましたが、どのパスに保存されているかを調べています。testみたいなフォルダを作って入れた場合に、同じフォルダ名がある場合にどこにあるのかわからなくなるのでフルパスを表示するために上記の処理をしています。

  // 日付でスプレッドシートを別名保存
  const filename = spreadsheet.getName() + "_" + Utilities.formatDate(new Date(), SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(), 'yyyyMMdd_HHmmss');
  console.log("new name:" + filename);
  const newss = spreadsheet.copy(filename);

上記で別名保存しています。秒数まであるファイル名にしていますが通常は日付ぐらいで問題がないはずです。ただテストでがんがん実行すると同じファイル名にならない秒数付きが便利です。日次でバックアップする場合には作成日よりは前日の日付の方がわかりやすいかもしれません。

  // フォルダを移動
  const file = DriveApp.getFileById(newss.getId());
  file.moveTo(folder);

ここが重要なのですが、新規作成した直後はルートディレクトリに保存されます。ドライブの関数を利用して元あったフォルダに移動しています。

トリガーを利用した定期実行

スクリプトのトリガーを選び、トリガーの追加をすることで設定が可能です。

上記のような画面で設定可能で、呼び出す関数名の他にトリガー条件を選択します。

イベントのソースイベントの種類
スプレットシートから起動時
スプレットシートから編集時
スプレットシートから変更時
スプレットシートからフォーム送信時
時間主導型特定の日時
時間主導型分ベースのタイマー
時間主導型時間ベースのタイマー
時間主導型日付ベースのタイマー
時間主導型週ベースのタイマー
時間主導型月ベースのタイマー
カレンダーからカレンダー更新済み

上記のトリガーがあり、スプレットシートからのトリガーなども便利なのですが、今回は時間主導型で定期実行をします。

とりあえず日付ベースで午前0時から1時に設定すると、毎日1回別名保存されています。気をつけないといけないのは実行される時間枠は1時間で、ぴったり0時0分に実行することはできません。

深夜に更新されることがないデータなどであればこの設定で問題ないと思います。なるべくぴったり変更したい場合には毎分で呼び出して0時0分のときに実行することも可能ですが、他に実行が無駄になるのであまりおすすめしません。

ただ今回は時系列データなので、内部のデータもぴったり分離したいのでトリガーを利用したログローテーションは利用しません。

データ登録時に日次ログローテーション

データ登録時に日付をチェックしてログローテーションをする例になります。

// デバッグ用関数
function debug() {
  const e = {
    parameter: {
      d2: "d2_data",
      d1: 'd1_data',
      d3: "d3_data"
    }
  }
  doGet(e);
}

// データ登録用関数
function doGet(e) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheets()[0];
  let params = [new Date()];

  // シート名確認
  checkSheet(sheet);

  // データ登録
  let parameter = Object.keys(e.parameter);
  parameter.sort();
  parameter.forEach((key) => params.push(e.parameter[key]));
  sheet.appendRow(params);
  console.log(e.parameter);
  console.log(params);

  return ContentService.createTextOutput('sccess');
}

// シート名を確認して違っていたらバックアップしてクリアする
function checkSheet(sheet) {
  let sheetName = sheet.getName();
  console.log("sheetName:" + sheetName);

  // 日次で切り替える場合にはyyyyMMddでチェックする
  const today = Utilities.formatDate(new Date(), SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(), "yyyyMMdd");
  const datePattern = /^\d{8}$/; // YYYYMMDDの形式チェック
  if (datePattern.test(sheetName) && sheetName !== today) {
    Logger.log(`シート名は本日ではありません。`);

    // 別名保存してバックアップ
    backup();

    // 既存データを消す
    sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).clearContent();
    Logger.log(`2行目以降のデータを削除しました。`);

    // シート名を本日に変更
    try {
      sheet.setName(today);
      Logger.log(`シート名を "${today}" に変更しました。`);
    } catch (e) {
      Logger.log("シート名の変更に失敗しました: " + e.message);
    }
  } else if (!datePattern.test(sheetName)) {
    // シート名が指定書式以外だった場合には変更する
    try {
      sheet.setName(today);
      Logger.log(`シート名を "${today}" に変更しました。`);
    } catch (e) {
      Logger.log("シート名の変更に失敗しました: " + e.message);
    }
  } else {
    Logger.log("シート名はすでに本日の日付です。");
  }
}

// 同じフォルダに別名保存する
function backup() {
  // 対応しているスプレッドシートを取得
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  console.log("name:" + spreadsheet.getName());

  // スプレッドシートの保存されているフォルダを取得
  const ssId = spreadsheet.getId();
  const parentFolder = DriveApp.getFileById(ssId).getParents();
  const folder = parentFolder.next();

  // 親のパスを調べる
  let path = folder;
  var folderPath = folder.getName();
  while (1) {
    var searchPath = path.getParents();
    if (!searchPath.hasNext()) {
      break;
    }
    path = searchPath.next();
    folderPath = path.getName() + "/" + folderPath;
  }
  console.log("folderPath:" + folderPath);

  // 日付でスプレッドシートを別名保存
  const filename = spreadsheet.getName() + "_" + Utilities.formatDate(new Date(), SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(), 'yyyyMMdd_HHmmss');
  console.log("new name:" + filename);
  const newss = spreadsheet.copy(filename);

  // フォルダを移動
  const file = DriveApp.getFileById(newss.getId());
  file.moveTo(folder);
}

debug()は前回とほぼ同じですがdoGet()関数は微妙に手を加えています。

  let params = [new Date()];

  // シート名確認
  checkSheet(sheet);

まず別名処理が10秒前後時間がかかるので、データを受け取った時間をはやめに取得するように修正しています。その後シート名確認するcheckSheet関数を呼び出しています。あとは同じ処理になります。

// シート名を確認して違っていたらバックアップしてクリアする
function checkSheet(sheet) {
  let sheetName = sheet.getName();
  console.log("sheetName:" + sheetName);

日次バックアップですが、シート名にデータを保存した日付を設定し、別の日になったら別名保存してバックアップする作りになっています。上記でとりあえずシート名を取得しています。

  // 日次で切り替える場合にはyyyyMMddでチェックする
  const today = Utilities.formatDate(new Date(), SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(), "yyyyMMdd");
  const datePattern = /^\d{8}$/; // YYYYMMDDの形式チェック

上記で本日の日付を取得して、シート名と比較する準備をしています。

  if (datePattern.test(sheetName) && sheetName !== today) {
    Logger.log(`シート名は本日ではありません。`);

    // 別名保存してバックアップ
    backup();

シート名が日付の形式にマッチして、本日でなかった場合には別の日付のデータなので、別名保存でバックアップしています。

    // 既存データを消す
    sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).clearContent();
    Logger.log(`2行目以降のデータを削除しました。`);

バックアップ完了後に既存のデータは不要なので削除します。この例だと1行目はデータ名が設定されて、2行目から実データがあるのを想定しています。

    // シート名を本日に変更
    try {
      sheet.setName(today);
      Logger.log(`シート名を "${today}" に変更しました。`);
    } catch (e) {
      Logger.log("シート名の変更に失敗しました: " + e.message);
    }

そしてシート名を本日に変更します。これでログローテーションが完了しています。

  } else if (!datePattern.test(sheetName)) {
    // シート名が指定書式以外だった場合には変更する
    try {
      sheet.setName(today);
      Logger.log(`シート名を "${today}" に変更しました。`);
    } catch (e) {
      Logger.log("シート名の変更に失敗しました: " + e.message);
    }

次の条件はシート名がパターンマッチしない場合です。新規作成直後などでシート1などになっている場合には本日の日付に変更しています。コピペで新規作成したときにやりがちなのでフォローしています。

  } else {
    Logger.log("シート名はすでに本日の日付です。");
  }

それ以外の場合には本日の日付なのでなにも処理をしません。

これだけの処理で日次ログローテーションが可能になります。データ登録時にログローテーションをしているので、登録されるデータが前後の日付に混ざってしまうこともありません。データを登録していないときにはログローテーションされないので営業日のみデータ登録される系のデータには日次ローテーションが便利だと思います。

データ登録時に月次ログローテーション

日次ローテーションでも十分便利なのですが、環境データなどを保存しているとファイル数が増えすぎてしまいます。そこで月次ローテーションを検討してみます。

// デバッグ用関数
function debug() {
  const e = {
    parameter: {
      d2: 1,
      d1: 10,
      d3: 14
    }
  }
  doGet(e);
}

// データ登録用関数
function doGet(e) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheets()[0];
  let params = [new Date()];

  // シート名確認
  checkSheet(sheet);

  // データ登録
  let parameter = Object.keys(e.parameter);
  parameter.sort();
  parameter.forEach((key) => params.push(e.parameter[key]));
  sheet.appendRow(params);
  console.log(e.parameter);
  console.log(params);

  return ContentService.createTextOutput('sccess');
}

// シート名を確認して違っていたら月が変わったらバックアップしてクリア、日付が違ったらシート追加
function checkSheet(sheet) {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  let sheetName = sheet.getName();
  console.log("sheetName:" + sheetName);

  // 日次で切り替える場合にはyyyyMMddでチェックする
  const today = Utilities.formatDate(new Date(), SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(), "yyyyMMdd");
  const month = Utilities.formatDate(new Date(), SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(), "yyyyMM");
  const datePattern = /^\d{8}$/; // YYYYMMDDの形式チェック
  if (datePattern.test(sheetName) && sheetName !== today) {
    Logger.log(`シート名は本日ではありません。`);

    if (month == sheetName.substring(0, 6)) {
      // 同じ月なのでシート追加

      // シート名を本日に変更
      try {
        sheet.setName(today);
        Logger.log(`シート名を "${today}" に変更しました。`);
      } catch (e) {
        Logger.log("シート名の変更に失敗しました: " + e.message);
      }

      // 現在のシートをコピーする
      const newSheet = sheet.copyTo(spreadsheet);
      newSheet.setName(sheetName);

      // 2番目に移動させる
      spreadsheet.setActiveSheet(newSheet);
      spreadsheet.moveActiveSheet(2);
      spreadsheet.setActiveSheet(sheet);

      // 既存データを消す
      sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).clearContent();
      Logger.log(`2行目以降のデータを削除しました。`);
    } else {
      // 別の月なので別名保存してバックアップ
      backup();

      // 不要なシートを削除する
      const sheets = spreadsheet.getSheets(); // 全てのシートを取得
      sheets.forEach(st => {
        if (st.getSheetId() !== sheet.getSheetId()) {
          Logger.log(`シート "${st.getName()}" を削除しました。`);
          spreadsheet.deleteSheet(st); // 現在のシート以外を削除
        }
      });

      // 既存データを消す
      sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).clearContent();
      Logger.log(`2行目以降のデータを削除しました。`);

      // シート名を本日に変更
      try {
        sheet.setName(today);
        Logger.log(`シート名を "${today}" に変更しました。`);
      } catch (e) {
        Logger.log("シート名の変更に失敗しました: " + e.message);
      }
    }
  } else if (!datePattern.test(sheetName)) {
    // シート名が指定書式以外だった場合には変更する
    try {
      sheet.setName(today);
      Logger.log(`シート名を "${today}" に変更しました。`);
    } catch (e) {
      Logger.log("シート名の変更に失敗しました: " + e.message);
    }
  } else {
    Logger.log("シート名はすでに本日の日付です。");
  }
}

// 同じフォルダに別名保存する
function backup() {
  // 対応しているスプレッドシートを取得
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  console.log("name:" + spreadsheet.getName());

  // スプレッドシートの保存されているフォルダを取得
  const ssId = spreadsheet.getId();
  const parentFolder = DriveApp.getFileById(ssId).getParents();
  const folder = parentFolder.next();

  // 親のパスを調べる
  let path = folder;
  var folderPath = folder.getName();
  while (1) {
    var searchPath = path.getParents();
    if (!searchPath.hasNext()) {
      break;
    }
    path = searchPath.next();
    folderPath = path.getName() + "/" + folderPath;
  }
  console.log("folderPath:" + folderPath);

  // 日付でスプレッドシートを別名保存
  const filename = spreadsheet.getName() + "_" + Utilities.formatDate(new Date(), SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(), 'yyyyMMdd_HHmmss');
  console.log("new name:" + filename);
  const newss = spreadsheet.copy(filename);

  // フォルダを移動
  const file = DriveApp.getFileById(newss.getId());
  file.moveTo(folder);
}

少し複雑になっていますが、基本は日次と同じような処理になります。月次では日付ごとにシートを分割して、月が変わった場合には別名保存する仕様としています。

debug()とdoGet()、backup()は日次と変更ありません。

// シート名を確認して違っていたら月が変わったらバックアップしてクリア、日付が違ったらシート追加
function checkSheet(sheet) {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  let sheetName = sheet.getName();
  console.log("sheetName:" + sheetName);

  // 日次で切り替える場合にはyyyyMMddでチェックする
  const today = Utilities.formatDate(new Date(), SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(), "yyyyMMdd");
  const month = Utilities.formatDate(new Date(), SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(), "yyyyMM");
  const datePattern = /^\d{8}$/; // YYYYMMDDの形式チェック

checkSheet()では日次のシート切り替えと、月次の別名保存があるので比較用文字を2つ作っています。

  if (datePattern.test(sheetName) && sheetName !== today) {
    Logger.log(`シート名は本日ではありません。`);

    if (month == sheetName.substring(0, 6)) {
      // 同じ月なのでシート追加

日次と同じくシート名が本日かを確認をして、違う場合の処理になります。最初に月次チェックを行っていて、同じ月の場合にはシート追加の処理になります。

      // シート名を本日に変更
      try {
        sheet.setName(today);
        Logger.log(`シート名を "${today}" に変更しました。`);
      } catch (e) {
        Logger.log("シート名の変更に失敗しました: " + e.message);
      }

まずシート名の重複ができないので、本日の日付に変更します。

      // 現在のシートをコピーする
      const newSheet = sheet.copyTo(spreadsheet);
      newSheet.setName(sheetName);

その後にシートをコピーします。このときコピーされたシートにはグラフの設定なども残ったまますべてコピーされます。また、コピーしたシートは一番右に追加され、名前も自動的に生成されるので、変更前のシート名につけ直します。

      // 2番目に移動させる
      spreadsheet.setActiveSheet(newSheet);
      spreadsheet.moveActiveSheet(2);
      spreadsheet.setActiveSheet(sheet);

シートの位置を本日の右に移動させたいので、追加されたシートを選択して、moveActiveSheet(2)で左から2番目に移動させます。その後、本日のシートをアクティブに戻します。

      // 既存データを消す
      sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).clearContent();
      Logger.log(`2行目以降のデータを削除しました。`);

本日のシートから過去分のデータを消して日次のシート追加は終了です。

    } else {
      // 別の月なので別名保存してバックアップ
      backup();

月が違う場合には別名で保存します。

      // 不要なシートを削除する
      const sheets = spreadsheet.getSheets(); // 全てのシートを取得
      sheets.forEach(st => {
        if (st.getSheetId() !== sheet.getSheetId()) {
          Logger.log(`シート "${st.getName()}" を削除しました。`);
          spreadsheet.deleteSheet(st); // 現在のシート以外を削除
        }
      });

別名保存が終わったので、アクティブなシート以外はすべて削除します。

      // 既存データを消す
      sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).clearContent();
      Logger.log(`2行目以降のデータを削除しました。`);

      // シート名を本日に変更
      try {
        sheet.setName(today);
        Logger.log(`シート名を "${today}" に変更しました。`);
      } catch (e) {
        Logger.log("シート名の変更に失敗しました: " + e.message);
      }

その後は既存データを消して、本日のシート名に変更することで月次ログローテーションは完了です。

ログローテーションのメリット

登録時にチェックしているので、ローテーションのタイミングなどでデータの日付が前後することがありません。デメリットとしてログローテーション時には10秒ぐらい登録が遅延する可能性があります。環境データなどで数分間隔で登録している場合にはあまり問題にならないと思います。

ただし、複数台から同時登録している場合などは排他処理していないのでこの方式でのログローテーションは利用できません。

ちなみにデータにグラフを設定していても、シートごとコピーされているので日次でのグラフが確認可能です。データが増えていくとグラフも自動更新されていくのはかなり便利ですね。

まとめ

かなり便利にログローテーションが可能になりました。スクリプトもIDなどの埋め込みがないのでコピペで利用しやすいと思います。Web上の参考例は微妙に使いにくいのが多かったので、なるべくコピペで使えるように組んであります。

コメント