Automatic Transaction ID with Apps Script for neighborhood fund ledger

Project info

• ThemeNeighborhood Fund Ledger
• FeatureAutomation · Intermediate
• ToolGoogle Sheets
• Price (one price)US$33

Functions used

• Apps Script
• onEdit
• LockService
• PropertiesService

Every transaction has a tidy unique number for tracking.

Formulas in the file

How to use it

1. 1Paste the code in Apps Script and save.
2. 2Open the Data sheet and fill Item on a new row.
3. 3Column H fills with INV-0001 automatically.
4. 4Allow permission when first asked.
5. 5Find transactions by ID with the Rekap!B3 formula.

Apps Script code

The script is delivered as a separate Code.gs file inside the ZIP. Paste it into Extensions, Apps Script. A short excerpt:

function onEdit(e) {
  var sheet = e.range.getSheet();
  if (sheet.getName() !== "Data") return;
  var baris = e.range.getRow();
  if (baris < 2) return;
  var kolomId = 8;
  var sel = sheet.getRange(baris, kolomId);
  var item = sheet.getRange(baris, 2).getValue();
  if (item === "" || sel.getValue() !== "") return;
  var lock = LockService.getScriptLock();
  lock.waitLock(5000);
  var props = PropertiesService.getScriptProperties();
  var nomor = Number(props.getProperty("nomorTerakhir") || 0) + 1;
  props.setProperty("nomorTerakhir", String(nomor));
  lock.releaseLock();
  sheet.getRange(1, kolomId).setValue("ID");
  sel.setValue("INV-" + ("0000" + nomor).slice(-4));
  SpreadsheetApp.flush();
}

Sample content

Columns of the Data sheet:

• Cash Description
• Category
• Residents or Months
• Amount
• Status
• Date

Master sheet items

Dashboard: Neighborhood Fund Dashboard

• Total Cash In
• Total Cash Out
• Cash Balance
• Residents Unpaid

Tips

• Record each fee with the resident name and month
• Keep expense receipts for reporting
• Announce the fund balance monthly in the resident group

The sample data is synthetic.

Price and ordering

Frequently asked questions