// ============================================================================= // EV charging SPOT-price tracker — Shelly 3EM PRO/Gen3 (device side, Espruino/mJS) // Measures charging energy at 15-min resolution, prices it as SPOT + transfer + tax // and POSTs every active interval to a Google Apps Script web app. // // ============================================================================= // ------------------------------- CONFIG -------------------------------------- // All editable without reading the code. Values in (€ or kr)/kWh (VAT included if you want gross). let CONFIG = { APPS_SCRIPT_URL: "https://script.google.com/macros/s/PASTE_YOUR_DEPLOY_ID/exec", SECRET_KEY: "change-this-to-your-own-secret", AREA: "FI", // FI, SE1-SE4, NO1-NO5, DK1-DK2, EE, LV, LT POWER_THRESHOLD_W: 100, // standby/charging threshold (avg power per 15 min). // 3EM dedicated to the charger -> ~100 W separates // standby from charging and also captures short edge blocks. PRICE_RESOLUTION_60MIN: false, // false = 15 min spot price. true = 60 min hourly average. TRANSFER_DAY: 0.0255, // grid transfer fee, day (€ or kr)/kWh TRANSFER_NIGHT: 0.0112, // grid transfer fee, night (€ or kr)/kWh NIGHT_HRS: [22,23,0,1,2,3,4,5,6], // night hours (local time) TAX: 0.02917875, // electricity tax (€ or kr)/kWh MARGIN: 0.0, // your own margin (€ or kr)/kWh EM_ID: 0 // 3EM PRO/Gen3 triphase EMData }; // Hard cap for buffered points (KVS limit is 50 keys). Protects the store from overflow. let MAX_PENDING = 45; // ------------------------------- STATE (globals) ----------------------------- let g_lastMin = -1; // last processed minute (once per minute) let g_started = true; // boot: warm the price cache immediately let g_busy = false; // prevent overlapping boundary processing let g_havePrice = false; // has the SPOT price been fetched at least once let g_blockPriceEur = 0; // SPOT (€ or kr)/kWh — cached for pricing the NEXT block let g_closePriceEur = 0; // SPOT (€ or kr)/kWh — frozen for the block closing NOW let g_blockEndSec = 0; // end of the closing block (epoch, UTC) let g_sessionId = 0; // 0 = idle; otherwise epoch of the session's first block let g_retryItems = []; // KVS pending points for the retry round let g_retryIdx = 0; let g_retryCurKey = ""; let g_freshDp = null; // point currently being sent (for the callback) // ------------------------------- HELPERS ------------------------------------- function r3(x) { return Math.round(x * 1000) / 1000; } function r4(x) { return Math.round(x * 10000) / 10000; } // Epoch of the current 15-min boundary (= end of the closing block). This aligns to the local // wall-clock quarter-hour for ANY whole-hour UTC offset, so BOTH CET (SE/NO/DK, UTC+1/+2) and // EET (FI/EE/LV/LT, UTC+2/+3) work — no supported AREA has a half-hour offset. Uses UTC epoch, // matching EMData's UTC timestamps; day/night below uses local device time instead. function alignedNowSec() { return Math.floor(Date.now() / 1000 / 900) * 900; } // ------------------------------- PRICE FETCH --------------------------------- function fetchPrice() { // priceResolution: "15" = current 15-min period price; "60" = current hour's average // (for consumers still billed per hour, not yet on 15-min metering). let resMin = CONFIG.PRICE_RESOLUTION_60MIN ? "60" : "15"; Shelly.call("HTTP.GET", { url: "https://api.spot-hinta.fi/JustNow?region=" + CONFIG.AREA + "&priceResolution=" + resMin, timeout: 10, ssl_ca: "*" }, onPrice, null); } function onPrice(res, err, msg, ud) { if (err === 0 && res && res.code === 200 && res.body) { let p = null; try { p = JSON.parse(res.body); } catch (e) { p = null; } // PriceWithTax = spot incl. VAT/ALV — the exact value the legacy JustNowPrice returned. if (p !== null && p.PriceWithTax !== undefined && p.PriceWithTax !== null) { g_blockPriceEur = (p.PriceWithTax * 1); // (€ or kr)/kWh incl. ALV g_havePrice = true; } } // On failure keep the previous cache; if a price was never obtained, the closing // block is skipped (see processClosingBlock). } // ------------------------------- BOUNDARY HANDLING --------------------------- function tick() { if (g_busy) { return; } // previous boundary still processing let now = new Date(); // local time (DST-safe) let m = now.getMinutes(); if (m === g_lastMin) { return; } // once per minute g_lastMin = m; if (g_started) { // boot: fetch price, no block g_started = false; fetchPrice(); return; } if (m % 15 !== 0) { return; } // only on 15-min boundaries onBoundary(now); } function onBoundary(now) { g_blockEndSec = alignedNowSec(); // end of the closing block g_closePriceEur = g_blockPriceEur; // FREEZE the price before the new fetch (no race) // Fetch the price for the STARTING block. The flag controls the API resolution (15/60 min), // NOT the fetch frequency: in 60-min mode every quarter simply gets the same hourly average. fetchPrice(); g_busy = true; retryPending(); // -> processClosingBlock -> ... -> g_busy=false } // ------------------------------- BUFFER RETRY -------------------------------- // Retry everything pending in KVS BEFORE processing the new point. function retryPending() { Shelly.call("KVS.GetMany", { match: "pending_*" }, onPendingList, null); } function onPendingList(res, err, msg, ud) { g_retryItems = []; if (err === 0 && res && res.items) { let it = res.items; // array: [{key, etag, value}, ...] for (let i = 0; i < it.length; i++) { g_retryItems.push(it[i]); } } g_retryIdx = 0; retryNext(); } function retryNext() { if (g_retryIdx >= g_retryItems.length) { processClosingBlock(); // retry done -> handle the fresh block return; } let item = g_retryItems[g_retryIdx]; g_retryCurKey = item.key; let dp = null; try { dp = JSON.parse(item.value); } catch (e) { dp = null; } if (dp === null) { // corrupt -> delete and continue Shelly.call("KVS.Delete", { key: g_retryCurKey }, null, null); g_retryIdx++; retryNext(); return; } postDp(dp, onRetryResult); } function onRetryResult(res, err, msg, ud) { if (err === 0 && res && res.code === 200) { Shelly.call("KVS.Delete", { key: g_retryCurKey }, null, null); // success -> delete } g_retryIdx++; retryNext(); // continue (failed ones stay for the next round) } // ------------------------------- BLOCK PROCESSING ---------------------------- function processClosingBlock() { if (!g_havePrice) { // no price yet (e.g. boot + fetch failed) g_busy = false; // skip this block; the price catches up next round return; } // Read the closing block's [end-900, end) one-minute records (15 of them). Shelly.call("EMData.GetData", { id: CONFIG.EM_ID, ts: g_blockEndSec - 900, end_ts: g_blockEndSec - 1, add_keys: false }, onBlockData, null); } function onBlockData(res, err, msg, ud) { let wh = 0; if (err === 0 && res && res.data) { // Triphase key indices (add_keys:false): a=0, b=16, c=32 total_act_energy (Wh). let d = res.data; for (let i = 0; i < d.length; i++) { let vals = d[i].values; if (!vals) { continue; } for (let j = 0; j < vals.length; j++) { let row = vals[j]; if (row) { wh += (row[0] || 0) + (row[16] || 0) + (row[32] || 0); } } } } finishBlock(wh); } function finishBlock(wh) { // Active if avg power > threshold. avgW = wh / 0.25 h => active when wh > threshold*0.25. let active = (wh > (CONFIG.POWER_THRESHOLD_W * 0.25)); if (!active) { g_sessionId = 0; // charging -> idle g_busy = false; return; } // idle -> charging: the session starts at this block let blockStart = g_blockEndSec - 900; if (g_sessionId === 0) { g_sessionId = blockStart; } let kwh = wh / 1000; let hour = new Date(blockStart * 1000).getHours(); // local hour for day/night let transfer = (CONFIG.NIGHT_HRS.indexOf(hour) > -1) ? CONFIG.TRANSFER_NIGHT : CONFIG.TRANSFER_DAY; let spot = g_closePriceEur; // (€ or kr)/kWh (frozen at block start) let totalEur = spot + transfer + CONFIG.TAX + CONFIG.MARGIN; let costEur = kwh * totalEur; g_freshDp = { ts: blockStart, // block start, epoch UTC (also the idempotency key) sid: g_sessionId, kwh: r3(kwh), spot_c: r3(spot * 100), // c/kWh for the report transfer_c: r3(transfer * 100), tax_c: r3(CONFIG.TAX * 100), total_c: r3(totalEur * 100), cost: r4(costEur) // (€ or kr) }; postDp(g_freshDp, onFreshResult); } function onFreshResult(res, err, msg, ud) { if (!(err === 0 && res && res.code === 200)) { bufferPending(g_freshDp); // send failed -> buffer to KVS } g_busy = false; } // ------------------------------- SEND & BUFFER ------------------------------- function postDp(dp, cb) { let body = JSON.stringify({ secret: CONFIG.SECRET_KEY, // NOTE: secret in the body — Apps Script cannot see headers ts: dp.ts, sid: dp.sid, kwh: dp.kwh, spot_c: dp.spot_c, transfer_c: dp.transfer_c, tax_c: dp.tax_c, total_c: dp.total_c, cost: dp.cost }); // HTTP.POST uses the content_type parameter (default application/json), NOT a headers object. // Shelly follows the 302 redirect (Apps Script /exec -> googleusercontent) → res.code=200. Shelly.call("HTTP.POST", { url: CONFIG.APPS_SCRIPT_URL, body: body, timeout: 10, ssl_ca: "*", content_type: "application/json" }, cb, null); } function bufferPending(dp) { if (g_retryItems.length >= MAX_PENDING) { return; } // cap: don't exceed the KVS limit Shelly.call("KVS.Set", { key: "pending_" + dp.ts, value: JSON.stringify(dp) }, null, null); } // Start the 15 s poll loop LAST — mJS has no function hoisting, so every function // referenced here (tick and everything it calls) must already be declared above. print("EV-SPOT: started. AREA=" + CONFIG.AREA + ", threshold=" + CONFIG.POWER_THRESHOLD_W + "W"); Timer.set(15000, true, tick, null);

/** * EV charging SPOT-price tracker — Google Apps Script (bound to the template Sheet) * * Responsibilities: * doPost(e) — receives Shelly datapoints, validates the secret, writes to the "data" tab * (LockService + idempotency = no duplicate rows even if the Shelly retries) * setup() — builds the "data", "Report" and hidden "_calc" tabs with their formulas * onOpen() — the ⚡ EV-SPOT menu (setup / secret / test / PDF / help) * * Session grouping + month/year filtering is done with FORMULAS ONLY (QUERY/SUM) → always * live, no time-driven trigger, no race conditions. */ var SHEET_DATA = 'data'; var SHEET_REPORT = 'Report'; var SHEET_CALC = '_calc'; var DATA_HEADERS = ['Timestamp','Session_ID','Energy_kWh','SPOT_c_per_kWh', 'Transfer_c_per_kWh','Tax_c_per_kWh','Total_c_per_kWh','Cost']; // ============================================================================ // HTTP INTERFACE // ============================================================================ function doPost(e) { var lock = LockService.getScriptLock(); lock.waitLock(30000); // serialize concurrent POSTs try { var secret = PropertiesService.getScriptProperties().getProperty('SECRET_KEY'); var body = {}; if (e && e.postData && e.postData.contents) { body = JSON.parse(e.postData.contents); } else if (e && e.parameter) { body = e.parameter; // fallback: secret+values in the query string } if (!secret || String(body.secret) !== String(secret)) { return jsonOut({ ok: false, error: 'unauthorized' }); } var ss = SpreadsheetApp.getActiveSpreadsheet(); var sh = ss.getSheetByName(SHEET_DATA); if (!sh) { sh = buildDataSheet(ss); } var ts = Number(body.ts); if (!ts) { return jsonOut({ ok: false, error: 'missing ts' }); } // Idempotency: the same block (ts) only once, even if the response was lost and the Shelly retries. if (tsExists(sh, ts)) { return jsonOut({ ok: true, dup: true }); } sh.appendRow([ new Date(ts * 1000), // Timestamp as a real datetime value (Sheet tz) Number(body.sid), Number(body.kwh), Number(body.spot_c), Number(body.transfer_c), Number(body.tax_c), Number(body.total_c), Number(body.cost) ]); return jsonOut({ ok: true }); } catch (err) { return jsonOut({ ok: false, error: String(err) }); } finally { lock.releaseLock(); } } // Opening in a browser: confirms the deployment works. function doGet(e) { return jsonOut({ ok: true, service: 'ev-spot', hint: 'POST datapoints to this URL' }); } function jsonOut(obj) { return ContentService.createTextOutput(JSON.stringify(obj)) .setMimeType(ContentService.MimeType.JSON); } // Is ts already logged? Scan only the last ~400 rows (retries are recent). function tsExists(sh, ts) { var last = sh.getLastRow(); if (last < 2) { return false; } var start = Math.max(2, last - 400); var vals = sh.getRange(start, 1, last - start + 1, 1).getValues(); for (var i = 0; i < vals.length; i++) { var v = vals[i][0]; if (v instanceof Date && Math.round(v.getTime() / 1000) === ts) { return true; } } return false; } // ============================================================================ // MENU // ============================================================================ function onOpen() { SpreadsheetApp.getUi() .createMenu('⚡ EV-SPOT') .addItem('⚙️ Build / repair sheets', 'setup') .addItem('🔑 Set secret key', 'setSecret') .addSeparator() .addItem('🧪 Send test datapoint', 'sendTestPoint') .addSeparator() .addItem('❔ Setup guide', 'showHelp') .addToUi(); } function setSecret() { var ui = SpreadsheetApp.getUi(); var r = ui.prompt('Set secret key', 'Enter the shared SECRET_KEY (exactly the same as CONFIG.SECRET_KEY in the Shelly script):', ui.ButtonSet.OK_CANCEL); if (r.getSelectedButton() === ui.Button.OK) { var k = r.getResponseText(); if (k && k.length > 0) { PropertiesService.getScriptProperties().setProperty('SECRET_KEY', k); ui.alert('✅ Secret saved to Script Properties.'); } else { ui.alert('Empty secret was not saved.'); } } } function sendTestPoint() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sh = ss.getSheetByName(SHEET_DATA) || buildDataSheet(ss); var blockStart = Math.floor(Date.now() / 1000 / 900) * 900 - 900; // previous 15-min block if (tsExists(sh, blockStart)) { blockStart -= 900; } sh.appendRow([new Date(blockStart * 1000), blockStart, 2.500, 5.100, 2.550, 2.828, 10.478, 0.2620]); SpreadsheetApp.getUi().alert('🧪 Test datapoint added to the "data" tab. Check "Report".'); } function showHelp() { var html = '<div style="font-family:Arial;font-size:13px;line-height:1.5">' + '<h3>Setup (~2 min)</h3><ol>' + '<li>Run <b>⚡ EV-SPOT → ⚙️ Build / repair sheets</b>.</li>' + '<li><b>Deploy → New deployment → Web app.</b> Execute as: <i>Me</i>. Who has access: <i>Anyone</i>.</li>' + '<li>Approve the authorization prompt (Advanced → Go to project → Allow).</li>' + '<li>Copy the <b>Web app URL</b> → the Shelly script\'s <code>APPS_SCRIPT_URL</code>.</li>' + '<li><b>⚡ EV-SPOT → 🔑 Set secret key</b> (same as the Shelly <code>SECRET_KEY</code>).</li>' + '<li>Fill in the "Report" tab rows 1–4 (Metering point / Name / Address / Vehicle registration).</li>' + '<li>Test: <b>🧪 Send test datapoint</b> → a row appears on the "data" tab and "Report" updates.</li>' + '</ol><p>Pick the month/year from the "Report" tab dropdown. PDF: <b>File → Download → PDF</b>.</p></div>'; SpreadsheetApp.getUi().showSidebar( HtmlService.createHtmlOutput(html).setTitle('EV-SPOT — setup')); } // ============================================================================ // SHEET BUILDERS (setup) // ============================================================================ function setup() { var ss = SpreadsheetApp.getActiveSpreadsheet(); buildDataSheet(ss); buildCalcSheet(ss); buildReportSheet(ss); SpreadsheetApp.getUi().alert( '✅ Sheets built. Next: ' + '1) Deploy → New deployment → Web app ' + '2) 🔑 Set secret key ' + '3) Fill in Report tab rows 1–4'); } function buildDataSheet(ss) { var sh = ss.getSheetByName(SHEET_DATA) || ss.insertSheet(SHEET_DATA); sh.getRange(1, 1, 1, DATA_HEADERS.length).setValues([DATA_HEADERS]) .setFontWeight('bold').setBackground('#e8eaed'); sh.setFrozenRows(1); sh.getRange('A2:A').setNumberFormat('yyyy-mm-dd hh:mm'); sh.getRange('C2:C').setNumberFormat('0.000'); sh.getRange('D2:G').setNumberFormat('0.000'); sh.getRange('H2:H').setNumberFormat('0.0000'); sh.setColumnWidth(1, 130); return sh; } // Hidden intermediate calc: grouped QUERY whose WHERE filters by the Report!B6 selection. function buildCalcSheet(ss) { var sh = ss.getSheetByName(SHEET_CALC) || ss.insertSheet(SHEET_CALC); sh.clear(); // A1: sessions grouped by Session_ID, filtered by the selected YYYY-MM. // NOTE: QUERY month() is 0-based (Jan=0) → hence (month - 1). var q = '=IFERROR(QUERY(' + SHEET_DATA + '!A2:H,' + '"select B, min(A), max(A), sum(C), sum(H) where B is not null"' + '&IF(OR(' + SHEET_REPORT + '!$B$6="",' + SHEET_REPORT + '!$B$6="All"),"",' + '" and year(A)="&LEFT(' + SHEET_REPORT + '!$B$6,4)&" and month(A)="&(VALUE(MID(' + SHEET_REPORT + '!$B$6,6,2))-1))' + '&" group by B order by B desc",0),)'; // B (=Session_ID epoch) desc = newest session first sh.getRange('A1').setFormula(q); // Column G: dropdown source — "All" + the months present in the data (YYYY-MM). sh.getRange('G1').setValue('All'); sh.getRange('G2').setFormula( '=IFERROR(SORT(UNIQUE(FILTER(TEXT(' + SHEET_DATA + '!A2:A,"yyyy-mm"),' + SHEET_DATA + '!A2:A<>"")),1,FALSE),)'); sh.hideSheet(); return sh; } function buildReportSheet(ss) { var sh = ss.getSheetByName(SHEET_REPORT) || ss.insertSheet(SHEET_REPORT); // Move Report to the first visible position. ss.setActiveSheet(sh); ss.moveActiveSheet(1); // --- Header block, rows 1–4 (the user fills column B) ---------------------- sh.getRange('A1').setValue('Metering point').setFontWeight('bold'); sh.getRange('A2').setValue('Name').setFontWeight('bold'); sh.getRange('A3').setValue('Address').setFontWeight('bold'); sh.getRange('A4').setValue('Vehicle registration').setFontWeight('bold'); sh.getRange('B1:F1').merge(); sh.getRange('B2:F2').merge(); sh.getRange('B3:F3').merge(); sh.getRange('B4:F4').merge(); sh.getRange('B1:B4').setBackground('#fff8e1'); // --- Filter, row 6 --------------------------------------------------------- sh.getRange('A6').setValue('Period (month):').setFontWeight('bold'); // B6 MUST be plain text ("@"): otherwise Google auto-parses "2026-07" as a date, whose // value then no longer matches the text months in _calc!G (→ "violates data validation"), // and it would also break the QUERY month filter (LEFT/MID on B6). sh.getRange('B6').setNumberFormat('@'); var rule = SpreadsheetApp.newDataValidation() .requireValueInRange(ss.getSheetByName(SHEET_CALC).getRange('G1:G100'), true) .setAllowInvalid(true).build(); // show dropdown; never hard-block (warning at most) sh.getRange('B6').setDataValidation(rule).setValue('All').setBackground('#e3f2fd'); // --- Summary, row 8 -------------------------------------------------------- sh.getRange('A8').setValue('Total energy (kWh):').setFontWeight('bold'); sh.getRange('B8').setFormula('=IFERROR(SUM(D11:D),0)').setNumberFormat('0.000'); sh.getRange('D8').setValue('Total cost (€/kr):').setFontWeight('bold'); sh.getRange('E8').setFormula('=IFERROR(SUM(F11:F),0)').setNumberFormat('0.00'); // --- Table headers, row 10 ------------------------------------------------- var hdr = ['Start','End','Duration','Energy (kWh)','Average price (c/kWh)','Cost (€/kr)']; sh.getRange(10, 1, 1, hdr.length).setValues([hdr]) .setFontWeight('bold').setBackground('#e8eaed'); // --- Session rows from row 11 (ARRAYFORMULA over _calc) -------------------- // _calc: A=SID, B=min(A)=start, C=max(A)=last block's start, D=sum energy, E=sum cost. var C = "'" + SHEET_CALC + "'"; sh.getRange('A11').setFormula('=ARRAYFORMULA(IF(' + C + '!A1:A="","",' + C + '!B1:B))'); // Start sh.getRange('B11').setFormula('=ARRAYFORMULA(IF(' + C + '!A1:A="","",' + C + '!C1:C+(15/1440)))'); // End = last block's start + 15 min sh.getRange('C11').setFormula('=ARRAYFORMULA(IF(' + C + '!A1:A="","",(' + C + '!C1:C-' + C + '!B1:B)+(15/1440)))'); // Duration sh.getRange('D11').setFormula('=ARRAYFORMULA(IF(' + C + '!A1:A="","",' + C + '!D1:D))'); // Energy sh.getRange('E11').setFormula('=ARRAYFORMULA(IF(' + C + '!A1:A="","",IF(' + C + '!D1:D=0,0,' + C + '!E1:E/' + C + '!D1:D*100)))'); // Average price c/kWh sh.getRange('F11').setFormula('=ARRAYFORMULA(IF(' + C + '!A1:A="","",' + C + '!E1:E))'); // Cost // --- Formats --------------------------------------------------------------- sh.getRange('A11:A').setNumberFormat('yyyy-mm-dd hh:mm'); sh.getRange('B11:B').setNumberFormat('yyyy-mm-dd hh:mm'); sh.getRange('C11:C').setNumberFormat('[h]:mm'); sh.getRange('D11:D').setNumberFormat('0.000'); sh.getRange('E11:E').setNumberFormat('0.00'); sh.getRange('F11:F').setNumberFormat('0.00'); sh.setColumnWidth(1, 130); sh.setColumnWidth(2, 130); sh.setColumnWidth(3, 70); sh.setColumnWidth(4, 100); sh.setColumnWidth(5, 150); sh.setColumnWidth(6, 120); sh.setFrozenRows(10); return sh; }