Automatic Transaction ID with Apps Script for personal shopping and pre-orders

Project info

• ThemePersonal Shopper and Pre-order Tracker
• FeatureAutomation · Intermediate
• ToolGoogle Sheets
• Price (one price)US$36

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:

• Requested Item
• Category
• Quantity Ordered
• Item Price
• Status
• Order Date

Master sheet items

Dashboard: Personal Shopper and Pre-order Dashboard

• Total Orders
• Payments Received
• Estimated Service Profit
• Orders Not Purchased

Tips

• Set a clear pre-order closing date
• Ask for a deposit so orders are not cancelled
• Record exchange rate and shipping to compute profit

The sample data is synthetic.

Price and ordering

Frequently asked questions