さて、前回は月次決算の安定化・早期化のための管理ツールとしてのスプレッドシートの概要と設計趣旨を説明してきました。
ご確認されたい方は、以下の記事をご確認ください。
さて、これからは前回紹介したツールを楽に活用するためのスクリプト他の紹介です。
安定化・早期化を目指していくにあたり、管理するツール自体の作成に手間をかけていては本末転倒なので、そこはGASやSlackさんにお世話になりながら楽をしていきましょう。
管理シートを毎月自動でコピーしていく方法
さて、まずは管理用のシート2枚を毎月コピーしていきます。そして、シート内にはworkday関数で所定営業日を自動計算するようになっているので、その部分もスクリプトで置き換えていきましょう。
スプレッドシート構成のおさらいとゴールイメージ

こんな感じでMasterシートが2つ作成されています。これを、現在月(今だと2020/02)のシートを2枚作っていきます。
出来上がりはこんな感じです。

Master_Tasksシートは、”2月_Tasks”シートとして、Master_振り返りシートは”2月_振り返り”シートとしてコピーされています。
実際のスクリプト
function copyMasterToCurrentMonth() { //日付関係のデータ作成 var now = new Date(); var CurrentMonth = now.getMonth()+1; var year = now.getFullYear(); var date = 01; var startday = year + "/" + CurrentMonth + "/" + date; //シートを取得 var ss = SpreadsheetApp.getActiveSpreadsheet(); var task_sheet_original = ss.getSheetByName("Master_Tasks"); var review_sheet_original = ss.getSheetByName("Master_振り返り"); //originalのシートをコピー task_sheet_original.copyTo(ss); review_sheet_original.copyTo(ss); //copyされたシートを取得 var task_sheet_copied = ss.getSheetByName("Master_Tasks のコピー"); var review_sheet_copied = ss.getSheetByName("Master_振り返り のコピー"); //シートの名前を変更(X月シート内容) task_sheet_copied.setName(CurrentMonth + "月_Tasks"); review_sheet_copied.setName(CurrentMonth + "月振り返り"); //各タスクの完了予定日を入力(計算式で対応 workday関数) var lastRow = task_sheet_copied.getDataRange().getLastRow(); task_sheet_copied.getRange(2, 5, lastRow, 1).setFormula('=WORKDAY("' +startday+'",D2)') }
やってることは基本シンプルで以下の2つになっています。
- Masterシートをコピーして、対象月の名前をつける
- タスクシートは営業日を計算する部分でWORKDAY関数を各月の初日日付を入れて更新する
WORKDAY関数とは?
Excel時代から営業日(WD)ベースで話をされる方はよくお世話になったのではないでしょうか。WORKDAY関数について少し説明をしていきます。
WORKDAY関数:指定された就業日数をもとに終了日を計算します。
構文:=WORKDAY(開始日, 日数, [休日])
開始日:計算の起点となる日付を指定。月次決算の場合、月初日を開始日に指定することになります
日数:開始日
以降の就業日数を指定。負の値を指定すると、開始日から遡って計算される。
休日:オプショナル。休日とみなす日付を含む範囲または配列定数を指定する。
関数をGASで一括展開するには?
実際に上で説明したWORKDAY関数を対象列の対象行まで一括で展開しています。対象のスクリプトは以下の部分。
//各タスクの完了予定日を入力(計算式で対応 workday関数)
var lastRow = task_sheet_copied.getDataRange().getLastRow();
task_sheet_copied.getRange(2, 5, lastRow, 1).setFormula('=WORKDAY("' +startday+'",D2)')
シートの最終行を取得してきて、レンジオブジェクト(getRangeで取得)にsetFormulaで一括して同一の計算式を展開できます。
今回のように式の中に変数をいれる場合シングルクウォート(‘ ‘)とダブルクウォート(” “)をうまく活用して、文字列化する部分、変数として生きさせる部分を切り分けてください。
追記2020/02/19:トリガーの設定
スクリプトの説明だけでイベントトリガーの設定について説明するのを忘れていましたので、追記します。
このスクリプトに対するトリガーは、以下の設定になります。

時間主導型で設定していただき、”月ベースのタイマー” > “1日”で設定していただく必要があります。
1日に設定している理由は、スクリプト内のCurrentMonthが実行時の月を指定しますので、月ベーストリガー31日で設定すると前月のシート名のシートが出来上がってしまいます。
前月31日作り上げておきたいという方は、CurrentMonth変数を以下のように変更が必要です。
var CurrentMonth = now.getMonth()+2;
まとめ
さて、今回はシートのコピーと関数の展開方法を紹介しました。
管理をするにせよ、管理するための作業に時間を費やすことは本質ではありません。本質はいかに改善をして本来の目的である月次決算を安定化・早期化することです。管理するツールを作ることはあくまで補助ですよね。補助は自動化して楽にして本来人にしかできない部分に注力しましょう。
本来はSlackへの通知も説明予定でしたが、それは次回で。