Apps Script Code.gs 묵회

회계_시트

회원_시트

 

 

 

 

function onEdit(e) {

  const ss = SpreadsheetApp.getActiveSpreadsheet();

  const sheet = e.range.getSheet();

  // 회계 시트만

  if (sheet.getName() !== "회계") return;

  const row = e.range.getRow();

  if (row < 3) return;

  const col = e.range.getColumn();

  // B(이름), C(입금), D(지출)

  if (![2, 3, 4].includes(col)) return;

  /** -------------------

   * 1️⃣ 잔액 자동 계산

   ------------------- */

  const prevBalance = sheet.getRange(row - 1, 5).getValue() || 0;

  const income = sheet.getRange(row, 3).getValue() || 0;

  const expense = sheet.getRange(row, 4).getValue() || 0;

  sheet.getRange(row, 5).setValue(prevBalance + income - expense);

  /** -------------------

   * 2️⃣ 회원명 정제 (회비_ 제거)

   ------------------- */

  const memberRaw = sheet.getRange(row, 2).getValue();

  if (!memberRaw) return;

  const memberName = memberRaw.replace("회비_", "").trim();

  /** -------------------

   * 3️⃣ 회원별 누계 계산

   ------------------- */

  const accountingData = sheet.getRange(2, 2, sheet.getLastRow() - 1, 2).getValues();

  let total = 0;

  accountingData.forEach(r => {

    const name = (r[0] || "").replace("회비_", "").trim();

    if (name === memberName) {

      total += r[1] || 0;

    }

  });

  /** -------------------

   * 4️⃣ 회원 시트 E열에 반영

   ------------------- */

  const memberSheet = ss.getSheetByName("회원");

  const members = memberSheet.getRange(3, 1, memberSheet.getLastRow() - 2, 1).getValues();

  for (let i = 0; i < members.length; i++) {

    if ((members[i][0] || "").trim() === memberName) {

      memberSheet.getRange(i + 3, 5).setValue(total);

      break;

    }

  }

}