以前に紹介した以下の記事に引き続き、GASを用いてスプレッドシートの業務管理を効率化する方法をご紹介します。
人手不足が続くIT業界では、「少数の正社員が多数の非正規社員を管理する「といった業務形態が一般的になりつつあります。 私が従事している実際のプロジェクトでも、正社員4名に対して非正規（派遣）社員8名という業務形態がおよそ1年間続いてい[…]
この記事では、2つのシート間の特定カラムに対して差分を取り、その小計をSlackに通知するというスクリプトを紹介します。
以下のような方にご覧いただき、業務内の課題解決の一助となれば幸いです。特に後者の方は、フォーマット（行列数など）が異なるシート間の差分をとる場合や、小計をとる場合の前処理が不要となるため、大幅に業務効率を上げることが可能になると思います。
- 複数人で行う長期間の作業について、一日、一人当たりのベロシティ（速度）を集計し、業務改善の参考にしたい方
- シート間の差分、シート内の小計をExcel操作で取っている方
対象とするスプレッドシートは以下のようなフォーマットを想定しています。7列目（done）の件数について、newシートとoldシートの差分を取り、staffごとの小計をSlackに通知するように実装しています。diffシートはスクリプト実行に作成されるものです。diffシートが存在する状態でスクリプトを実行した場合は、既存のdiffシートは削除されます。
1. 事前準備
1-1. スプレッドシートの作成
前述のスプレッドシートと同様のフォーマットのシートを作成してください。newシートのみでもstaffごとの小計をとることは可能です。
スクリプトの埋め込みは、「ツール」->「スクリプト エディタ」から行います。
1-2. Slackの設定
Slackへのメッセージ投稿を可能とするために、Slackの拡張アプリ「Incoming Webhook」の設定を事前に行う必要があります。設定方法は以下の記事の4章を参考にしてください。
人手不足が続くIT業界では、「少数の正社員が多数の非正規社員を管理する「といった業務形態が一般的になりつつあります。 私が従事している実際のプロジェクトでも、正社員4名に対して非正規（派遣）社員8名という業務形態がおよそ1年間続いてい[…]
2. アプリケーション概要
2-1. 開発環境
- [開発環境]: Google Chrome（バージョン: 80.0.3987.122（Official Build） （64 ビット））
- [開発言語]: Google Apps Script (GAS)
2-2. Main.gs
スクリプトのメイン処理を実装しています。
onOpenはスプレッドシートが開かれた時に実行されるメソッドで、このメソッドによって「集計/開始」をメニューが追加されます。「集計/開始」を選択した時にの実行されるメソッドは、functionNameで指定したaggregateで、このメソッドの処理フローは以下通りです。
- 最新のシート名（前述のnew）をユーザに指定させるためのダイアログボックスを表示する
- 最新のシートの情報を取得する（※）
- 最新のシートをコピーし、差分シート（前述のdiff）を作成する
- 差分シートの情報を取得する（※）
- 基準とするシート名（前述のold）をユーザに指定させるためのダイアログボックスを表示する
- 基準とするシートの情報を取得する（※）
- 差分シートと基準とするシートの差分を取り、差分シートの内容を更新する
（※）詳細はsheetInfoクラスのフィールドとメソッドを参照
2-3. SheetInfo.gs
シート・セルの操作、およびセル情報の取得、集計用のメソッドを実装しています。
| メソッド名 | setColumnList |
| メソッド名（和名） | カラム名一覧の取得 |
| 処理概要 | 1. header_numsで指定したヘッダ行のカラム名を一覧化する |
| 2. サブ関数（get_column_list）でスプレッドシートのカラム名を取得 | |
| 入力（クラス内フィールド） | 1. header_nums: ヘッダ行の開始/終了の行列数 ※終了列数はシートの最大列数 |
| 出力（クラス内フィールド） | 1. cols: カラム名一覧 |
| 特記事項 | 1. ヘッダ行の開始列数が2以上の場合、開始列数以前のカラム名は全て空白””とする |
| 2. ヘッダ行が2行以上におよぶ場合、カラム名はスラッシュ”/”で連結されたものとする |
| メソッド名 | isInRequiredCols |
| メソッド名（和名） | 必須カラム名が含まれているか判定 |
| 処理概要 | 1. カラム名一覧に必須カラム名が全て含まれていることを判定する |
| 入力（引数） | 1. cols: 必須カラム名一覧 |
| 入力（クラス内フィールド） | 1. required_cols: 必須カラム名一覧 ※引数未指定の場合のデフォルト値） |
| 2. cols: カラム名一覧 | |
| 出力（復帰値） | 1. 判定結果（true or false) |
| 特記事項 | なし |
| メソッド名 | updateInfo |
| メソッド名（和名） | シート情報の更新 |
| 処理概要 | 1. 最新のシート情報をもとにクラス内フィールドを更新する |
| 入力（引数） | 1. ss_obj: スプレッドシートのインスタンス |
| 出力（クラス内フィールド） | 1. sheet_obj: シートのインスタンス |
| 2. vals: セルの設定値一覧 | |
| 3. row_nums: シートの最大行数 | |
| 4. col_nums: シートの最大列数 | |
| 5. header_nums: ヘッダの開始/終了の行列数 ※終了列数はシートの最大列数 |
|
| 6. body_nums: ボディの開始/終了の行列数 ※開始行数はヘッダの開始行数+1、終了行数はシートの最大行数、開始/終了行数はヘッダの開始/終了行数と同じ |
|
| 特記事項 | なし |
| メソッド名 | copyTo |
| メソッド名（和名） | シート情報の更新 |
| 処理概要 | 1. API(copyTo)を使用してシートをコピーする |
| 2. API(setName)を使用して、コピーしたシートをリネームする | |
| 入力（引数） | 1. sheet_name: コピー後のシート名 |
| 2. ss_obj: スプレッドシートのインスタンス | |
| 出力 | なし（スプレッドシート上にシートがコピーされる） |
| 特記事項 | なし |
| メソッド名 | deleteSheet |
| メソッド名（和名） | シートの削除 |
| 処理概要 | 1. API(deleteSheet)を使用してシートを削除する |
| 入力（引数） | 1. ss_obj: スプレッドシートのインスタンス |
| 出力 | なし（スプレッドシート上のシートが削除される） |
| 特記事項 | なし |
| メソッド名 | calcSubTotal |
| メソッド名（和名） | 小計をとる |
| 処理概要 | 1. subtotal_key, val_colで指定したカラム名に対応する列数を取得する |
| 2. 全ての行について、処理1で取得した列数に該当するデータの小計をとる | |
| 入力（クラス内フィールド） | 1. body_nums: ボディの開始/終了の行列数 ※開始行数はヘッダの開始行数+1、終了行数はシートの最大行数、開始/終了行数はヘッダの開始/終了行数と同じ |
| 2. cols: カラム名一覧 | |
| 3. subtotal_key: 小計の基準（キー）とするカラム名 | |
| 4. val_col: 小計対象の値が記録されているカラム名 | |
| 5. vals: セルの設定値一覧 | |
| 出力（クラス内フィールド） | 1. subtotal_map: 小計のマッピングデータ |
| 特記事項 | なし |
| メソッド名 | setValsMap |
| メソッド名（和名） | 特定カラム間のマッピングデータの作成 |
| 処理概要 | 1. key_cols, val_colで指定したカラム名に対応する列数を取得する |
| 2. 全ての行について、処理1で取得した列数に該当するデータ間のマッピングデータを作成する | |
| 入力（クラス内フィールド） | 1. body_nums: ボディの開始/終了の行列数 ※開始行数はヘッダの開始行数+1、終了行数はシートの最大行数、開始/終了行数はヘッダの開始/終了行数と同じ |
| 2. cols: カラム名一覧 | |
| 3. key_cols: マッピングデータのキーとするカラム名一覧 | |
| 4. val_col: マッピングデータの値とするカラム名 | |
| 5. vals: セルの設定値一覧 | |
| 出力（クラス内フィールド） | 1. vals_map: 特定カラム間のマッピングデータ |
| 特記事項 | 1. key_colsのサイズが2行以上の場合、キーはスラッシュ”/”で連結された文字列とする |
| 2. 複数行でキーが重複する場合、行数が大きい値がマッピングされる | |
| 3. キーとするカラムのデータに欠損値が含まれる場合は、マッピングしない |
| メソッド名 | setValsMapToCell |
| メソッド名（和名） | マッピングデータをセルに反映 |
| 処理概要 | 1. key_cols, val_colで指定したカラム名に対応する列数を取得する |
| 2. 全ての行について、 setValsMapメソッドで作成したマッピングデータをもとにセルの値を更新する | |
| 入力（クラス内フィールド） | 1. body_nums: ボディの開始/終了の行列数 ※開始行数はヘッダの開始行数+1、終了行数はシートの最大行数、開始/終了行数はヘッダの開始/終了行数と同じ |
| 2. cols: カラム名一覧 | |
| 3. key_cols: マッピングデータのキーとするカラム名一覧 | |
| 4. val_col: マッピングデータの値とするカラム名 | |
| 5. vals_map: セルの設定値一覧 | |
| 6. sheet_obj: シートのインスタンス | |
| 出力 | なし（スプレッドシート上のセルの値が更新される） |
| 特記事項 | 1. setValsMapメソッドと同様 |
2-4. PostMessageToSlack.gs
Slackへのメッセージ投稿に必要なフィールドとメソッドを実装しています。
処理概要は、以下の記事で紹介しているため、割愛します。
人手不足が続くIT業界では、「少数の正社員が多数の非正規社員を管理する「といった業務形態が一般的になりつつあります。 私が従事している実際のプロジェクトでも、正社員4名に対して非正規（派遣）社員8名という業務形態がおよそ1年間続いてい[…]
3. 実行結果（Slackへの投稿メッセージ）
3-1. 基準とするシートを指定する場合
sheet2(old)に指定したシート名が出力されます。
3-2. 基準とするシートを指定しない場合
sheet2(old)にシート名が出力されません。存在しないシート名を指定した場合も同様にシート名は出力されません。
