PCA会計のGLデータを加工しやすい形に整形する

読んで欲しい人

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

やりたいこと

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 = [];

  //３行で1つの仕訳のため、3つ分の配列を1つの配列として３次元配列を作成
  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つです。

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

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

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

配列はブランクのままですと、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;
    }
  }

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

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

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

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

コードとしては難しい部分はないので説明は割愛しますが、考え方として１行１レコードで取得される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データは非常にいいサンプルで、複数の処理を組み合わせないと加工しやすいデータフォーマットになりませんが、ちょっとした加工のコツをお伝えできるものでした。

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

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