複式簿記ができるようにメンターに聞いて作ってみた話

フリーランスの皆さん、確定申告お疲れ様でした。
確定申告が終わるまではなんで今まで真面目に領収書とってなかったり記録つけてなかったんだろうという後悔と、確定申告が終わったら今年は真面目に記録つけていこう！という決意を数日するというのを毎年繰り返していた私です。（きっと自分だけじゃないはず）

業種にもよりますが、今まで物販に取り組んできていた時から比べるとIT系は扱う費用項目が少なく済むので楽なのですが、それでも1年分溜めてしまうと結構大変です。
そもそも確定申告とか普段の金銭管理って、まとめてやるものではなくて日々続けることで資金の流れとかを可視化しようというのが前提にあるので、自分で事業するならしっかりやれよって話です。

とはいえ、巷のサービスを利用しようとするとカードの登録枚数に制限があったり、記録や仕訳がしにくかったり、仕訳が終わっても総勘定元帳などの作成には有料となったり色々不便があるように思っていまして（特に小規模のビジネス＋企業したてであればあるほど）

今回はスプレッドシートを利用して複式簿記をしつつ、仕訳したら総勘定元帳とか試算表とか作れるようなものがあればいいなと思いまして、メンター（ChatGPT）に聞きながら作成してみました。

ちなみに、仕訳自体も詳しくないので、この時はどう書いたら良いのかというのも聞きながらやってみています。

仕訳帳

こちらが仕訳帳になります。項目はプルダウンで選択できるようにしています。
請求書への支払いなどで『未払金』の項目を仕訳に入れた時には、行全体が赤くなるようにしています（編集時トリガー使用）。そして、別日に実際の支払いがあったときには該当行のH列チェックをTrueにすることで背景色がリセットされるような仕組みになっています。
これは請求コードなどもメモに残しておくことによって後々コードから検索しても照合できるようにしました。
カスタマーサポート業務で受付番号やら機械番号やら聴取する習慣ができたので、これを活かしているという状態です。

ファイル全体像

順序が逆になりましたが、全体像はこんな感じ。
試算表が表示されていますが、これは自動で用意されるようになっています。

GASコード

function allDo(){
  generateGeneralLedger();
  SpreadsheetApp.flush();

  generateTrialBalance();
  SpreadsheetApp.flush();
  
  generatePL();
  SpreadsheetApp.flush();
  
  generateBS()
}
function sortByDate() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()); // 2行目から最終行までを取得
  range.sort({column: 1, ascending: true});  // A列（1列目）で降順（新しい日付が上）に並び替え
}


function onEdit(e) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("支出_生活費関連");
  if (!sheet) return;
  
  var editedCell = e.range;
  var row = editedCell.getRow();
  var col = editedCell.getColumn();
  
  // 「更新」列（6列目）の2行目が変更された場合のみ処理
  if (row === 2 && col === 7) {
    var value = editedCell.getValue();
    
    if (value === true) { // チェックボックスがTrueのとき
      var data = sheet.getRange(2, 1, 1, 6).getValues()[0]; // 2行目のデータを取得（A~F列）
      
      if (data.some(cell => cell !== "")) { // 空でなければ処理を続行
        var lastRow = sheet.getLastRow();
        var newRow = lastRow < 26 ? 26 : lastRow + 1; // 26行目以降の最終行を特定
        
        sheet.getRange(newRow, 1, 1, 6).setValues([data]); // データを貼り付け
        
        sheet.getRange(2, 1, 1, 6).clearContent(); // 2行目のデータをクリア
        sheet.getRange(2, 7).setValue(false); // チェックボックスをFalseにリセット
      } else {
        editedCell.setValue(false); // データが空のままチェックした場合はFalseに戻す
      }
    }
  }
}


function payableRow(){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("仕訳帳");
  if(!sheet || ss.getActiveSheet().getName() !== "仕訳帳") return;

  const activeCell = sheet.getActiveCell();
  const activeRow = activeCell.getRow();
  const activeCol = activeCell.getColumn();
  const lastColumn = sheet.getLastColumn();

  // D列の記入時に”未払金”を選択したときに行全体を赤く塗りつぶす
  if(activeCol === 4 && activeCell.getValue() == "未払金") {
    console.log("未払金の項目を検知")
    sheet.getRange(activeRow, 1, 1, lastColumn).setBackground("#ff0000");
    return;
  }

  // H列にチェックが入ったら行全体の背景色を元に戻す
  if(activeCol === 8 && activeCell.getValue() === true){
    console.log("未払金の支払いを完了")
    sheet.getRange(activeRow, 1, 1, lastColumn).setBackground("");
    return
  }
}

// Compiled using undefined undefined (TypeScript 4.9.5)
"use strict";
/**
 * 仕訳帳シート（"仕訳帳"）のデータをもとに、総勘定元帳シート（"総勘定元帳"）を更新します。
 * 仕訳帳は1行目がヘッダー（「借方」「借方金額」「貸方」「貸方金額」）となっており、
 * 2行目以降が取引データとなっています。
 */
function updateLedger() {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const journalSheet = ss.getSheetByName("仕訳帳");
    const ledgerSheet = ss.getSheetByName("総勘定元帳");
    if (!journalSheet || !ledgerSheet) {
        SpreadsheetApp.getUi().alert("仕訳帳シートまたは総勘定元帳シートが見つかりません。");
        return;
    }
    // ヘッダー行を除くデータ範囲（A列～D列、2行目以降）を取得
    const lastRow = journalSheet.getLastRow();
    if (lastRow < 2) {
        // データが存在しない場合は何もしない
        return;
    }
    const dataRange = journalSheet.getRange(2, 1, lastRow - 1, 4);
    const data = dataRange.getValues(); // [ [借方, 借方金額, 貸方, 貸方金額], ... ]
    // 各勘定科目ごとに残高を計算（借方は加算、貸方は減算）
    const accounts = {};
    data.forEach((row) => {
        const debitAccount = row[1]; // 借方（列A）
        const debitAmount = Number(row[2]) || 0; // 借方金額（列B）
        const creditAccount = row[3]; // 貸方（列C）
        const creditAmount = Number(row[4]) || 0; // 貸方金額（列D）
        if (debitAccount) {
            if (!accounts[debitAccount]) {
                accounts[debitAccount] = 0;
            }
            accounts[debitAccount] += debitAmount;
        }
        if (creditAccount) {
            if (!accounts[creditAccount]) {
                accounts[creditAccount] = 0;
            }
            accounts[creditAccount] -= creditAmount;
        }
    });
    // 総勘定元帳シートの内容をクリアして、新たにデータをセットアップ
    ledgerSheet.clear();
    // ヘッダー行を設定（例：「勘定科目」「残高」）
    ledgerSheet.getRange(1, 1, 1, 2).setValues([["勘定科目", "残高"]]);
    // 各勘定科目ごとに計算した残高を配列にまとめる
    const ledgerData = [];
    for (const account in accounts) {
        ledgerData.push([account, accounts[account]]);
    }
    if (ledgerData.length > 0) {
        ledgerSheet.getRange(2, 1, ledgerData.length, 2).setValues(ledgerData);
    }
}

// function editedShiwakeSheet() {
//     const ss = SpreadsheetApp.getActiveSpreadsheet();
//     const sheet = ss.getActiveSheet();
//     if (!sheet || sheet.getName() !== "仕訳帳") return;
//     const activeCell = sheet.getActiveCell();
//     const col = activeCell.getColumn();
//     const val = activeCell.getValue();
//     if (col === 3) {
//         activeCell.offset(0, 2).setValue(val);
//     }
//     else if (col === 5) {
//         activeCell.offset(0, -2).setValue(val);
//     }
// }


function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('会計ツール')
    .addItem('シート並べ替え', 'sortByDate')
    .addItem('全て更新', 'allDo')
    .addItem('総勘定元帳を更新', 'generateGeneralLedger')
    .addItem('試算表を更新', 'generateTrialBalance')
    .addItem('損益計算書を更新', 'generatePL')
    .addItem('貸借対照表を更新', 'generateBS')
    .addToUi();
}

function generateGeneralLedger() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('仕訳帳');
  var ledgerSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('総勘定元帳');
  ledgerSheet.clear();
  
  var data = sheet.getDataRange().getValues();
  var ledger = {};

  for (var i = 1; i < data.length; i++) {
    var date = data[i][0];
    var debitAccount = data[i][1];
    var debitAmount = data[i][2];
    var creditAccount = data[i][3];
    var creditAmount = data[i][4];
    var description = data[i][5];
    var remarks = data[i][6];
    var paid = data[i][7];
    var referenceCode = data[i][8];

    if (!ledger[debitAccount]) ledger[debitAccount] = [];
    if (!ledger[creditAccount]) ledger[creditAccount] = [];

    ledger[debitAccount].push([date, debitAccount, debitAmount, '', '', description]);
    ledger[creditAccount].push([date, '', '', creditAccount, creditAmount, description]);
  }

  var output = [['日付', '借方科目', '借方金額', '貸方科目', '貸方金額', '摘要']];
  
  for (var account in ledger) {
    // 科目名を9列に合わせる
    output.push([account, '', '', '', '', '']);
    output = output.concat(ledger[account]);
  }

  ledgerSheet.getRange(1, 1, output.length, 6).setValues(output);  // 9列に固定
}


function generateTrialBalance() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('総勘定元帳');
  var balanceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('試算表');
  balanceSheet.clear();
  
  var data = sheet.getDataRange().getValues();
  var balance = {};

  for (var i = 1; i < data.length; i++) {
    var debitAccount = data[i][1];
    var debitAmount = data[i][2] || 0;
    var creditAccount = data[i][3];
    var creditAmount = data[i][4] || 0;

    if (debitAccount) {
      if (!balance[debitAccount]) balance[debitAccount] = [0, 0];
      balance[debitAccount][0] += debitAmount;
    }
    if (creditAccount) {
      if (!balance[creditAccount]) balance[creditAccount] = [0, 0];
      balance[creditAccount][1] += creditAmount;
    }
  }

  var output = [['勘定科目', '借方合計', '貸方合計']];
  for (var account in balance) {
    output.push([account, balance[account][0], balance[account][1]]);
  }

  balanceSheet.getRange(1, 1, output.length, output[0].length).setValues(output);
}

function generatePL() {
  var trialSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('試算表');
  var plSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('損益計算書');
  plSheet.clear();
  
  var data = trialSheet.getDataRange().getValues();
  var revenue = 0, expenses = 0;

  for (var i = 1; i < data.length; i++) {
    var account = data[i][0];
    var debitTotal = data[i][1];
    var creditTotal = data[i][2];

    if (account.includes('売上')) {
      revenue += creditTotal;
    } else if (account.includes('費')) {
      expenses += debitTotal;
    }
  }

  var output = [['項目', '金額'], ['売上高', revenue], ['費用合計', expenses], ['純利益', revenue - expenses]];
  plSheet.getRange(1, 1, output.length, output[0].length).setValues(output);
}

function generateBS() {
  var trialSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('試算表');
  var bsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('貸借対照表');
  bsSheet.clear();
  
  var data = trialSheet.getDataRange().getValues();
  var assets = 0, liabilities = 0, equity = 0;

  // 確認用ログ（試算表データを出力）
  console.log("試算表データ:", data);

  // 勘定科目の分類（明確に資産・負債・純資産を定義）
  var assetAccounts = ['現金', '預金', '売掛金', '棚卸資産', '固定資産']; // 資産の科目
  var liabilityAccounts = ['買掛金', '借入金', '未払金', '未払費用']; // 負債の科目
  var equityAccounts = ['資本金', '繰越利益剰余金']; // 純資産の科目

  for (var i = 1; i < data.length; i++) {
    var account = data[i][0];
    var debitTotal = Number(data[i][1]) || 0; // NaN を防ぐ
    var creditTotal = Number(data[i][2]) || 0; // NaN を防ぐ

    if (assetAccounts.some(a => account.includes(a))) {
      assets += debitTotal;
    } else if (liabilityAccounts.some(a => account.includes(a))) {
      liabilities += creditTotal;
    } else if (equityAccounts.some(a => account.includes(a))) {
      equity += creditTotal;
    }
  }

  var output = [
    ['項目', '金額'],
    ['資産合計', assets],
    ['負債合計', liabilities],
    ['純資産合計', equity]
  ];
  bsSheet.getRange(1, 1, output.length, output[0].length).setValues(output);
}

やりたいことを投げればコード作ってくれるので大変助かりますね。
onOpen関数も用意してカスタムメニュー作っているので、手動で更新させることもできます。
日付気にせず仕訳帳に入力し、完了したら日付順にソートさせることも作成したのでいちいち操作しなくてもOK。

便利な世の中です。