【GAS】 Google Apps Scriptを使って給与支払報告書を作成する(1) 〜データのMergeまで〜

はじめに

可茂IT塾では、インターンで働いてくれた学生にアルバイト代を払っています。年末も近づいてきましたので給与支払報告書の作成の準備をしているところです。
今回はGASを使って、給与支払報告書を作る方法について紹介したいと思います。

今回やること

名簿シートから必要なデータを取り出す

• 不要な列データ no. etc1 etc2を削除する

[ { name: '山田太郎',
    birth: 'H15.9.2',
    'postal code': '123-0001',
    address: '岐阜県' },
  { name: '渡辺みつお',
    birth: 'H13.1.18',
    'postal code': '234-0002',
    address: '愛知県' },
  { name: '小島武雄',
    birth: 'H10.7.29',
    'postal code': '345-0003',
    address: '三重県' } ]

給与支払シートから必要なデータを取り出す

• 不要な列データ etc1 etc2を削除する
• 必要な行データ 2022年データ を取り出す
• 同じ「氏名」で給与支払額の合計額を算出し、{ 氏名、年間給与支払額 }のデータにする

[ { name: '山田太郎', nenkanKyuyo: 80000 },
  { name: '渡辺みつお', nenkanKyuyo: 60000 },
  { name: '小島武雄', nenkanKyuyo: 20000 } ]

名簿データと年間給与データを合わせる

  [ { name: '山田太郎',
    birth: 'H15.9.2',
    'postal code': '123-0001',
    address: '岐阜県',
    nenkanKyuyo: 80000 },
  { name: '渡辺みつお',
    birth: 'H13.1.18',
    'postal code': '234-0002',
    address: '愛知県',
    nenkanKyuyo: 60000 },
  { name: '小島武雄',
    birth: 'H10.7.29',
    'postal code': '345-0003',
    address: '三重県',
    nenkanKyuyo: 20000 } ]

ここまでできるようにします

名簿オブジェクトの作成

クラス作成

はじめに、特定のシートを選択して、オブジェクトを作成するのは同一手順なのでクラス化します。

'use strict'

class DataSheet {
  constructor(ssId, shName,) {
    this.ssId = ssId;
    this.shName = shName;
    this.targetSh = SpreadsheetApp.openById(this.ssId).getSheetByName(this.shName);
    this.array = this.targetSh.getDataRange().getValues();
  }

  toObj() {
    // オブジェクトにする
    const [header, ...records] = this.array;  //１行目がヘッダー、以降がレコード
    const objects = records.map(
      record => record.reduce((acc, value, index) => {
        acc[header[index]] = value;
        return acc;
      }, {})
    );
    return objects;
  }
}

参考：[GAS]二次元配列からオブジェクト/MAPオブジェクトに変換する (MapとReduceで)

これにより次のコードで名簿シートのオブジェクト取得することができます

const memberSh = new DataSheet('1Z******************DU', '名簿'); // シートID,シート名
const memberObj = memberSh.toObj();

データの整形

不要な列データを削除します

const deleteKeyMember = ['no.','etc1', 'etc2',] // 不要な列項目

memberObj
  .map((key) => {
    for (const element of deleteKeyMember) {
      delete key[element]; // 不必要な列データを削除する
    }
  })

年間給与オブジェクトの作成

• オブジェクト作成

const kyuyoShiharaiSh = new DataSheet('1Z******************DU', '給与支払');
var kyuyoShiharaiObj = kyuyoShiharaiSh.toObj();

• 給与支払年が2022年であるデータを抽出する

var filteredkyuyoShiharaiObj = kyuyoShiharaiObj.filter(data => data['yearMonth'].includes('2022'));

• 氏名が同一の給与を足していき、 {氏名,年間給与} のオブジェクトにする

var nenkanKyuyoObj = [...
    filteredkyuyoShiharaiObj.reduce(
      (m, item) => m.set(item.name, (m.get(item.name) || 0) + item.kyuyo)
      , new Map()
    )].map(([name, nenkanKyuyo]) => ({ name, nenkanKyuyo }));

参考：連想配列からカテゴリごとの小計を求める方法

名簿オブジェクトと年間給与オブジェクトを名前でマージする

• Object.assign(obj_a, obj_b)で、オブジェクトをマージすることができます
• mapメソッドで２つのオブジェクトを回して、同じ氏名のオブジェクトをマージします

// データをmergeする
let kyuyoHoukokuObj = [];
nenkanKyuyoObj.map((element) => {
  memberObj.map((member) => {
    if (element.name === member.name) { //名前が一緒のobjctをmergeする
      Object.assign(member, element);
      kyuyoHoukokuObj.push(member);
    }
  })
});

参考：JavaScriptでオブジェクトをマージ（結合）する方法
これで以下のデータの完成です

[ { name: '山田太郎',
   birth: 'H15.9.2',
   'postal code': '123-0001',
   address: '岐阜県',
   nenkanKyuyo: 80000 },
 { name: '渡辺みつお',
   birth: 'H13.1.18',
   'postal code': '234-0002',
   address: '愛知県',
   nenkanKyuyo: 60000 },
 { name: '小島武雄',
   birth: 'H10.7.29',
   'postal code': '345-0003',
   address: '三重県',
   nenkanKyuyo: 20000 } ]

まとめ

名簿オブジェクトと年間給与オブジェクトを合わせること(merge)ができました。
実際の給与支払報告書には他にもいろいろ入力するデータがあるかと思いますが、今回紹介したコードを参考に不要な項目を削除したり、データの抽出をして最後にマージをすれば好みのデータを作成できるのではないかと思っています。
入力データを作成することができたので、次回は給与支払報告書のスプレッドシートに今回作成したデータを入力して、それをPDF化して保存するまでを紹介したいと思います。