読んで欲しい人
- 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 = [];
//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つです。
- ブランクセルは配列では、空白になってしまい、そのまま使うとundefinedとなるため、全てゼロで埋める
- 3行で1つの仕訳データとなっているため、2次元配列3つを1つの配列にして、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データは非常にいいサンプルで、複数の処理を組み合わせないと加工しやすいデータフォーマットになりませんが、ちょっとした加工のコツをお伝えできるものでした。
他の会計ソフトでも加工しにくいフォーマットが存在しているようであれば、教えてもらえれば是非加工してみたいなと思います。