PCA会計の使いずらい仕訳CSVデータを利用しやすい形式に整形するGASコード

GAS

読んで欲しい人

  • PCA会計を利用している方で、総勘定元帳データを加工したい人
  • 複数レコードを1つの配列にする加工がしたい人
  • 不整形のデータ加工をしたい人

やりたいこと

PCA会計を利用している場合、総勘定元帳(GL)データをダウンロードすると以下みたいな形式でエクセルに取り込まれます。

PCA GLデータ サンプル

ただ、このデータプログラミングで取り扱うには非常に処理しにくい、いわゆるブサイクな不整形データとなっています。きっとPCAさんはcsvをダウンロードして印刷してもらうことを前提に作っているのかもですが、様々加工する際には非常に不便な形式になっています。

では、どういうデータだったら加工しやすいかというと、以下のようなデータ形式になっていればその後の加工も楽だと思いませんか。

正規化されたデータイメージ(ゴール)

では、実際に上記のようなformatの変更を行うGASを実装していきます。

Format変更用コード

コード全体

function dataArrange() {
  //SSの取得
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1");
  
 //データの最終行はgetLastRowで、カラムは固定長なので数値指定
  var numRows = sheet.getLastRow();
  var numColumns = 9;
  
  var datas = sheet.getRange(8, 1, numRows, numColumns).getValues();
  
 //配列を取得するとブランクばかりの配列になっている(SS上でセルに値がない部分)ため、一旦全てブランクをゼロで埋める
  for(var i = 0; i < datas.length; i++){
    for(var j = 0; j < 9; j++){
      if ((datas[i][j])) continue;
        datas[i][j] = 0;
    }
  }
  
  var inputData = [];
  var outputData = [];

  //3行で1つの仕訳のため、3つ分の配列を1つの配列として3次元配列を作成
  for(var i = 0; i<= datas.length; i= i+3){
    inputData.push([datas[i],datas[i+1],datas[i+2]]);
  }
  
 //必要なデータを取り出す
 for(var j = 0; j < inputData.length -1 ; j++){
    outputData.push([inputData[j][0][0], inputData[j][2][0], inputData[j][0][1], inputData[j][1][1], inputData[j][0][3], inputData[j][2][4],inputData[j][2][6]]);
   }
  
 //日付データは同一日の場合、一番上の仕訳にしかデータが含まれていないため、調整
  for(var k = 0; k<outputData.length; k++){
    if(outputData[k][0] === 0){
      outputData[k][0] = outputData[k-1][0];
    }
  }
  
 //表示するアイテムの項目タイトル一覧を配列で作成して、データファイルの先頭に差し込む
  var title = ["伝票日付","伝番","相手科目","科目補助","摘要","借方","貸方"];
  outputData.unshift(title);

 //SSに貼り付ける(今回は簡易的に、PCA仕訳データの右側に貼り付ける)
  sheet.getRange(1, 12, outputData.length, outputData[0].length).setValues(outputData);

}

コード説明

今回のコード実装でポイントになる部分は主に以下の3つです。

  1. ブランクセルは配列では、空白になってしまい、そのまま使うとundefinedとなるため、全てゼロで埋める
  2. 3行で1つの仕訳データとなっているため、2次元配列3つを1つの配列にして、3次元配列を作成する
  3. 日付データが埋まっていない部分を埋める

それでは、一つづつ見ていきます。

ブランクセルに対応する配列要素にゼロを埋め込む

配列はブランクのままですと、undefinedとなり、エラーを生じさせやすくなります。そのため、今回は一旦ブランクの部分をゼロで埋めて、加工しやすい配列に変更することを一番最初に実施します。

//配列を取得するとブランクばかりの配列になっている(SS上でセルに値がない部分)ため、一旦全てブランクをゼロで埋める
  for(var i = 0; i < datas.length; i++){
    for(var j = 0; j < 9; j++){
      if ((datas[i][j])) continue;
        datas[i][j] = 0;
    }
  }

2次元配列で取得されているので、for文を2重でループさせながら、一つづつ要素を判定していっています。if式ではデータが格納されていた場合は、continue(処理せずにパス)し、ブランクの場合はゼロとします。

仕訳データ単位に配列を加工する

PCA仕訳データの処理しにくさの一つの要素である3行で1つの仕訳データとなっている部分を加工していきます。3行で1つの仕訳なので、配列も3つを1つの配列にすることで、1つの配列要素に1つの仕訳データが集められるようにします。

//3行で1つの仕訳のため、3つ分の配列を1つの配列として3次元配列を作成
  for(var i = 0; i<= datas.length; i= i+3){
    inputData.push([datas[i],datas[i+1],datas[i+2]]);
  }

コードとしては難しい部分はないので説明は割愛しますが、考え方として1行1レコードで取得されるGASにおいては、複数レコードで1つの要素となる場合、配列の次元数を1つあげて複数個を1個の配列にするように加工すると便利です。

日付データのブランク部分を埋める

データの特徴で、1日に複数の仕訳がある場合、一番最初の仕訳にしか日付情報が含まれていません。このままでもデータの特徴を理解していれば問題はないのですが、もし加工後のデータから一定条件(例えば金額や摘要の内容等)でデータを抽出して別のファイルに転記した場合、日付データがブランクのデータになってしまいます。それは避けたいところです。そのため各仕訳データに日付データを持たせることにします。

//日付データは同一日の場合、一番上の仕訳にしかデータが含まれていないため、調整
  for(var k = 0; k<outputData.length; k++){
    if(outputData[k][0] === 0){
      outputData[k][0] = outputData[k-1][0];
    }
  }

対応方法はシンプルで、if式で日付データ対象要素がゼロ(前処理でゼロうめしているので、日付かゼロが配列要素になっています)の場合に、一つ前のデータを参照させています。これで、1日に複数起票された場合、2つ目以降がブランクだった日付データが全て埋まることになります。

まとめ

MFやfreeeなどベンチャー系の会計ソフトは加工しやすいデータが抽出できることが多いのですが、昔からある会計ソフトではデータフォーマットが加工に向いていないケースがあります。その場合、一旦今後の分析や抽出を容易にするために、データの加工を自分でできるようになっておくと便利です。

今回のPCAのGLデータは非常にいいサンプルで、複数の処理を組み合わせないと加工しやすいデータフォーマットになりませんが、ちょっとした加工のコツをお伝えできるものでした。

他の会計ソフトでも加工しにくいフォーマットが存在しているようであれば、教えてもらえれば是非加工してみたいなと思います。

GAS会計
スポンサーリンク
シェアする
ks_Accountingをフォローする
経営管理deプログラミング

コメント