どうもY-nek0(@Ynek0)です。
今回は毎月新しいスプレッドシートにデータを書き込む方法っていうのをご紹介出来ればと思います。
ん？どういうこと？と思われるかも知れません。
例えば、Googleサービスのフォームを使って特定のスプレッドシートに追記し続けるようなスプレッドシートがありますが、これを続けていくと日に日に莫大な量のデータが蓄積されていきます。
データ量が多くなるとスプレッドシートの挙動が重くなったり、履歴を表示させにくくなったりといくつかの弊害が起こってきます。
なので一定期間区切りを決めて新しくスプレッドシートを作成し、そこにデータを移動させたほうがデータの操作もしやすいですし管理もしやすいです。
そう言うわけで今回は毎月新しいスプレッドシートに書き込みをするワザをご紹介出来ればと思います。
これ以外にも方法っていくつかあると思うのですが、自分のたどり着いた考えはこの様になっていて参考にしてもらえたらと思います。
スプレッドシートID記録用のスプレッドシートを作成する
まず事前準備としてスプレッドシートのID記録用のスプレッドシートを作成します。
このスプレッドシートにはID情報を保持するデータベースという役割を担ってもらいます。
そのスプレッドシートの特定のセルに最初に記録するスプレッドシートのIDを記録しておく。
最初に記録しておくスプレッドシートは普通に作成してID登録してもらえれば大丈夫です。
次月のスプレッドシートを自動作成するGASを作る
流石に毎月新しいスプレッドシートを作るのは面倒なのでGASで記載をしていきます。
自動的にスプレッドシートを作るGASの記事は以下をご参照頂けると幸いです。
ただ今回はそのGASを作る際に前項で作成したデータを保管するスプレッドシートに
データを移すスプレッドシートのIDを書き変える必要があります。
その為に書き加えたGASが以下の通りになります。
//データ集計シート作成
const id = SpreadsheetApp.create("データ累積用スプレッドシート").getId();
//新しく作ったスプレッドシートののIDを記録用スプレッドシートに登録
var spreadsheet = SpreadsheetApp.openById("記録用スプレッドシートのID");
var sheet = spreadsheet.getSheetByName("シート1");
sheet.getRange(1,2).setValue(id);
新しく作成したスプレッドシートのIDをID記録用のスプレッドシートのA2セルに書き換えを行う処理を行っています。
さらにこのGASをトリガーで月に1回実行する設定を行います。
トリガーの設定方法につきましてはこちらの記事をご覧下さい。
これでスプレッドシートを作成して、同時に記録しているIDを書き換えるGASが完成しました。
データを移し替えるGASを作る
次に、記録用のスプレッドシートに記載されているIDを参照して書き込みを行うGASを作ります。
この記事の応用編ですね。
これは以下の様なプログラムで可能です。
function Listcopy(){
//対象先スプレッドシートID取得
var ss_copyTo = SpreadsheetApp.openById(accesId(1));
//シート指定
var sheet_copyTo = ss_copyTo.getSheetByName('シート1');
//対象元スプレッドシート
var ss_copyFrom =SpreadsheetApp.openById(accesId(i));
var sheet_copyFrom = ss_copyFrom.getSheetByName('シート1');
//対象元スプレッドシートの最終行、最終列を取得
var lastRow = sheet_copyFrom.getLastRow();
//最終行を取得
var lastColumn = sheet_copyFrom.getLastColumn();
//最終列を取得 //対象元スプレッドシートの最終行、列をコピー
var copyValue = sheet_copyFrom.getRange(2,1,lastRow,lastColumn).getValues();
//対象先のシートの既に値の入っている行数を確認し、最終行の次の行を指定する。
var targetRow = getLastRowCount(sheet_copyTo, 'A') + 1;
//自分のシートにコピーした値を最終行の次にペースト
sheet_copyTo.getRange(targetRow,1,lastRow,lastColumn).setValues(copyValue);
//対象元スプレッドの値削除
sheet_copyFrom.getRange(2,1,lastRow,lastColumn).clearContent();
}
//最終行カウント
function getLastRowCount(sheet, rowName) {
var values = sheet.getRange(rowName + ':' + rowName).getValues();
return values.filter(String).length;
}
//accessNoを指定してBook_IDを取得する。
function accesId(accessNo){
var spreadsheet = SpreadsheetApp.openById("記録用スプレッドシートのID");
var sheet = spreadsheet.getSheetByName('シート1');
var range = sheet.getRange("B"+accessNo);
//セルA1を取得
return range.getValues();
}
このGASを適度な時間トリガーで定期実行すれば元のスプレッドシートからデータ蓄積用のスプレッドシートに定期的にデータが移行されていくので今後は脳死で毎日スプレッドシートのデータをコピペする作業から抜け出せて楽になれますね！
ぜひ何かの効率化に役立てて頂ければ幸いです。
では次回もまたご贔屓に。
