【GAS】スプレッドシートバックアップを自動で決まった時間に行う方法

2023年7月26日2024年4月25日

Googleスプレッドシートは便利なのですが、複数の方で編集作業を実施すると、予期せぬデータ削除に見舞われて絶望することがときどきあります。
定期的に自動でスプレッドシートの内容をバックアップ出来たらなーとおもい、GAS（Google Apps Script）を使って実現してみました。

同じように悩んでいる方がいましたら、ぜひ参考にしてみてくださいませー。

記事を書いた人

こんにちは。当ブログの管理人の『くろん』です。
３０代サラリーマン、新規事業推進室に所属。ブラック企業努め時代に身に着けた業務効率化ノウハウをアウトプットしていきます。
読んでくれた方の業務が一秒でも早く終わりますようにの精神で記事書いてます！！

自動スプレッドシートバックアップGASの仕様

ファイルをまるごとバックアップする
バックアップファイルは、自分のGoogle ドライブの専用フォルダに保存
１日に１回、２２時台にバックアップされる
直近７回分まで保持、それ以前のバックアップファイルは削除

このようなバックアップができるプログラムを作ってみます。

あ、もちろんですが、保存先でしたり、バックアップ時間、直近何回分まで保持するかは、プログラムをちょちょいといじれば実現可能ですので、運用しているスプレッドシートの仕様に合わせてカスタマイズしてください！

早速、スプレッドシートバックアップGASの共有（一部修正の必要あり）

実際に試す前に、バックアップしたいスプレッドシートについて、上部メニュー『ファイル』→『ダウンロード』でExcelファイルとしてダウンロードをするなどして、なにかミスがあったとしても、復元できるようにしておいてください。GASで、スプレッドシートをいじるスクリプトを最初に試すときはは絶対必要な工程です。

function backupSpreadsheet() {
  // コンテナバインドされてるスプレッドシートを取得
  let spreadsheet1 = SpreadsheetApp.getActiveSpreadsheet();
  // バックアップフォルダIDを指定、IDに紐づくフォルダを取得
  let folderId = 'YOUR_FOLDER_ID';
  let folder = DriveApp.getFolderById(folderId);
  
  // ファイル名に日付と時刻をつけてコピー
  let fileName = Utilities.formatDate(new Date(), "JST", "yyyyMMdd_HHmmss") + "_" + spreadsheet1.getName();
  let file = DriveApp.getFileById(spreadsheet1.getId()).makeCopy(fileName, folder);
  
  // 直近7回分までのバックアップを保持し、それ以前のものは削除
  let files = folder.getFiles();
  let backupFiles = [];
  while(files.hasNext()){
    let f = files.next();
    backupFiles.push(f);
  }
  backupFiles.sort(function(a, b){
    return a.getDateCreated() < b.getDateCreated() ? 1 : -1;
  });
  for(let i = 7; i < backupFiles.length; i++){
    backupFiles[i].setTrashed(true);
  }
}

コンテナバインド型GASの立ち上げ

スプレッドシートに対してコンテナバインドしたGASで実行する場合、下記記事を参考にGASを立ち上げてください。

（余談）コンテナバインド型GASじゃなく実行したいときは

コンテナバインドしていないGASで実行する場合、

  // コンテナバインドされてるスプレッドシートを取得
  let spreadsheet1 = SpreadsheetApp.getActiveSpreadsheet();

この部分を、スプレッドシートのID指定で取得してあげればOKです。
こんな感じです。

  // コンテナバインドされてるスプレッドシートを取得
  let spreadsheet1 = SpreadsheetApp.openById('シートID');

シートIDは、スプレッドシートのURLの以下の文字列になります！

https://docs.google.com/spreadsheets/d/('シートID')/edit

フォルダーの指定（ここを、ご自身の状況に合わせて修正する必要があります）

上記のGASは少し修正する部分があります。
『let folderId = ‘YOUR_FOLDER_ID’;』の部分なのですが、ここに『フォルダーID』を入力する必要があります。

『フォルダーID』の取得方法について、順を追ってバックアップ先のフォルダーを専用に立ち上げるところから説明していきます。

スプレッドシートのホーム画面右側に、横棒３つのメニューボタンがあるかと思います。
そちらをクリックして、一番下に『ドライブ』とあるので、これをクリックしてください。
もしくは、GoogleドライブのホームページURL（https://drive.google.com/drive/u/0/my-drive）こちらをクリックでもOKです。

次に、Googleドライブのホームページ左側に『＋新規』ボタンがあるので、こちらをクリック。
表示されたメニューの一番上『新しいフォルダ』をクリック。
すると、フォルダ名を指定するようにポップアップがでてきますので、わかりやすいフォルダ名をつけて、作成をクリックしてください。
すると、Googleドライブのホーム画面に戻され、作成したフォルダが一覧に表示されているはずです。
それをダブルクリックしてください。

すると、フォルダ個別のページに遷移するかと思います。
そのときのURL『https://drive.google.com/drive/u/0/folders/*****************************?AAAAAAA』(?以降のパラメータは付くときと付かないときがあります）の、アスタリスク部分が『フォルダーID』になります。

トリガーの設置

このGASを手動実行すれば、バックアップファイルを作成できるのですが、いちいち手動実行は手間です。
GASにはトリガー機能があり、今回は時間主導型のトリガーを用いて、毎日22時台にこのGASが起動するように設定していきます。

GAS（Google Apps Script）のエディタ画面の左端のメニューに時計のボタンがあるかと思います。
こちらをクリックしてください。『トリガー』の設定画面になります。

現在設定されている『トリガー』一覧が表示されます。画面右下に、青ボタンで『＋トリガーを追加』とあるので、これをクリックしてください。

するとこのような設定画面が表示されるので。
実行する関数が、今回作成したGASのものになっているかを確認し、下記の通り設定してください。

設定項目	設定内容
イベントのソースを選択	時間主導型
時間ベースのトリガーのタイプを選択	日付ベースのタイマー
時刻を選択	午後１０時～１１時

エラーの通知はお好みで大丈夫です。
作った最初は、エラーが発生しやすいので、頻度短めに教えてくれるのがいいかと思います。

時間主導型のトリガーですが、１時間ごとの設定になります。つまり午後１０時きっかりに動かしたいということは、できない仕様となってますので、念頭においといてください。

スプレッドシートバックアップGASの実際の動きについて確認

まずは、手動で７回実行して、バックアップファイルを７つ作成してみました。

すべて、バックアップ元のファイルと内容が同じであることを確認してます。

これが、トリガーによって動かされると。。。

一番下に新しいファイルが追加され、一番上の古いファイルが削除されました！

このように、トリガーが実行されているのも、トリガーのページから確認することができます。

スプレッドシートバックアッププログラムの解説

function backupSpreadsheet() {
  // コンテナバインドされてるスプレッドシートを取得
  let spreadsheet1 = SpreadsheetApp.getActiveSpreadsheet();
  // バックアップフォルダIDを指定、IDに紐づくフォルダを取得
  let folderId = 'YOUR_FOLDER_ID';
  let folder = DriveApp.getFolderById(folderId);
  
  // ファイル名に日付と時刻をつけてコピー
  let fileName = Utilities.formatDate(new Date(), "JST", "yyyyMMdd_HHmmss") + "_" + spreadsheet1.getName();
  let file = DriveApp.getFileById(spreadsheet1.getId()).makeCopy(fileName, folder);
  
  // 直近7回分までのバックアップを保持し、それ以前のものは削除
  let files = folder.getFiles();
  let backupFiles = [];
  while(files.hasNext()){
    let f = files.next();
    backupFiles.push(f);
  }
  backupFiles.sort(function(a, b){
    return a.getDateCreated() < b.getDateCreated() ? 1 : -1;
  });
  for(let i = 7; i < backupFiles.length; i++){
    backupFiles[i].setTrashed(true);
  }
}

さて改めて、このプラグラムの内容について解説していきたいと思います。

第１セクション（使用するファイル、フォルダの指定）

  // コンテナバインドされてるスプレッドシートを取得
  let spreadsheet1 = SpreadsheetApp.getActiveSpreadsheet();
  // バックアップフォルダIDを指定、IDに紐づくフォルダを取得
  let folderId = 'YOUR_FOLDER_ID';
  let folder = DriveApp.getFolderById(folderId);

序盤では、今回使用するスプレッドシート、及び保存先のフォルダーの変数への格納を行ってます。
getActiveSpreadsheetメソッドは、コンテナバインドされているGoogleスプレッドシートでしか反応しないので、このGASはコンテナバインド型であることが要求されます。
保存先フォルダーの指定は、『フォルダーID』を用いて指定しています。その際のメソッドは、getFolderByIdです。

第２セクション（ファイルをコピーする）

 // ファイル名に日付と時刻をつけてコピー
  let fileName = Utilities.formatDate(new Date(), "JST", "yyyyMMdd_HHmmss") + "_" + spreadsheet1.getName();
  let file = DriveApp.getFileById(spreadsheet1.getId()).makeCopy(fileName, folder);

中盤では、バックアップの肝心要のファイルをコピーする部分です。
まず、バックアップファイルの名前を名付けます。バックアップされる側のファイル名の前に、”yyyyMMdd_HHmmss”形式の時間文字列を付加したものを今回はバックアップ時のファイル名とします。

少し余談になりますが、”yyyyMMdd”形式ですと、手動でバックアップするなどして、１日２回以上バックアップする際に、同名のファイル名が作られてしまうことになりますので、時間部分も付け加えました。
ちなみに、Google ドライブでは、同名のファイルは許容され、同時に存在することができるので、余計にどっちが後のファイルなのか分からなくなり混乱の元となってしまうことが予想されます。

第３セクション（バックアップファイルが７つよりも多いとき、古いのを削除する）

  // 直近7回分までのバックアップを保持し、それ以前のものは削除
  let files = folder.getFiles();
  let backupFiles = [];
  while(files.hasNext()){
    let f = files.next();
    backupFiles.push(f);
  }
  backupFiles.sort(function(a, b){
    return a.getDateCreated() < b.getDateCreated() ? 1 : -1;
  });
  for(let i = 7; i < backupFiles.length; i++){
    backupFiles[i].setTrashed(true);
  }

バックアップファイルが溜まっていくと、Googleドライブの容量が圧迫してしまうので、バックアップするファイル数に上限はもたせたいところ。
今回は、直近７つ（１週間分）まで保持し、それ以上の古いファイルは削除するというプログラムにしてあります。

getFilesメソッドで指定したフォルダ内のすべてのファイル（これがバックアップファイル）を取得します。このFileIteratorを返します。イテレーターとは、配列のようなもので、オブジェクトを順序よく並べてくれるやつです。でも配列ではありません。（ひじょーに分かりづらいので、そういうものなんだで飛ばしちゃってください）

次に、このイテレーターを使用してwhile(files.hasNext())ループを作り、各バックアップファイルを一つずつ取得してbackupFiles配列に追加していきます。
files.hasNext()は、イテレーターに抽出できるオブジェクトが残っていればtrueを、残っていなければfalseを返しますので、抽出できるオブジェクトがあるかぎり動き続けろという指示になります。

こうして、全てのファイルがbackupFilesに格納されたわけですが、イテレーターからそのまま配列にした状態ですと、中身がぐちゃぐちゃの順番になっています。
なので、ファイルの作成日時に基づいて降順（新しいものから古いものへ）にソートします。これはbackupFiles.sort()で行われています。

最後に、ソート済みのbackupFiles配列をループで処理し、配列の7番目以降の各ファイルを削除（ゴミ箱に移動）します。
これはfor(let i = 7; i < backupFiles.length; i++)ループとbackupFiles[i].setTrashed(true)で行われています。
配列のインデックスは0から始まるため、インデックス7は実際には8番目のファイルを指します。つまり、８番目のファイル以降に格納されているファイルは、ゴミ箱にポイポイしていきますねとなります。

以上の手順により、直近の７つのバックアップファイルだけが保持され、それより古いバックアップファイルは全て削除が実行されました。

ここの数字をいじることで、直近何個のバックアップファイルを保持するかをコントロールすることが出来ますので、もっと多く保持したいよーというニーズがある場合は、ここの数字を増やしておいてください。

スプレッドシートバックアップGASまとめ

スプレッドシートのバックアップはGASで実現できる

GASで、指定ファイルをコピー、保存先を指定、何個まで保持するかを設定
トリガー機能を用いて、何時に実行するかを設定

バックアップをする重要性

複数人でスプレッドシートをイジっていると、ある日突然操作ミスなどで、データが吹き飛んでしまうことがある。
重要なデータであればあるだけ、吹き飛んだときのダメージがほんとに大きい。
そうなる前に、バックアップを自動で行えるシステムを組み込むことは大事。

Googleスプレッドシートを自動でバックアップ作成するGASについて説明させていただきました！
重要なファイルで、複数人がいじっているファイルほど、ある日突然だれかが操作ミスなどして、データが吹き飛んだり、狂ってしまうことがあります。

狂ってしまったとき、マジで絶望しますので、絶対バックアップはとっておいたほうが良いです。重要なスプレッドシートには、ぜひ実装をお願いします！

よかったらシェアしてね！

URLをコピーしました！

URLをコピーしました！