GASで効率化させる月次決算進捗管理〜月次シートの自動作成と締め日のSlack通知〜

さて、前回は月次決算の安定化・早期化のための管理ツールとしてのスプレッドシートの概要と設計趣旨を説明してきました。

ご確認されたい方は、以下の記事をご確認ください。

さて、これからは前回紹介したツールを楽に活用するためのスクリプト他の紹介です。

安定化・早期化を目指していくにあたり、管理するツール自体の作成に手間をかけていては本末転倒なので、そこはGASやSlackさんにお世話になりながら楽をしていきましょう。

管理シートを毎月自動でコピーしていく方法
1. スプレッドシート構成のおさらいとゴールイメージ
2. 実際のスクリプト
  1. WORKDAY関数とは？
  2. 関数をGASで一括展開するには？
追記2020/02/19：トリガーの設定
まとめ

管理シートを毎月自動でコピーしていく方法

さて、まずは管理用のシート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で一括して同一の計算式を展開できます。

今回のように式の中に変数をいれる場合シングルクウォート(‘ ‘)とダブルクウォート(” “)をうまく活用して、文字列化する部分、変数として生きさせる部分を切り分けてください。