Normalize electricity and gas meter readings against heating/cooling degree days for weather-adjusted site intensity. The workbook becomes the system of record; Apps Script owns the joins and business rules so analysts are not pasting monthly formulas.
Entry point `normalizeUtilityUsage` reads typed columns with SpreadsheetApp batch APIs. SpreadsheetApp is the primary service; Time-driven describes how you usually invoke it after authorization.
Keep thresholds (grace days, bands, alert emails, API keys) in Config cells or Script Properties so the same .gs file promotes from sandbox to production without code edits.
This page is technical only: sheet layout, edge cases, runnable code, deploy steps, and FAQs for normalize utility usage by degree days. No consulting pitch—just the pattern you can paste and harden.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Tab | Columns | Notes |
|---|---|---|
| MeterLog | Date, SiteId, Utility, RawUsage, Unit | Daily meters |
| DegreeDays | Date, StationId, HDD, CDD | Weather |
| SiteMap | SiteId, StationId, SqFt | Join |
| Normalized | Script output | Intensity |
What this script does
normalizeUtilityUsage() normalize electricity and gas meter readings against heating/cooling degree days for weather-adjusted site intensity.
- HDD for gas, CDD for electric
- THERM→kBtu conversion
- Skip zero degree-day days
Prerequisites
Container-bound script on the workbook that contains the tabs in the setup table. Authorize SpreadsheetApp scopes on first run.
- V8 runtime
- Exact sheet names
- Script timezone = ops timezone
Walkthrough
Map SiteId→StationId, join HDD/CDD by date, choose denominator by utility, write intensity, then optionally mail spikes.
Paste the code, set Config/Properties, run normalizeUtilityUsage once, verify the output tab, then attach a Time-driven trigger.
Edge cases
Zero degree-day days are skipped (no floor of 1). THERM rows convert ×100 to kBtu before intensity.
Testing
Use a sandbox copy with 3–5 known rows. Compute expected outputs for normalize utility usage by degree days offline, run normalizeUtilityUsage, and diff the output tab including one intentional bad row.
Hardening
Add LockService around multi-sheet writes if triggers can overlap. Log run timestamps. Keep API keys and alert emails in PropertiesService—not in shared cells.
Variations
Fork ideas: filter to one business unit, change grain (daily→weekly), or POST a summary payload after normalizeUtilityUsage succeeds.
Operations notes
Assign an owner for the output tab, document regenerate steps, and treat `normalizeUtilityUsage` as source of truth over ad-hoc cell formulas.
Full code: normalizeUtilityUsage()
Run normalizeUtilityUsage when source tabs are current. Edit sheet names and Config/Properties first.
const METER='MeterLog', DEGREE='DegreeDays', SITES='SiteMap', OUT='Normalized';
const ALERT_PCT=0.25;
function normalizeUtilityUsage(){
const ss=SpreadsheetApp.getActive();
const lock=LockService.getScriptLock();
if(!lock.tryLock(30000)) return;
try{
const siteMap={};
ss.getSheetByName(SITES).getDataRange().getValues().slice(1).forEach(r=>{
siteMap[String(r[0])]={station:String(r[1]), sqft:Number(r[2])||1};
});
const dd={};
ss.getSheetByName(DEGREE).getDataRange().getValues().slice(1).forEach(r=>{
const k=Utilities.formatDate(new Date(r[0]), Session.getScriptTimeZone(),'yyyy-MM-dd')+'|'+r[1];
dd[k]={hdd:Number(r[2])||0,cdd:Number(r[3])||0};
});
const meters=ss.getSheetByName(METER).getDataRange().getValues().slice(1);
const out=[];
meters.forEach(r=>{
const siteId=String(r[1]), utility=String(r[2]).toUpperCase();
let usage=Number(r[3])||0;
if(String(r[4]).toUpperCase()==='THERM') usage*=100;
const site=siteMap[siteId]; if(!site) return;
const dkey=Utilities.formatDate(new Date(r[0]), Session.getScriptTimeZone(),'yyyy-MM-dd')+'|'+site.station;
const w=dd[dkey]; if(!w) return;
const denom=utility==='GAS'?w.hdd:w.cdd; if(denom<=0) return;
const intensity=usage/denom;
out.push([new Date(r[0]),siteId,utility,usage,denom,intensity]);
});
const sh=ss.getSheetByName(OUT);
sh.clearContents();
sh.appendRow(['Date','SiteId','Utility','Usage','DegreeDays','Intensity']);
if(out.length) sh.getRange(2,1,out.length+1,6).setValues(out);
} finally { lock.releaseLock(); }
}- Line 3: Entry point — bind triggers to normalizeUtilityUsage.
- Line 5: Serialize overlapping trigger runs.
- Line 9: Batch read; avoid per-cell getValue in loops.
- Line 33: Batch write output rows in one setValues call.
- Line 6: Rename sheet constants before production.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before you run: normalize utility usage by degree days
- 1Setup tabs exist with headers matching normalizeUtilityUsage
- 2Dry-run on a copy workbook
- 3Timezone verified
- 4SpreadsheetApp authorization completed
- 5Output spot-checked against hand calc
- 6Time-driven trigger added only after validation
- 7Owners + alert recipients documented
Frequently asked questions
At minimum the tabs listed in the setup table for Normalize Utility Usage by Degree Days. Output tabs may be cleared each run—do not store source-of-truth data there.
Run from the Apps Script editor for dry runs. Production usually uses a Time-driven trigger after OAuth succeeds once.
Zero degree-day days are skipped (no floor of 1). THERM rows convert ×100 to kBtu before intensity.
Per-cell calls burn quota and wall time. One read + one write keeps this pattern under the 6-minute execution cap longer.
PropertiesService (Script Properties) for API keys and alert inboxes. Config sheet is fine for non-secret thresholds.
Simple ratios maybe; normalize utility usage by degree days needs joins, branching, or side effects (email/Docs/API) that Apps Script handles cleanly.
Keep the .gs in clasp/git. Avoid divergent copies of formulas on the output tab—regenerate from the script.