FIFO Cost Calculation: Copy-Paste Apps Script Pattern

What this script does

Prerequisites

Walkthrough

Edge cases

Idempotency

How to test

Hardening for production

Variations

Full code: fifoCostCalculation()

Load CostLayers and uncosted Sales, then run fifoCostCalculation() once per batch.

/**
 * FIFO unit cost: consume oldest layers first for a sale quantity.
 */
const LAYERS = "CostLayers"; // A lotId, B sku, C qtyRemaining, D unitCost, E receivedAt
const SALES = "Sales";       // A saleId, B sku, C qty, D fifoCost (out)

function fifoCostCalculation() {
  const ss = SpreadsheetApp.getActive();
  const layerSheet = ss.getSheetByName(LAYERS);
  const layers = layerSheet.getDataRange().getValues();
  // sort copy by receivedAt ascending for each sku during consume

  const salesSheet = ss.getSheetByName(SALES);
  const sales = salesSheet.getDataRange().getValues();

  for (let s = 1; s < sales.length; s++) {
    if (sales[s][3] !== "" && sales[s][3] != null) continue; // already costed
    const sku = sales[s][1];
    let need = Number(sales[s][2]) || 0;
    let cost = 0;

    // collect indexes for sku sorted by date
    const idxs = [];
    for (let i = 1; i < layers.length; i++) {
      if (layers[i][1] === sku && Number(layers[i][2]) > 0) idxs.push(i);
    }
    idxs.sort(function (a, b) {
      return new Date(layers[a][4]) - new Date(layers[b][4]);
    });

    for (let k = 0; k < idxs.length && need > 0; k++) {
      const i = idxs[k];
      const avail = Number(layers[i][2]);
      const take = Math.min(avail, need);
      cost += take * Number(layers[i][3]);
      layers[i][2] = avail - take;
      need -= take;
    }
    if (need > 0) throw new Error("Insufficient layers for sale " + sales[s][0]);
    sales[s][3] = Math.round(cost * 100) / 100;
  }

  layerSheet.getRange(1, 1, layers.length, layers[0].length).setValues(layers);
  salesSheet.getRange(1, 1, sales.length, sales[0].length).setValues(sales);
}

1. Line 4: Layer quantity reduced as sales allocate.
2. Line 4: Sort key for oldest-first.
3. Line 17: Skips sales with fifoCost filled.
4. Line 34: Partial layer consumption.
5. Line 39: Hard fail when stock layers run out.
6. Line 43: Persists depleted layer quantities.