How to Automate Bill of Quantities Using Google Sheets

Why Google Sheets for BOQ?

A Bill of Quantities is a living document. Quantities change as designs evolve, unit rates update with market conditions, and multiple team members contribute measurements simultaneously. Google Sheets handles all of this natively — real-time collaboration, automatic saving, version history, and access from any device. You never hear "who has the latest version?" again.

Structuring the Workbook

Create separate sheets for each CSI division or work package: earthworks, concrete, masonry, steel, finishes. Link them all to a summary sheet that pulls totals using IMPORTRANGE or direct sheet references. This modular structure lets different team members work on different sections without conflicts.

Setting Up the Item Template

Each BOQ line item needs: item number, description, unit, quantity, unit rate, and amount. Add a column for the measurement breakdown (length × width × height × number of pieces) so the quantity is transparent and auditable. Use formulas to compute the quantity from measurements and the amount from quantity × unit rate.

Smart Data Validation

Create a reference sheet with standard item descriptions, units, and rate ranges for your region. Use data validation dropdowns so team members select from pre-defined items rather than typing free-text descriptions. This prevents duplicate entries with slightly different descriptions — the bane of every quantity surveyor.

Automated Summaries and Reports

Use QUERY functions to generate pivot-style summaries: total cost by work package, material quantities aggregated by type, and labor requirements by trade. These summaries update automatically as quantities change, giving project managers real-time cost visibility.

Version Control and Approvals

Use Google Sheets' built-in version history to track who changed what and when. Protect the formula columns so only designated users can modify the calculation logic. Leave the measurement columns editable for the team. Add a status column with dropdown options: Draft, For Review, Approved — creating a simple workflow within the spreadsheet.

Handling Provisional Sums and Contingencies

Create dedicated sections for provisional sums, prime cost items, and contingency allowances. These are standard BOQ components that many templates overlook. Link contingency percentages to the subtotal so they adjust automatically as the measured work total changes.

Sample Code

// Google Apps Script: auto-total BOQ sheet
function updateBOQ() {
  const ss   = SpreadsheetApp.getActiveSpreadsheet();
  const sh   = ss.getSheetByName('BOQ');
  const last = sh.getLastRow();

  let grandTotal = 0;

  // Columns: A=Item, B=Description, C=Unit, D=Qty, E=Rate, F=Amount
  for (let r = 2; r <= last; r++) {
    const qty  = sh.getRange('D' + r).getValue();
    const rate = sh.getRange('E' + r).getValue();
    const amt  = qty * rate;

    sh.getRange('F' + r).setValue(amt);
    grandTotal += amt;
  }

  // Write grand total
  sh.getRange('F' + (last + 1)).setValue(grandTotal);
  sh.getRange('E' + (last + 1)).setValue('TOTAL');

  SpreadsheetApp.getUi().alert('BOQ updated: ' + grandTotal);
}