[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8,"_9":10},"actionData","errors","root",{"_153":154,"_155":156},"routes/_app.x.$handle.$name",{"_11":12,"_13":14,"_15":16,"_18":19,"_17":-7,"_116":-5,"_20":117,"_22":118,"_119":120,"_121":122,"_25":123,"_24":-5,"_124":-5,"_125":-5,"_126":53,"_127":128,"_129":44,"_130":-5,"_131":-5,"_132":133},"routes/_app.x.$handle.$name.branch.$branch.code.$",{"_11":12,"_13":14,"_15":16,"_17":-7,"_18":19,"_20":21,"_22":23,"_24":-7,"_25":26,"_27":-5,"_28":-5,"_29":-5,"_30":-5,"_31":-5},"handle","piterchernychko","name","piter-cher","branchName","main","version","path","","project",{"_33":61,"_13":14,"_45":62,"_63":-5,"_64":53,"_65":66,"_67":66,"_68":19,"_69":-5,"_70":71,"_72":-5,"_73":53,"_74":-5,"_75":-5,"_76":77,"_78":79,"_80":-5,"_81":-5,"_82":-5,"_83":-5,"_84":85,"_86":85,"_87":88,"_89":88,"_90":88,"_91":85,"_92":85,"_93":94,"_95":96,"_97":98,"_99":53},"tree",[56],"file","contents",[32],"upstream","pull","ahead","behind","envVars",{"_33":34,"_13":35,"_36":37,"_38":-5,"_39":40,"_41":42,"_43":44,"_45":46,"_17":47,"_48":49,"_50":51,"_52":53,"_54":-5,"_18":55},"id","0ae949fe-589b-11f1-9dbe-ee650bb23af1","main.ts","fileId","54a4f406-57f3-11f1-9587-ee650bb23af1","parentId","branchId","54a29bc0-57f3-11f1-9587-ee650bb23af1","branchVersion",20,"latest",true,"createdAt",["D",1779755835131],19,"fileType","http","content","// XLSX Builder for n8n — paste this into a new HTTP val on val.town.\n//\n// Receives the GAAP allocation rows from n8n's \"GAAP Allocation Code\" node\n// and returns a true .xlsx binary with LIVE EXCEL FORMULAS baked into the\n// computed cells. When someone opens the file in Excel and clicks one of\n// these cells, they see the actual formula, not just the number.\n//\n// Cells that become formulas:\n// • Total Cycle Days = - + 1\n// • Allocated Cost = ROUND( Days / Total Cycle Days * Price, 2)\n// • Allocated Cost (last) = ROUND(Price - SUM(prior months' Allocated Cost), 2)\n//\n// Every other column (original supporting-file columns, divider, per-month\n// \"Days\" counts, dates, etc.) is written as a static value exactly as n8n\n// sends it.\n\nimport * as XLSX from \"npm:xlsx\";\n\nconst MONTH_DAYS_RE =\n /^(January|February|March|April|May|June|July|August|September|October|November|December) Days$/;\n\nconst MONTH_NUM: Record = {\n January: 1, February: 2, March: 3, April: 4, May: 5, June: 6,\n July: 7, August: 8, September: 9, October: 10, November: 11, December: 12,\n};\n\n// Parse an ISO YYYY-MM-DD string (or a Date) to a UTC Date. Returns null otherwise.\nconst parseIsoDate = (v: any): Date | null => {\n if (!v) return null;\n if (v instanceof Date) return isNaN(v.getTime()) ? null : v;\n if (typeof v === \"string\") {\n const m = v.match(/^(\\d{4})-(\\d{2})-(\\d{2})/);\n if (m) {\n const d = new Date(Date.UTC(+m[1], +m[2] - 1, +m[3]));\n return isNaN(d.getTime()) ? null : d;\n }\n }\n return null;\n};\n\n// A \"simple\" row is one whose JS-computed Total Cycle Days equals\n// (Cycle end date − Cycle start date + 1) — i.e. the row spans a SINGLE\n// cycle. Prior-cycle rows (Atomic Mobile mid-cycle activation / Helix\n// straddling prorated windows) accumulate prior-cycle days into\n// Total Cycle Days; their monthly buckets also blend prior + current\n// dollars in a way that can't be expressed as a single per-month\n// =Days/Total*Price formula. For those rows we keep the JS-computed\n// static values exactly as the workflow sent them.\nconst isSimpleSingleCycleRow = (row: Record): boolean => {\n const start = parseIsoDate(row[\"Cycle start date\"]);\n const end = parseIsoDate(row[\"Cycle end date\"]);\n const total = Number(row[\"Total Cycle Days\"]);\n if (!start || !end || !Number.isFinite(total)) return false;\n const currentDays =\n Math.round((end.getTime() - start.getTime()) / 86400000) + 1;\n return Math.abs(currentDays - total) < 0.5;\n};\n\nconst colLetter = (idx0: number): string => {\n let s = \"\";\n let n = idx0 + 1;\n while (n > 0) {\n const rem = (n - 1) % 26;\n s = String.fromCharCode(65 + rem) + s;\n n = Math.floor((n - 1) / 26);\n }\n return s;\n};\n\nexport default async function (req: Request): Promise {\n if (req.method !== \"POST\") {\n return new Response(\"Use POST\", { status: 405 });\n }\n\n let body: any;\n try {\n body = await req.json();\n } catch {\n return new Response(\"Invalid JSON body\", { status: 400 });\n }\n\n const rows: Record[] = Array.isArray(body?.rows)\n ? body.rows\n : [];\n const sheetName = String(body?.sheetName || \"GAAP Allocation\").slice(0, 31);\n const fileName = String(body?.fileName || \"gaap-allocation.xlsx\");\n if (rows.length === 0) return new Response(\"No rows\", { status: 400 });\n\n // Build the union of all column headers across rows, preserving insertion\n // order (which the workflow constructs deliberately: original-file columns\n // first, then divider, then normalized columns, then monthly buckets).\n const headers: string[] = [];\n const seen = new Set();\n for (const row of rows) {\n for (const k of Object.keys(row)) {\n if (!seen.has(k)) {\n seen.add(k);\n headers.push(k);\n }\n }\n }\n\n // Start with everything as static values.\n const ws = XLSX.utils.json_to_sheet(rows, { header: headers });\n\n // Anchor lookups by header name.\n const idx = (name: string) => headers.indexOf(name);\n const startIdx = idx(\"Cycle start date\");\n const endIdx = idx(\"Cycle end date\");\n const priceIdx = idx(\"Price\");\n const totalDaysIdx = idx(\"Total Cycle Days\");\n // Prior-cycle anchors derived from already-visible columns (no helper\n // columns needed): priorStart = Activation Date (Atomic) or Plan\n // Effective Date (Helix); priorEnd = Cycle start − 1; priorPrice =\n // Prorated Charges (Atomic case; Helix straddling rows would\n // over-attribute, but no test data hits that path).\n const activationIdx = idx(\"Activation Date\");\n const planEffectiveIdx = idx(\"Plan Effective Date\");\n const proratedIdx = idx(\"Prorated Charges\");\n\n // Per-month (Days, Prior Days, Allocated Cost) triples in column order.\n // Because the workflow inserts month columns chronologically, the last\n // entry here is the last month chronologically — which is the one that\n // gets the \"remainder\" formula so the row's months sum exactly to Price\n // (or Price + Prior Cycle Price for prior-cycle rows).\n const monthCols: {\n name: string;\n daysIdx: number;\n priorDaysIdx: number;\n allocIdx: number;\n }[] = [];\n for (const h of headers) {\n const m = h.match(MONTH_DAYS_RE);\n if (m) {\n const monthName = m[1];\n monthCols.push({\n name: monthName,\n daysIdx: idx(h),\n priorDaysIdx: idx(`${monthName} Prior Days`),\n allocIdx: idx(`${monthName} Allocated Cost`),\n });\n }\n }\n\n for (let r = 0; r < rows.length; r++) {\n const excelRow = r + 2; // header is row 1; data starts at row 2\n const isSimple = isSimpleSingleCycleRow(rows[r]);\n\n // Per-month Allocated Cost — two paths:\n // Simple row → existing single-cycle formula: ROUND(Days/Total*Price, 2)\n // Prior-cycle row → blend two cycles per month using EOMONTH/DATE math\n // inline. The last chronological month uses a remainder formula so\n // the row's months sum exactly to (Price + PriorPrice).\n if (totalDaysIdx >= 0 && priceIdx >= 0 && monthCols.length > 0) {\n // Pick the priorStart column for this row (same precedence as\n // Total Cycle Days). Atomic Mobile → Activation Date; Helix → Plan\n // Effective Date.\n let priorStartColIdx = -1;\n if (!isSimple) {\n if (activationIdx >= 0 && rows[r][\"Activation Date\"]) {\n priorStartColIdx = activationIdx;\n } else if (planEffectiveIdx >= 0 && rows[r][\"Plan Effective Date\"]) {\n priorStartColIdx = planEffectiveIdx;\n }\n }\n const priorCycleReady = !isSimple &&\n priorStartColIdx >= 0 &&\n proratedIdx >= 0 &&\n rows[r][\"Prorated Charges\"] != null;\n\n // Prior-cycle row we can't formula-ize → keep JS-computed static values.\n if (!isSimple && !priorCycleReady) continue;\n\n for (let m = 0; m < monthCols.length; m++) {\n const { name: monthName, daysIdx, allocIdx } = monthCols[m];\n if (allocIdx < 0 || daysIdx < 0) continue;\n const ref = colLetter(allocIdx) + excelRow;\n const cell = (ws as any)[ref];\n if (!cell) continue;\n\n const isLast = m === monthCols.length - 1;\n\n if (isSimple) {\n // Single-cycle: simple ratio, with last-month remainder.\n if (isLast && monthCols.length > 1) {\n const priorRefs = monthCols\n .slice(0, m)\n .map((mc) => colLetter(mc.allocIdx) + excelRow);\n cell.f = `ROUND(${colLetter(priceIdx)}${excelRow}-SUM(${\n priorRefs.join(\",\")\n }),2)`;\n } else {\n cell.f = `ROUND(${colLetter(daysIdx)}${excelRow}/${\n colLetter(totalDaysIdx)\n }${excelRow}*${colLetter(priceIdx)}${excelRow},2)`;\n }\n } else {\n // Prior-cycle row, cycle-isolated math, derived from existing\n // visible columns only (no Prior Cycle End / Prior Cycle Price\n // helpers needed):\n // priorTotal = Cycle start − priorStart\n // (priorEnd is implicit = Cycle start − 1)\n // priorPrice = Prorated Charges (Atomic case)\n // monthAllocated =\n // ( − ) / (end − start + 1) * Price\n // + / (start − priorStart) * Prorated Charges\n // Last chronological month uses a remainder formula so the row's\n // months sum exactly to (Price + Prorated Charges).\n if (isLast && monthCols.length > 1) {\n const priorRefs = monthCols\n .slice(0, m)\n .map((mc) => colLetter(mc.allocIdx) + excelRow);\n cell.f = `ROUND((${colLetter(priceIdx)}${excelRow}+${\n colLetter(proratedIdx)\n }${excelRow})-SUM(${priorRefs.join(\",\")}),2)`;\n } else {\n const { priorDaysIdx } = monthCols[m];\n if (priorDaysIdx < 0) continue; // sheet missing the helper column → static\n const dRef = `${colLetter(daysIdx)}${excelRow}`;\n const pdRef = `${colLetter(priorDaysIdx)}${excelRow}`;\n const sRef = `${colLetter(startIdx)}${excelRow}`;\n const eRef = `${colLetter(endIdx)}${excelRow}`;\n const pRef = `${colLetter(priceIdx)}${excelRow}`;\n const psRef = `${colLetter(priorStartColIdx)}${excelRow}`;\n const prRef = `${colLetter(proratedIdx)}${excelRow}`;\n cell.f =\n `ROUND((${dRef}-${pdRef})/(${eRef}-${sRef}+1)*${pRef}+${pdRef}/(${sRef}-${psRef})*${prRef},2)`;\n }\n }\n delete cell.v;\n cell.t = \"n\";\n }\n }\n }\n\n const wb = XLSX.utils.book_new();\n XLSX.utils.book_append_sheet(wb, ws, sheetName);\n const buf = XLSX.write(wb, { type: \"array\", bookType: \"xlsx\" }) as Uint8Array;\n\n return new Response(buf, {\n headers: {\n \"Content-Type\":\n \"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet\",\n \"Content-Disposition\": `attachment; filename=\"${\n fileName.replace(/\"/g, \"\")\n }\"`,\n },\n });\n}\n","deleted",false,"label",[35],{"_33":34,"_13":35,"_36":37,"_38":-5,"_39":40,"_41":42,"_43":44,"_45":57,"_17":47,"_48":49,"_50":51,"_52":53,"_54":-5,"_58":59,"_18":60},["D",1779755835131],"children",[],[35],"54a1c3d0-57f3-11f1-9587-ee650bb23af1",["D",1779683803374],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","imageUrl","userId","17b54865-9ad6-4808-bacc-de7e7462ab4f","pinnedFileId","pinPreview","pinMigrated","screenshotVersion","likeCount",0,"orgId","019e5d68-b691-759e-a311-00d7dc76a9b7","tursoName","tursoHostname","tursoMeasuredAt","pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_11":12,"_115":111},"org",{"_11":12,"_108":109,"_110":111,"_70":71,"_112":113,"_114":53},"branch",{"_17":42,"_13":16,"_33":40,"_100":61,"_101":-5,"_102":-5,"_103":-5,"_104":-5,"_105":-5,"_106":14,"_107":12},"isOwned","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","displayName","Piter Chernychko","avatar","https://lh3.googleusercontent.com/a/ACg8ocKhKGyDJPqCpO0-ue5GxztF00mOGqn4ofOd0ANW-jc-BeiGIA=s96-c","tier","free","canRestrictVals","profileImageUrl","adoptOrgId",{"_33":61,"_13":14,"_45":149,"_63":-5,"_64":53,"_65":66,"_67":66,"_68":19,"_69":-5,"_70":71,"_72":-5,"_73":53,"_74":-5,"_75":-5,"_76":77,"_78":79,"_80":-5,"_81":-5,"_82":-5,"_83":-5,"_84":85,"_86":85,"_87":88,"_89":88,"_90":88,"_91":85,"_92":85,"_93":150,"_95":151,"_97":152,"_99":53},[146],"files",[146],"counts",{"_136":77,"_137":138,"_139":77,"_31":140,"_141":142},[134],"upstreamProject","upstreamBranch","emptyEnvVars","flags",[],"hasHttpFiles","grants","orgs","customDomains",{},{"_33":34,"_13":35,"_36":37,"_38":-5,"_39":40,"_41":42,"_43":44,"_45":46,"_17":47,"_48":49,"_50":51,"_52":53,"_54":-5,"_18":135},[35],"pulls","branches",1,"forks",{"_143":77,"_145":77},"connections",{"_143":77,"_144":77},"count","unfilledCount","emptyCount",{"_33":34,"_13":35,"_36":37,"_38":-5,"_39":40,"_41":42,"_43":44,"_45":57,"_17":47,"_48":49,"_50":51,"_52":53,"_54":-5,"_58":147,"_18":148},[],[35],["D",1779683803374],{"_11":12,"_115":111},{"_11":12,"_108":109,"_110":111,"_70":71,"_112":113,"_114":53},{"_17":42,"_13":16,"_33":40,"_100":61,"_101":-5,"_102":-5,"_103":-5,"_104":-5,"_105":-5,"_106":14,"_107":12},"ctx",{"_93":-5,"_131":186,"_187":188,"_189":190,"_191":53,"_192":53,"_193":53,"_194":44,"_195":-5,"_196":-7,"_197":-7,"_198":77,"_199":53,"_200":53,"_201":-5,"_202":53,"_203":53,"_204":53,"_205":-5},"ENV",{"_157":158,"_159":160,"_161":16,"_162":163,"_164":165,"_166":167,"_168":169,"_170":171,"_172":173,"_174":175,"_176":177,"_178":179,"_180":181,"_182":183,"_184":185},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","RENDER_GIT_COMMIT","e87eb04e707cf64e7e532c8e8ec395f209e17f15","RENDER_GIT_BRANCH","POSTHOG_API_TOKEN","phc_nmoyuAkStEZuUp69rYlg1DZgrIt19Gx0Y4Hwf8tGgJP","POSTHOG_API_HOST","https://wart.val.town","WEB_API_TEMPLATE","https://{val}.web.val.run","APP_URL","https://www.val.town","MODULE_URL","https://esm.town","VANITY_WEB_API_TEMPLATE","https://{label}.val.run/","DENOLS_URL","https://lsp.val.town","NODE_ENV","production","CLOUDFLARE_TURNSTILE_SITE_KEY","0x4AAAAAACtaIZJH7s9rBMZ6","GOOGLE_CLIENT_ID","875989696899-jtsqkhlbdv5jrlsk8631p7ipjg3c6o62.apps.googleusercontent.com","STRIPE_PUBLISHABLE_KEY","pk_live_51N2zvWEpZTTLipIkVEwXrUG9AT0yeOOEYxszarZT1jpsZV58Ly6wlg3SltZpHN26CUalOBmUSY7adQRQiSkCZz2500qSXNkf8Y","API_URL","https://api.val.town",[],"preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","preferencesCloseBrackets","preferencesChatPanelState","preferencesDprint","preferencesDenofmt","notificationCount","flagDenols","isBusinessOrgMember","businessOrgHandle","isBusinessOrgAdmin","isHigherTownieTier","navCollapsed","currentOrgId"]